In databases, as in other programming,  one key to good code is to evaluate not only _what_ you are doing but _when_. A good example of this is a performance tuning I just finished up on a sproc which is the bane of this application.

It's a search through the database and it uses full text search and security tights need to be taken into account to filter the results. On top of that, it allows for the creation of complex search criteria.

To implement the complex search, I am actually taking an XML block and running multiple searches, one for each row of criteria and then AND-ing and OR-ing the results as required. Since this application has a web front end, results have to be available in 30 seconds or less, else the web times out.

Now, I have come back to this sproc several times to find ways to clean it up and almost every time the performance gains have been finding something being done in an inner loop (thus executing multiple times) and moving it to a more outer loop where it executes fewer times.

Yesterday's tuning consistently brought the sproc into 15 second range, which is alright for now but I know I am going to need to make a fundamental change in database design for more improvement, but that is another day and another post.