Optimizing Your WHERE Clause

The WHERE clause is one of the most commonly used optional part of a query. Simply put, it filters out rows and narrows the number of rows returned by a query based on the condition included in the clause.

It's a common misconception that the SQL optimizer will always use an index whenever the table has a useful one. This is not always the case. In some cases, index will not be use and a table/index scan will be performed resulting in slow processing.

It's also widely accepted that the arrangement of expressions and the operator used does not matter, since the optimizer will parse and prepare an execution plan for the query anyway. Although this is true most of the time, arranging the logical expression properly can improve processing.

Here are some consideration that I keep in mind whenever I build my WHERE clause.

Avoid using expression that has a function on a column. This will prevent the optimizer from using the index and instead perform a table/index scan.

This query will not use an index:

select *
from AdventureWorksDW..FactInternetSalesReason
WHERE substring(SalesOrderNumber,1,2) = 'SI'

Modified, this one will use an index:

select *
from AdventureWorksDW..FactInternetSalesReason
WHERE SalesOrderNumber like 'SI%'

If the use of a function can not be avoided, use an indexed computed column instead.

If the SQL Server is not configured to be case-sensitive, do not bother using LOWER() or UPPER() functions .

These are three identical queries that will return identical results:

select * 
from AdventureWorksDW..DimGeography 
WHERE CountryRegionCode = 'AU'

select * 
from AdventureWorksDW..DimGeography 
WHERE upper(CountryRegionCode) = 'au'

select * 
from AdventureWorksDW..DimGeography 
WHERE CountryRegionCode = 'au'

The second query, however, will not use an index.

Use the equal (=) operator to compare two strings instead of LIKE.

These two queries will return the same results:

select * from AdventureWorksDW..FactInternetSales
WHERE salesordernumber = 'SO43703'

select * from AdventureWorksDW..FactInternetSales
WHERE  salesordernumber LIKE 'SO43703'

The first query is more efficient than the second one. If the LIKE operator can not be avoided, use as much leading character as much as possible. If the application performs too many LIKE operation, consider SQL Server's full-text search option instead.

select * from AdventureWorksDW..FactInternetSalesReason
WHERE salesordernumber like 'S%1'

The above query will perform faster than the query below:

select * from AdventureWorksDW..FactInternetSalesReason
WHERE salesordernumber like '%1'

Although they are not identical, given the choice, use the former rather than the latter.

Here are the most common operators in WHERE clause arranged based on best performing first:

=
>, <, >=, <= 
LIKE 
<>, NOT

Avoid using NOT operator as much as possible. Although not always the case, WHERE clause that uses the NOT operator does not utilize index.

select * from AdventureWorksDW..FactInternetSales
WHERE not ShipDateKey >= 10

will perform faster as:

select * from AdventureWorksDW..FactInternetSales
where ShipDateKey <10

Given the choice, use EXISTS() instead of IN(). Moreover, IN() have some issues handling with NULL values.

Force the optimizer to utilize an index by using index hint on the query. Use this as the last resort for optimization.

Given the choice, use BETWEEN instead of IN. The BETWEEN operator performs faster than IN.

If the clause have multiple logical expression connected by two or more AND operators, locate the expression that will be LEAST likely to be true. This way, if it's false, the clause will immediately end. If both expressions are equally likely to be false, test the least complex expression first. This way, if it's false, the more complex one need not be tested. Also, consider creating an index for a selective column or a covering index for the query.

If the clause have multiple logical expression connected by two or more OR operators, locate the expression that will be MOST likely to be true. This way, if it's true, the clause will immediately end. If both expressions are likely to be true, test the least complex expression first. This way, if it's true, the more complex one need not be tested.

Remember that IN operator is actually another form of OR, so place the most probable value at the start of the list.

A query will perform a table/index scan if it contains OR operator and if any of the referenced column does not have a useful index.

This query will perform a table/index scan. For this query to utilize an index, there must be an index on all three columns in the clause. Even if two out of the four columns have an index, it will still perform a table/index scan.

select *
from AdventureWorksDW..FactInternetSales
where ShipDateKey = 3
or PromotionKey = 3
or SalesTerritoryKey = 5
or SalesOrderLIneNumber = 3

If creating an index for each of these columns is not an option, rewrite the query to use UNION ALL (not UNION) instead. This way the query with useful index will be utilize, even if it's just the one or two out of the four query. It will still execute more efficiently.

