Hi all,
I have a question that maybe someone can provide some information on.
I have a stored procedure that has a cursor (loop #1) and that iteration calls another stored procedure and that stored procedure also has a cursor (loop #2). Now, these cursors are NOT used with a large amount of data sets nor is the information or fields in the datasets large. For example: Loop #1 has usually 20 records and Loop #2 usually has 20 records so a total in both cursors is 40 records.
What is happening is intermittent time-out occurring. Granted, the time-out is defined in the ASP.NET page but we did some tracking in the stored procedures and it takes a almost a minute to get through 1 iteration (1 record in loop #1 and 10 records in loop #2). This 1 minute is causing the asp.net page to exceed it's timeout. But it should NOT be taking a minute.
Now, we have a DEV database machine and the problem is NOT happening in that machine. There is only one instance on that that machine.
However, the PROD database is on a shared server. Could it possibly be the CPU or memory utilizaton on that shared server? Could there be a configuration of the instance? I would point to the cursor and remediate the cursor IF the cursor was using a larger set of data (way more than then 40 records.)
Any suggestions/advice on what may be happening here or ways to troubleshoot or remediate?
Information would be greatly appreciated and valued!
I have a question that maybe someone can provide some information on.
I have a stored procedure that has a cursor (loop #1) and that iteration calls another stored procedure and that stored procedure also has a cursor (loop #2). Now, these cursors are NOT used with a large amount of data sets nor is the information or fields in the datasets large. For example: Loop #1 has usually 20 records and Loop #2 usually has 20 records so a total in both cursors is 40 records.
What is happening is intermittent time-out occurring. Granted, the time-out is defined in the ASP.NET page but we did some tracking in the stored procedures and it takes a almost a minute to get through 1 iteration (1 record in loop #1 and 10 records in loop #2). This 1 minute is causing the asp.net page to exceed it's timeout. But it should NOT be taking a minute.
Now, we have a DEV database machine and the problem is NOT happening in that machine. There is only one instance on that that machine.
However, the PROD database is on a shared server. Could it possibly be the CPU or memory utilizaton on that shared server? Could there be a configuration of the instance? I would point to the cursor and remediate the cursor IF the cursor was using a larger set of data (way more than then 40 records.)
Any suggestions/advice on what may be happening here or ways to troubleshoot or remediate?
Information would be greatly appreciated and valued!
