开发者

Different execution time for same query - SQL Server

I have a query:

Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)

When I am executing this query, it is taking 1-2 seconds to execute, but when I am using the same query in stored procedure, the below query is taking more than 5 minute:

  If(Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12))
    BEGIN
       -- CREATE TEMPOR开发者_Python百科ARY TABLE [Say: #temp1]
 #temp1 => Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)
      inserting the same value in the temp table
      drop #temp1
    END

what could be the reason of this? and how can I resolve this? I am running the SP from asp.net


An EXISTS will short circuit the IF for you

If EXISTS (Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12))
    BEGIN
       -- CREATE TEMPORARY TABLE [Say: #temp1]
 #temp1 => Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)
      inserting the same value in the temp table

    END

However, why not query tbl_abc and tbl_xyz once?

   Select a
   INTO #temp1 
   from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)
   IF EXISTS (SELECT * FROM #temp1) /* or use @@ROWCOUNT*/
   BEGIN
     --DoStuff
   END
   drop TABLE #temp1


try this

declare @Count int

select @Count = count (a) from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)

if(@Count > 0)
begin
   #temp1 => Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)
      inserting the same value in the temp table
      drop #temp1
end

i also had the same situation and solved like this.

this may be because the query is executing two times and it contains a subquery. don't know what exactly happens inside while executing a query like this. but changing the query like this solved my problem of getting delayed


Is the mainid value actually hard coded (12), or is this just and example and, in reality, you are passing this value in to your stored proc as a parameter? (If it is hard coded, you may wish to ignore the following).

If "12" is infact a parameter, you could be the victim of Parameter Sniffing. Here's a question with some useful answers.

One solution mentioned but not explained is to mask the parameters - do this by declaring a local variable, setting it to the value of your parameter and use this in your query.

0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