January 30, 2013 at 5:12 pm
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
January 30, 2013 at 6:52 pm
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.
January 31, 2013 at 8:31 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2013 at 10:45 am
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.
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply