November 25, 2005 at 9:19 am
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.
Query:
CREATE CLUSTERED INDEX IDX_History_ProductAccounts_ProductAccountID ON History_ProductAccounts1 (ProductAccountID)
CREATE CLUSTERED INDEX IDX_History_ProductAccountDocuments_ProductAccountID 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.ProductAccountId,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.OpenedDate = b.OpenedDate) or (a.OpenedDate is null and b.OpenedDate is null))
and ((a.ClosedDate = b.ClosedDate) or (a.ClosedDate is null and b.ClosedDate is null))
and ((a.NSCCActiveDate = b.NSCCActiveDate) or (a.NSCCActiveDate is null and b.NSCCActiveDate is null))
and ((a.NAVCodeID = b.NAVCodeID) or (a.NAVCodeID is null and b.NAVCodeID is null))
and ((a.LOIAmount = b.LOIAmount) or (a.LOIAmount is null and b.LOIAmount is null))
and ((a.LOIExpirationDate = b.LOIExpirationDate) or (a.LOIExpirationDate is null and b.LOIExpirationDate is null))
and ((a.DistributionTypeID = b.DistributionTypeID) or (a.DistributionTypeID is null and b.DistributionTypeID is null))
and ((a.PositionDate = b.PositionDate) or (a.PositionDate is null and b.PositionDate is null))
and ((a.PositionSourceID = b.PositionSourceID) or (a.PositionSourceID is null and b.PositionSourceID is null))
and ((a.Quantity = b.Quantity) or (a.Quantity is null and b.Quantity is null))
and ((a.TeamID = b.TeamID) or (a.TeamID is null and b.TeamID is null))
and a.Source = b.Source
and ((a.PendingBy = b.PendingBy) or (a.PendingBy is null and b.PendingBy is null))
and ((a.PendingDate = b.PendingDate) or (a.PendingDate is null and b.PendingDate is null))
and ((a.MatchedBy = b.MatchedBy ) or (a.MatchedBy is null and b.MatchedBy is null))
and ((a.MatchedDate = b.MatchedDate) or (a.MatchedDate is null and b.MatchedDate is null))
and ((a.RegTextLine1 = b.RegTextLine1 ) or (a.RegTextLine1 is null and b.RegTextLine1 is null))
and ((a.RegTextLine2 = b.RegTextLine2) or (a.RegTextLine2 is null and b.RegTextLine2 is null))
and ((a.RegTextLine3 = b.RegTextLine3 ) or (a.RegTextLine3 is null and b.RegTextLine3 is null))
and ((a.RegTextLine4 = b.RegTextLine4) or (a.RegTextLine4 is null and b.RegTextLine4 is null))
and ((a.RegTextLine5 = b.RegTextLine5 ) or (a.RegTextLine5 is null and b.RegTextLine5 is null))
and ((a.RegTextLine6 = b.RegTextLine6 ) or (a.RegTextLine6 is null and b.RegTextLine6 is null))
and ((a.RegTextLine7 = b.RegTextLine7 ) or (a.RegTextLine7 is null and b.RegTextLine7 is null))
and ((a.AddressLineCode = b.AddressLineCode) or (a.AddressLineCode is null and b.AddressLineCode is null))
and ((a.SocialCode = b.SocialCode ) or (a.SocialCode is null and b.SocialCode is null))
and ((a.ReviewedBy = b.ReviewedBy) or (a.ReviewedBy is null and b.ReviewedBy is null))
and ((a.ReviewedDate = b.ReviewedDate ) or (a.ReviewedDate is null and b.ReviewedDate is null))
and ((a.StateID = b.StateID) or (a.StateID is null and b.StateID is null))
and ((a.ZipCode = b.ZipCode ) or (a.ZipCode is null and b.ZipCode is null))
and ((a.LastMaintenanceDate = b.LastMaintenanceDate ) or (a.LastMaintenanceDate is null and b.LastMaintenanceDate is null))
and ((a.DealerLevelControlCode = b.DealerLevelControlCode ) or (a.DealerLevelControlCode is null and b.DealerLevelControlCode is null))
and ((a.SSNStatusCode = b.SSNStatusCode ) or (a.SSNStatusCode is null and b.SSNStatusCode is null))
and ((a.ReinvestToOtherAccount = b.ReinvestToOtherAccount ) or (a.ReinvestToOtherAccount is null and b.ReinvestToOtherAccount is null))
and ((a.ROALinkageID = b.ROALinkageID ) or (a.ROALinkageID is null and b.ROALinkageID is null))
and ((a.PenaltyWithholding = b.PenaltyWithholding ) or (a.PenaltyWithholding is null and b.PenaltyWithholding is null))
and ((a.AccruedDividend = b.AccruedDividend ) or (a.AccruedDividend is null and b.AccruedDividend is null))
and ((a.LOINumber = b.LOINumber) or (a.LOINumber is null and b.LOINumber is null))
and ((a.EmployeeFlag = b.EmployeeFlag ) or (a.EmployeeFlag is null and b.EmployeeFlag is null))
and ((a.PositionFileSeqNum = b.PositionFileSeqNum ) or (a.PositionFileSeqNum is null and b.PositionFileSeqNum is null))
and ((a.LastTransactionAppliedDate = b.LastTransactionAppliedDate ) or (a.LastTransactionAppliedDate is null and b.LastTransactionAppliedDate is null))
and ((a.TIN_2 = b.TIN_2 ) or (a.TIN_2 is null and b.TIN_2 is null))
and ((a.SSNStatusCode_2 = b.SSNStatusCode_2 ) or (a.SSNStatusCode_2 is null and b.SSNStatusCode_2 is null))
and ((a.LOILinkageID = b.LOILinkageID ) or (a.LOILinkageID is null and b.LOILinkageID is null))
and ((a.SponsorStatusID = b.SponsorStatusID ) or (a.SponsorStatusID is null and b.SponsorStatusID is null))
and ((a.ShortSaleQuantity = b.ShortSaleQuantity) or (a.ShortSaleQuantity is null and b.ShortSaleQuantity is null))
and ((a.MTD_ProfitLoss = b.MTD_ProfitLoss ) or (a.MTD_ProfitLoss is null and b.MTD_ProfitLoss is null))
and ((a.SettleDateQuantity = b.SettleDateQuantity ) or (a.SettleDateQuantity is null and b.SettleDateQuantity is null))
and ((a.SegregatedQuantity = b.SegregatedQuantity ) or (a.SegregatedQuantity is null and b.SegregatedQuantity is null))
and ((a.TransitQuantity = b.TransitQuantity ) or (a.TransitQuantity is null and b.TransitQuantity is null))
and ((a.TransferQuantity = b.TransferQuantity ) or (a.TransferQuantity is null and b.TransferQuantity is null))
and ((a.LegalTransferQuantity = b.LegalTransferQuantity ) or (a.LegalTransferQuantity is null and b.LegalTransferQuantity is null))
and ((a.NonNegotiableQuantity = b.NonNegotiableQuantity ) or (a.NonNegotiableQuantity is null and b.NonNegotiableQuantity is null))
and ((a.NSCCRequestedDate = b.NSCCRequestedDate ) or (a.NSCCRequestedDate is null and b.NSCCRequestedDate is null))
and a.RequestTransmissionFlag = b.RequestTransmissionFlag
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,@ProductAccountID1,@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,@ProductAccountID1,@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, OpenedDate = H.OpenedDate, ClosedDate = H.ClosedDate,
NSCCActiveDate= H.NSCCActiveDate, NAVCodeID = H.NAVCodeID, LOIAmount= H.LOIAmount,
LOIExpirationDate = H.LOIExpirationDate, DistributionTypeID = H.DistributionTypeID,
PositionDate = H.PositionDate, PositionSourceID = H.PositionSourceID,
Quantity = H.Quantity, TeamID = H.TeamID, Source = H.Source, PendingBy = H.PendingBy,
PendingDate = H.PendingDate, MatchedBy = H.MatchedBy, MatchedDate = H.MatchedDate,
RegTextLine1 = H.RegTextLine1, RegTextLine2 = H.RegTextLine2, RegTextLine3 = H.RegTextLine3,
RegTextLine4 = H.RegTextLine4, RegTextLine5 = H.RegTextLine5, RegTextLine6 = H.RegTextLine6,
RegTextLine7 = H.RegTextLine7, AddressLineCode = H.AddressLineCode, SocialCode = H.SocialCode,
ReviewedBy = H.ReviewedBy, ReviewedDate = H.ReviewedDate, StateID = H.StateID,
ZipCode = H.ZipCode, LastMaintenanceDate = H.LastMaintenanceDate,
DealerLevelControlCode = H.DealerLevelControlCode, SSNStatusCode = H.SSNStatusCode,
ReinvestToOtherAccount = H.ReinvestToOtherAccount, ROALinkageID = H.ROALinkageID,
PenaltyWithholding = H.PenaltyWithholding, AccruedDividend = H.AccruedDividend,
LOINumber = H.LOINumber, EmployeeFlag = H.EmployeeFlag, PositionFileSeqNum = H.PositionFileSeqNum,
LastTransactionAppliedDate = H.LastTransactionAppliedDate, TIN_2 = H.TIN_2, SSNStatusCode_2 = H.SSNStatusCode_2,
LOILinkageID = H.LOILinkageID, SponsorStatusID = H.SponsorStatusID, ShortSaleQuantity = H.ShortSaleQuantity,
MTD_ProfitLoss = H.MTD_ProfitLoss, SettleDateQuantity = H.SettleDateQuantity,
SegregatedQuantity = H.SegregatedQuantity, TransitQuantity = H.TransitQuantity,
TransferQuantity = H.TransferQuantity, LegalTransferQuantity = H.LegalTransferQuantity,
NonNegotiableQuantity = H.NonNegotiableQuantity, NSCCRequestedDate = H.NSCCRequestedDate,
RequestTransmissionFlag = H.RequestTransmissionFlag, 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_ProductAccounts_ProductAccountID DROP INDEX History_ProductAccountDocuments1.IDX_History_ProductAccountDocuments_ProductAccountID
Thanks in advance
Regards
LakshmiPK
November 28, 2005 at 8:00 am
This was removed by the editor as SPAM
November 28, 2005 at 1:42 pm
That query is huge.
I'll look into it tomorrow
December 1, 2005 at 6:42 am
do you need ansi nulls off ?
I usually make use of the showplan in QA to improve performance.
As with anything large and complex usually breaking down into smaller parts may help.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 1, 2005 at 4:10 pm
From my quick look at the sql you posted, it looks like you are using the cursor to select a couple of values then doing an insert and an update using those values.
For each set of values retrieved, you keep repeating the inserts and updates.
Why not use the set theory that SQL was designed for. Basically what you have to do is reuse that huge uber query in the insert and the update.
INSERT table
(column list)
SELECT
(value list)
FROM
(huge uber query)
UPDATE table
SET
(columns) = (values)
FROM
TABLE
JOIN (huge uber query)
Using this, the insert and the update work with multiple rows at a single time.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply