Solved

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

Posted on 2014-01-27
6
259 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
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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 142

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

18 Experts available now in Live!

Get 1:1 Help Now