Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL query taking too long and not correct!

Author  Topic 

ranmandaman
Starting Member

4 Posts

Posted - 2013-07-18 : 05:51:38
Hi guys,
Im a new joiner to this forum Ive recently moved job and the SQL wiz who I used to ask for help all the time is no longer available to me :(

Ive been reading multiple books and sites and tried to fix this myself but getting stuck. I hope someone can help - I am faily new to SQL.

So I've got the following query which pulls all the trades and joins a few tables to it. The query takes less then a minute which is good but it slows down massively when I try to pull a query on whether a trade was found in a particular table (the commented section). Is anyone able to help me out or point me in the right direction?



With
CA_CLS_Audit As
( select OTPTradeId, ConfoAuditUserId,
ROW_NUMBER() OVER (PARTITION BY OTPTradeId, InformationType ORDER BY ConfoAuditDisplayDate DESC) As CARow
From ConfirmationAudit
where ConfoAuditSenderRef LIKE 'HBEUMN%' AND ConfoAuditUserId <> 'SYSTEM' AND InformationType = 'Audit'),
CA_CLS_Note As
( select OTPTradeId, ConfoAuditUserId,
ROW_NUMBER() OVER (PARTITION BY OTPTradeId, InformationType ORDER BY ConfoAuditDisplayDate DESC) As CARow
From ConfirmationAudit
where ConfoAuditSenderRef LIKE 'HBEUMN%' AND ConfoAuditUserId <> 'SYSTEM' AND InformationType = 'Note'),
C_Sent As
( select ConfoId, TradeId, ConfoSentAddrMethod, ConfoSentMessageStatus,
ROW_NUMBER() OVER (PARTITION BY TradeId ORDER BY TradeId DESC) As CSRow
From ConfirmationSent),
CA_Audit As
( select OTPTradeId, ConfoAuditUserId,
ROW_NUMBER() OVER (PARTITION BY OTPTradeId, InformationType ORDER BY ConfoAuditDisplayDate DESC) As CARow
From ConfirmationAudit
where ConfoAuditSenderRef LIKE 'HBEUM%' AND ConfoAuditUserId <> 'SYSTEM' AND InformationType = 'Audit'),
CA_Note As
( select OTPTradeId, ConfoAuditUserId,
ROW_NUMBER() OVER (PARTITION BY OTPTradeId, InformationType ORDER BY ConfoAuditDisplayDate DESC) As CARow
From ConfirmationAudit
where ConfoAuditSenderRef LIKE 'HBEUM%' AND ConfoAuditUserId <> 'SYSTEM' AND InformationType = 'Note')
Select
T.TradeId,
T.TradeDate,
T.TradeSettlementDate,
T.ProductFamily,
T.TradeStatus,
LE.OperationGroup,
LE.POId,
LE.ShortName,
c_s.ConfoId As 'CLS ConfoId',
ca_cls_a.ConfoAuditUserId as 'CLS Audit UserId',
ca_cls_n.ConfoAuditUserId as 'CLS Note UserId',
ca_a.ConfoAuditUserId as 'Non CLS Audit UserId',
ca_n.ConfoAuditUserId as 'Non CLS Note UserId',
--***SLOWS DOWN MASSIVELY ON THIS SECTION***
case when
(T.TradeId NOT IN (select distinct OTPTradeId from ConfirmationAudit where otptradeid is not null)
AND T.TradeId NOT IN (select distinct TradeId from ConfirmationSent))
Then 'No' Else 'Yes' End As 'TCM',
--*******************************************
case when
(ca_a.OTPTradeId IS NOT NULL or ca_cls_a.OTPTradeID IS NOT NULL)
Then 'Yes' Else 'No' End As 'Audit Exception',
case when
(ca_n.OTPTradeId IS NOT NULL or ca_cls_n.OTPTradeID IS NOT NULL)
Then 'Yes' Else 'No' End As 'Note Exception',
case when
(ca_a.OTPTradeId IS NOT NULL or ca_n.OTPTradeID IS NOT NULL OR
ca_cls_a.OTPTradeId IS NOT NULL or ca_cls_n.OTPTradeID IS NOT NULL)
Then 'Exception' Else 'Straight Through' End As 'Overall STP'
From
Trade T
left join LegalEntity LE on (LE.LegalEntityId = T.CounterpartyId AND LE.POId = T.POId)
left join C_Sent c_s on (c_s.TradeId = T.TradeId AND c_s.CSRow = 1)
left join CA_CLS_Audit ca_cls_a on (ca_cls_a.OTPTradeId = c_s.ConfoId AND ca_cls_a.CARow = 1)
left join CA_CLS_Note ca_cls_n on (ca_cls_n.OTPTradeId = c_s.ConfoId AND ca_cls_n.CARow = 1)
left join CA_Audit ca_a on (ca_a.OTPTradeId = T.TradeId AND ca_a.CARow = 1)
left join CA_Note ca_n on (ca_n.OTPTradeId = T.TradeId AND ca_n.CARow = 1)
Where
T.InternalExternal = 'External'
AND T.TradeSettlementDate BETWEEN '17 Jun 2013' and '17 JUN 2013'
AND LE.LegalEntityRole = 'Counterparty'
AND LE.POId IN
('6540358',
'1235429')
AND T.counterpartyid not in (2584704)
Order by 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 06:01:18
Have a look at execution plan and see what are the costly steps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ranmandaman
Starting Member

4 Posts

Posted - 2013-07-18 : 06:02:58
Im afraid I dont have access to the execution plan :(
Go to Top of Page

ranmandaman
Starting Member

4 Posts

Posted - 2013-07-18 : 06:02:58
Im afraid I dont have access to the execution plan :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 06:14:47
Sorry then there's not much we can help you with as we dont know anything about your tables structures,indexes present etc and neither has any idea on logic you tried to implement.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ranmandaman
Starting Member

4 Posts

Posted - 2013-07-18 : 08:47:49
Apologies - maybe I can explain what part is causing the slowdown and what I am trying to do?


The part that is slowing the whole process is

case when
(T.TradeId NOT IN (select distinct OTPTradeId from ConfirmationAudit where otptradeid is not null)
AND T.TradeId NOT IN (select distinct TradeId from ConfirmationSent))
Then 'No' Else 'Yes' End As 'TCM',

What this is supposed to do is take every Trade ID and check against 2 tables to see if it can find them in there. If it can find them in either of them then it returns a 'Yes' otherwise if it cannot find it then it returns 'No'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 09:27:30
try using NOT EXISTS instead of NOT IN or do LEFT JOIN with two tables and check for NULLs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-20 : 13:37:52
[code]CREATE TABLE #Stage
(
OtpTradeID INT NOT NULL,
ConfoAuditUserID INT NOT NULL,
InformationType VARCHAR(5) NOT NULL,
ConfoAuditSenderRef VARCHAR(20) NOT NULL,
ConfoAuditDisplayDate DATETIME NOT NULL
);

WITH cteSource(OTPTradeID, ConfoAuditUserID, InformationType, ConfoAuditSenderRef, ConfoAuditDisplayDate, theRow)
AS (
SELECT OTPTradeID,
ConfoAuditUserID,
InformationType,
ConfoAuditSenderRef,
ConfoAuditDisplayDate,
ROW_NUMBER() OVER (PARTITION BY OTPTradeID, InformationType, ConfoAuditSenderRef ORDER BY ConfoAuditDisplayDate DESC) AS theRow
FROM dbo.ConfirmationAudit
WHERE ConfoAuditSenderRef LIKE 'HBEUM%'
AND ConfoAuditUserId <> 'SYSTEM'
AND InformationType IN ('Note', 'Audit')
)
INSERT #Stage
(
OtpTradeID,
ConfoAuditUserID,
InformationType,
ConfoAuditSenderRef,
ConfoAuditDisplayDate
)
SELECT OTPTradeID,
ConfoAuditUserID,
InformationType,
ConfoAuditSenderRef,
ConfoAuditDisplayDate
FROM cteSource
WHERE theRow = 1;

CREATE UNIQUE CLUSTERED INDEX CX_Stage ON #Stage (OTPTradeID, InformationType, ConfoAuditSenderRef)

CREATE TABLE #Confirmation
(
ConfoID INT NOT NULL,
TradeID INT NOT NULL,
ConfoSentAddrMethod VARCHAR(20) NOT NULL,
ConfoSentMessageStatus VARCHAR(20) NOT NULL,
CSRow INT NOT NULL
);

INSERT #Confirmation
(
ConfoID,
TradeID,
ConfoSentAddrMethod,
ConfoSentMessageStatus,
CSRow
)
SELECT ConfoID,
TradeID,
ConfoSentAddrMethod,
ConfoSentMessageStatus,
ROW_NUMBER() OVER (PARTITION BY TradeID ORDER BY TradeID DESC) AS CSRow
FROM dbo.ConfirmationSent;

SELECT t.TradeID,
t.TradeDate,
t.TradeSettlementDate,
t.ProductFamily,
t.TradeStatus,
le.OperationGroup,
le.POId,
le.ShortName,
c_s.ConfoID AS [CLS ConfoId],
ca_cls_a.ConfoAuditUserId AS [CLS Audit UserId],
ca_cls_n.ConfoAuditUserId AS [CLS Note UserId],
ca_a.ConfoAuditUserId AS [Non CLS Audit UserId],
ca_n.ConfoAuditUserId AS [Non CLS Note UserId],
CASE
WHEN w.TradeID IS NULL THEN 'No'
ELSE 'Yes'
END AS TCM,
CASE
WHEN ca_a.OTPTradeId IS NULL AND ca_cls_a.OTPTradeID IS NULL Then 'No'
Else 'Yes'
End As 'Audit Exception',
CASE
WHEN ca_n.OTPTradeId IS NULL AND ca_cls_n.OTPTradeID IS NULL Then 'No'
Else 'Yes'
End As [Note Exception],
CASE
WHEN ca_a.OTPTradeId IS NULL AND ca_n.OTPTradeID IS NULL AND ca_cls_a.OTPTradeId IS NULL AND ca_cls_n.OTPTradeID IS NULL THEN 'Straight Through'
ELSE 'Exception'
END AS [Overall STP]
FROM dbo.Trade AS t
LEFT JOIN dbo.LegalEntity AS le ON le.LegalEntityID = t.CounterpartyID
AND le.POId = t.POId
LEFT JOIN #Confirmation AS c_s ON c_s.TradeId = t.TradeId
AND c_s.CSRow = 1
OUTER APPLY (
SELECT TOP(1) s.OTPTradeId,
s.ConfoAuditUserId
FROM #Stage AS s
WHERE s.ConfoAuditSenderRef LIKE 'HBEUMN%'
AND s.InformationType = 'Audit'
AND s.OTPTradeId = c_s.ConfoId
ORDER BY s.ConfoAuditDisplayDate
) AS ca_cls_a
OUTER APPLY (
SELECT TOP(1) s.OTPTradeId,
s.ConfoAuditUserId
FROM #Stage AS s
WHERE s.ConfoAuditSenderRef LIKE 'HBEUMN%'
AND s.InformationType = 'Note'
AND s.OTPTradeId = c_s.ConfoId
ORDER BY s.ConfoAuditDisplayDate
) AS ca_cls_n
OUTER APPLY (
SELECT TOP(1) s.OTPTradeId,
s.ConfoAuditUserId
FROM #Stage AS s
WHERE s.InformationType = 'Audit'
AND s.OTPTradeId = t.TradeID
ORDER BY s.ConfoAuditDisplayDate
) AS ca_a
OUTER APPLY (
SELECT TOP(1) s.OTPTradeId,
s.ConfoAuditUserId
FROM #Stage AS s
WHERE s.InformationType = 'Note'
AND s.OTPTradeId = t.TradeID
ORDER BY s.ConfoAuditDisplayDate
) AS ca_n
LEFT JOIN (
SELECT OTPTradeId AS TradeID
FROM dbo.ConfirmationAudit
WHERE otptradeid IS NOT NULL

UNION

SELECT TradeId
FROM #Confirmation
) AS w ON w.TradeID = t.TradeID
WHERE t.InternalExternal = 'External'
AND t.TradeSettlementDate BETWEEN '17 Jun 2013' AND '17 JUN 2013'
AND le.LegalEntityRole = 'Counterparty'
AND le.POId IN ('6540358', '1235429')
AND t.counterpartyid NOT IN (2584704)
ORDER BY t.TradeID;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -