December 5, 2003 at 12:21 pm
I have been looking for an alternative to using a cursor for a query I have been tasked with. I used the cursor to get an initial snapshot of data but I'd like to find and alternative that may be faster.
The basic scenario is this:
I need to collect all of the customers of a certain type and gather statistics on thier top 100 products.
My current logic goes like this (sql example to follow):
Create a cursor that is populated with the ID of each of the customers who match the criteria.
Using this cursor, loop through each customer and select the top 100 products and the specified statistics and insert that information into a data mart. In order to gather some of the stats a self join is required.
Example:
DECLARE @ID as int,
@STARTDATE datetime,
@ENDDATE datetime
....
DECLARE customers CURSOR FAST_FORWARD FOR
SELECT ID FROM CUSTOMERS WHERE CUSTOMERTYPE='x'
OPEN customers
FETCH NEXT FROM customers INTO @ID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO DATAMARTTABLE
SELECT TOP 100 C.Stat AS PRODCOUNT1,
D.Stat AS PRODCOUNT2,
E.Stat AS PRODCOUNT3,
getdate() -- DateCreated
FROM
CUSTOMERDATA C
INNER JOIN CUSTOMERDATA D ON C.CDID=D.CDID AND PRODUCTTYPE='x'
...
WHERE
ID=@ID
AND DATERANGE BETWEEN @STARTDATE AND @ENDDATE
...
ORDER BY PRODUCTCOUNT
FETCH NEXT FROM customers INTO @ID
END
CLOSE customers
DEALLOCATE customers
I had thought about pulling this into a vb.net app and using ADO but I am not sure if that would be quicker or not. The customer count is more than 10,000 so that query would have to be performed 10,000 times. The query only needs to run once a week, but other jobs will be running at the same time.
Unfortunately, this is about as clear as I can be due to the sensitivity of the exact data.
Thanks for you help
Cliff
December 5, 2003 at 1:33 pm
You can work this by pulling in data and updating it based on which data from customerData is already in the data mart. I've done something similar (I think) where I pull in 1 row (select top 1) based on some order and insert this into another table. This query is based on the select joining the destination to be sure the data is not already there. Then the next time it runs, the "next" row is inserted.
Hope this is clear and helps.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply