SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL query taking too long and not correct!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranmandaman
Starting Member

4 Posts

Posted - 07/18/2013 :  05:51:38  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/18/2013 :  06:01:18  Show Profile  Reply with Quote
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 - 07/18/2013 :  06:02:58  Show Profile  Reply with Quote
Im afraid I dont have access to the execution plan :(
Go to Top of Page

ranmandaman
Starting Member

4 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  06:14:47  Show Profile  Reply with Quote
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 - 07/18/2013 :  08:47:49  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/18/2013 :  09:27:30  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 07/20/2013 :  13:37:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000