Hi,

Please help me how to improve performance of my query
Here is my query to delete duplicate records from a History tables and update the main tables.
ProductAccounts1 -- Main table
ProductAccountDocuments1 -- Child table
History_ProductAccounts1 -- History table for main table
History_ProductAccountDocuments1 -- History for child table
History table will contain all the version for a perticular account in the main and child tables
So depends on some business logic I have to find the duplicates (which will be having different versions but will have same data) in history tables and then I have to delete those duplicates and then rearrange all the versions and finally updating the main tables with latest version for a perticular account.
Here I created Clustered index for all the tables what ever I am using. with this performance improved a lot but still it is taking time as all the tables having huge data. I pasted few columns. the tables are having nearly 75 columns.

Query:
CREATE CLUSTERED INDEX IDX_History_ProductAccounts_ProductAccountID ON History_ProductAccounts1 (ProductAccountID)
CREATE CLUSTERED INDEX IDX_History_ProductAccountDocuments_ProductAccount ID ON History_ProductAccountDocuments1 (ProductAccountID)
GO
update statistics History_productaccounts1
go
DECLARE @ProductAccountID int
DECLARE @UpdateVersion int

DECLARE @ProductAccountID1 int
DECLARE @UpdateVersion1 int

SET ANSI_NULLS OFF

CREATE TABLE [dbo].[tmpAccounts_Versions] (
[ProductAccountID] [int] NOT NULL ,
[UpdateVersion] [int] NOT NULL)

CREATE CLUSTERED INDEX IDX_tmpAccounts_Versions_ProductAccountID ON tmpAccounts_Versions (ProductAccountID)

DECLARE MyCursor CURSOR FOR
SELECT a.ProductAccountId,a.UpdateVersion,b.ProductAccoun tId,b.UpdateVersion
FROM History_ProductAccounts1 AS a
JOIN History_ProductAccounts1 AS b
ON (b.ProductAccountId = (SELECT Max(z.ProductAccountId)
FROM History_ProductAccounts1 AS z
WHERE z.ProductAccountId <= a.ProductAccountId) and
b.UpdateVersion = (SELECT Max(z.UpdateVersion)
FROM History_ProductAccounts1 AS z
WHERE z.UpdateVersion < a.UpdateVersion and z.ProductAccountId <= a.ProductAccountId)
and a.RecStatus = b.RecStatus and a.UpdatedBy = b.UpdatedBy
and a.CreatedDate = b.CreatedDate and a.CreatedBy = b.CreatedBy
and a.ProductID = b.ProductID and a.AccountID = b.AccountID
and a.AccountTypeID = b.AccountTypeID
and ((a.SponsorAccount = b.SponsorAccount) or (a.SponsorAccount is null and b.SponsorAccount is null))
and a.Status = b.Status
and ((a.NSCCLastTransmissionDate = b.NSCCLastTransmissionDate ) or (a.NSCCLastTransmissionDate is null and b.NSCCLastTransmissionDate is null))
and ((a.RelatedTradeIndicator = b.RelatedTradeIndicator ) or (a.RelatedTradeIndicator is null and b.RelatedTradeIndicator is null))
and ((a.RelatedAcctNumber = b.RelatedAcctNumber ) or (a.RelatedAcctNumber is null and b.RelatedAcctNumber is null))
and ((a.BookPhysicalID = b.BookPhysicalID ) or (a.BookPhysicalID is null and b.BookPhysicalID is null))
and ((a.WithholdingIndicatorID = b.WithholdingIndicatorID ) or (a.WithholdingIndicatorID is null and b.WithholdingIndicatorID is null))
and ((a.ContractStateID = b.ContractStateID) or (a.ContractStateID is null and b.ContractStateID is null))
and a.Status in ('CLD','ICT'))



OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ProductAccountID,@UpdateVersion,@ProductAccountID 1,@UpdateVersion1
WHILE @@FETCH_STATUS = 0
BEGIN


IF Exists (select a.ProductAccountID, a.UpdateVersion, Count(*) from History_ProductAccountDocuments1 a
join History_ProductAccountDocuments1 b ON a.ProductAccountID = @ProductAccountID
and a.DocumentID = b.DocumentID where
a.ProductAccountID = @ProductAccountID and a.UpdateVersion = @UpdateVersion and
b.ProductAccountID = @ProductAccountID1 and b.UpdateVersion = @UpdateVersion1
and a.DocumentID = b.DocumentID and a.RecStatus = b.RecStatus
and a.RequiredFlag = b.RequiredFlag and a.IGOFlag = b.IGOFlag
and ((a.Explanation = b.Explanation) or (a.Explanation is null and b.Explanation is null))
group by a.ProductAccountID, a.UpdateVersion
having Count(*) = (select Count(documentID) from History_ProductAccountDocuments1 where
ProductAccountID = @ProductAccountID and UpdateVersion = @UpdateVersion
group by ProductAccountID, UpdateVersion)) or (NOT EXISTS (select * from History_ProductAccountDocuments1
where ProductAccountID = @ProductAccountID and UpdateVersion = @UpdateVersion) and
NOT EXISTS (select * from History_ProductAccountDocuments1
where ProductAccountID = @ProductAccountID1 and UpdateVersion = @UpdateVersion1))

BEGIN
insert into tmpAccounts_Versions values(@ProductAccountID,@UpdateVersion)

END

FETCH NEXT FROM MyCursor INTO @ProductAccountID,@UpdateVersion,@ProductAccountID 1,@UpdateVersion1
END
CLOSE MyCursor
DEALLOCATE MyCursor

select * into History_ProductAccountDocuments11 from History_ProductAccountDocuments1
select * into History_ProductAccounts11 from History_ProductAccounts1

TRUNCATE TABLE History_ProductAccountDocuments1
TRUNCATE TABLE History_ProductAccounts1

insert into History_ProductAccountDocuments1 select a.* from History_ProductAccountDocuments11 AS a
where a.UpdateVersion not in (select c.UpdateVersion from tmpAccounts_Versions C
where c.ProductAccountId = a.ProductAccountId)
order by a.ProductAccountId, a.UpdateVersion

insert into History_ProductAccounts1 select a.* from History_ProductAccounts11 AS a
where a.UpdateVersion not in (select c.UpdateVersion from tmpAccounts_Versions C
where c.ProductAccountId = a.ProductAccountId)
order by a.ProductAccountId, a.UpdateVersion

DROP TABLE History_ProductAccountDocuments11
DROP TABLE History_ProductAccounts11

DECLARE @Counter int
DECLARE MyCursor CURSOR FOR
select distinct ProductAccountID from tmpAccounts_Versions
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ProductAccountID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE MyCurH CURSOR FAST_FORWARD FOR
select ProductAccountID,UpdateVersion from History_ProductAccounts1
where ProductAccountID = @ProductAccountID
order by ProductAccountID, UpdateVersion

OPEN MyCurH

FETCH NEXT FROM MyCurH INTO @ProductAccountID1,@UpdateVersion1

SET @Counter = 1
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Counter = @Counter + 1
UPDATE History_ProductAccounts1 set UpdateVersion = @Counter where
ProductAccountID = @ProductAccountID1 and UpdateVersion = @UpdateVersion1

UPDATE History_ProductAccountDocuments1 set UpdateVersion = @Counter where
ProductAccountID = @ProductAccountID1 and UpdateVersion = @UpdateVersion1

FETCH NEXT FROM MyCurH INTO @ProductAccountID1,@UpdateVersion1

END
UPDATE History_ProductAccounts1 set LastVersion = 1 where
ProductAccountID = @ProductAccountID1 and UpdateVersion = @Counter

UPDATE History_ProductAccountDocuments1 set LastVersion = 1 where
ProductAccountID = @ProductAccountID1 and UpdateVersion = @Counter

UPDATE ProductAccounts1 set
UpdateVersion = H.UpdateVersion, EntryDate = H.EntryDate, RecStatus = H.RecStatus, LastVersion = 1,
LastUpdate = H.LastUpdate, UpdatedBy = H.UpdatedBy, CreatedDate = H.CreatedDate,
CreatedBy = H.CreatedBy, ProductID = H.ProductID, AccountID = H.AccountID,
AccountTypeID = H.AccountTypeID, SponsorAccount = H.SponsorAccount,
Status = H.Status, NSCCLastTransmissionDate = H.NSCCLastTransmissionDate,
RelatedTradeIndicator = H.RelatedTradeIndicator, RelatedAcctNumber = H.RelatedAcctNumber,
BookPhysicalID =H.BookPhysicalID, WithholdingIndicatorID = H.WithholdingIndicatorID,
ContractStateID = H.ContractStateID
FROM History_ProductAccounts1 H INNER JOIN ProductAccounts1 M
ON H.ProductAccountID = M.ProductAccountID where H.ProductAccountID = @ProductAccountID1
and H.UpdateVersion = @Counter


UPDATE ProductAccountDocuments1 set UpdateVersion = C.UpdateVersion,
RecStatus = C.RecStatus,RequiredFlag = C.RequiredFlag,IGOFlag = C.IGOFlag,
Explanation = C.Explanation,LastVersion = 1
From History_ProductAccountDocuments1 C INNER JOIN ProductAccountDocuments1 M
ON C.ProductAccountID = M.ProductAccountID and C.DocumentID = M.DocumentID
where C.ProductAccountID = @ProductAccountID1
and C.UpdateVersion = @Counter

CLOSE MyCurH
DEALLOCATE MyCurH
FETCH NEXT FROM MyCursor INTO @ProductAccountID
END
CLOSE MyCursor
DEALLOCATE MyCursor

Drop table tmpAccounts_Versions
DROP INDEX History_ProductAccounts1.IDX_History_ProductAccoun ts_ProductAccountID
DROP INDEX History_ProductAccountDocuments1.IDX_History_Produ ctAccountDocuments_ProductAccountID
Thanks in advance
Regards
LakshmiPK