Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dand
13 Posts |
 Posted - 2005-08-25 : 11:25:48
|
| The main table (tbltelemarketing) has over 17M rows. We create a summary table (tblTMBranchFiles) every week. The summary table creates blocks of 5000 records for the user to download. At the end of the week, this process updates tbltelemarketing with the dates that the user downloaded records. Right now this process takes over 3 hours. Can someone suggest a way to get rid of the cursors - at least one of them, hopefully both. Here's the code:CREATE PROCEDURE sp_TMPreLoad ASDECLARE @ID int, @rpt_promo varchar(50), @promo_code varchar(20), @division varchar(4), @region varchar(4), @branch varchar(4), @drop_date smalldatetime, @update_date smalldatetime, @download smalldatetime, @total_names int, @startrec int, @endrec int, @totalcnt int, @counter intDECLARE RecCursor CURSOR FORSELECT dbo.tblTMBranchFiles.rpt_promo, dbo.tblTMBranchFiles.promo_code, dbo.tblTMBranchFiles.division, dbo.tblTMBranchFiles.region, dbo.tblTMBranchFiles.branch, dbo.tblTMBranchFiles.drop_date, dbo.tblTMBranchFiles.update_date, dbo.tblTMBranchFiles.download, dbo.tblTMBranchFiles.startrec, dbo.tblTMBranchFiles.endrec, dbo.tblTMBranchFiles.total_namesFROM dbo.tblTMBranchFiles with (readuncommitted)WHERE dbo.tblTMBranchFiles.download is not nullOPEN RecCursorFETCH NEXT FROM RecCursorINTO @rpt_promo, @promo_code, @division, @region, @branch, @drop_date, @update_date, @download, @startrec, @endrec, @totalcntWHILE @@FETCH_STATUS = 0BEGIN SET @counter = 1 DECLARE RecCursor2 CURSOR FOR SELECT dbo.tbltelemarketing.ID FROM dbo.tbltelemarketing with(readuncommitted) WHERE dbo.tbltelemarketing.branch = @branch and dbo.tbltelemarketing.promo_code = @promo_code and dbo.tbltelemarketing.drop_date = @drop_date and dbo.tbltelemarketing.delete_cd is null and dbo.tbltelemarketing.live is not null Order By branch, promo_code, drop_date, delete_cd, live, prop_zip5, prop_zip4, hh_score2 OPEN RecCursor2 FETCH NEXT FROM RecCursor2 INTO @ID WHILE @@FETCH_STATUS = 0 BEGIN IF @counter >= @startrec and @counter <= @endrec UPDATE tbltelemarketing SET download = @download WHERE ID = @ID SET @counter = @counter + 1 FETCH NEXT FROM RecCursor2 INTO @ID END CLOSE RecCursor2 DEALLOCATE RecCursor2 FETCH NEXT FROM RecCursor INTO @rpt_promo, @promo_code, @division, @region, @branch, @drop_date, @update_date, @download, @startrec, @endrec, @totalcntENDCLOSE RecCursorDEALLOCATE RecCursorThanks,Dan |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-25 : 12:55:10
|
| Hey Dan. Wow, nested cursors on 17M rows, that's brutal. Seems pretty straight forward but could explain the logic behind the startrec, endrec columns? I don't get what's going on there.Be One with the OptimizerTG |
 |
|
|
dand
13 Posts |
Posted - 2005-08-25 : 13:35:04
|
| The client that we manage this for has about 400 branches. Each branch is allowed to mail to leads in their area in order to generate sales. The leads come from various sources and after processing end up in the tbltelemarketing table. The person who wrote this is long gone but there were performance problems trying to download more than 5000 records at a time so he devised this method. If a branch has 12000 records in the table he would create 3 rows for the branch. The first row would have a startrec=1 and endrec=5000, the second row would have startrec=5001 and endrec=10000 and the third row would have startrec=10001 and endrec=12000.So, the branch could then download the first 5000 rows and the "download" column in tblTMBranchFiles would be updated with the current date. Then this process looks at the download date for each row in tblTMBranchFiles and updates the number of records in the "total_names" columns with the download date. In the example I gave above the "total_names" column would have 5000 for the first two rows and 2000 for the third row. All of this is the first part of a three part process. The second part of this whole process is for all of the rows in the tbltelemarketing table to be updated as to whether the person bought something or if they said don't contact them again, etc. and then they are taken out of circulation. The third part of the process rebuilds the tblTMBranchFiles again with the new counts. I'm going to eventually look at doing away with the summary table and let the branches download all of their records at one time but the first step is just to improve this process. The entire three step process takes 26 hours to run right now.I hope that helps to explain the process better and the significance of the startrec and endrec columns. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-25 : 14:11:30
|
quote:
The first row would have a startrec=1 and endrec=5000, the second row would have startrec=5001 and endrec=10000 and the third row would have startrec=10001 and endrec=12000.
Do these 3 rows have the same ID or different ID values?If the IDs are different I think this would work:update b set b.download = a.downloadfrom dbo.tblTMBranchFiles ajoin dbo.tbltelemarketing b on a.branch = b.branch and a.promo_code = b.promo_code and a.drop_date = b.drop_date and a.[ID] = b.[ID]where a.download is not nulland b.delete_cd is null and b.live is not null If it's totally off base, post the DDL of the 2 tables and provide insert statements for a small sample of data (like 2 branches with 3 marketing rows each)Be One with the OptimizerTG |
|
|
|
dand
13 Posts |
Posted - 2005-08-25 : 15:15:53
|
| Thanks TG. I'll test this and let you know. |
|
|
|
dand
13 Posts |
Posted - 2005-09-13 : 15:38:19
|
| TG,That didn't work. There is no id column in the tblTMBranchFiles table. The query I used couldn't distinguish between rows in tblTMBranchFiles with different update_date.Any other ideas?Dan |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
convert cursor process to set-based