Posted by Erland Sommarskog on 07/06/05 13:51

(dawatson833@hotmail.com) writes:
> 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?

What happened if you change back?

SQL Server has a feature known as parameter sniffing. This means that
the first time a procedure is run, the optimizer takes the input values
as guidance for building the plan. This has the effect if the first call
is with atypical values, this can lead to a poor plan sticks in the cache -
a plan which is poor for the more typical values, that is. Thus, according
to this theory, the change in data types has no importance, but the
mere fact that you flushed the query plan from the procedure cache had
importance.

There some other possibilities on the same theme if there are indexed views
or indexed computed columns involved. In such case it could be the case
that the procedure was originally created with QUOTED_IDENTIFIER and/or
ANSI_NULLS off. These settings are saved with hte procedure, and the two
must be on for indexes on views and computed columns to be used. If you
recreated the procedure from Query Analyzer it is likely that you had
these settings on. (As they are on by default from QA.)

Yet another possibility is that some of the parameters had an incorrect
type, and you replaced with a correct type. In this case implicit
conversions can lead to indexes not being used.


We use a lot of user-defined types in our database, and we have never
noticed any problems with them as such.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]