none
Can I make this query go any faster?

    Question

  • I have the following query that gets the total amount of image hits for the current day:

     

    select COUNT(*) as hits from ImageHits where CreateDate BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))

     

    This takes around 30s to execute. Can I do this any faster?



    • Edited by infused Monday, January 09, 2012 8:48 AM
    Monday, January 09, 2012 8:46 AM

Answers

  • The plan you have posted shows that the query is performing an index scan on IX_ImageHits. You can get the details of this index by. sp_helpindex 'ImageHits'
    However it would seem that this index may not be the best solution, the plan believes there is an 88.5 impact in adding an index:
     
    <MissingIndexes>
      <MissingIndexGroup Impact="88.5628">
        <MissingIndex Database="[iforce]" Schema="[dbo]" Table="[ImageHits]">
          <ColumnGroup Usage="INEQUALITY">
            <Column Name="[CreateDate]" ColumnId="2" />
          </ColumnGroup>
        </MissingIndex>
      </MissingIndexGroup>
    </MissingIndexes>
    
    So perhaps you could look at adding an index on CreateDate alone.
    CREATE NONCLUSTERED INDEX [IX_ImageHits_CreateDate] ON [dbo].[ImageHits] ([CreateDate])
    
    COUNT(1) or COUNT(*) should not make any difference.
    Depending on your requirements you may want to look at an index view:
    CREATE VIEW [dbo].[DailyImageHits]
    WITH SCHEMABINDING
    AS
    SELECT CAST([CreateDate] AS DATE) [CreateDate],COUNT_BIG(*) [Count]
    FROM [dbo].[ImageHits]
    GROUP BY CAST([CreateDate] AS DATE)
    GO
    CREATE UNIQUE CLUSTERED INDEX [cl] ON [dbo].[DailyImageHits]([CreateDate])
    GO
    SELECT * FROM [dbo].[DailyImageHits]
    

    Jon

    • Proposed as answer by Naomi NModerator Monday, January 09, 2012 3:45 PM
    • Edited by Jon Gurgul Monday, January 09, 2012 3:45 PM
    • Marked as answer by infused Monday, January 09, 2012 7:18 PM
    Monday, January 09, 2012 3:43 PM
  • This is the relevant thread  http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/e093eac0-2ae9-4b08-9374-a1742b234351 - I believe 2 replies by Erland in that thread will apply for this situation as well.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by infused Monday, January 09, 2012 9:12 PM
    Monday, January 09, 2012 4:02 PM
    Moderator
  • The order of the columns in the index is important. If you're only searching by a range of dates, you need to have CreateDate as the first column in the index.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by infused Monday, January 09, 2012 9:12 PM
    Monday, January 09, 2012 7:21 PM
    Moderator
  • > Can someone please explain with indexes, why my first index was not working correctly?
    > It had two columns ImageName (ASC), CreateDate (ASC).

    As Naomi already commented, the order of the columns in the key is important, since histogram values applies just to the first key column (column most to the left) of the statistics object.

    For more info about the histogram, check DBCC SHOW_STATISTICS in BOL.

    Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
    http://msdn.microsoft.com/en-us/library/dd535534.aspx

     


    AMB

    Some guidelines for posting questions...

    Monday, January 09, 2012 7:31 PM
    Moderator
  • I get the feeling we don't have the right information, and most people seem to want to fix it in the wrong place.

    First of all, make sure the data type in the WHERE clause is identical. So if CreateDate really is a datetime, then make sure you compare them with other datetime values. You are currently not doing that.

    Secondly, double check your index. The index on CreateDate is only really effective if CreateDate is the leading column in the index (and preferably the only column).

    And finally, you may want to run UPDATE STATISTICS on the table, to make sure the index' statistics are up to date. If the statistics are not up to date. The query plan shows that the optimizer thinks that only one row will match, when you seem to suggest that it is closer to 180,000. This underestimation is typically caused by statistics that are out of date, and typically leads to suboptimal query plans.

    Good luck.



    Gert-Jan
    • Marked as answer by infused Monday, January 09, 2012 9:13 PM
    Monday, January 09, 2012 7:47 PM

