Solved

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

Posted on 2014-01-27
6
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 100 total points
ID: 39813208
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 39813218
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
ID: 39813514
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
ID: 39814444
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
ID: 39842164
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 39842296
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

615 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