Post reply

Calling SUB Stored Procedure Within Cursor Loop

  • 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.......

  • August 7, 2008 at 6:26 am

    #854394

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • August 7, 2008 at 8:28 am

    #854491

    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.

  • August 7, 2008 at 8:53 am

    #854510

    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.

    😎

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

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