locked
Script is taking long time to execute RRS feed

  • Question

  • Hi:

    This is the query I am executing, this takes a lot of time, how can I use temp tables to make this work fast:

    There are only about 6000 records but even then taking lot of time

    SET NOCOUNT ON
     DECLARE @SName VARCHAR(40), @ContactNo varchar(11), @Code VARCHAR(20), @EMail VARCHAR(40),@CodeId int

    DECLARE C1 CURSOR
     STATIC FOR SELECT [Staff Name],[Contact No],[Code],[EMail ID] FROM AB
     OPEN C1
      IF @@CURSOR_ROWS > 0
       FETCH NEXT FROM C1 INTO @SName,@ContactNo,@Code,@EMail
       WHILE @@Fetch_status = 0
       BEGIN
        IF EXISTS (SELECT * FROM AddressBook WHERE  Name=@SName)
         BEGIN
          UPDATE AddressBook
          SET MobileNo=@ContactNo, EMailId=@EMail
          WHERE Name=@SName
         END
        ELSE
         BEGIN
          INSERT INTO AddressBook
          VALUES(@SName,'',@ContactNo,@EMail,'',GETDATE(),GETDATE(),'','A',8,NULL,NULL)
         END
       END
     CLOSE C1
     DEALLOCATE C1
     SET NOCOUNT OFF

    Thursday, March 6, 2014 8:32 AM

Answers

  • Instead of cursor have you tried to look at MERGE command?

    IF OBJECT_ID('t1') IS NOT NULL
        DROP TABLE t1 
    GO
    CREATE TABLE t1 (id INT PRIMARY KEY, name1 VARCHAR(10))
    INSERT INTO t1
    SELECT 1, 'name 1' UNION ALL
    SELECT 2, 'name 2' UNION ALL
    SELECT 3, 'name 3' UNION ALL
    SELECT 4, 'name 4' UNION ALL
    SELECT 5, 'name 5'
    GO 


    DECLARE @id INT = 6, @name1 VARCHAR(10) = 'name 6'

    MERGE t1
    USING (SELECT @id AS id, @name1 AS name1) AS t2 ON t1.id = t2.id
    WHEN MATCHED
        THEN UPDATE SET t1.name1 = t2.name1
    WHEN NOT MATCHED
        THEN INSERT VALUES(@id, @name1 );



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by eralper Thursday, March 6, 2014 9:07 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:56 AM
    Thursday, March 6, 2014 8:44 AM
    Answerer
  • MERGE AddressBook
    USING AB ON AddressBook.Name = AB.[Staff Name]
    WHEN NOT MATCHED THEN
       INSERT (col1, col2, col3, ...)
         VALUES([Staff Name], ''. [Contact No], '', getdate(), getdate(), '', 'A', 8)
    WHEN MATCHED THEN
        UPDATE
        SET MobileNo = AB.[Contact No],
            EmailId = AB.[Email Id]
    ;

    Note that this syntax:

      INSERT AdressBook VALUES (...)

    Without specifyin a column list is not acceptable in production code. You should always specify an explicit column list to insert into.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by eralper Thursday, March 6, 2014 9:08 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:56 AM
    Thursday, March 6, 2014 8:46 AM
  • Use MERGE

    Eg:(Not tested)

     Declare @sdate datetime =Getdate()
     
      MERGE AddressBook AS target
        USING (SELECT * From AB) AS source 
        ON (target.Name = source.[Staff Name])
        WHEN MATCHED THEN 
            UPDATE SET MobileNo=[Contact No], EMailId=[EMail ID]
    	WHEN NOT MATCHED THEN	
    	    INSERT (Name,....<list of columns>)
    	    VALUES ([Staff Name],'',[Contact No],[EMail ID],'',@sdate,'','A',8,NULL,NULL);

    • Proposed as answer by eralper Thursday, March 6, 2014 9:08 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 8:48 AM
  • May be you need this for SQL server 2005 :-

    Declare @sdate datetime =Getdate()
     

    insert into AddressBook (Name,....<list of columns>)
    select [Staff Name],'',[Contact No],[EMail ID],'',@sdate,'','A',8,NULL,NULL
    from AB as s
    left join AddressBook A on A.Name = s.[Staff Name]
    where A.Name is null

    update a set MobileNo=[Contact No], EMailId=[EMail ID]
    from AddressBook a join AB s on a.Name = s.[Staff Name]
    where isnull(a.MobileNo,-1) <> isnull(s.[Contact No],-1)
    or isnull(a.EMailId,'') <> isnull(s.[EMail ID],'')

    • Proposed as answer by Fanny Liu Friday, March 7, 2014 2:28 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 10:04 AM
  • Try the below: You do not need CURSOR. You may go with SET BASED approach to get more performance.

     update A SET A.MobileNo=B.[Contact No], A.EMailId=[EMail ID] 
     From AddressBook A
    	Inner Join ab B On A.Name = B.[Staff Name]
    	
    	Insert into AddressBook
    	Select A.[Staff Name],a.[Contact No],a.[EMail ID],'',GETDATE(),GETDATE(),'','A',8,NULL,NULL 
    	From AB A
    	 Left Join AddressBook B On B.Name = A.[Staff Name] Where B.Name is null


    • Edited by SQLZealots Thursday, March 6, 2014 10:07 AM
    • Proposed as answer by Fanny Liu Friday, March 7, 2014 2:28 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 10:06 AM
  • Thanks for your response, but I want this on 2005 and MERGE was introduced in 2008.

    Moral: always specify which version of SQL Server, so that you don't waste everyone's time, including yours. Had you said that you're on SQL 2005 from the start, we would have given you the UPDATE + INSERT solution directly.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 10:42 AM

