Three Ideas for Returning Microsoft SQL Queries Faster

Would you like to make your users happy? Give them the information they need through optimization that allows SQL queries to be returned faster.

Here are three quick items to add to your “do” list to generally improve performance by increasing system concurrency.

One caveat: these suggestions may not provide uniform results. These tips should be applicable across different types of databases, but your mileage may vary. Before plunging ahead, you may want to make certain your databases are fully backed up in case problems arise.

Partitioned Tables

Moving large amounts of data is always tough, but using partitioned tables can help you avoid large data moves. If you have SQL Server Enterprise, you can take advantage of this feature.

All tables in SQL Server are partitioned from their creation. They can be further separated into multiple partitions as the user wishes. That’s not necessary for this technique to work properly.

Sean McCown, writing in InfoWorld, says he had a client with a convoluted workaround for loading data at the end of a business day into an archive table before the data was deleted from the live table. Eventually, the multi-step process became cumbersome, taking 1.5 hours for each of several tables to copy.

Using the features in partitioning can alleviate some of these and other bottlenecks. In this case, the single SWITCH command was able to take the place of two other commands. McCown notes:

The SWITCH command allowed the company to avoid all of the writes because it assigned the pages to the archive table. It’s only a metadata change. The SWITCH took on average between two and three seconds to run. If the current load ever fails, you SWITCH the data back into the original table. This is a case where understanding that all tables are partitions slashed hours from a data load.

Forget Cursors

Another way to optimize query returns is to forget cursors. Period.

There’s not a database professional you’ll find who suggests using cursors with SQL Server. These slow the system thanks to the overhead they create.

First, ask yourself if you really need to use cursors. If you don’t, don’t use them! If you can’t escape their use, try using temp tables instead of bogging down the system.

By comparing data in a temp table rather than in a live environment, you reduce the time the UPDATE statement needs to compare data against a live table. And there’s less data to compare. This workaround can eliminate concurrency problems.

Temp-Valued Functions

Using table-valued functions is another great trick to keep the system performing well and one Microsoft advocates:

User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

Some additional solutions designed to help you more fully leverage the time and resources invested in your SQL Server are discussed in detail in a white paper Top SQL Server Issues, by Andy McDermid, Datavail’s SQL Server principal database administrator.

To learn more about SQL performance tuning or to find out more about how we can tailor a solution for your organization’s specific SQL needs, please contact Datavail.

Image by nicemonkey/123RF