sql select in list take long time to execute

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

General SQL Server Forums

New to SQL Server Programming

sql select in list take long time to execute

Author

Topic

jimmy2090
Starting Member

26 Posts

Posted - 2015-04-29 : 02:25:14

hi all,
here is my sql script.


		select distinct b.TrxhdID
	from CustomerMaster a(nolock), TransactionHd b(nolock), TransactionDetail c(nolock), TimeSlot d(nolock), CustomerAgent e
	
	where a.CustomerCode = b.CustomerCode
	and b.TrxhdId = c.TrxhdId
	and c.timeSlotID = d.timeSlotID
	and d.ProductId = '1787'
	
	and ((a.CustomerName like  'test')  )
	or ( e.TrxHdID = b.TrxhdID and b.TrxhdID in (	select distinct TrxHdID from CustomerAgent e(nolock) where  e.CustomerName like 'test'  )) --new add

any idea? it take very long time to execute after i add the or script.

jimmy2090
Starting Member

26 Posts

Posted - 2015-04-29 : 03:13:21

after i change to below, it takes 16 seconds to execute.
anyone please help.

	
	select distinct b.TrxhdID
	from CustomerMaster a(nolock), TransactionHd b(nolock), TransactionDetail c(nolock), TimeSlot d(nolock)
	
	where (a.CustomerCode = b.CustomerCode
	and b.TrxhdId = c.TrxhdId
	and c.timeSlotID = d.timeSlotID
	and d.ProductId = '1734')

	and (a.CustomerName like  'loh'
	or b.TrxhdID in (	select distinct TrxHdID from CustomerAgent e where  e.CustomerName like 'loh'))

jimmy2090
Starting Member

26 Posts

Posted - 2015-04-29 : 03:15:58

this is original code, it take 0 second to execute.

select distinct b.TrxhdID
	from CustomerMaster a(nolock), TransactionHd b(nolock), TransactionDetail c(nolock), TimeSlot d(nolock)
	
	where (a.CustomerCode = b.CustomerCode
	and b.TrxhdId = c.TrxhdId
	and c.timeSlotID = d.timeSlotID
	and d.ProductId = '1734')

	and a.CustomerName like  'loh'

Kristen
Test

22859 Posts

Posted - 2015-04-29 : 09:48:53

First thing I try when I have performance problems on an "OR" is to split the OR to use a UNION instead

NOLOCK = Really REALLY bad practice. You really REALLY want to avoid using that, unless the ONLY people who are using the output of this query are DBAs, and they know what the consequences are.

It looks like an end user report?? in which case you definitely should not be using NOLOCK if users will be making business decisions based on what they see - because what they see may be entirely the wrong data. Some transactions twice, some missing, sometimes errors. Then this happens if the REFRESH/rerun the report they will see a different answer

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-29 : 10:11:56

Try:


SELECT TrxhdID
FROM TransactionHd T
WHERE EXISTS
(
	SELECT 1
	FROM CustomerMaster M
	WHERE M.CustomerCode = T.CustomerCode
		AND
		(
			M.CustomerName = 'loh'
			OR EXISTS
			(
				SELECT 1
				FROM CustomerAgent A
				WHERE A.TrxHdID = T.TrxHdID
					AND A.CustomerName = 'loh'
			)
		)
)
	AND EXISTS
	(
		SELECT 1
		FROM TransactionDetail D
		WHERE D.TrxhdId = T.TrxhdId
			AND EXISTS
			(
				SELECT 1
				FROM TimeSlot S
				WHERE S.timeSlotID = D.timeSlotID
					AND S.ProductId = '1734'
			)
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources