The value of a website can be placed on the SQL Server performance. If the SQL Server isn’t able to produce results in a reasonable amount of time, the website visitors lose patience and move on to another domain. Some simple ways to improve SQL Server performance is implementing the right tweaks to the select statements. These few changes to the code can mean the difference between a SQL Server that produces results within a few seconds or several minutes.
Optimize SQL Select Queries Using Table Indexes
When the programmer queries information, the “where” clause should contain a table field that is a part of the table index. A table index is normally created by the database administrator, and it decreases the amount of time it takes for a database server to find records. It’s advantageous for a database administrator to insert table indexes on fields that are commonly queries. For instance, if the database table has an index on the last name for a customer, the most efficient way to optimize SQL select statements is to use the last name field. The following is an example of a SQL select statement that queries the customer’s last name:
select first_name, last_name, customerID from customer where last_name like ‘%smith%’
The statement pulls all customers who have a name with the string “smith” in it. This is different than a SQL select statement that pulls only customers with the exact “smith” string. The following query retrieves only customers with a last name of “smith.”
select first_name from customer where last_name=’smith’
Specifying Column Names in SQL Select Statements
The asterisk (*) character is used to return all columns in a table. This character is used in SQL Server queries, but it’s a lazy implementation. It also reduces performance on the server. Instead, SQL Server developers should specify each column in the queries. For instance, the following query leads to poor SQL Server performance:
select * from customer
To optimize SQL select queries, it’s better to specify each column needed for the results. The following query is more beneficial for performance:
select first_name, last_name, address from customer
Using more specific columns with a where clause that has an index associated with it greatly improves performance. These query styles should be implemented for all queries including on-the-fly queries created in the SQL Server Management Studio software. Stored procedures should also follow this standard, since these objects are used for website queries.
SQL update statements also provide a way to optimize the SQL Server. These statements are used to edit data in tables. Along with select statements, update statements should also be optimized to avoid slowness issues on the database server.