Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-27
6
Medium Priority
?
272 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 70

Accepted Solution

by:
Scott Pletcher earned 400 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 800 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 400 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 400 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 800 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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