CODEWITHSUNDEEP
sqlsql-servercursor
Code Rider
Code Rider

Reputation: 2063

How to improve the performance of a Cursor in SQL Server

I have a table tblLogins which has 200k users data saved in it.

I need to insert 30 records for each user in an another table. I used the cursor for this task. But the script I have written takes a lot of time.

It has inserted data only for 60 thousand users in 2 hours.

I have looked over google for the solution but didn't find anything related to improve the performance.

Below is the script I have written.

DECLARE @LoginID int
DECLARE @DomainID int

DECLARE curDomain CURSOR FAST_FORWARD 
FOR SELECT tbldomains_id FROM  tblDomains

OPEN curDomain 

FETCH NEXT FROM curDomain INTO @DomainID

WHILE @@FETCH_STATUS = 0

BEGIN
 --cur2 starts

 DECLARE curLogin CURSOR FAST_FORWARD 
 FOR SELECT tbllogins_id FROM  tbllogins where tbldomains_id = @DomainID

 OPEN curLogin 

 FETCH NEXT FROM curLogin INTO @LoginID

 WHILE @@FETCH_STATUS = 0

 BEGIN

 --code starts

 if not exists(select 1 from tblWidgetProperties where tblLogin_id = @LoginID)
 begin
 Insert tblWidgetProperties values(1,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(2,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(3,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(4,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(5,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(6,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(7,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(8,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(9,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(10,@LoginID,'isEnabled','True')
 Insert tblWidgetProperties values(11,@LoginID,'isEnabled','True')
 end

 if not exists(select 1 from tblWidgetPosition where tblLogins_id = @LoginID)
 begin
 Insert tblWidgetPosition values(3,1.0,@LoginID)
 Insert tblWidgetPosition values(4,1.01,@LoginID)
 Insert tblWidgetPosition values(5,1.02,@LoginID)
 Insert tblWidgetPosition values(11,1.03,@LoginID)
 Insert tblWidgetPosition values(1,2.00,@LoginID)
 Insert tblWidgetPosition values(7,2.01,@LoginID)
 Insert tblWidgetPosition values(9,2.02,@LoginID)
 Insert tblWidgetPosition values(8,2.03,@LoginID)
 Insert tblWidgetPosition values(6,3.0,@LoginID)
 Insert tblWidgetPosition values(2,3.01,@LoginID)
 Insert tblWidgetPosition values(10,3.02,@LoginID)
 end

 --code ends

 FETCH NEXT FROM curLogin INTO @LoginID

 END

 CLOSE curLogin 

 DEALLOCATE curLogin 

 --cur2 ends

 FETCH NEXT FROM curDomain INTO @DomainID

 END

Upvotes: 0

Views: 6990

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

You should be able to write these as just two inserts, no cursors at all

Something like:

;WITH NewData AS (
   SELECT 1 as n UNION ALL
   SELECT 2 as n UNION ALL
   SELECT 3 as n UNION ALL
   SELECT 4 as n UNION ALL
   SELECT 5 as n UNION ALL
   SELECT 6 as n UNION ALL
   SELECT 7 as n UNION ALL
   SELECT 8 as n UNION ALL
   SELECT 9 as n UNION ALL
   SELECT 10 as n UNION ALL
   SELECT 12 as n
)
INSERT INTO tblWidgetProperties (/* Some column list, currently unknown */)
SELECT nd.n,tl.tbllogins_id,'isEnabled','true'
FROM
    NewData nd
       cross join
    tblLogins tl
WHERE
    tl.tbldomains_id in (select tbldomains_id from tblDomains) and
    tl.tbllogins_id not in (select tblLogin_id from tblWidgetProperties)

Exercise left for the reader to perform essentially the same transformation for the other target table. If the data varies per-row then add more columns in the NewData CTE. If the data is fixed for all rows, keep the values inline in the select, as shown above.

Upvotes: 4

user1694674
user1694674

Reputation: 126

You are doing an awful lot of inserts. To lower the amount of inserts, try creating two temporary tables. One for each set of inserts. Then you could do something like.

if not exists(select 1 from tblWidgetProperties where tblLogin_id = @LoginID)
 begin
    insert into tblWidgetProperties 
    Select [1],@LoginID,[2],[3]) from #tmpWidgetProperties
 end

if not exists(select 1 from tblWidgetPosition where tblLogins_id = @LoginID)
 begin
    Insert tblWidgetPosition 
    select [1], [2], @LoginID from #tmpWidgetPositions
 end

But before doing this I would take a look at CTE and MERGE.

Cheers Martin

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

Inserting row by row can be very slow. Prepare the data into a CSV file and use BULK INSERT to do the job. Be aware of the special characters in the data that may spoil the inserts.

BULK INSERT tblWidgetProperties
FROM 'c:\temp\WidgetProperties.tbl'  
WITH  
  (  
     FIELDTERMINATOR =',',  
     ROWTERMINATOR = '\n'
   );

If BULK INSERT is not an option then you should monitor what is slowing down your inserts. Disabling triggers on tables where you insert may help.

Upvotes: 0

Related Questions