We all knows that the SQL provides the basic functionality, in terms of what can be searched for or filtered by.
But some more advanced functions may be obtained, if the user knows a few tricks. Here are given few very basic tricks which can help to speed up the executions of your SQL scripts
Introduction
We all knows that the SQL provides very basic functionality, in terms of what can be searched for or filtered by.
But some more advanced functions may be obtained, if the user knows a few tricks. Here are given few very basic tricks which can help to speed up the executions of your SQL scripts.
Objective
There are many online sites and books available to teach you the SQL scripts and optimization techniques. Few of them exclusively focus on tricks and few focus on finding solutions. My goal here is to provide few basic SQL tricks that can help to boost the query performance and streamline some sql writting guidelines.
Using the code
Given below are little known tips that you can use to ensure your Transact-SQL queries are performing in the most efficient manner possible. 1. Avoid '*' in select query.
Restrict the queries result set by returning only the particular columns from the table and not all the table's columns. The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
2. Avoid COUNT(*) in select statement to check the existence of records in table.
Instead use IF EXISTS() to check records.
- Write the query as:
IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)
- Instead of :
SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’
3. Use alternate of SELECT COUNT(*). Use an alternative way instead of the SELECT COUNT(*) statement to count the number of records in table.
- SELECT COUNT(*) statement makes a full table scan to return the total table's row count which can take an extremely long time for large tables.
Use alternate query to achieve the same
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2
4. Use TOP keyword or the SET ROWCOUNT to fetch first Nth row from table.
- TOP or SET ROWCOUNT clause restricts the number of result and returns the smaller result set. This helps to reduce the data trafic between server and client.
5. Use ORDER BY clause with Primary or Indexed column of table
- Fetching the result set order by primary column or Indexed column added big performance benefits as SQL server don't have to perform the extra overheads to rearrange data. Also try to implement the ORDER BY clause on Integer column instead of VARCHAR or STRING column.
6. Avoid ORDER BY on multiple columns.
- Implementing the order by clause on multiple column degrade the query performance as the SQL server has to run data sorting algorithm independently on each column or result set.
7. Use 'WHERE' instead of 'HAVING'
- 'HAVING' clause is used to filter the rows after all the rows are selected. It is just like a filter who filter data from selected list.
- 'WHERE' clause work along with select statement to select only respective rows Do not use HAVING clause for any other purposes.
8. Avoid Mathematical expression on column. Avoid mathematical expression on column in WHERE clause. We should avoid computation on columns as far as possible and hence we will get an index scan instead of a seek
- For example : SELECT * FROM Orders WHERE OrderID*3 = 33000
degrade the performace as query performing calculation on column
9. Minimize the number of subquery block in your query.
The more number of sub query makes the execution plan complicated. Keep the script logic as simple as possible.
10. Use 'LIKE' clause in query instead of SUBSTR() function.
The SUBSTR() function first extract the value from data and then matching result get added in result set. LIKE clause has potential great advantage over SUBSTR() whenever string data need to compare - Write the query as:
SELECT id, first_name, age FROM student_details WHERE first_name LIKE 'ABC%'
- Instead of :
SELECT id, first_name, age FROM student_details WHERE SUBSTR(first_name,1,3) = 'ABC';
11. Use 'BETWEEN' operator instead of >= and <= operators to select data in range.
12. Wisely use the EXISTS,IN clauses in sub query select statement.
- IN has the slowest performance as data is filtered between the range.
- IN is efficient when most of the filter criteria is in the sub-query.
- EXISTS is efficient when most of the filter criteria is in the main query.
13. Avoid 'NOT IN' in select clause. Because when we use “NOT IN” in SQL queries, the query optimizer uses 'Nested table scan' technique to perform the activity
14. Use Stored Procedure, functions(UDF) and views instead of heavy-duty queries.
- The application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time). This can reduce network traffic as your client will send to the server only stored procedures, function or view name (perhaps with some parameters) instead of large heavy-duty queries text. The use of SP, UDF or view also improves DB security as this can be used to facilitate permission management. You can restrict user access to table columns they should not see.
15. Use 'SET NOCOUNT ON' statement into your stored procedures or UDF.
Bydefault the SQL server returns the number of affected rows for each query in SP or UDF functionality. If the SP or UDF involves lots of insert,update or delete statement (DML scripts) then this messeging mechanism will consume lots of I/O resource as for every query execution it send back the number or rows affected.
'SET NOCOUNT ON' statement at the begining of SP or UDF body skips the messeging and never return how many rows altered through DML scripts.
16. Use constraints instead of triggers.
Constraints are logical conditions setup on table which allow/restrict the operation on table. Where as Triggers are special block of program (like Stored procedure) which get execute on certain action on table like insert,update, delete. Triggers and constraints both are used to maintain the integrity of database but constraints have more benefits over triggers as it uses very less resources.
17. Use table variable over temp table object. Use table variables objects instead of temporary tables object. Table variable object declare with '@' symbol where as temp table object is declare with '#' sign as the suffix of table name.
#tempTable -> Temp. table object
@tempTable -> in-memory table variable
A table variable is created in memory, and so performs slightly better than #temp tables. Also because there is even less locking and logging in a table variable.
Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data. The SQL server mentain very less log related table variable and log activity is truncated immediately.
while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts
18. use UNION ALL statement instead of UNION, whenever possible
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, while the UNION statement does look for duplicate rows, whether they exist or not
19. Use DISTINCT only whenever it is neccessery to use.
DISTINCT clause filter the result for unique combination of all columns in result rows which results in some performance degradation. You should use this clause only when it is absolutely necessary and should be used with minimum columns on table.
20. Use Indexes on table
Database indexes are similar to those you can find in libraries. They allow the database to find the requested information faster, just like a library index will allow a reader to find what they’re looking for without loosing time.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order.
Conclusion
There are many more performance improvement techniques in SQL script writing. But the given above are very generic techniques which can be adopted and implemented in our everyday scripts and can make a habit of writing optimized sql scripts
Reference
1.
https://play.google.com/store/books/details?id=X_FPu1eluu0C&rdid=book-X_FPu1eluu0C&rdot=1&source=gbs_vpt_reviews
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.