December 10, 2003 at 5:20 am
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
December 10, 2003 at 7:16 am
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
December 10, 2003 at 11:20 am
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!
December 11, 2003 at 8:13 am
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
December 11, 2003 at 9:28 am
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
December 11, 2003 at 12:08 pm
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!
December 15, 2003 at 3:21 am
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