top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

What is the best way of removing a cursor from SQL procedure if we have large amount of data to process

+3 votes
115 views
What is the best way of removing a cursor from SQL procedure if we have large amount of data to process
posted Feb 28, 2014 by Prachi Agarwal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

You should always CLOSE the cursor variable and then DEALLOCATE the cursor variable with

CLOSE {MyCursorVariableNameHere}
DEALLOCATE  {MyCursorVariableNameHere}

Example:

USE tempdb;
GO
declare @c cursor;

set @c = CURSOR LOCAL FAST_FORWARD
FOR
SELECT [name]
FROM sys.databases
WHERE database_id > 4;

OPEN @c;
GO
SELECT * FROM sys.dm_exec_cursors(0);
GO
declare c CURSOR GLOBAL FAST_FORWARD
FOR
SELECT [name]
FROM sys.databases
WHERE database_id > 4;

OPEN c;
GO
SELECT * FROM sys.dm_exec_cursors(0);
GO
CLOSE c;
DEALLOCATE c;
GO
declare c CURSOR LOCAL FAST_FORWARD
FOR
SELECT [name]
FROM sys.databases
WHERE database_id > 4;

OPEN c;
GO
SELECT * FROM sys.dm_exec_cursors(0);
GO

AMB

answer Mar 12, 2014 by Amit Kumar Pandey
Similar Questions
+1 vote

I have data in table A as below

Assetid   attribute   value
    1546    Ins_date   05062011
    1546    status     active
    1546    X          10.4567
    1546    Y          27.56
    1546    size       17
    675     X          4.778
    675     Y          53.676
    675     depth      5
    675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

+2 votes

I currently have a problem on my backup server with very large number of small files in a large number of directories. I would like to delete them as fast as possible. Currently I use:

rsync -a --delete /empty_directory/ dir_to_clean/

I've read that rsync will be faster than rm or find. Can someone recommend something? I use an ext4 filesystem.

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...