Exceeds CPU time:When I Fetch the data from linked server and Insert into temp tabl

• ramar.karthik

  SSC Rookie

  Points: 25

  More actions

  August 29, 2011 at 10:47 am

  #242436

  Requirement: IN CURSOR

  DECLARE CURS_USER CURSOR FOR

  select distinct ID from R..DimValue Dev_User

  WHERE NOT EXISTS

  (SELECT [USER_ID] FROM D..

  WHERE Dev_User.Ads_Id = .[USER_ID]) and Id is not null

  OPEN CURS_USER

  FETCH NEXT FROM CURS_USER INTO @USER_CURSOR

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SELECT

  @USER_ID=Id,

  @FULL_NM=Dimension_value,

  @EMAIL_AD_TX =Email_Id FROM RPC..Dimension_Value where Id = @USER_CURSOR

  INSERT INTO D.. ([USER_ID], [FULL_NM], [EMAIL_AD_TX], [CREAT_TS], [LST_UPDT_TS])

  VALUES (@USER_ID,@FULL_NM,@EMAIL_AD_TX,getdate(),null )

  FETCH NEXT FROM CURS_USER INTO @USER_CURSOR

  END

  CLOSE CURS_USER

  DEALLOCATE CURS_USER

  My Solution in SETBASED QUERY:

  I am trying two ways

  1.Fetch value from one server store it in tmp table, Using this i can fill another table.

  2.Direct Insert.

  FOR EX:

  INSERT INTO D.. ([USER_ID], [FULL_NM], [EMAIL_AD_TX], [CREAT_TS], [LST_UPDT_TS])

  SELECT Id,Dimension_value,Email_Id,getdate(),null FROM RPC..Dimension_Value WHERE ID IN

  (SELECT distinct Id from R..DimValue Dev_User

  WHERE NOT EXISTS (SELECT [USER_ID] FROM D..

  WHERE Dev_User.Id = .[USER_ID]) and Id is not null)

  Any one help me to reduce CPU timing while using this set based query?

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  August 29, 2011 at 11:37 am

  #1374613

  Depending on the size of the tables and the indexes in place it may be a bit faster to use inner joins instead of nested exists.

  I might have missed something in the joins but I think something like this should get you started:

  SELECT Id,Dimension_value,Email_Id,getdate(),null

  FROM RPC..Dimension_Value dv

  join R..DimValue Dev_User du on du.Id = dv.ID

  join D.. u on u.[USER_ID] = du.Id

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/