Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query tuning Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 5:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 5:11 PM
Points: 1, Visits: 16
Hi,

I had a performance issue in our stored procedure. Later I found out that the below simple query took 36 secs.

SELECT EMP_NO
FROM EMP
WHERE EMP_STATUS !='01'
AND EMP_NO NOT IN (
SELECT EMP_NO
FROM EMP
WHERE EMP_STAT NOT IN ('01','02')
AND [YEAR] > 2009
)
AND [YEAR] < 2009

Then,I have fixed the performance issue by loading the subquery results into a temp table and used the temp table results in place of subquery. Please see below. Now it just took 4 secs to process but really I don't know what was the issue in the above query and how it got fixed once I've loaded into a temp table. Can someone please explain why the above query took 36 secs and the below one just took 4 secs ?

INSERT INTO #TEMP
SELECT EMP_NO
FROM EMP
WHERE EMP_STAT NOT IN ('01','02')
AND [YEAR] > 2009
GO

SELECT EMP_NO
FROM EMP
WHERE EMP_STATUS !='01'
AND EMP_NO NOT IN (
SELECT EMP_NO
FROM #TEMP
)
AND [YEAR] < 2009
Post #1413800
Posted Wednesday, January 30, 2013 6:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:13 PM
Points: 22,529, Visits: 30,303
A few things would help. First, the actual execution plan for both queries. Second, what is the query attempting to accomplish? If you could post the DDL (CREATE TABLE statement) for the table, some sample data for the table that is representative of the question being asked, and expected resutls based on the sample data I'm sure we could explain what is going on. Just from your queries, I am getting a little confused.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1413814
Posted Thursday, January 31, 2013 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
You have so much negative logic in here it is really hard to figure out what you are doing. All that negative logic is going to make performance of this more difficult.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414172
Posted Wednesday, April 17, 2013 10:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 225, Visits: 700
Also be sure your column emp_status does not contain nulls or else a
emp_status !='01'


will not return the nulled columns. I don't know if this is the intended behavior.
Post #1443381
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse