Tips to increase your Transact-SQL efficiency Part 2

First Part : http://www.developerscloud.org/2013/09/tips-to-increase-your-transact-sql.html


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