Ado Examples and Best Practices

A customer wrote that they had a performance problem executing a stored procedure. They had a table with serial numbers (about 500,000 records) and when performing the following query they got a timeout:

SELECT Title, Type FROM Titles where serialno like '%a0332%' and type = 1

Now the problem is the wildcard before the serialnumber. They discovered with some help from us, that this approach causes SQL Server to execute a table scan, hitting all 500,000 rows. However, by keeping the % wildcard at the end of the expression, the performance is much higher.

Table scans can take a long time. If you think this is necessary, check whether there is a specific section of the serialno that users tend to want to search on—for example, the second through the sixth position of the key. It may make sense to break that portion of the serialno out into a separate indexed field if you're planning to do this type of search very often. If that's not an option, you might try selecting into a temp table using only the criteria that can allow the optimizer to use an index, and then search the temp table using the wildcard search. This should greatly reduce the number of rows that need to be scanned.

Remember that temp tables and SELECT…INTO are usually frowned upon because they place exclusive locks on the sysobjects, syscolumns, and sysindexes tables in TempDB, which can interfere with other processes going on at the same time. This can cause serious degradation in overall server performance, so this solution should be considered only as a last resort. Having said that, I should now say that I have had to resort to this solution a couple of times myself with no serious ramifications (so far). So, what you need to consider is how often will the SELECT…INTO #temp be performed, how many concurrent users will be running it, and how long does the actual SELECT…INTO #temp take? Keep these factors in mind when deciding whether or not to use this approach.