October 30, 2007 at 7:48 am
Hi All,
To optimize the performance, we did try to avoid and replace certain CURSORS. To avoid CURSORS, most of the places we have used DERIVED tables.
But there are some places, where we need to store the values of the rows into variables for extra calculations and processing.
At this stage, we are facing problem.
Is there any way, where we can use variables dynamically to store the values from the above mentioned DERIVED tables?
Please let me know any alternates.
Thanks,
Subhash
October 30, 2007 at 8:33 am
Well - given the lack of detail you're giving, I suppose the answer to your question is...
"Yes"
Be more specific - it will be easier to be more helpful that way....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2007 at 9:03 am
Shy details, it sounds like you're talking about temporary tables.
"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
October 30, 2007 at 1:18 pm
Details are important if you want a decent answer. Please, Help us help you 😉
* Noel
October 31, 2007 at 1:02 am
Hi Guys,
Am very sorry for not giving enough details.
Let me explain the scenario as follows:
I have a stored procedure to publish a report which in turn is calling many sub stored procedures. Each and every stored procedure is dealing with some set of tables to process the data and is using CURSORS for the same.
For example,
First CURSOR is written on SALESTABLE to fetch Division and Department.
Inside this CURSOR, an other CURSOR is written on a table called Miscellaneous charges to fetch the charge details based on the above fetched Division and Department.
From the while loop of the second(nested cursor), depending on the fetched values, calculations and processing is happening and storing in multiple variables.
At the end of each records iteration, the above calculated/processed variables are inserting into a REPORT table.
So, to avoid the CURSORS, we tried with derived tables and able to retrieve the necessary rows, but the problem over here is we are not able to process each and every record and store the results into variables as we can in CURSORS. once after the values are calculated, we should also insert the same into the REPORT table.
Hope, I have mentioned the scenario in detail. Tried pasting the code as well, but i could not, since it is very huge.
Expecting a better solution to avoid the CURSORS.
Thanks in advance.
Subhash
October 31, 2007 at 6:47 am
Well, again, there's not enough detail to start to give you very specific answers. It sounds like you're doing RBAR (row by agonizing row) processing. I'd strongly suggest you examine the code to see if you can reevaluate it in a set-based manner. That's what TSQL is for, working with sets. All these "we're retrieving data from table 'A' using a cursor" statements are indications of RBAR.
That said, I'd still suggest you look into temporary tables or table variables. I don't think either one should be used the way you're using them, but either one will work somewhat better than cursors.
Without sample code and sample structures and some degree of logic to why you have to do RBAR, you can only get vague suggestions as answers.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply