July 17, 2009 at 11:39 am
I know cursors are generally not recommended but in this case it's needed. The issue I have is that when I run a sproc, it calls a spreadsheet and loads the data into tables and then uses that data the in the cursor to do it's processing. The sproc will run fine for the first couple of spreadsheets but when it gets to the 3rd or 4th sheet, it starts to run long. The first 2 sheets will take about 20 mins but the next few will take hours, usually 1 to 2 hours to process. Now these spreadsheets are all about the same size, meaning that they have similar number of records, maybe a difference of 100 or so. The size of the spreadsheets are all around 20MBs or so.
I am using SQL Server 2008 Standard Edition, 64 bit on a Windows 2008 Server.
Can anyone tell my why the sproc will take longer to run with each spreadsheet that is processed?
Thanks for any help!
July 17, 2009 at 1:42 pm
Is the process dropping the data into the same table for all sheets? Is that table empty at the beginning of the process? Is there an index on the tables the data is loaded into? Have you modified the process to capture any time metrics while it runs to help point out exactly what section the process is spending all its time in?
From your post, 1 spreadsheet, 4 sheets all roughly the same size, processing time gets longer for each sheet. That about right?
Perhaps a bit more about what is happening..
CEWII
July 17, 2009 at 1:58 pm
KT8177 (7/17/2009)
I know cursors are generally not recommended but in this case it's needed.
Heh... says you. 😉 Perhaps you just haven't thought of the way to do it without one, yet. I've seen lots of people make lots of claims that some things can't be done without a cursor... they've been correct in 2 out of 300 cases.
--Jeff Moden
July 17, 2009 at 2:11 pm
The process will just do one sheet at a time. When that is done, then we start the process again by specifying the next file. The table gets cleared out and then it's imported in. There's just a lot of business logic that's done inbetween. The table is indexed properly. I just wonder if the issue is that we keep running these over and over again, there are about 10 spreadsheets to process, and so each time it just takes longer and longer because of locked resources used somehow by the cursor and it's not releasing it? Is that possible?
Actually, what I meant by the cursor is needed is that I'm sure it can be rewritten, it's just that they don't want to redo it at this time. I recommend rewriting the sprocs but for now, that's not an option.
July 17, 2009 at 2:51 pm
Perhaps I misunderstood.... is there a cursor within the business logic for each spreadsheet or is there just one cursor that that controls which sheets are loaded one at a time?
So far as it getting slower each time goes, it sounds like it may be reusing the execution plan for each spreadsheet... ie: Parameter Sniffing. I could certainly be wrong there but you may have to force a recompile after each spreadsheet is loaded.
--Jeff Moden
September 1, 2009 at 4:31 am
I dont know the exact prob but why don't u use SSIS for import. call that SSIS package from your cursor.
Pls check the details as I think its possible with SSIS.
CREATE SSIS PACKAGE IN BUSINESS INTELLIGENCE SSIS
AND Execute using following query from your production server.
EXEC master..xp_cmdshell 'DTEXEC /F "\\192.168.9.99\SSIS_FOLDER\IMPORT_EXCEL.dtsx" '
Regards,
Shantaram
September 1, 2009 at 8:17 am
KT8177 (7/17/2009)
The issue I have is that when I run a sproc, it calls a spreadsheet and loads the data into tables and then uses that data the in the cursor to do it's processing. The sproc will run fine for the first couple of spreadsheets but when it gets to the 3rd or 4th sheet, it starts to run long. The first 2 sheets will take about 20 mins but the next few will take hours, usually 1 to 2 hours to process. Now these spreadsheets are all about the same size, meaning that they have similar number of records, maybe a difference of 100 or so. The size of the spreadsheets are all around 20MBs or so.I am using SQL Server 2008 Standard Edition, 64 bit on a Windows 2008 Server.
I feel for you, because I have to deal with lots of cursors I don't have the option of removing. First of all, what is the cursor used for? Is it controlling which spreadsheet is currently being imported, or is it iterating the data within the spreadsheet? I'm guessing the latter, which is probably where the performance problem is.
One thing you can try is to "cache" the imported data in a table variable instead of inserting it directly into a table, then later use the table variable insert/merge to the final destination. That usually gets me around a lot of index fragmentation and transaction log bloat from many, many inserts.
September 1, 2009 at 9:48 am
What you seem to be doing seems to be a regular process. In this case I would agree with Shataram about using ssis. You may find it far easier to do your business inteliigence in ssis. As far as cursor is concerned sql server 2005 optimiser cannot generate query plan for cursors. I guess it may be true for 2008 too. You may think of using something esle.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply