Summarising By Time Interval

  • I'm wondering if there's any way to speed up the following query. It takes 53 secs when the parameters require all rows (table tblVisitorInfo has 750,000 rows). I've put a clustered index on Visit_date. Setup is Win NT4/SQL Server 7 with 256MB.

    SELECT Convert(smalldatetime,Case @TimeInterval

    When 'day' Then Convert(Char(10),Visit_date,103)

    When 'week' Then Convert(Char(10),Visit_date-DatePart(weekday,Visit_date)+1,103)

    When 'month' Then Convert(Char(10),'01/'+Cast(DatePart(month,Visit_date) As varchar(2))+'/'+Cast(DatePart(year,Visit_date) As char(4)),103)

    When 'quarter' Then Convert(Char(10),'01/'+Cast(DatePart(quarter,Visit_date) As varchar(2))+'/'+Cast(DatePart(year,Visit_date) As char(4)),103)

    When 'year' Then Convert(Char(10),'01/01/'+Cast(DatePart(year,Visit_date) As char(4)),103)

    End) As TimePeriod,

    Count(*) As NoOfVisitors,

    Sum(Case Print_Submit When 'S' Then 1 Else 0 End) As NoOfOrders,

    IsNull(Sum(Case Print_Submit When 'S' Then Order_Subtotal Else 0 End),0) As SumSubTotal

    FROM tblVisitorInfo

    WHERE Visit_date >= @StartDate

    And Visit_date <= @EndDate

    GROUP BY Convert(smalldatetime,Case @TimeInterval

    When 'day' Then Convert(Char(10),Visit_date,103)

    When 'week' Then Convert(Char(10),Visit_date-DatePart(weekday,Visit_date)+1,103)

    When 'month' Then Convert(Char(10),'01/'+Cast(DatePart(month,Visit_date) As varchar(2))+'/'+Cast(DatePart(year,Visit_date) As char(4)),103)

    When 'quarter' Then Convert(Char(10),'01/'+Cast(DatePart(quarter,Visit_date) As varchar(2))+'/'+Cast(DatePart(year,Visit_date) As char(4)),103)

    When 'year' Then Convert(Char(10),'01/01/'+Cast(DatePart(year,Visit_date) As char(4)),103)

    End) WITH ROLLUP

    ORDER BY TimePeriod DESC

  • One suggestion:

    Can you create 4 columns that get populated at the Update or Insert time from the visit date column. With that you will get rid of MOST your converts!!

    and it will look like

    SELECT Case @TimeInterval

    When 'day' Then Visit_day,

    When 'week' Then Visit_weekday,

    When 'month' Then Visit_month,

    When 'quarter' Then Visit_quarter,

    When 'year' Then Visit_year End) As TimePeriod,

    ...

    GROUP BY(Case @TimeInterval

    When 'day' Then Visit_day,

    When 'week' Then Visit_weekday,

    When 'month' Then Visit_month,

    When 'quarter' Then Visit_quarter,

    When 'year' Then Visit_year END)

    WITH ROLLUP

    ORDER BY TimePeriod DESC


    * Noel

  • What really speeds things up is creating pre-calculated tables. These tables can be updated via triggers every time the master table updates. Then query off the pre-calculated table. Very fast - I used this technique to speed queries from several minutes to a few seconds.

    If you can use this method for any part of your query, the time savings can be huge.

    Data: Easy to spill, hard to clean up!

  • Hi Stuart (stubob),

    I agree with you. Pre-calculation makes sense. This calculation is done only once and it is a time-saver. (If you do not pre-calculate, the same time-consuming calculations and conversions have to be done at every execution of the query).

    Thanks

    RA

  • quote:


    Hi Stuart (stubob),

    I agree with you. Pre-calculation makes sense. This calculation is done only once and it is a time-saver. (If you do not pre-calculate, the same time-consuming calculations and conversions have to be done at every execution of the query).

    Thanks

    RA


    What really speeds things up is creating pre-calculated tables. These tables can be updated via triggers every time the master table updates. Then query off the pre-calculated table. Very fast - I used this technique to speed queries from several minutes to a few seconds.

    If you can use this method for any part of your query, the time savings can be huge.

    quote:



    Do you plan to PRECALCULATE ALL Time Periods of the ABOVE query? If you do you'd better use OLAP


    * Noel

  • Obviously a strategy has to fit the circumstances. The precalculated summary tables I used for dramatic gains involved data that was fully updated only once a day at 2AM, and only small parts were ever changed outside of that update. It took maybe 15 minutes to update the precalculated tables, during which time the older pre-calculated data was still available. It was the right solution for that job.

    If inserts & updates are frequent, then that must be taken into account. Use Common Sense!

    The only normal people are those you don't know well - Oscar Wilde

    Data: Easy to spill, hard to clean up!

  • Thank you all for those suggestions, it sounds very promising.

    Barry

Viewing 7 posts - 1 through 6 (of 6 total)

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