All replies

  • How big is your table and is there any index on created date?Furthermore, can you post the table structure and query plan??

    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Edited by Rishabh K Monday, January 09, 2012 8:50 AM
    Monday, January 09, 2012 8:50 AM
  • Create an index on Create Date and also include  with (nolock)  as below

    select COUNT(*) as hits from ImageHits  with (nolock) where CreateDate BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
    
     this should reduce the time taken to acquire locks and indexes would speedup the  search. it would be preferable if you use a particular column name in the Count() instead of *

     

     


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

    Surender Singh Bhadauria

    My Blog

     



    Monday, January 09, 2012 9:12 AM
  • You can enable sql server optimizer to use an index on CreateDate (if it is) by re-write as below
     WHERE CreateDate >=DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND CreateDate < DATEADD(d,DATEDIFF(d,0,GETDATE()),0)+1

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 09, 2012 9:19 AM
    Answerer
  • Hi. Try this:

    Declare @from DATE = GETDATE()
    Declare @to DATE = DATEADD(DAY, 1, @from)
    
    select COUNT(*) as hits 
    from ImageHits 
    where CreateDate >= @from
    	And
    	CreateDate <= @to
    

    Also please send structure of the table with indexes.

    • Proposed as answer by venku9 Monday, January 09, 2012 10:24 AM
    Monday, January 09, 2012 9:23 AM
  • Table has 33m rows. It grows by 180k rows per day.

    There is an index on createdate. Not sure what you mean by query plan? But here is the table structure:

    ImageHitsID [BigInt] [PrimaryKey]
    CreateDate [Datetime] 
    ImageName [Varchar(50)]
    ImageSize [Int]

    No allow nulls. 

    I'm not the best at SQL, the projects just sort of grown, so maybe I didn't design the table structure the best...

    Monday, January 09, 2012 9:34 AM
  • can you post the result of below sql

    set showplan_xml on

    GO

    select COUNT(*) as hits from ImageHits where CreateDate BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
    

    Thanks and regards, Rishabh , Microsoft Community Contributor
    Monday, January 09, 2012 9:43 AM
  • Infused,

    I believe your table must have a PK in it,

    instead of select COUNT(*), why don't you have select count(YourPKColumn)


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Monday, January 09, 2012 9:46 AM
  • Try the below,

     

    Declare @Fromdate as Date
    Set @Fromdate = CAST(GETDATE() AS DATE)
    Declare @todate as Date
    Set @todate = DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
    select COUNT(1) as hits from ImageHits with (nolock) where CreateDate BETWEEN @Fromdate AND @todate
    



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

    Surender Singh Bhadauria

    My Blog

     

    Monday, January 09, 2012 9:50 AM
  • Two second change with this code.
    Monday, January 09, 2012 10:04 AM
  • I got:

    <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1617.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="set showplan_xml on&#xd;&#xa;&#xd;&#xa;" StatementId="1" StatementCompId="1" StatementType="SET ON/OFF"/></Statements></Batch></BatchSequence></ShowPlanXML>

    <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1617.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="&#xd;&#xa;select COUNT(*) as hits from ImageHits where CreateDate BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="170.792" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0xE408DF3088599811" QueryPlanHash="0x6490646E7B099AC8"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="16" CompileTime="11" CompileCPU="3" CompileMemory="216"><MissingIndexes><MissingIndexGroup Impact="88.5628"><MissingIndex Database="[iforce]" Schema="[dbo]" Table="[ImageHits]"><ColumnGroup Usage="INEQUALITY"><Column Name="[CreateDate]" ColumnId="2"/></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1.12265e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="170.792" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Expr1003"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"/><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1008],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"/></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.12265e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="170.792" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Expr1008"/></OutputList><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"/><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"/></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="1.03775" EstimateIO="0" EstimateCPU="0.0285013" AvgRowSize="9" EstimatedTotalSubtreeCost="170.792" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><Parallelism><RelOp NodeId="3" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1.03775" EstimateIO="135.177" EstimateCPU="19.7704" AvgRowSize="15" EstimatedTotalSubtreeCost="154.948" TableCardinality="3.59461e+007" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues/><Object Database="[iforce]" Schema="[dbo]" Table="[ImageHits]" Index="[IX_ImageHits]" IndexKind="NonClustered"/><Predicate><ScalarOperator ScalarString="[iforce].[dbo].[ImageHits].[CreateDate]&gt;=CONVERT(date,getdate(),0) AND [iforce].[dbo].[ImageHits].[CreateDate]&lt;=dateadd(day,(1),CONVERT(date,getdate(),0))"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[iforce]" Schema="[dbo]" Table="[ImageHits]" Column="CreateDate"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator><Convert DataType="date" Style="0" Implicit="0"><ScalarOperator><Intrinsic FunctionName="getdate"/></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[iforce]" Schema="[dbo]" Table="[ImageHits]" Column="CreateDate"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1005"><ScalarOperator><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"/></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator><ScalarOperator><Convert DataType="date" Style="0" Implicit="0"><ScalarOperator><Intrinsic FunctionName="getdate"/></ScalarOperator></Convert></ScalarOperator></Intrinsic></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></Parallelism></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

    Monday, January 09, 2012 10:05 AM
  • Have you tried the query posted by Uri?? Remove count(*) and try to include count(1) as already suggested

    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Edited by Rishabh K Monday, January 09, 2012 10:19 AM
    Monday, January 09, 2012 10:18 AM
  • Yes

    select COUNT(1) as hits from ImageHits with (nolock) WHERE CreateDate >=DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND CreateDate < DATEADD(d,DATEDIFF(d,0,GETDATE()),0)+1
    

    has dropped it from 30 seconds to 21 seconds.

    Is there anything more I can look at doing?

    Thanks for the help so far, I've learnt quite a lot.

    Monday, January 09, 2012 10:26 AM
  • try with the below index and see if you get any performance improvement

    create nonclustered index IDX_createdate_include_imagesize on test(createdate) include (imagesize)

    --query

    select COUNT(*) as hits from test
    WHERE CreateDate >=DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND CreateDate < DATEADD(d,DATEDIFF(d,0,GETDATE()),0)+1


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Edited by Rishabh K Monday, January 09, 2012 11:24 AM
    Monday, January 09, 2012 11:24 AM
  • Try using the one with variables. Because there is no point in performing Cast and Date Add 3 million times. It should reduce the time further.

     

    Declare @Fromdate as Date
    Set @Fromdate = CAST(GETDATE() AS DATE)
    Declare @todate as Date
    Set @todate = DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
    select COUNT(1) as hits from ImageHits with (nolock) where CreateDate BETWEEN @Fromdate AND @todate

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

    Surender Singh Bhadauria

    My Blog

     

    Monday, January 09, 2012 11:39 AM
  • Hi,

    Try this:

    select COUNT(*) as hits from ImageHits where CreateDate BETWEEN GETDATE() AND DATEADD(DAY, 1, GETDATE())
    

    Cheers,

    IT-Singh

    Monday, January 09, 2012 11:50 AM
  • This won't working as if Createdate column has some data from that day(GETDATE()) with time as 11 AM for example it will note return
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 09, 2012 11:58 AM
    Answerer
  • I don't think you need to use NOLOCK hint. Using variables may help as I remember there was some bug in regards to using DATEADD functions in the parameters.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 09, 2012 2:47 PM
    Moderator
  • The plan you have posted shows that the query is performing an index scan on IX_ImageHits. You can get the details of this index by. sp_helpindex 'ImageHits'
    However it would seem that this index may not be the best solution, the plan believes there is an 88.5 impact in adding an index:
     
    <MissingIndexes>
      <MissingIndexGroup Impact="88.5628">
        <MissingIndex Database="[iforce]" Schema="[dbo]" Table="[ImageHits]">
          <ColumnGroup Usage="INEQUALITY">
            <Column Name="[CreateDate]" ColumnId="2" />
          </ColumnGroup>
        </MissingIndex>
      </MissingIndexGroup>
    </MissingIndexes>
    
    So perhaps you could look at adding an index on CreateDate alone.
    CREATE NONCLUSTERED INDEX [IX_ImageHits_CreateDate] ON [dbo].[ImageHits] ([CreateDate])
    
    COUNT(1) or COUNT(*) should not make any difference.
    Depending on your requirements you may want to look at an index view:
    CREATE VIEW [dbo].[DailyImageHits]
    WITH SCHEMABINDING
    AS
    SELECT CAST([CreateDate] AS DATE) [CreateDate],COUNT_BIG(*) [Count]
    FROM [dbo].[ImageHits]
    GROUP BY CAST([CreateDate] AS DATE)
    GO
    CREATE UNIQUE CLUSTERED INDEX [cl] ON [dbo].[DailyImageHits]([CreateDate])
    GO
    SELECT * FROM [dbo].[DailyImageHits]
    

    Jon

    • Proposed as answer by Naomi NModerator Monday, January 09, 2012 3:45 PM
    • Edited by Jon Gurgul Monday, January 09, 2012 3:45 PM
    • Marked as answer by infused Monday, January 09, 2012 7:18 PM
    Monday, January 09, 2012 3:43 PM
  • Two second change with this code.


    You might consider removing the NOLOCK as Naomi suggested and adding OPTION(RECOMPILE) so you'll get the best plan for the supplied values.  Are you seeing an index seek or scan now?

    Note that the difference between COUNT(*) vs. COUNT(1) is insignificant compile-time overhead (milliconds or microseconds).  My guess is that there was something else that reduced the duration in your earlier test with the the suboptimal parallel plan.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, January 09, 2012 3:53 PM
  • I read here some time ago about a problem using DATEDIFF for the parameter. I suppose it may have influenced the plan. I'll try to search for this thread.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 09, 2012 3:58 PM
    Moderator
  • This is the relevant thread  http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/e093eac0-2ae9-4b08-9374-a1742b234351 - I believe 2 replies by Erland in that thread will apply for this situation as well.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by infused Monday, January 09, 2012 9:12 PM
    Monday, January 09, 2012 4:02 PM
    Moderator
  • make use of index, may give you better result.
    Monday, January 09, 2012 5:47 PM
  • Thanks for the replies. Let me try these today and come back to you.
    Monday, January 09, 2012 7:00 PM
  • Correct. Adding an index with just CreateDate seems to have fixed the issue.

    So I did: CREATE NONCLUSTERED INDEX [IX_ImageHits_CreateDate] ON [dbo].[ImageHits] ([CreateDate])

    Then the old query

    select COUNT(1) as hits from ImageHits with (index(IX_ImageHits_CreateDate)) WHERE CreateDate >=DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND CreateDate < DATEADD(d,DATEDIFF(d,0,GETDATE()),0)+1

    1 second select time now.

    On using vars, I read that MSSQL is quite good in the fact it will know it's going to do a command 33m times, so it doesn't actually re-evaluate these each time. Using vars and not using vars made no difference in speed.

    Can someone please explain with indexes, why my first index was not working correctly? It had two columns ImageName (ASC), CreateDate (ASC).

    Thank you very much for the help.


    • Edited by infused Monday, January 09, 2012 7:18 PM
    Monday, January 09, 2012 7:18 PM
  • The order of the columns in the index is important. If you're only searching by a range of dates, you need to have CreateDate as the first column in the index.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by infused Monday, January 09, 2012 9:12 PM
    Monday, January 09, 2012 7:21 PM
    Moderator
  • > Can someone please explain with indexes, why my first index was not working correctly?
    > It had two columns ImageName (ASC), CreateDate (ASC).

    As Naomi already commented, the order of the columns in the key is important, since histogram values applies just to the first key column (column most to the left) of the statistics object.

    For more info about the histogram, check DBCC SHOW_STATISTICS in BOL.

    Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
    http://msdn.microsoft.com/en-us/library/dd535534.aspx

     


    AMB

    Some guidelines for posting questions...

    Monday, January 09, 2012 7:31 PM
    Moderator
  • I get the feeling we don't have the right information, and most people seem to want to fix it in the wrong place.

    First of all, make sure the data type in the WHERE clause is identical. So if CreateDate really is a datetime, then make sure you compare them with other datetime values. You are currently not doing that.

    Secondly, double check your index. The index on CreateDate is only really effective if CreateDate is the leading column in the index (and preferably the only column).

    And finally, you may want to run UPDATE STATISTICS on the table, to make sure the index' statistics are up to date. If the statistics are not up to date. The query plan shows that the optimizer thinks that only one row will match, when you seem to suggest that it is closer to 180,000. This underestimation is typically caused by statistics that are out of date, and typically leads to suboptimal query plans.

    Good luck.



    Gert-Jan
    • Marked as answer by infused Monday, January 09, 2012 9:13 PM
    Monday, January 09, 2012 7:47 PM
  • Thanks for all your help, much appreciated!
    Monday, January 09, 2012 9:06 PM