Query tuning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Query tuning

Query tuning

Rate Topic

Display Mode

Topic Options

Author

Message

m.praki

Posted Wednesday, January 30, 2013 5:12 PM

Forum 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

Lynn Pettis

Posted Wednesday, January 30, 2013 6:52 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

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.

Cool

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

Sean Lange

Posted Thursday, January 31, 2013 8:31 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204

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

Post #1414172

mmartin1

Posted Wednesday, April 17, 2013 10:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:43 PM
Points: 192, Visits: 640

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 »

Permissions