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
– Zerotoinfinity, Commented Jan 28, 2011 at 8:49

gbn · Accepted Answer · 2011-01-28 06:48:30Z

3

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

answered Jan 28, 2011 at 6:48

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Binil · Accepted Answer · 2011-01-28 06:44:57Z

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, 2011 at 6:44

Binil

6,6033 gold badges34 silver badges41 bronze badges

3 Comments

gbn Over a year ago

This is not efficient although it will work. You'd use EXISTS as per my answer and this stackoverflow.com/questions/3271455/…

Binil Over a year ago

@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 Over a year ago

@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

Community · Accepted Answer · 2017-05-23 09:58:25Z

0

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.

edited May 23, 2017 at 9:58

CommunityBot

11 silver badge

answered Jan 28, 2011 at 8:45

Brett

1,67010 silver badges13 bronze badges

Collectives™ on Stack Overflow

Different execution time for same query - SQL Server

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related