question

HserEh avatar image
HserEh asked

OPTIMIZATION

Hi all,Please make me understand to optimize this query. Please don't tell me to create index. I want to know other ways. Create PROC [dbo].[Status_Optimized] @empId As Bigint ,@status AS nvarchar(1) AS DECLARE @sql NVARCHAR(1000) set @sql=N' update dbo.employee set STATUS=@status where EMPID=@empId' execute sp_executesql @sql, N'@empId nvarchar(max), @status nvarchar(max)' ,@empId ,@status
sql-server-2008databasesql server 2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
get rid of using sp_executesql, change it to declare @empId nvarchar(max), @status nvarchar(max) update dbo.employee set status = @status where empid = @empid (why do you need nvarchar(max) ?)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
@HserEh How did you find out that this procedure needs to be optimized at first place? Was it a general question being asked by someone? If yes, then was the question about optimization only or about the wrongness / any weirdness as well? Regarding optimization of this procedure, as a general recommendation any implicit conversion should be avoided in your queries. Here, by looking at your procedure definition, it seems like the **EMPID** column is of data type `BIGINT` and the column **STATUS** is data type `NVARCHAR(1)`. If this assumption is correct, then the paramenter definition of `sp_executesql` is making sure that an [implicit conversion][1] must take place i.e. N'@empId nvarchar(max), @status nvarchar(max)' which is bad in **certain scenarios** and could lead to drastic decline in performance. So the above parameter definition must be changed to N'@empId bigint, @status nvarchar(1)' This is as per general recommendation which should be followed almost every time. As far is using `sp_executesql` is concerned, since you are using a Stored procedure, it is redundant and for brevity and better management of your environment (Do not use dynamic SQL unless absolutely necessary), your procedure should have been like this CREATE PROCEDURE [dbo].[Status_Optimized] ( @empId As Bigint ,@status AS nvarchar(1)) AS update dbo.employee set STATUS = @status where EMPID = @empId go Or if you cannot change your code too much and must use `sp_executesql` for some reason, then the following could be used CREATE PROCEDURE [dbo].[Status_Optimized] ( @empId As Bigint ,@status AS nvarchar(1)) AS DECLARE @sql NVARCHAR(MAX) set @sql=N'update dbo.employee set STATUS=@status where EMPID=@empId' execute sp_executesql @sql, N'@empId bigint, @status nvarchar(1)' ,@empId ,@status go Now the final part, will that optimize this specific procedure, then it depends. If you already have the index on EMPID column, then you would not be able to see much difference, as the Data Type Conversion precedence will convert `nvarchar(max)` to `bigint` for the predicate `"EMPID= @empid"` and the optimizer can seek the value through index. But if there is no index, then you can evaluate the difference. Following is a test bud for you to play with --Use temporary DB use tempdb go create table employee ( EMPID bigint identity(1,1) not null , STATUS nvarchar(1) ) go INSERT employee select case when RowNum % 2 = 0 THEN 'a' ELSE 'b' END AS Status from (select TOP (1000000) ROW_NUMBER() over(order by (select null)) RowNum from sys.all_columns c cross join sys.all_columns c2 cross join sys.all_columns c3) A go --warm the cache select COUNT(*) from employee go CREATE PROCEDURE [dbo].[Status_Optimized_Original] ( @empId As Bigint ,@status AS nvarchar(1)) AS DECLARE @sql NVARCHAR(1000) set @sql=N'update dbo.employee set STATUS=@status where EMPID=@empId' execute sp_executesql @sql, N'@empId nvarchar(max), @status nvarchar(max)' ,@empId ,@status go CREATE PROCEDURE [dbo].[Status_Optimized_Usman] ( @empId As Bigint ,@status AS nvarchar(1)) AS update dbo.employee set STATUS = @status where EMPID = @empId go CREATE PROCEDURE [dbo].[Status_Optimized_Usman_ExecSql] ( @empId As Bigint ,@status AS nvarchar(1)) AS DECLARE @sql NVARCHAR(MAX) set @sql=N'update dbo.employee set STATUS=@status where EMPID=@empId' execute sp_executesql @sql, N'@empId bigint, @status nvarchar(1)' ,@empId ,@status go --Compile all procedures exec [dbo].[Status_Optimized_Original] 6, N'c' go exec [dbo].[Status_Optimized_Usman] 6, N'd' go exec [dbo].[Status_Optimized_Usman_ExecSql] 6, N'e' go --Measuring the timings and IO set nocount on; go set statistics IO on; go declare @time datetime; set @time = CURRENT_TIMESTAMP; exec [dbo].[Status_Optimized_Original] 7, N'c'; print 'Time taken by Original procedure is ' + convert(varchar(30),datediff(ms, @time, current_timestamp)) + ' milliseconds'; go declare @time datetime; set @time = CURRENT_TIMESTAMP; exec [dbo].[Status_Optimized_Usman] 8, N'd'; print 'Time taken by changed procedure without DSE is ' + convert(varchar(30),datediff(ms, @time, current_timestamp)) + ' milliseconds'; go declare @time datetime; set @time = CURRENT_TIMESTAMP; exec [dbo].[Status_Optimized_Usman_ExecSql] 9, N'e' print 'Time taken by changed procedure with DSE is ' + convert(varchar(30),datediff(ms, @time, current_timestamp)) + ' milliseconds'; go [1]: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.