SQL Server Performance Forum – Threads Archive
Procedure with Parameters Performance
We create a stored procedure likeCREATE PROCEDURE test
(@cli_id int)
AS
select * from BV_ADRESSE where CLI_ID = @cli_id when we try it in query Analyser (exec test 1), the execution time is
SQL Server Execution Times:
CPU time = 1281 ms, elapsed time = 4030 ms. when we do directly the query : declare @CLI_ID int
SET @CLI_ID = 1
SELECT CLI_ID FROM BV_ADRESSE Where [CLI_ID] = @CLI_ID
the execution time is
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 30 ms. Why this difference ?
select * from BV_ADRESSE where CLI_ID = @cli_id SELECT CLI_ID FROM BV_ADRESSE Where [CLI_ID] = @CLI_ID
This is "not" the same thing. You should never use * in a procedure; and it will make a difference in execution time. It also utilizes resources to figure out what the columns are. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Say the queries had been the same then the difference between the direct query and the stored procedure has to do with how sql server compiles the query plan. With the stored procedure, sql server doesn’t know the value coming in (compared to the direct query where you do SET @CLI_ID = 1) and has to rely in index statistics. So if you send in value 5 to the procedure the first time a query plan will be built for value 5. But this might not be a good plan for the value 1234 etc. To solve this make sure you have updated statistics (sp_updatestats) or assign the stored procedure variables to local vairables inside the procedure and then use those local variables in the query. This allows sql server to build a better plan. Example:
CREATE PROCEDURE test
(@cli_id_sp int)
AS
DECLARE @cli_id INT
SET @cli_id = @cli_id_sp
select * from BV_ADRESSE where CLI_ID = @cli_id
minus the select * Why do people keep using that when they don’t need to? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Derrick…they don’t know better. I guess that where the lemons start dropping from the sky. Step into Argyle’s shoes and reconstruct your query. By the way, how much data do you have? What is the response time after running the query several times over? Does the execution plan change after you restructure the query? Nathan H.O.
Moderator
SQL-Server-Performance.com
]]>