Solved

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

Posted on 2014-01-27
6
265 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 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