Post reply

User defined data type for parameters in stored procedure

  • July 5, 2005 at 8:52 pm

    #86746

    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

  • July 5, 2005 at 9:50 pm

    #571084

    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.

  • July 6, 2005 at 8:26 am

    #571255

    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

  • July 6, 2005 at 8:47 am

    #571273

    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.

  • July 7, 2005 at 2:48 am

    #571681

    Look up 'parameter sniffing' on Google - that might help explain what you are seeing ...

     

  • July 7, 2005 at 6:35 am

    #571724

    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