September 25, 2008 at 3:50 am
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
September 25, 2008 at 7:21 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply