Query optimization help

  • I have one simple query to optimize but somehow after creating couple of index this query is still having more logical reads and time.

    DECLARE @CURRENT smalldatetime

    set @current = '6/30/2013'

    select Key, MAX(dateended) DateEnded

    from InternalEstimate

    where dateended< @Current and ms = 1 and Upd<2

    group by Key

    I have created index as

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

    ON [dbo].[OperateEstimate] ([Ms],[DateEnded],[Upd])

    INCLUDE ([Key])

    GO

    But still logical reads are about 66,000 and taking 7-8 second to execute.

    Any help would be greatly appreciated.

    Need urgently.

  • Table definitions, index definitions and execution plan please.

    How many rows qualify for that where clause predicate?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have attached execution plan.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'OperatorCBGEstimate'. Scan count 3, logical reads 66974, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Total rows affected by where clause: 1099.

    Please let me know if more information is required.

  • Table definitions and index definitions please.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Please find the attached excel sheet for more information.

  • Your query filters out a very small number of rows from the table - you may as well scan them all from an index ordered by , which should give you a fast streaming aggregate instead of a slower hash aggregate. Something like this should do the trick:

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

    ON [dbo].[OperateEstimate] ([Key])

    INCLUDE ([Ms],[DateEnded],[Upd])

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Does not make any difference.

    Getting same result.

  • ChrisM@Work (4/15/2014)


    Your query filters out a very small number of rows from the table - you may as well scan them all from an index ordered by , which should give you a fast streaming aggregate instead of a slower hash aggregate. Something like this should do the trick:

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

    ON [dbo].[OperateEstimate] ([Key])

    INCLUDE ([Ms],[DateEnded],[Upd])

    This might be slightly better

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

    ON [dbo].[OperateEstimate] ([Ms],[Key])

    INCLUDE ([DateEnded],[Upd])

    Filter on the equality and still have the resulting rows in order for the stream aggregate (which adding the other columns as index keys will prevent)

    Worth trying

    How many rows are in the table?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

  • EasyBoy (4/15/2014)


    I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

    Did you look at the execution plans to see if the hash aggregate was replaced with a stream aggregate? If you're not sure, then create the index which Gail suggests and post the execution plan here.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • EasyBoy (4/15/2014)


    I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

    You're retrieving 14+ million rows from one index or another on a table containing 15+ million rows. Whichever of these alternative indexes is used, they all contain the same columns so the count of logical reads isn't likely to differ much between them, and is about as low as it can get. That doesn't mean the query cannot be made to run faster - it means you have to look elsewhere in the query plan.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (4/16/2014)


    EasyBoy (4/15/2014)


    I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

    Did you look at the execution plans to see if the hash aggregate was replaced with a stream aggregate? If you're not sure, then create the index which Gail suggests and post the execution plan here.

    Please find the updated execution plan.

  • How many rows total in the table?

    You told me earlier that there were 1099 rows which qualified for the where clause, but the plan shows 14 million rows being returned from the filtered index seek. That's a large difference.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Table contains total 14208765 rows but query itself fetching total 1099 rows.

  • The query is reading just over 14 million rows and aggregating that down to 1099. If it's reading 14 million rows from a table (the rows which satisfy the where clause), the reads you're seeing are pretty much expected (you're reading the majority of the table) and hence you may not be able to reduce it much more.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply