Coding for Fast Database Connections
If you're a database developer, one of your most important tasks is to write your code properly, so pages load as quickly as possible. If your pages load slowly, your visitors are likely to go elsewhere!
Remember that every separate SQL statement you issue requires data to go back and forth to the database. Sometimes those handshakes and open/closing of connection can take longer than the actual database query! Look closely at all SQL statements you have on a given page. Try to combine them into as few queries as possible.
Next, run an 'explain' on each query to determine how the database will execute it. Make sure you tweak your indices and keys until the query runs as efficiently as possible. You want to avoid table scans - try to do your "where" clause to use indexed fields as much as possible. If you do a "where site_id = 1 and cat_id = 2", make sure you have an index set up on both site_id and cat_id.
When you do issue a SQL statement, use it as quickly as you can and then close it up. Don't leave it open while you do other things. Grab the values you need, stick them into variables, and then close up the statement.
Finally, be sure to set all connections back to nothing when you're done with them.
The more efficient your code is, the more quickly the page will get to the end user - and the happier your visitors will be!
SQL Command Listing for ASP