开发者

optimizing conditional statement in sql

select Item 
into #transferTheseItems
from IDTable where id = @myCondition

if exists (select 1 from #transferTheseI开发者_如何学编程tems)
/*
insert this huge data to a remote database over linked server
*/

although there is no record in the temp the query takes too long to execute.

I've also tried

if not exists (select 1 from #transferTheseItems)
return

but nothing changed.

I think the sql server tries to analyze the part that it will never execute.

Is there a way to skip this?


May be it help you:

select Item 
into #transferTheseItems
from IDTable where id = @myCondition

IF @@ROWCOUNT > 0
    ....
ELSE
    ....


Finally I realized;

try this

if 1 = 0
select * from a
join b on a.ID = b.ID
join c on c.ID = b.ID

For the first execution the query takes time to execute, even if your complex query won't be executed. Because sql generates an "execution plan".

run it again, and you see that it takes nearly no time. Because sql server saved the execution plan for that query.


I'm wondering if perhps you intended to have more than one statement in the IF block and forgot to use BEGIN and END to keep them in the IF block. Even for a large query creating an execution plan shouldn't take long, it is the running of the query that generally takes the most time. However when using conditional processing, only the first statment after the if is part of the IF unless you specifiy differently by using BEGIN and END blocks. Even when I intend to put only one statment in the IF I use BEGIN and END to mantaintibilty because it is a subtle bug that can be hard to find just scanning the code.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