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.
精彩评论