sql executive time question

  • This is my sql statement:

    SELECTSCSLA.SaleCycleStateId

    FROMdbo.SaleCSLeadAssign AS SCSLA WITH (NOLOCK)

    INNER JOIN

    (

    SELECT SCS1.SaleCycleStateID

    FROM dbo.SaleCycleState SCS1 WITH (NOLOCK, INDEX(IxN_SaleCycleState_SaleCycleId_SaleCycleStatusId))

    WHERE scs1.salecycleid = scs1.salecycleid and SCS1.SaleCSRecordStatusCode = 1

    AND SCS1.SaleCycleStatusId = 109

    ) AS SCS

    ON SCSLA.SaleCycleStateId = SCS.SaleCycleStateId

    WHERESCSLA.AssignedToOrgId = 62 AND

    SCSLA.FollowupDate BETWEEN 'Jan 1 1900 12:00AM' and 'Jun 26 2010 5:18PM'

    This is the execution plan:

    |--Parallelism(Gather Streams)

    |--Hash Match(Inner Join, HASH:([SCSLA].[SaleCycleStateId])=([SCS1].[SaleCycleStateId]))

    |--Bitmap(HASH:([SCSLA].[SaleCycleStateId]), DEFINE:([Bitmap1002]))

    | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([SCSLA].[SaleCycleStateId]))

    | |--Index Seek(OBJECT:([SmartOpsProd410].[dbo].[SaleCSLeadAssign].[IxN_SaleCSLeadAssign_AssignedToOrgId_FollowUpDate] AS [SCSLA]), SEEK:([SCSLA].[AssignedToOrgId]=62 AND [SCSLA].[FollowUpDate] >= 'Jan 1 1900 12:00AM' AND [SCSLA].[Foll

    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([SCS1].[SaleCycleStateId]), WHERE:(PROBE([Bitmap1002])=TRUE))

    |--Index Scan(OBJECT:([SmartOpsProd410].[dbo].[SaleCycleState].[IxN_SaleCycleState_SaleCycleId_SaleCycleStatusId] AS [SCS1]), WHERE:(([SCS1].[SaleCSRecordStatusCode]=1 AND [SCS1].[SaleCycleStatusId]=109) AND [SCS1].[SaleCycleId]=[SCS1].[S

    I run it three times:

    ROWNUMBER CPU Reads Duration

    ----------- ----------- -------------------- --------------------

    12 6952 480033 4326

    15 7533 479987 2190

    18 7436 479798 2046

    I expect the 2nd and 3rd time will be very fast

    But you can see the CPU, Read IO and duration is almost the same

    Can anyone explain why and how to solve it

    Thanks

  • What do you mean "solve it"?

    SQL still has to run the query, read through the data, and process the results. They could change. The time savings usually comes from things being cached in memory (raw data), not the query results.

  • I don't know your database, but I'm curious about the inline SQL. Since you are not summarizing anything and appear to just be limiting records, have you tried rewriting the SQL to: (Also, have you tried without the index hint? SQL may have a better index)

    SELECT SCSLA.SaleCycleStateId

    FROM dbo.SaleCSLeadAssign AS SCSLA WITH (NOLOCK)

    INNER JOIN dbo.SaleCycleState SCS1 WITH (NOLOCK INDEX(IxN_SaleCycleState_SaleCycleId_SaleCycleStatusId))

    ON SCSLA.SaleCycleSateID = SCS1.SaleCycleStateID

    and scs1.salecycleid = scs1.salecycleid

    and SCS1.SaleCSRecordStatusCode = 1

    AND SCS1.SaleCycleStatusId = 109

    WHERE SCSLA.AssignedToOrgId = 62 AND

    SCSLA.FollowupDate BETWEEN 'Jan 1 1900 12:00AM' and 'Jun 26 2010 5:18PM'

    It might help. It might not.

    good luck with it though.

    If the phone doesn't ring...It's me.

  • Ow! Why are you using Nolock? Is potentially incorrect data acceptable?

    Why are you forcing an index? Are you absolutely 100% sure that you know better than the query optimiser?

    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
  • Reads are just logical reads, either from buffer cache or physical.

    It still takes the same number of reads and cpu cycles to run the query, but on the 2nd and 3rd executions, the data was in ram so it didn't have to go to disk to get it, therefore it takes less time.

    Optimizing this query is a whole different ballpark though...

Viewing 5 posts - 1 through 4 (of 4 total)

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