asp.netsql-serversql-server-2005executiontimeout

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 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


Solution

  • 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