Calling SUB Stored Procedure Within Cursor Loop

Question

Calling SUB Stored Procedure Within Cursor Loop

Paresh Randeria

Ten Centuries

Points: 1150
More actions
August 7, 2008 at 4:25 am

#382172

I have main Stored Procedure (sp_Main) which is using Cursor to fetch record for each client sequentially (Total Client around 1,000,000) and for each client it's calling another Stored Procedure (sp_Detail) within Cluster loop which is processing each Trade Order records (Approx 50-100 records for each client) for the client using AGAIN Cursor.
Now sp_Detail is using temporary tables and it's recompiling every time it's called by the sp_Main Stored Procedure. Now sp_detail will be called 1,000,000 times and overall performance is suffering.
Please note that I can not avoid Cursor due to completed logic for each client for Trade orders.....
My question:
1. Is it advisable to copy sp_Detail logic in the Main Procedure to better performance? It mean it will be nested cluster loop.
Will Combining Two procedures into one give better PERFORMANCE?
ANy suggesstion is appreciated.......

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 1

You will get slightly better performance combining the two procedures. Avoiding the recompile and the waits that it entails will help.

Honestly though, it's not a solution to your problem. You're running through a million rows one at a time. That's just simply going to be a performance bottleneck that is unavoidable.

If you see Jeff coming, run. He carries pork chops and knows how to use them.

"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

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 2

Any time you create a temporary table within a stored procedure, you increase the likelihood that you will force a recompile of that procedure.

Try creating a permanant "work" table for your procedure instead of a temporary table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Although you may think you can't eliminate the cursors, I'd suggest that you look seriously at rewriting the process as a set-based solution. It may take time, but in the end, it may pay-off significantly.

I am sure you can't post the actual code involved, but if you can create something similiar and post that, I am sure there are people out here that can help you come up with a better solution. Based on your original post, the inner cursor is the first one to start looking at.

😎