Tips to optmize SQL query Part II

Beside of SQL statement itself, there are other issues could effect query performance.

1. SET TALK OFF, SET ECHO OFF, SET ESCAPE OFF
Turn these settings OFF could speed up your query for 2X.

2. Close your result set once your don't need them anymore before run your next query.
I have a complex report that requires 10 tables with more than 1,000,000 records to be processed. It takes 10 minutes to be generated. After I placed code to close any intermediate resultset once no longer need, the performance is much improved by only require 4~5 minutes to process all.

If your machine has 512KB, or up to GigaByte of RAM, these tips may not help much. However, it may safe your life on machine with limited memory spaces. It is a best practise while writing code.

Even though the tips above are the most basic technique for optimization. However, we always forgot or undertake it.

Hope it helps