Different execution time for same query - SQL Server

Question

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 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
      drop #temp1
    END

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

Answer 1

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

Answer 2

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

Answer 3

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.