How to improve the performance of a Cursor in SQL Server

Question

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

is reading from file an option for you? then use Bulk insert — Radim Bača, Aug 29 '17 at 08:39
It is not possible to serialize the data into a file on the server? — Radim Bača, Aug 29 '17 at 08:42
@RadimBača Actually I'm not an SQL expert. So i don't know that can be done or not. That's why I'm in this problem. If you provide any example then i can try. — Code Rider, Aug 29 '17 at 08:44
It is not about SQL. Writing into a file has to be done using some other language or tool. — Radim Bača, Aug 29 '17 at 08:45
@RadimBača OK, so you are asking to have everything in XML and then use the bulk insert. But to write XML for 2 lacs users, it also needs so much time. — Code Rider, Aug 29 '17 at 08:48

score 4 · Accepted Answer · answered Aug 29 '17 at 09:15

4

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.

answered Aug 29 '17 at 09:15

Damien_The_Unbeliever

234,701
27
340
448

Thanks. It works great. Inserted approx 37 lacs records in just 2 minutes. – Code Rider Aug 29 '17 at 11:18
@CodeRider Please don't use indian words here, people from other places will not understand them. – James Z Aug 29 '17 at 16:10
@JamesZ - I used to correct the usage. But I'm starting to switch around. I've seen lacs or lakhs used frequently enough that I know what is meant. I'm not sure why it's felt to be important enough to be a named unit in India but I wouldn't be surprised if it entered English soon. – Damien_The_Unbeliever Aug 29 '17 at 17:29
@JamesZ If you understood that it's an Indian word only and you know the meaning as well. Then other also would know. – Code Rider Sep 01 '17 at 04:40
@CodeRider No, of course not. That's totally ridiculous. You should use universal measurement like others and not any Indian words. – James Z Sep 02 '17 at 06:22

score 0 · Answer 2 · answered Aug 29 '17 at 08:51

0

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.

answered Aug 29 '17 at 08:51

Radim Bača

10,646
1
19
33

You program in C# and C++, so any of them to prepare the CSV file. I have no idea how you obtain the data. If it is appropriate to do it manually then do it. – Radim Bača Aug 29 '17 at 08:57

score 0 · Answer 3 · answered Aug 29 '17 at 09:04

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

How to improve the performance of a Cursor in SQL Server

3 Answers3