October 2, 2007 at 12:21 am
Hello everyone,
I have a confusion that whether using sp_executesql to get a value for a variable is faster and efficient or use select query, asign it to a temporary table and then to the variable is faster.
when I noted the difference between them I got using temp. table is faster. But using temp. table is not good.
Can anyone explain it to me?
thanks
Him
October 2, 2007 at 12:48 am
It really depends on a number of factors. Usually when my developers ask which of a couple of ways will be faster, I tell them to try both and see how they work.
Without seeing the code, it's hard to make any sort of informed, useful comment
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2007 at 5:33 am
Your second option with the temp table is a bit confusing. If you post the T-SQL, we can probably tell you why one is faster than the other and then tell you the third alternative that is faster than both of these (just speculating on that one).
Also be careful with statements like "temp tables are bad". Getting something like that in your head can easily begin to sway your designs in the wrong direction in the future.
The truth is that nearly every feature in SQL has times where they are efficient and times where they are not. Don't let yourdelf get stuck in the rut of eliminating an option in every situation. There are times when something is clearly inappropriate, but it is important to test several options in situations where performance is critical. Even the most experienced DBA can be surprised now and then.
October 2, 2007 at 10:02 pm
Here's the two scripts that I told earlier:
Code using temp. table:
CREATE TABLE #tmp_db_prop
(
dup_countINT
);
SET @v_dup_count_qry = 'SELECT COUNT(doc_id_pk) AS dup_count FROM '+ @v_db_full_name +
' WHERE '+ @p_pc_universal_id + ' = ' +
' ( ' +
' SELECT '+ @p_pc_universal_id + ' FROM ' + @v_db_full_name +
' WHERE doc_id_pk = ' + CAST(@p_doc_id AS VARCHAR(10)) +
' ) ';
INSERT INTO #tmp_db_prop
EXEC (@v_dup_count_qry);
DECLARE @v_dup_count INT;
SET @v_dup_count = 0;
SELECT @v_dup_count = dup_count FROM #tmp_db_prop ;
DROP # tmp_db_prop;
----------------------------------------------------------------------
Query using sp_executesql
DECLARE @DUP_COUNT INT;
DECLARE @QUERY_SQL NVARCHAR(4000);
DECLARE @ParmDefinition nvarchar(500);
SET @QUERY_SQL = N'SELECT @v_dup_count_out= COUNT(doc_id_pk) FROM '+ 'db_ors_aug24.dbo.opt_test_4' +
N' WHERE '+ 'universalid' + ' = ' +
N' ( ' +
N' SELECT '+ 'universalid' + ' FROM ' + 'db_ors_aug24.dbo.opt_test_4' +
N' WHERE doc_id_pk = ' + CAST(69 AS NVARCHAR(12)) +
N' ) ';
SET @ParmDefinition = N'@v_dup_count_out INT output'
EXEC sp_executesql @QUERY_SQL, @ParmDefinition, @v_dup_count_out=@DUP_COUNT OUTPUT;
SELECT 'DUP_COUNT:',@DUP_COUNT;
-----------------------------------------------------------------------
October 3, 2007 at 4:42 am
Fix the query, not the query calling mechanism. 🙂
DECLARE @DUP_COUNT INT;
DECLARE @QUERY_SQL NVARCHAR(4000);
DECLARE @ParmDefinition nvarchar(500);
SET @QUERY_SQL = N'SELECT @v_dup_count_out= COUNT(doc_id_pk) FROM '+ 'db_ors_aug24.dbo.opt_test_4' +
N' WHERE '+ 'universalid' + ' = ' +
N' ( ' +
N' SELECT '+ 'universalid' + ' FROM ' + 'db_ors_aug24.dbo.opt_test_4' +
N' WHERE doc_id_pk = ' + CAST(69 AS NVARCHAR(12)) +
N' ) ';
SET @ParmDefinition = N'@v_dup_count_out INT output'
EXEC sp_executesql @QUERY_SQL, @ParmDefinition, @v_dup_count_out=@DUP_COUNT OUTPUT;
SELECT 'DUP_COUNT:',@DUP_COUNT;
The issue is the select you use to get the universalid. Simply do that by itself BEFORE you make the dynamic string. Then you will have a HARD-CODED value to use in the WHERE clause, which will be guaranteed to give the most efficient query plan. NOTE however that you could wind up with a bad query plan being cached (search for Parameter Sniffing on this or other websites). But if the universalid is an identity or a value that is evenly distributed then this won't be a problem.
something like this:
DECLARE @DUP_COUNT INT, @universalid int
DECLARE @QUERY_SQL NVARCHAR(4000);
DECLARE @ParmDefinition nvarchar(500);
SELECT @universalid = universalid FROM db_ors_aug24.dbo.opt_test_4
WHERE doc_id_pk = 69
SET @QUERY_SQL = N'SELECT @v_dup_count_out= COUNT(doc_id_pk) FROM '+ 'db_ors_aug24.dbo.opt_test_4' +
N' WHERE '+ 'universalid' + ' = ' + @universalid
SET @ParmDefinition = N'@v_dup_count_out INT output'
EXEC sp_executesql @QUERY_SQL, @ParmDefinition, @v_dup_count_out=@DUP_COUNT OUTPUT;
SELECT 'DUP_COUNT:',@DUP_COUNT;
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply