User defined data type for parameters in stored procedure
-
I have several stored procedures with parameters that are defined with user defined data types. The time it takes to run the procedures can take 10 - 50 seconds depending on the procedure.
If I change the parameter data types to the actual data type such as varchar(10), etc., the stored procedure takes less that a second to return records. The user defined types are mostly varchar, but some others such as int. They are all input type parameters.
Any ideas on why the stored procedure would run much faster if not using user defined types?
Thanks,
DW
-
Can you catch both execution plans and post them here?
I suspect that the server has to do a cast of some sort to apply a where condition, forcing an index scan instead of a seek.
-
With testing, I've discovered further info.
If I declare variables in the stored procedure, and then set the declared variables to the values passed as parameters, and use the declared variables instead of the parameters in the where condition, the results are returned almost instaneously.
It doesn't matter if I change the data types in the parameters from user defined data types are not.
This does change the query plan. Both plans still use a clustered index seek.
But in the faster plan, one bookmark lookup that was 1%, one index scan that was 3%, and one filter that was 30% is no longer in the plan.
ALTER procedure spSelTMScheduleReportTest
@tmpOfficeId varchar(10),
@tmpTMId varchar(10),
@InputWeek varchar(10),
@tmpTeamId varchar(4)
AS
SET NOCOUNT ON
DECLARE @ErrCode INT
DECLARE @rc INT
Declare @WeekOf smalldatetime
declare @OfficeId udt_OfficeId,
@TMId udt_TeamMemberID,
@TeamId udt_TeamNumber
set @OfficeId =@tmpOfficeId
set @TMId = @tmpTMId
set @TeamId = @tmpTeamId
-
check the filter, there must be a convert hidden in the user defined type version while there won't be any in the other version.
-
Look up 'parameter sniffing' on Google - that might help explain what you are seeing ...
-
Maybe that could be it, but I'd really lean toward the datatype used. But then again I don't see how he changed the code so you may be totally right
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply