Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Optimising Table SELECTs - whether to use a view and UDF or a computed column

Posted on 2008-10-06
4
Medium Priority
?
158 Views
Last Modified: 2010-03-19
Hi,

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:

ID
UnitPrice
TaxPercentage
Discount

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.


0
Comment
Question by:gw5434
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 80

Accepted Solution

by:
arnold earned 300 total points
ID: 22653970
You have to balance out resource usage.  It is good to calculate and store the data in a view if that will save you from performing repetitive tasks.

However, using a view and the server to calculate the price of buying two units of A at price B for one users while tabulating the price of ten units of A at Price B for another, is a waste of resources.  A calculation of number of item times the price is not an intensive enough transaction to dedicate the SQL server's resources for it can be performed on the server when the data is retrieved.

Creating Views to simplify/speed up repetitive queries are a good use of resources.
I.e. instead of generating join queries, creating an aggregate view of those joins and then querying the view with a limiting where clause could speed up the performance.  Note however, that views often use up system resources when changes to underlying tables occur.

0
 
LVL 15

Assisted Solution

by:jorge_toriz
jorge_toriz earned 300 total points
ID: 22674720
I preffer to use a computed persisted column, I think your data won't be volatile (a lot of changes)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question