How do I optimise this query?

• oupa sebuiwa

  Newbie

  Points: 2

  More actions

  September 25, 2008 at 3:50 am

  #72130

  This query is very very slow can anyone help please

  /*

  TABLE_WITHKEYS has about 11 millions records

  LASTKEYTABLE always hold 1 record because when the link server goes down the cursor will use the record in this table to position itself

  */

  DECLARE @start DATETIME

  SET @start = CURRENT_TIMESTAMP

  DECLARE @oKey INT,@oTrade_ID INT,@oRec_Date varchar (20)

  DECLARE @LastTableRows INT

  DECLARE @QUERY1 nvarchar(4000), @QUERY2 nvarchar(4000)

  DECLARE @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)

  --Declare and initialize the cursor

  DECLARE MYCURSED_CURSOR CURSOR FOR

  SELECT [Key],[Trade_Id],[Rec_Date] FROM TABLE_WITHKEYS

  WHERE [Key] = (select [Key] from LastKeyTable where [Key] is not null)

  AND [Trade_id] >(select [Trade_Id] from LastKeyTable where [Trade_Id] is not null)

  OR [Key] >(select [Key] from LastKeyTable where [Key] is not null)

  OPEN MYCURSED_CURSOR

  --Fetch the first row

  FETCH NEXT FROM MYCURSED_CURSOR INTO @oKey, @oTrade_ID,@oRec_Date

  --When the initial fetch was successfull

  WHILE @@FETCH_STATUS = 0

  BEGIN

  BEGIN TRANSACTION

  --Clear the LastKeyTable

  TRUNCATE TABLE LastKeyTable

  --Add the latest Key & Transaction ID

  INSERT INTO dbo.LastKeyTable

  VALUES(@oKey,@oTrade_ID,@oRec_Date)

  COMMIT TRANSACTION

  --Query the prod mirror using the latest key

  SET @LinkedServer = 'REMOTESERVER'

  SET @QUERY1='SELECT [REC_DATE],[EXTRACTED_IND],,[TRADE_ID],

  [TRAN_TYPE],[PAY_METH],[PAY_VEHICLE],LEFT([REC_BANKID],35) AS [REC_BANKID],

  LEFT([REC_ACCOUNT],35) AS [REC_ACCOUNT] ,LEFT([SENDER_TEXT],35) AS [SENDER_TEXT] ,LEFT([REC_NAME],35) AS [REC_NAME] ,[AMOUNT],

  [SENDER_REF],[BACK_OFFICE_CODE],LEFT([SENDER_ACC],35)AS [SENDER_ACC],LEFT([CDD],3) AS [CDD],

  LEFT([A1_REC],35) AS [A1_REC] ,LEFT([A2_REC],16) AS [A2_REC] ,LEFT([INV_PTF_NAME],16) AS [INV_PTF_NAME],[ORDER_DATE],[SETTLE_DATE],

  [CURRENCY],[AGGREGATE_ORDER],[DR_CR_IND],[PROCESSED],[PROCESSED_DATE],

  [ACTION],[DISTRIBUTION_NO],[ADJUSTMENT_NO],[BUS_FUNC_CLASS],[POLICY_ID],

  LEFT([CLIENT_CSN],50) AS [CLIENT_CSN],LEFT([CLIENT_NAME],30) AS [CLIENT_NAME],LEFT([CLIENT_SURNAME],30) AS [CLIENT_SURNAME],LEFT([COMPANY_NAME],50) AS

  [COMPANY_NAME],LEFT([APPLICATION_NO],15) AS [APPLICATION_NO],

  [INVESTMENT],[T0_IND],LEFT([REC_COMPANY],10) AS [REC_COMPANY],[DEALSHEET_REF],[ORDER_REF_GRP] FROM REMOTETABLE

  WHERE = ' + cast(@oKey as varchar(15)) + ''+' AND [TRADE_ID]= ' ++ cast(@oTrade_ID as varchar(15)) + ''

  SET @TSQL = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''+@QUERY1+''') '

  INSERT INTO REMOTETABLE_COPY ([REC_DATE],[EXTRACTED_IND],,[TRADE_ID],

  [TRAN_TYPE],[PAY_METH],[PAY_VEHICLE],[REC_BANKID],

  [REC_ACCOUNT],[SENDER_TEXT],[REC_NAME],[AMOUNT],

  [SENDER_REF],[BACK_OFFICE_CODE],[SENDER_ACC],[CDD],

  [A1_REC],[A2_REC],[INV_PTF_NAME],[ORDER_DATE],[SETTLE_DATE],

  [CURRENCY],[AGGREGATE_ORDER],[DR_CR_IND],[PROCESSED],[PROCESSED_DATE],

  [ACTION],[DISTRIBUTION_NO],[ADJUSTMENT_NO],[BUS_FUNC_CLASS],[POLICY_ID],

  [CLIENT_CSN],[CLIENT_NAME],[CLIENT_SURNAME],[COMPANY_NAME],[APPLICATION_NO],

  [INVESTMENT],[T0_IND],[REC_COMPANY],[DEALSHEET_REF],[ORDER_REF_GRP])

  EXEC(@TSQL)

  FETCH NEXT FROM MYCURSED_CURSOR INTO @oKey, @oTrade_ID,@oRec_Date

  END

  IF @@FETCH_STATUS <> 0

  -- BEGIN

  -- BEGIN TRANSACTION

  -- SET @LastTableRows = (SELECT COUNT(*) FROM LastKeyTable)

  -- IF @LastTableRows > 0

  -- BEGIN

  -- TRUNCATE TABLE LastKeyTable

  -- END

  --

  -- ELSE

  -- --Insert last keys

  -- BEGIN

  -- INSERT INTO dbo.LastKeyTable

  -- VALUES(@oKey,@oTrade_ID)

  -- END

  --

  -- --Insert and ensure no duplicates

  -- BEGIN

  -- INSERT INTO ErrorKeyTable([Key],[Trade_Id])

  --SELECT [Key],[Trade_Id] FROM LastKeyTable

  -- WHERE [Key] not in (SELECT [Key] FROM ErrorKeyTable)

  -- END

  --

  -- COMMIT TRANSACTION

  -- END

  CLOSE MYCURSED_CURSOR

  --Release the resources

  DEALLOCATE MYCURSED_CURSOR

• Jack Corbett

  SSC Guru

  Points: 184397

  More actions

  September 25, 2008 at 7:21 am

  #876977

  It will be very difficult to improve the performance of this query as you are using 2 poor-performing items, a cursor and a linked server.

  One thing that I have found that speeds up linked server queries is to create a stored procedure and call the stored procedure on the linked server.

  Also you have no order by on the cursor select so you are not guaranteed to get ascending order on the key column so you could potentially the last key value first.

  What is the final goal of this code?

  Have you tried a join across to the linked server.

  Select

  columns

  From

  table_withkeys T Join

  linkedserver.database.schema.remotetable R On

  T.key = R.key And

  T.trade_id = R.trade_id

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question