Optimising Table SELECTs - whether to use a view and UDF or a computed column
Posted on 2008-10-06
I have a relatively complex database for use in a web application.
The tables themselves are fairly simple, with only a few columns of standard datatypes. However, I am unsure of the most suitable way to proceed with regard to optimisation.
For example, imagine I have a table which stores prices. The table might have columns such as:
Rather than calculate the "actual" price in the web application, I would rather do this at the database level. In effect, I would like a column such as "TaxPrice" (i.e. UnitPrice * TaxPercentage) or "SalePrice" (UnitPrice - Discount).
As I can see, there are two ways to go.
1. Use a computed column in the table itself.
2. Use a view which has a subquery which returns the necessary value.
At the moment I am using option 2. I have views set up anyway as I am performing various JOINs. I also thought it best to move the subquery to a UDF which returns a scalar value. This has particular attraction since I can abstract out some commonly used functions.
Which is the better approach? The table rows will be in the region of thousands of rows at most rather than tens of thousands. The selects will generally only be returning 1 - 10 rows at a time, and in the case of large result sets, these will be paged.
For a simple calculation (e.g A - B or A*B) then is the computed column more efficient?
I also have subqueries which reference other tables - obviously I cannot put these into computed columns, but I could use the UDF in the computed column. I would prefer not to do this as it strikes me as probably having high overhead.