Advanced Performance Tuning – 3 :: Designing for better performance

  • Article

If you have read my earlier post, you might be saying “Heck! This guy loves to post questions without providing proper answers!” Believe me, that is not my intention. My intention here is to help you understand common mistakes that people make, so that you can avoid them in your projects.

The biggest challenge for a SQL Server Developer is to write efficient code. The expectations are that the code must complete in the least possible time, produce correct results and use minimum resources possible. We all know that there are various ways to tune slow performing queries – Indexes (with their varieties and flavours), Query Hints, PlanGuides; you name it!

This is, however, not the intention behind this post. My intention here is to emphasize the importance of Database Design on the Query Performance. All of us understand and agree that bad code can never provide us good performance; however, we seem to overlook that good (read: Excellent!) code working on a poorly designed database can never provide us the Performance we expect. Most organizations that I have worked with, have Architect Teams and Developer Teams; however, it is unfortunate that these teams work in their own silos without much collaboration between them.

Let us consider the example we have seen in my previous post in Scenario 2. The fn_CalculateTaxPaid function takes the EmpID as input, and calculates the Tax for that employee. What is the best way to retrieve the list of employees who have paid more than 10,000 USD as Tax? Do I want to write the query as:

 SELECT <Column List>
FROM <Table Name>
WHERE fn_CalculateTaxPaid (EmpID) > 10000

You might be saying – “Obviously not! You told me not to use a function on the Left of the Operator.” But then, what other options do we have?

We do have options; and this is one classic example how performance can benefit from a good database design. I can think of two ways at present, but I am sure that there will be many more ways to achieve the same.

Option 1:

Add a Computed Column to the table which stores the Tax paid by that employee. Now, create an Index on the Computed Column. Definition to add the Computed Column might look like the following:

 ALTER TABLE dbo.EmpSal 
ADD EmpTAX AS dbo.fn_CalculateTaxPaid(EmpID)
PERSISTED
GO

Now, create an Index on the EmpTAX column.

 CREATE NONCLUSTERED INDEX [IX_EmpTax] ON [dbo].[EmpSal] 
(
    [EmpTAX] ASC
)
GO

Now, if the original query is modified as below, can we utilize the Index and make the query faster?

 SELECT <Column List>
FROM <Table Name>
WHERE [EmpTAX] > 10000

Important Note: The article Creating Indexes on Computed Columns lists all requirements for defining Indexes on Computed Columns.

Option 2:

Let us assume that due to various constraints, you are unable to add an additional column to the table. In that case, we can make use of Indexed Views. We can use the following code to create an Indexed View for our purpose:

 CREATE VIEW [dbo].[vw_EmpSal]
WITH SCHEMABINDING 
AS 
SELECT [EmpID], [Salary], dbo.fn_CalculateTaxPaid(EmpID) AS [EmpTAX]
FROM dbo.EmpSal
GOCREATE UNIQUE CLUSTERED INDEX [IX_EmpID] ON [dbo].[vw_EmpSal] 
(
    [EmpID] ASC
)
GOCREATE NONCLUSTERED INDEX [IX_EmpSal] ON [dbo].[vw_EmpSal] 
(
    [EmpTAX] ASC
)
GO

 

Important Note: The Creating Indexed Views article lists all requirements for creating Indexes Views.

I hope, you can understand very well how, a great database design can influence Query Performance. A good Database Design is as important as writing good code to support your Business Requirements. There is no better ingredient for success than Great Design couples with Great Code.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.