query optimization

I truly believe that no query should be longer than 32 seconds to return data. If it takes longer it should be checked for performance optimization. Of course there are some exceptions to this but for the most part, most people do not like to wait longer than 30 seconds to retrieve data. They will move on to another site or try to get their information a different way if they are forced to wait extended period of time for data elements to return.

I first try to limit my query to the simplest return set if possible. I try not to do too many sorts, calculations, aggregations, etc in my initial return set. I try to stay away from too many where clauses. For example if I am trying to retrieve data within a date range and I want to do more with the data after I get the initial subset, I will try to break it into two queries. I also try to break my procedures down whenever possible so that they can be ran from start and stop points. What I mean here is that if I am doing a lot of data manipulation for a report. I will first grab a subset of data, then I will do another subset, then another, until i end up with a final output set of data. This way if there is a problem, I don't necessarily have to run everything from the beginning in order to try and diagnose an issue. I try to split the data in like items. Like select information for a particular department within a date range. (I put this return set into a temp table) then from there, I may create another subset that joins to another large table but only has to search through the smaller subset to get the data back. Then I will put the calculations and transforming into another temp table and then I will finish all the prettiness into a final subset of data. I find this works for me and it is really fast if you need to find data issues quickly. You don't have the headache of reading through excessive amounts of code to find a small tiny data issue, or code issue. I sometimes will right the big procedure as one huge select then I will actually go back and break it up especially if I think it will increase query and return set peformance. I usually always leave the end user parameters for sorting to the final return set. I am not sure if all end users really know what they are looking for.