User defined data type used in stored procedure parameters

[dawatson833]

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?

Using SQL Server 2000.

Thanks,
DW

[David Portas]

Why do you use UDTs for parameters? I've no idea if there's a performance
difference but UDT rules and defaults don't get applied to variables so I
see little or no benefit in using them.

UDTs, rules and defaults are backwards compatibility features. Going
forward, it is highly recommended that you use constraints instead - much
more powerful and easier to use.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

Why do you use UDTs for parameters? I've no idea if there's a performance
difference but UDT rules and defaults don't get applied to variables so I
see little or no benefit in using them.

UDTs, rules and defaults are backwards compatibility features. Going
forward, it is highly recommended that you use constraints instead - much
more powerful and easier to use.

I'm sorry, but I think this is very poor advice.

First, user-defined data types are by no means a deprecated feature. (In
SQL 2005, Microsoft has added proper DDL syntax to create them.) User-
defined data tyepes are a not wholly satisfactory replacement for domains,
but it's definitely a useful feature. In the database I work with, about 99%
of the character columns are defined through some user-defined data type.

Take an example: say in your database there is a code, which appears in
many tables, and there are plenty of parameters and variables in stored
procedures etc. Say that this code is five characters long, and you now find
that you need to make it longer, say eight chars. If you use a UDT, there
is one file you need to check out and change. If you have created all the
columns as varchar(5) declared all parameters and columns etc as, you
have tons of work to find all references, and you can never be really sure
that you missed one references somewhere. (To make it really worse, assume
that you have several other varchar(5) entities that should not be changed.)
Even with a UDT you have a lot of work to compose a script to implement the
change in the tables. But this is a script that you can run and test, and
you can be sure that once it passes the tests it's OK. If you have a
plain varchar(5) you may find that the response to your customer is
"sorry, we cannot implement this change safely".

Second, rules and defaults are indeed deprecated by Microsoft, but that's
sillyness from their side. Say that you have a type column, which permits
the values A, B and C, and this type column appears in several tables.
If you define a rule and bind to the type, it's a very simple matter to
permit the value D as well. If you have constraints, you have a number of
objects in the database saying the same thing. Binding rules and defaults
to a user-defined data type is very powerful feature in SQL Server.
(Bind rules and defaults directly to columns is another matter; in this case
constraints are better.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

[Erland Sommarskog]

(da*********@ho tmail.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_IDENTIFI ER 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, es****@sommarsk og.se

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

[David Portas]

I could have said UDTs <emphasis> as implemented in SQL Server 2000
</emphasis> exist for backwards compatibility reasons but the OP had
already stated he was using 2000.

I don't see that change control is any easier with a UDT than can be
achieved with a data dictionary and proper naming conventions. As far
as I can see UDTs make things much harder in the example you gave
because you have to unbind rules and defaults, alter the column type,
drop and recreate the UDT, alter the column type again and then rebind.
I can't say I've tried this heavily in production systems but it seems
like a lot more complexity and overhead than a single ALTER table
statement. I'm interested if your experience is difference.

Rules are deprecated by MS as you rightly say. This is a good thing for
two excellent reasons. Firstly, rules don't offer anything like the
functionality of CHECK constraints - specifically they can only
reference a single column at a time. Even assuming you find some
benefit in Rules you'll still need to use constraints as well so why
persist your business rules in two different and potentially
contradictory places? Secondly, the optimizer won't take advantage of
rules as it will the equivalent constraints. This can make an big
difference in some cases. Given these two advantages I don't see any
argument for rules at all.

Defaults? I don't know what they offer that default constraints don't.

Finally, an unquantifiable benefit. Most people I know follow the
"official" line and consider these as legacy features. If people are
using them less and less then I expect many of us will forget the
syntax and inticacies of bound rules and defaults (I know I do). That
means code we write today will be obscure to others in future and may
make them less productive (as an example, look at the number of people
who are still puzzled by the old *= syntax).

I still think this is good advice. Maybe I should have explained my
reasoning a bit better to start with so that the OP can make up his own
mind.

--
David Portas
SQL Server MVP
--

[dawatson833]

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 spSelTMSchedule ReportTest
@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_TeamMemberI D,
@TeamId udt_TeamNumber

set @OfficeId =@tmpOfficeId
set @TMId = @tmpTMId
set @TeamId = @tmpTeamId
David Portas wrote:

I could have said UDTs <emphasis> as implemented in SQL Server 2000
</emphasis> exist for backwards compatibility reasons but the OP had
already stated he was using 2000.

I don't see that change control is any easier with a UDT than can be
achieved with a data dictionary and proper naming conventions. As far
as I can see UDTs make things much harder in the example you gave
because you have to unbind rules and defaults, alter the column type,
drop and recreate the UDT, alter the column type again and then rebind.
I can't say I've tried this heavily in production systems but it seems
like a lot more complexity and overhead than a single ALTER table
statement. I'm interested if your experience is difference.

Rules are deprecated by MS as you rightly say. This is a good thing for
two excellent reasons. Firstly, rules don't offer anything like the
functionality of CHECK constraints - specifically they can only
reference a single column at a time. Even assuming you find some
benefit in Rules you'll still need to use constraints as well so why
persist your business rules in two different and potentially
contradictory places? Secondly, the optimizer won't take advantage of
rules as it will the equivalent constraints. This can make an big
difference in some cases. Given these two advantages I don't see any
argument for rules at all.

Defaults? I don't know what they offer that default constraints don't.

Finally, an unquantifiable benefit. Most people I know follow the
"official" line and consider these as legacy features. If people are
using them less and less then I expect many of us will forget the
syntax and inticacies of bound rules and defaults (I know I do). That
means code we write today will be obscure to others in future and may
make them less productive (as an example, look at the number of people
who are still puzzled by the old *= syntax).

I still think this is good advice. Maybe I should have explained my
reasoning a bit better to start with so that the OP can make up his own
mind.

--
David Portas
SQL Server MVP
--

[--CELKO--]

what we really need is the CREATE DOMAIN from SQL-92. Same idea,
better syntax..

[Erland Sommarskog]

--CELKO-- (jc*******@eart hlink.net) writes:

what we really need is the CREATE DOMAIN from SQL-92. Same idea,
better syntax..

Yupp. Will have to admit that I don't know exactly what a domain buys
me, but I expect it to be similar that the user-defined data types
in SQL Server, but with some extra bells an whistles.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

I could have said UDTs <emphasis> as implemented in SQL Server 2000
</emphasis> exist for backwards compatibility reasons but the OP had
already stated he was using 2000.
I'm sorry, but I can't make any sense of this at all. User-defined data
types is a first-class citizen in SQL 2000 as well in SQL 2005. Any
talk about user-defined type existing only for backwards compatibility
is incorrect.

Note also the terminology: in SQL 2005 "user-defined type" is a
data type implemented in a CLR Assembly. The types that are in
SQL 2000 are called "user-defined data types", UDDT or "alias data
types" in a confusing mix. (Documentation has "alias", tools appears
still go with UDDT.)
I don't see that change control is any easier with a UDT than can be
achieved with a data dictionary and proper naming conventions.
A type is something you can trust. Naming conventions is not. We have 3700
stored procedures, developed over a span of more than ten years. Should we
trawl them for naming conventions? That's not a serious suggestion.
As far as I can see UDTs make things much harder in the example you gave
because you have to unbind rules and defaults, alter the column type,
drop and recreate the UDT, alter the column type again and then rebind.
I can't say I've tried this heavily in production systems but it seems
like a lot more complexity and overhead than a single ALTER table
statement. I'm interested if your experience is difference.
Well, this is how one of our type-definition files looks like:

EXEC sp_addtype 'aba_upduser', 'varchar(30)'
go
CREATE DEFAULT aba_upduser_def AS system_user
go
EXEC sp_bindefault 'aba_upduser_de f', 'aba_upduser'
go
CREATE RULE aba_upduser_rul e AS @x = system_user
go
EXEC sp_bindrule 'aba_upduser_ru le', 'aba_upduser'
go

When we run this file, our load tool will automatically generate the
matching sp_drop and sp_unbind calls. Although, adding them manually is
no major task.

If we would need to change the type to nvarchar(128) that's a one-line
code change. Granted there are a couple of hundred tables to rebuild -
but at least it's a simple query to the system tables to find them.
(Or look up the cross-ref in DB-doc page for the type.) Running the
update script is another story.

Another example, say that we decide in 2005, that we want to use
ORIGINAL_LOGIN( ) rather than SYSTEM_USER. For us that a single file
to change and run. One single file.

With CHECK and DEFAULT constraints we would have several hundred tables
to change. Yes, we have a good tool for doing that. But we would have
to tailor our update scripts which are built on a principle that a
table change usually requires a reload.
Rules are deprecated by MS as you rightly say. This is a good thing for
two excellent reasons. Firstly, rules don't offer anything like the
functionality of CHECK constraints - specifically they can only
reference a single column at a time.
I never said that you should only use rules. Rules are good when you
have the same behaviour in many columns all over the database. Constraints
are good when you have a behaviour which is peculiar to one table.
Since rules applies to a domain, the fact that they cannot interact
with other columns is a moot point. (Yes, occasionally there may be a
pair of columns that appear in many tables and they have some common
rules between them. But that is not common.)

Rules and constraints are not contradictory features, but they supplement
each other.
Even assuming you find some benefit in Rules you'll still need to use
constraints as well so why persist your business rules in two different
and potentially contradictory places?
Huh? I'm sorry, but you are not making any sense. You are advocating
that the same business rule should be duplicated in many instances over
the database - and with the possibility that the copies are different.
Secondly, the optimizer won't take advantage of rules as it will the
equivalent constraints. This can make an big difference in some cases.
This is a correct observation. However, this presumes that the constraints
have never been enabled WITH NOCHECK, which unfortunately is the default
when you re-enable a constraint.
Given these two advantages I don't see any argument for rules at all.

Defaults? I don't know what they offer that default constraints don't.
Again: they reduce the number of places you have to change, and reduce
the number of duplicate information.
Finally, an unquantifiable benefit. Most people I know follow the
"official" line and consider these as legacy features. If people are
using them less and less then I expect many of us will forget the
syntax and inticacies of bound rules and defaults (I know I do). That
means code we write today will be obscure to others in future and may
make them less productive (as an example, look at the number of people
who are still puzzled by the old *= syntax).

I still think this is good advice. Maybe I should have explained my
reasoning a bit better to start with so that the OP can make up his own
mind.

You may not know and understand user-defined data types and bound rules
and bound defaults. But it's a huge leap to take it from there to tell
other people not to use it. I don't know DTS or Analysis Services, but
I would never dream of telling people not to use them.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

[Erland Sommarskog]

(da*********@ho tmail.com) writes:

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.

There is still too little information for me to get an understanding
what is really going on. Seeing the full procedure, type definitions
as well as the table and index definitions could give more clues,
although suspect hands-on would be required to get a real grip over
that situation. If even that is possible - it does sound mysterious.

Now, what you say that the plan change if you copy parameters to
local variables, is not completely unknown. We found in several cases
that this was a good idea, where we had code like:

CREATE PROCEDURE some_sp @datepar aba_date AS
...
IF @datepar IS NULL
SELECT @datepar = @today
...
WHERE datecol = @datepar

The standard call would be with @datepar as NULL, and the optimizer would
build the plan for NULL, and then the plan would be bad for the real
value.

When a query includes a condition to a local variable, the optimizer does
not know the value, so it will use a standard assumption, which is likely
to be better than a guess derived from a bogus value.

If statistics are inaccurate or out of date, this can happen with real
values as well, and sometimes the standard assumption can be better in
this case.

However, I can't place user-defined data types into the picture. A UDDT
comes with a nullability setting, but I cannot see how that could have
any impact.

Anyway, run an UPDATE STATISTICS tbl WITH FULLSCAN on the involved
tables, and then run sp_recompile on the procedure, and then try the
various combinations of declarations of parameters and local variables.

Not that I am sure that it will improve things, and even if it does,
we may still not understand what happened.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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