All replies

  • Instead of cursor have you tried to look at MERGE command?

    IF OBJECT_ID('t1') IS NOT NULL
        DROP TABLE t1 
    GO
    CREATE TABLE t1 (id INT PRIMARY KEY, name1 VARCHAR(10))
    INSERT INTO t1
    SELECT 1, 'name 1' UNION ALL
    SELECT 2, 'name 2' UNION ALL
    SELECT 3, 'name 3' UNION ALL
    SELECT 4, 'name 4' UNION ALL
    SELECT 5, 'name 5'
    GO 


    DECLARE @id INT = 6, @name1 VARCHAR(10) = 'name 6'

    MERGE t1
    USING (SELECT @id AS id, @name1 AS name1) AS t2 ON t1.id = t2.id
    WHEN MATCHED
        THEN UPDATE SET t1.name1 = t2.name1
    WHEN NOT MATCHED
        THEN INSERT VALUES(@id, @name1 );



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by eralper Thursday, March 6, 2014 9:07 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:56 AM
    Thursday, March 6, 2014 8:44 AM
    Answerer
  • MERGE AddressBook
    USING AB ON AddressBook.Name = AB.[Staff Name]
    WHEN NOT MATCHED THEN
       INSERT (col1, col2, col3, ...)
         VALUES([Staff Name], ''. [Contact No], '', getdate(), getdate(), '', 'A', 8)
    WHEN MATCHED THEN
        UPDATE
        SET MobileNo = AB.[Contact No],
            EmailId = AB.[Email Id]
    ;

    Note that this syntax:

      INSERT AdressBook VALUES (...)

    Without specifyin a column list is not acceptable in production code. You should always specify an explicit column list to insert into.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by eralper Thursday, March 6, 2014 9:08 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:56 AM
    Thursday, March 6, 2014 8:46 AM
  • Use MERGE

    Eg:(Not tested)

     Declare @sdate datetime =Getdate()
     
      MERGE AddressBook AS target
        USING (SELECT * From AB) AS source 
        ON (target.Name = source.[Staff Name])
        WHEN MATCHED THEN 
            UPDATE SET MobileNo=[Contact No], EMailId=[EMail ID]
    	WHEN NOT MATCHED THEN	
    	    INSERT (Name,....<list of columns>)
    	    VALUES ([Staff Name],'',[Contact No],[EMail ID],'',@sdate,'','A',8,NULL,NULL);

    • Proposed as answer by eralper Thursday, March 6, 2014 9:08 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 8:48 AM
  • Thanks for your response, but I want this on 2005 and MERGE was introduced in 2008.

    Can you let me know how can I improve the performance in 2005 fro the Query

    Thursday, March 6, 2014 9:58 AM
  • May be you need this for SQL server 2005 :-

    Declare @sdate datetime =Getdate()
     

    insert into AddressBook (Name,....<list of columns>)
    select [Staff Name],'',[Contact No],[EMail ID],'',@sdate,'','A',8,NULL,NULL
    from AB as s
    left join AddressBook A on A.Name = s.[Staff Name]
    where A.Name is null

    update a set MobileNo=[Contact No], EMailId=[EMail ID]
    from AddressBook a join AB s on a.Name = s.[Staff Name]
    where isnull(a.MobileNo,-1) <> isnull(s.[Contact No],-1)
    or isnull(a.EMailId,'') <> isnull(s.[EMail ID],'')

    • Proposed as answer by Fanny Liu Friday, March 7, 2014 2:28 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 10:04 AM
  • Try the below: You do not need CURSOR. You may go with SET BASED approach to get more performance.

     update A SET A.MobileNo=B.[Contact No], A.EMailId=[EMail ID] 
     From AddressBook A
    	Inner Join ab B On A.Name = B.[Staff Name]
    	
    	Insert into AddressBook
    	Select A.[Staff Name],a.[Contact No],a.[EMail ID],'',GETDATE(),GETDATE(),'','A',8,NULL,NULL 
    	From AB A
    	 Left Join AddressBook B On B.Name = A.[Staff Name] Where B.Name is null


    • Edited by SQLZealots Thursday, March 6, 2014 10:07 AM
    • Proposed as answer by Fanny Liu Friday, March 7, 2014 2:28 AM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 10:06 AM
  • Thanks for your response, but I want this on 2005 and MERGE was introduced in 2008.

    Moral: always specify which version of SQL Server, so that you don't waste everyone's time, including yours. Had you said that you're on SQL 2005 from the start, we would have given you the UPDATE + INSERT solution directly.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 5:57 AM
    Thursday, March 6, 2014 10:42 AM