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

For those who are facing the same issue: Both answers [by: binil & by: gbn] worked for me. — Zerotoinfinity, Jan 28 '11 at 08:49

score 3 · Accepted Answer · answered Jan 28 '11 at 06:48

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

score 2 · Answer 2 · answered Jan 28 '11 at 06:44

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

answered Jan 28 '11 at 06:44

Binil

6,445
3
30
40

This is not efficient although it will work. You'd use EXISTS as per my answer and this http://stackoverflow.com/questions/3271455/whats-the-best-to-check-if-item-exist-or-not-select-countidor-exist/3271464#3271464 – gbn Jan 28 '11 at 06:55
@gbn but while using "exists" i found the same problem. it was taking too much time to execute. but when i changed it like above answer, its started working as normal. i don't know why. – Binil Jan 28 '11 at 07:05
@gbn when you check it in sql server exists works normal. but while calling it from my application its getting delayed, some times throwing exception. and my table contains more than 1000000 records – Binil Jan 28 '11 at 07:07

score 0 · Answer 3 · edited May 23 '17 at 09:58

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.

Different execution time for same query - SQL Server

3 Answers3