5 Ways to Improve Execution Query in SQL Server

SQL Server is the popular products to store data in database. When the database growth up to large we need to care about two things. The first we must care about security. We could make schedule to backup and set policy to the user. And the second we should know when the data is large we will has problem with queries data from database. Why it make queries data slowly? Because it has a lot record, need take time to find data that we want. To increase performance of query in database is not

easy while we have more records in table.​ But these tips can help you to make queries in your database faster.

1. Change your Select Syntax 

I think many developers never think about this point. They are very lazy to write the query to select multi columns instead “*” in theirs queries. However it show the same result but when we select data by using “*” it required to reducing I/O in memory between database and application layers. Please 

change your habit query.

Select * from tablename

Change to

Select column1, column2, column3, … from tablename

2. Make maintenance schedule to rebuild index

All developer always use index for improve querying data from database. But we need to make schedule or plan to rebuild index that it help to optimizer when we execute query. Why we need to rebuild index? Because the index will out of date, we need to make new setup.

3. Use functions in WHERE clauses

This point in the search arguments (SARG’s) we are not recommend using function, we use the search arguments is better while the table has index or don’t have index. 

Please see below example:

Select productname 

from tblproduct

where productname like 'Co%'

Select productname 

from tblproduct

where productname substring(productname,1,2)='Co'

The first query is the best to run query.

4. Create table need to set primary key

We make sure when we create table, we need to set primary. Because it can help running faster when query data.

5. Use store procedure

Other way that you don’t miss in query data, please use procedure. The procedure is a good performance because procedure has execution plan to pull out of cache and the server don’t need to work out. It don’t need to more memory. It is really execute faster.