Friday, July 18, 2008

Bruce sent me a link the other day to an article T4 template for generating SQL view from C# enumeration which I found interesting from a modelling constants/enumerations in SQL viewpoint.

The example used was modelling an enumeration of ContactType which has valid items of Individual and Organisation.

The article used a view to model this, as per
CREATE VIEW enumContactType
AS
  SELECT
    0 AS Individual,
    1 AS Organization

and then using it within a SELECT as

SELECT *
FROM Contact
WHERE Type = (SELECT Organization FROM enumContactType)
(Note: in the original article Oleg used a schema called enum, but I'm just ignoring this at the moment and have thus changed the name from enum.ContactType to enumContactType)

An alternative

In my previous company, we used Scalar-Valued Functions to mimic constants, and I guess this could be extended to enumerations. I thought I'd re-create the above example and give it a try to see how it looks and compares.

So, to model the enumeration ContactType, I've created two functions as follows:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
AS
BEGIN
  RETURN 0
END
GO

CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
AS
BEGIN
  RETURN 1
END
GO

And then to reproduce the SELECT query I wrote:
SELECT *
FROM Contact
WHERE Type = dbo.enumContactTypeOrganisation()

The resulting data matches that used in the VIEW model and provides an alternative. I'm sure that a template could be written to produce those functions as an output as per the end part of Oleg's article.

Performance and timings

I was interested in the relative performance of these two methods, so armed with my timing code from last week I checked them out. I amended the SELECT to bring back the COUNT(*) FROM Contact into a local integer variable, and ran it 1000000 times.

The results are as follows:

Code	Description	TimeInMS
EnumView	Using the view	13010
EnumUDF	Using the UDF	21450

showing that the view method is more performant.

I then changed the function to make use of SCHEMABINDING. The new functions look like:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN 0
END
GO

CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN 1
END
GO

And the timings change to be:

Code	Description	TimeInMS
EnumView	Using the view	13010
EnumUDF	Using the UDF	20280

which do reduce the time taken for the UDF but still means that the view is faster.

For interests sake I then ran a comparison timing against the code using the literal as:
SELECT *
FROM Contact
WHERE Type = 1
which resulted in

Code	Description	TimeInMS
EnumLiteral	Using the literal	12043

showing it is faster, but not by much, than the view.

Summary

So, what has this shown?

• Using a view is quite efficient and effective for modelling enumerations
• Using a UDF is an alternative, but is slower
• Schema binding makes UDF usage quicker
• The difference between using a VIEW and using the hard-coded literal isn't a lot in perfomance terms

Labels: SQL, SQLServer2005

// posted by Jane @ 1:24 PM   

Comments:

Great article. Thanks Jane! I would not have expected a UDF to be slower than a VIEW. Any ideas on why?

# posted by  Oleg Sych : 7:11 PM

 

Nice article. Regarding Oleg's question, I believe the SQL engine is not optimized to see that the function is actually a constant and I guess it evaluates it for each row. We had many performance issues when using UDFs, which make code look nicer, but had to replace them with carefully written JOINs or even more "procedurally" looking code to have decent performance.

# posted by  Sergiu Damian : 2:19 PM

 

Post a Comment