Select Page

Three Ideas for Returning Microsoft SQL Queries Faster

Author: Andy McDermid | | January 13, 2015

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

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS