March 16, 2006 at 3:11 am
hi,
I have a table having some 10000 records in my database from this i want to take all the records one by one from the table and do some functional operations. For this i am using a cursor inside the stored procedure. The procedure takes long time to execute because of the cursor i used. Is there any other way to perform this with out using a cursor? I need a best solution to increase performance.
Manokarnan
March 16, 2006 at 3:27 am
If it is really needed that the records are handled one by one, the best solution is to write a program VB, VB.NET, C#,...
which extracts the records and processes them one by one.
March 16, 2006 at 7:00 am
What is your requirement ? Can you perform it using set base operation ?
March 16, 2006 at 10:00 am
If this is really required, and a set based operation is not an option, then another way to do this would be:
Create an Int variable and set it to 0
Creat and Int variable and set it to the max rows to process
Use a While statement for while rows <= Maxrows do something
At the end of the While, before the end, increment the counter for rows
Example:
Declare @RowCnt INT
Declare @MaxRows Int
Set @RowCnt = 0
Set @MaxRows = Count (field) From Table
While @RowCnt <= @MaxRow
Begin
do something here for processing the row
Set @RowCnt = @RowCnt + 1
End
This will loop your records, allowing you to walk the table, without using a cursor and incurring the overhead of the cursor. It is also easy to code, and easy for someone else to follow.
March 17, 2006 at 5:43 am
You can use table variables.
Declare @process table (col1 int,..)
Declare @pk int -- this may vary according to your primary key
Insert @process select .. from tablename
while exists (select top 1 * @process)
begin
--your process.....
delete @process where pk = @pk
end
--This is a hint only
R
March 21, 2006 at 4:27 am
Hi,
I have an idea. You can use the temporary tables in place of cursors.
Regards,
Amit Gupta ..
March 22, 2006 at 5:28 am
hi,
Can you please give me a simple example for using temporary tables instead of cursors.
Regards,
Manokarnan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply