Post reply

Larger Date Range taking MUCH less time

  • March 11, 2013 at 4:32 am

    #275143

    Hi

    I'm executing the below TSQL on a table with about 700,000 records. I am clearing the caching so it does not affect the queries.

    When the data range is between '2013-01-01' and '2013-01-31' (or even more), it takes less than one second to return the data.

    But, when the date range is between '2013-01-09' and '2013-01-31' it takes ages (4 min)

    Can anyone give me an explanation why this is happening.

    Thanks

    JP

    DBCC DROPCLEANBUFFERS

    go

    DBCC FREEPROCCACHE

    go

    Select STLStkCode

    From STranLine

    Where STranLine.STLTranDate Between '2013-01-09' and '2013-01-31'

  • March 11, 2013 at 5:00 am

    #1595452

    This perhaps

    http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

    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

  • March 11, 2013 at 5:02 am

    #1595454

    JP-1019544 (3/11/2013)

    Hi

    I'm executing the below TSQL on a table with about 700,000 records. I am clearing the caching so it does not affect the queries.

    When the data range is between '2013-01-01' and '2013-01-31' (or even more), it takes less than one second to return the data.

    But, when the date range is between '2013-01-09' and '2013-01-31' it takes ages (4 min)

    Can anyone give me an explanation why this is happening.

    Thanks

    JP

    DBCC DROPCLEANBUFFERS

    go

    DBCC FREEPROCCACHE

    go

    Select STLStkCode

    From STranLine

    Where STranLine.STLTranDate Between '2013-01-09' and '2013-01-31'

    execution plan can give you clear picture what/why is something happening.

    following might be reasons why you are getting faster results in your first filters.

    1) There could be less data in that range.

    2) time taken to pull the data from disk to cache is unnoticable.

    3 ) better statistics in case of that data range.

    but i still vote the first reason.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • March 11, 2013 at 5:23 am

    #1595469

    Bhuvnesh (3/11/2013)

    1) There could be less data in that range.

    ...

    but i still vote the first reason.

    How could there be less data between '2013-01-01' and '2013-01-31' than there is between '2013-01-09' and '2013-01-31' when the second range is a subset of the first?

    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

  • March 11, 2013 at 5:27 am

    #1595473

    GilaMonster (3/11/2013)

    Bhuvnesh (3/11/2013)

    1) There could be less data in that range.

    ...

    but i still vote the first reason.

    How could there be less data between '2013-01-01' and '2013-01-31' than there is between '2013-01-09' and '2013-01-31' when the second range is a subset of the first?

    ooh my bad ....i overlooked the range

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • March 11, 2013 at 5:49 am

    #1595485

    There is definitely more data as it is a subset, but it takes MUCH more time to pull the Subset data.

  • March 11, 2013 at 5:52 am

    #1595488

    JP-1019544 (3/11/2013)

    There is definitely more data as it is a subset, but it takes MUCH more time to pull the Subset data.

    Did you check out the blog post I referenced?

    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

  • March 11, 2013 at 6:12 am

    #1595502

    Yep, was looking into it.

    Think it SOLVED my problem. Will continue checking and will confirm later on, but speed definitely improved drastically.

    Thanks Again

    JP

  • March 12, 2013 at 12:06 am

    #1595821

    JP-1019544 (3/11/2013)

    Think it SOLVED my problem.

    How ? what changes you made ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • March 12, 2013 at 12:25 am

    #1595825

    I fixed my problem by updating the statistics of the table indexes (as stated by 'GilaMonster').

    Thanks to ALL

    JP

Viewing 10 posts - 1 through 9 (of 9 total)

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