Computed columns saves CPU

There was a query causing some high CPU, with 14 LIKE statements on a large table.  Which is a recipe for disaster.  But business dictated that this actually was a requirement to search the columns in this way.  For a while, I just let it go, but then when some CPU pressure happened on the server, I took a second look.

The REPLACE statement to remove dashes accounted for 70% of the amount of CPU needed.

AccountNumber LIKE ‘%00056127%’ OR
REPLACE(AccountNumber,’-‘,”) LIKE ‘%00056127%’ OR
Address1 LIKE ‘%00056127%’ OR
Address2 LIKE ‘%00056127%’ OR
Address3 LIKE ‘%00056127%’ OR
Address4 LIKE ‘%00056127%’ OR
Address5 LIKE ‘%00056127%’ OR
Name1 LIKE ‘%00056127%’ OR
Name2 LIKE ‘%00056127%’ OR
Name3 LIKE ‘%00056127%’ OR
Street1 LIKE ‘%00056127%’ OR
Street2 LIKE ‘%00056127%’ OR
City LIKE ‘%00056127%’ OR
Zip LIKE ‘%00056127%’

A computed column ended up being an ideal solution for this.  It drastically improved the performance of the query.

Before/after performance:
CPU time = 18,299 ms, elapsed time = 9,538 ms.
CPU time = 6,567 ms, elapsed time = 3,322 ms.

Also, it caused fewer reads on a different Table2 because of a different execution plan:
Table ‘Table2’. Scan count 3, logical reads 144041
Table ‘Table2’. Scan count 1, logical reads 10

The computed column was built on Alpha like this.


ALTER TABLE Table1 ADD AccountNumberNoDash
AS ( REPLACE(AccountNumber, '-', '')) PERSISTED
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s