select *
from AdventureWorksDW..FactInternetSales
where ShipDateKey = 3
union all
select *
from AdventureWorksDW..FactInternetSales
where PromotionKey = 3
union all
select *
from AdventureWorksDW..FactInternetSales
where SalesTerritoryKey = 5
union all
select *
from AdventureWorksDW..FactInternetSales
where SalesOrderLIneNumber = 3

The above query will give the same results but will run faster than using multiple ORs. If ShipDateKey and PromotionKey have useful index, it will use their respective index for that part of the query, improving the speed of the entire query.

Given the option, use EXIST or a LEFT JOIN instead of IN to test/compare the relationship of a parent-child tables.

Beware of redundant clause.

This query have a redundant WHERE clause:

select DueDateKey, ShipDateKey,
PromotionKey, CustomerKey
from AdventureWorksDW..FactInternetSales
WHERE
DueDateKey = 8 and ShipDateKey = 8 and
PromotionKey = 1 or ShipDateKey = 8 and PromotionKey = 1

ShipDateKey = 8 and PromotionKey = 1 is a subset of DueDateKey = 8 and ShipDateKey = 8 and PromotionKey = 1. What the optimizer will do is it will return all the rows requested.

Here's the result set:

DueDateKey  ShipDateKey PromotionKey CustomerKey
----------- ----------- ------------ -----------
13          8           1            11003
13          8           1            14501
13          8           1            21768
13          8           1            25863
13          8           1            28389

It may look like the query returned the requested rows efficiently. In reality what happen is, it returned these rows twice then perform a DISTINCT to remove the redundant rows.

Convert frequently running batch to stored procedure, specially, if the queries in the batch use user-defined variables. The optimizer might not take advantage of a useful index to run the queries in the batch. This is because the optimizer does not know the value of these variables when it chooses a way to access the data. If the batch is not frequently executed, consider including an INDEX hit on the query.

As much as possible, include a WHERE clause to the query. This will improve the way SQL Server retrieve the data.



~~CK

Using Encryption to Store Sensitive Data

SQL Server, even the earlier version, has a significant number of ways to protect data from unauthorized users (ie. Views, functions, user rights, etc). However, administrators and database owners can still view table contents that might be too sensitive for anyone to see, like password stored in tables that are frequently used by UIs and other front-end apps.

SQL Server 2005 has an undocumented system function that can be use to encrypt a string to store encrypted information. It should be noted, however, that the function is a one way hash encryption. It means you can not decrypt the stored data. The only way to use it is to compare it with an encrypted string.

declare @string varchar(50), @EncryptedString varbinary(max)

set @string = 'correctpassword'
set @EncryptedString = pwdencrypt(@string)

select
pwdcompare('correctpassword',@EncryptedString ) correctpass, 
  pwdcompare('wrongpassword',@EncryptedString ) wongpass

Here's the result set:

correctpass wongpass
----------- -----------
1           0  

This function is useful if you don't need to display the encrypted data later. If you need the decrypted data, later (like encrypting date of birth data then you'll need to display it later),it would be better to use a customized encryption function.



~~CK

How do you get the actual size of a Database?

How do you get the actual size of SQL Server Database? Quick answer: execute a sp_spaceused command. However, I found this excerpt from Books Online "When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections…..There are some situations, for example, after an index is dropped, when the space information for the table may not be current." In short, sp_spaceused is not always accurate.

For more accurate calculation of database size, try the following code:

select 
cast(size/128.0 as numeric(10,1)) as [size-mb], 
cast(FILEPROPERTY(name, 'spaceused')/128.0 as numeric(10,1)) as [used-mb], 
maxsize [maximum size-mb], 
cast(growth*8/1024 as varchar(11))+
 case when status&0x100000>0 then '%' else '' end [growth], 
isnull(filegroup_name(groupid),'log') [groupname], 
cast(100*(size/128-(FILEPROPERTY(name,'spaceused')/128))/(size/128.0)
   as int) [% free]
from sysfiles

Here's the result set:

size-mb  used-mb  maximum size-mb growth   groupname  % free
-------- -------- --------------- -------- ---------- -----------
500.0    10.4     -1              64       PRIMARY    98
58.0     5.3      268435456       16       log        91

~~CK