Query tuning

  • 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

  • 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.

  • 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/

  • 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