July 22, 2010 at 3:57 pm
Sorry for the lack of DDL. It complicated...
Since I am not providing DDL all speculation welcome.
If I run the statement where I am checking for existence I get a response time of less than 2 seconds.
If I run the entire IF EXISTS the query is running over one minute. I haven't had the patience to let it finish. My existence query is on a linked server.
Any thoughts??
IF EXISTS
(
SELECT * FROM
(
SELECT...
UNION
SELECT...
UNION
SELECT...
)
AS Alias1
WHERE....
)
BEGIN
--Send the email
PRINT 'Send the email'
END
ELSE
BEGIN
PRINT 'Terminate'
END
July 22, 2010 at 4:02 pm
I don't know it is the best solution.. But I have one. Please try
Use
If exists ( select 1 from...)
instead form If exists ( select * from...)
ram..
🙂
July 22, 2010 at 4:11 pm
Try optimising the query inside the EXISTS. If you want a hand with that, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2010 at 4:12 pm
Ram:) (7/22/2010)
I don't know it is the best solution.. But I have one. Please tryUse
If exists ( select 1 from...)
instead form If exists ( select * from...)
Doesn't make a difference. The T-SQL parser removes all column references from an EXISTS very early in the execution process, before the optimiser gets the query for optimisation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2010 at 5:00 pm
GilaMonster (7/22/2010)
Try optimising the query inside the EXISTS. If you want a hand with that, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Thanks Gail. One of my UNION queries seems to be the issue, that is when I remove it the whole statement runs in less than 2 seconds.
I will try to optimize. Its a proprietery database and unfortunately I can not share the entire structure/data.
When I run the query inside the exists by itself I get results in less than 2 seconds. Why would wrapping this query inside an EXISTS cause the execution time to increase so greatly? I had to cancel execution after 14 minutes.
The offending WHERE clause seems to be is:
WHERE
(STATUS = 'MYSTATUS') AND
(MY_DATE > DATEADD(day,-15,getdate()))
When I remove the data criteria I get execution in 3 seconds but I need that to satisfy my business logic. The field MY_DATE is datetime and it is indexed.
Also the query is referencing a linked server. When I move the query directly to the linked server I get sub-second execution.
Speculation: Its seems like the indexes are not being used when the query is run against a linked server and is wrapped in an EXISTS statement.
July 23, 2010 at 2:16 am
GilaMonster (7/22/2010)
Doesn't make a difference. The T-SQL parser removes all column references from an EXISTS very early in the execution process, before the optimiser gets the query for optimisation.
WHat, if i say it basically look for non zero records from internal query.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 23, 2010 at 9:11 am
I ended up inserting my 'exists' query into a temporary table. I then perform my actual existence check on this temporary table. Execution is 2-3 seconds. This works...not sure about the previous odd behavior.
July 23, 2010 at 9:15 am
There are a lot of 'funnies' around queries over linked server. I often recommend using OpenQuery instead of 4-part naming, and specifying exactly what should come back from the linked server. In this case, I'd probably stick a TOP (1) in there to ensure that as little comes back as possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2010 at 9:51 am
Using TOP 1 also resolves my issue. Thanks again.
July 23, 2010 at 9:55 am
Check each query separately for EXISTS, and put the most likely to be true first.
This should allow you to get rid of the UNION (which should be UNION ALL if you intend to keep them).
There is no need to run all the queries if the first one returns a result -- right? Or did I misunderstand something?
IF EXISTS (
SELECT TOP 1...
)
OR EXISTS (
SELECT TOP 1...
)
OR EXISTS (
SELECT TOP 1...
)
...
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 10:14 am
SQL doesn't short-circuit ORs or ANDs in an IF.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2010 at 10:17 am
SQL doesn't short-circuit ORs or ANDs in an IF.
Never??
That's a terrible thought. Why does it waste so many resources on unneeded processing?
I can't believe that. I really hope it's not true.
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 10:25 am
Of course just because SQL could short-circuit does not mean that it actually will.
But at least you give it a chance.
http://technet.microsoft.com/en-us/cc678236.aspx
Hi, I'm Nigel Ellis. I'm the development manager for the SQL Server Query Processor team.
paschott:
Q: This stopped some procs from working as they did in 7.0 because SQL no longer did a short-circuit to only test that first @var = xxx part. It now tests all parts of the WHERE clause from what I can tell instead of stopping once one part did.
Host Guest_nigele_ms:
A: There wasn't any change made in this area; the semantics of SQL does allow short-circuit evaluation, and we take advantage of this in our optimization engine. If you are seeing problems with this, please file a case with PSS.
[END from that page]
-- a few quick ways to test it
SELECT 'test short circuit'
WHERE 1 = 1 OR 1 / 0 = 0
if (1=1) or (1/0 = 0)
print 'true'
else
print 'false'
if (1=0) and (1/0 = 0)
print 'true'
else
print 'false'
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 12:14 pm
Maybe SQL doesn't do it on certain IF statements?? That would be unfortunate, since the same principle should apply. Hopefully, if needed, they will add that in the future.
I swear though I've seen SQL do it sometimes before, even on IF statements.
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 1:46 pm
I suspect it's something like 'short-circuiting' in a query's where clause. It may happen, but cannot be depended upon to always happen, not like C++ where as soon as the outcome is clear, processing of the IF conditions always stops.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply