December 22, 2010 at 1:00 pm
This query finds how many subscribed contacts there were everyday between a date range.
So example output would be:
CurrDate-------------------CurrCount
---------------------------------------
2010-11-20 00:00:00.000 59567
2010-11-21 00:00:00.000 59596
2010-11-22 00:00:00.000 59616
2010-11-23 00:00:00.000 59651
I've messed around with this quite a bit and I've gotten the run time down to around ~1 second with 71k Contacts (in the temp table, the full contact table has millions of rows) over a date range of 32 days. Just seems like I should be able to get this running even faster.
Declare @StartDate DateTime -- start of date range
Declare @EndDate DateTime -- end of date range
Declare @cstID Int -- PK for contact table
select @StartDate = '2010-11-20 00:00:00:000'
select @EndDate='2010-12-21 00:00:00:000'
select @cstID=8147
--fill a temp table with a date for everyday in the date range
create table #daterange (dt datetime)
while @StartDate <= @EndDate
begin
insert #daterange (dt) select @StartDate
select @StartDate = dateadd(dd,1,@StartDate)
end
--fill another temp table with Contacts that will be used
select cntID, cntDateSubed, ISNULL(cntDateUnsubed,'1/1/1901') as cntDateUnsubed , ISNULL(cntDateDeleted,'1/1/1901') as cntDateDeleted into #contacts From tblContact where cnt_cstID=@cstID and cntDateSubed IS NOT NULL
--find how many contacts exsisted on each day in the date range
SELECT dt as CurrDate,
(SELECT Count(*) FROM #contacts WHERE cntDateSubed <= dt AND
(cntDateSubed > cntDateUnsubed AND cntDateSubed > cntDateDeleted) OR (dt < cntDateUnsubed AND dt < cntDateDeleted)) as CurrCount
FROM #daterange
drop table #contacts
drop table #daterange
December 22, 2010 at 1:18 pm
Please post table definitions, index definitions for the contacts table as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
December 22, 2010 at 1:20 pm
I might be missing something, but couldn't you just do a GROUP BY on the date with a count? Seems like that would be a lot easier than loading temporary tables and walking through them row by row.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 22, 2010 at 1:40 pm
GilaMonster (12/22/2010)
Please post table definitions, index definitions for the contacts table as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Sorry, I went back and added the attachments
December 22, 2010 at 2:02 pm
Grant Fritchey (12/22/2010)
I might be missing something, but couldn't you just do a GROUP BY on the date with a count? Seems like that would be a lot easier than loading temporary tables and walking through them row by row.
How would I get my date range then?
December 22, 2010 at 2:08 pm
marc 96091 (12/22/2010)
GilaMonster (12/22/2010)
Please post table definitions, index definitions for the contacts table as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Sorry, I went back and added the attachments
I can't see much besides the insert into your date range table in the query plan, but looking at your schema and the query have you tried:
1) Adding an index on the tblContact table to cover the 4 cntDatexxxxx columns used in your query
2) Add a default value of '1/1/1901' to the cntDateUnsubed and cntDateDeleted columns if possible - other evaluations may rely on this being NULL.
This would eliminate the need for the ISNULL test and improve the index and SQLs use of the index
If you can't set a default ensure these 2 columns are on the right of the index
3) Don't use the #contacts table and change the select to:
SELECT dt as CurrDate,
SELECT Count(*) FROM tblContact WHERE cntDateSubed <= dt
AND(cntDateSubed > ISNULL(cntDateUnsubed,'1/1/1901')
AND cntDateSubed > ISNULL(cntDateDeleted,'1/1/1901'))
OR (dt < cntDateUnsubed AND dt < ISNULL(cntDateDeleted,'1/1/1901')) ) as CurrCount
FROM #daterange
Note you can remove the ISNULL test if you use a default on the columns.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
December 22, 2010 at 2:16 pm
Leo.Miller (12/22/2010)
marc 96091 (12/22/2010)
GilaMonster (12/22/2010)
Please post table definitions, index definitions for the contacts table as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Sorry, I went back and added the attachments
I can't see much besides the insert into your date range table in the query plan, but looking at your schema and the query have you tried:
1) Adding an index on the tblContact table to cover the 4 cntDatexxxxx columns used in your query
2) Add a default value of '1/1/1901' to the cntDateUnsubed and cntDateDeleted columns if possible - other evaluations may rely on this being NULL.
This would eliminate the need for the ISNULL test and improve the index and SQLs use of the index
If you can't set a default ensure these 2 columns are on the right of the index
3) Don't use the #contacts table and change the select to:
SELECT dt as CurrDate,
SELECT Count(*) FROM tblContact WHERE cntDateSubed <= dt
AND(cntDateSubed > ISNULL(cntDateUnsubed,'1/1/1901')
AND cntDateSubed > ISNULL(cntDateDeleted,'1/1/1901'))
OR (dt < cntDateUnsubed AND dt < ISNULL(cntDateDeleted,'1/1/1901')) ) as CurrCount
FROM #daterange
Note you can remove the ISNULL test if you use a default on the columns.
Cheers
Leo
Pretty sure your suggestion in #3 is where I came from, but I'll try it again with #1 (adding an index) to see how it compares.
December 22, 2010 at 6:43 pm
marc 96091 (12/22/2010)
GilaMonster (12/22/2010)
Please post table definitions, index definitions for the contacts table as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Sorry, I went back and added the attachments
Would you please go back and add some sample data to the table (via INSERT statements)? See the first link in my signature for how to do so.
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 24, 2010 at 1:04 pm
Leo.Miller (12/22/2010)
marc 96091 (12/22/2010)
GilaMonster (12/22/2010)
Please post table definitions, index definitions for the contacts table as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Sorry, I went back and added the attachments
I can't see much besides the insert into your date range table in the query plan, but looking at your schema and the query have you tried:
1) Adding an index on the tblContact table to cover the 4 cntDatexxxxx columns used in your query
2) Add a default value of '1/1/1901' to the cntDateUnsubed and cntDateDeleted columns if possible - other evaluations may rely on this being NULL.
This would eliminate the need for the ISNULL test and improve the index and SQLs use of the index
If you can't set a default ensure these 2 columns are on the right of the index
3) Don't use the #contacts table and change the select to:
SELECT dt as CurrDate,
SELECT Count(*) FROM tblContact WHERE cntDateSubed <= dt
AND(cntDateSubed > ISNULL(cntDateUnsubed,'1/1/1901')
AND cntDateSubed > ISNULL(cntDateDeleted,'1/1/1901'))
OR (dt < cntDateUnsubed AND dt < ISNULL(cntDateDeleted,'1/1/1901')) ) as CurrCount
FROM #daterange
Note you can remove the ISNULL test if you use a default on the columns.
Cheers
Leo
Method #3 wasn't any faster (in fact it was much slower) and the index from #1 didn't help any.
Once I put my query into a stored proc and it completes in about 700ms, which will have to do, I don't see any other way to get this going faster.
Thanks for your thoughts!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply