vendredi 30 janvier 2015

Issues with Optimizing TSQL Script


I have a temp table that has a SecurityCode column. This SecurityCode column gets updated based off value from a CrossReference table.


The value selected from the CrossReference table depends on how much data that row contains.


So the first update matches on CustomIdentifier. The 2nd update checks if CrossReference table CounterpartyID col has a value and updates relevant to that row. The 3rd update checks if CrossReference table CounterpartyID and AccountCode columns have values and updates relevant to that row.



INSERT into @ManagerData
SELECT
ManagerID,
SecurityManager,
SecurityAccount,
null as SecurityCode,
Broker,
AssetType,
InvestmentType,
Description,
Currency,
sum(Quantity) as Quantity
FROM
dbo.ManagerData
WHERE (ManagerID = @ManagerID)
AND (Account = @Account)
GROUP BY ManagerID, Account, Broker,AssetType,InvestmentType,Description, Currency


-- Update with Code Cross Reference By Instrument Identifier Only
Update @ManagerData
Set SecurityCode = CrossReference.GenevaCode
From @ManagerData MD
Inner Join CrossReference
On MD.ManagerID = CrossReference.CustomIdentifier
WHERE CrossReference.CounterPartyID = ''
AND CrossReference.AccountCode = ''

-- Update with Code Cross Reference By CounterParty & Instrument Identifier
Update @ManagerData
Set SecurityCode = CrossReference.GenevaCode
From @ManagerData MD
Inner Join CrossReference
On MD.ManagerID = CrossReference.CustomIdentifier
AND MD.SecurityManager = CrossReference.CounterPartyID
WHERE CrossReference.AccountCode = ''

-- Update with Code Cross Reference By CounterParty, CounterParty Account & Instrument Identifier
Update @ManagerData
Set SecurityCode = CrossReference.GenevaCode
From @ManagerData MD
Inner Join CrossReference
On MD.ManagerID = CrossReference.CustomIdentifier
AND MD.SecurityManager = CrossReference.CounterPartyID
AND MD.SecurityAccount = CrossReference.AccountCode


How do I improve this script into a single query if possible? I have tried something similar to below...



SELECT
ManagerID,
SecurityManager,
SecurityAccount,
null as SecurityCode,
Broker,
AssetType,
InvestmentType,
Description,
Currency,
sum(Quantity) as Quantity
FROM
dbo.ManagerData MD
LEFT Join CrossReference cr
On MD.ManagerID = cr.CustomIdentifier
AND (cr.CounterPartyID = MD.SecurityManager OR cr.CounterPartyID = '')
AND (cr.AccountCode = MD.SecurityAccount OR cr.AccountCode = '')
WHERE (ManagerID = @ManagerID)
GROUP BY ManagerID, Account, Broker,AssetType,InvestmentType,Description, Currency




Aucun commentaire:

Enregistrer un commentaire