User Defined Function vs. Case Stmt 3

[mcconnellj]

I am using user-defined functions in a SQL View to create additional columns. For example, I may have a product function that receives a parameter from a field and uses a case statement to pass back a product description such as "receiver". I used to have the case statements embedded directly in the views, but the SQL became so complex with 10 different case statements.

From a best practice and performance perspective, is the use of functions in views appropriate? I.e., are user-defined functions slowing down materialization of the view in any way? From a documentation perspective, it sure seems cleaner. Are there other methods I have not considered that are more appropriate?

Thanks for your help!

 

[jdgonzalez]

I try to avoid UDF's as much as I can. From what I understand, you'll take a performance hit because they operate a bit differently than system functions.

Just out of curiousity, why are you using a UDF? You could accomplish the same thing by having a related table for your descriptions.

 

[vongrunt]

Functions are slower than plain copy&paste SQL code. Is this penalty acceptable or not, it mostly depends on a) number of UDFs used in query, b) number of records that pass through UDF. Using UDFs to format already materialized 1,000 rows can be acceptable, but UDF in WHERE clause is almost always bad.

For when-this-then-that purposes another alternative is join on lookup table.

 

[SQLSister]

If you have a lot of records, you might even consider simply storing the description with the record. Yes that is denormalizing some, but it is often faster for large result sets than a join to a lookup table and certainly way faster than UDFs.

It is especially useful if the descriptions might change or go away but you need a historical record of what the description was at the time the record was created.

For instance I have a product table with all the prodcts we could put ina sales proposal, but we store the descriptions in the proposal itself, so we know what we intended to order if the part is discontinued (and thus no longer in the Products table) at the time we win the contract and need to buy the parts. Id we didn;t store the sdescription then we might have a product number with no way to tell what it was supposed to be or even who the manufacturer was. Would want to do the same thing with orders. If you need to look at past orders, it is best to know what exactly you ordered and from who.

Questions about posting. See faq183-874

 

[ESquared]

SQLSister, my own preference in the case you've described is to never delete products from the products table, but rather to mark them as obsolete or expired. Or, at the very least, to move them to an "expiredproducts" table.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)