Solved

SQL query extremely slower, then extremely faster, with bogus column

Posted on 2014-01-27
6
253 Views
Last Modified: 2014-02-20
I know without having the DB to look at, this may not be possible to answer, but here's hoping someone can point me in a general direction. We have a simple query run against a SQL Server 2008 DB...

SELECT DISTINCT
CASE when ([vglAccountGroups].[AccountGroupID] in ('A', 'B', 'C')) THEN 1 ELSE 0 END AS Selected,
vglAccountGroups.AccountGroupID
From...

The rest of the query has some joins and a where clause. On average, vglAccountGroups has 100 to 3000 rows, depending on the DB. As you can see, the query returns only 2 columns, with the first being 1 or 0 based on the value of the second column. (The query is generated dynamically, so the values change.)

It runs in 1-3 seconds. Then we had a DB with 12,000 rows in vglAccountGroups. At that point, it jumped up to over 10 minutes, after which we kill it. This baffles us. We then added this bogus column at the beginning (the underline part below)...

SELECT DISTINCT
0 as BOGUS,
CASE when ([vglAccountGroups].[AccountGroupID] in ('A', 'B', 'C')) THEN 1 ELSE 0 END Selected,
vglAccountGroups.AccountGroupID
From...

And it again worked in 1-3 seconds. Any thoughts on what's going on? We're stunned that the result is so extreme in both cases.
0
Comment
Question by:jjsather
6 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 100 total points
Comment Utility
It must be something in the joins.

You could, though, reduce the overhead in SQL by not generating the computed column until after the DISTINCT has been processed by adding an outer query and making the original query an inner/sub query:


SELECT
CASE when ([AccountGroupID] in ('A', 'B', 'C')) THEN 1 ELSE 0 END AS Selected,
[AccountGroupID]
FROM (
    SELECT DISTINCT
    vglAccountGroups.AccountGroupID
    From...rest_of_original_query
) AS original_query
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
this is very likely a parameter sniffing issue. see this article for explanation and solutions:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1258-Speedier-Execution-of-Stored-Procedures-in-SQL-Server.html
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 100 total points
Comment Utility
or this can be also due to locks placed on that table at that point of time... did you check that no one is updating or inserting or deleting data from the tables underlying in your joins...
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
Comment Utility
Hi,

One thought is that maybe the statistics are going stale enough to not generate a good plan, but not stale enough to trigger the automatic update.

If problem reoccurs, I'd be interested in what updating the stats on just that column achieves.

Regards
  David
0
 

Author Comment

by:jjsather
Comment Utility
Sorry for the delay... family stuff came up.

Thanks for the suggestions. We tried the outer/inner query but it made no difference, which surprised me. And this is not a proc, so param sniffing didn't matter here. And there are no locks -- just ran it on my machine by itself.

The problem still exists, so we may just add the generated "0 as BOGUS" field, but that seems really hokey. Quite puzzling. My guess is the execution plan must change as a result, but still stunned by it.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
Does the explain plan  show parallel query execution ?
If yes please try
option (maxdop 1)
Added to the end of the query.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now