We have seen cases where performance suffers on queries performed via ODBC or other ADO style connections. This is particularily evident when an Access-based application is ported to SQL server and contains searching or sorting on date or memo fields.

Other delays can occur if there is record locking occuring due to multiple pending updates or deletes on the same record. Usually these delays last for a short time then clear themselves once all processing has had time to complete.

Generally, its best to design the application with performance in mind (ie: index all join/where/orderby columns and so on). To test SQL you can usually run the query directly against the SQL server using Query Analyzer or other SQL Server client tool.

Note: When testing new code/queries, use "set showplan on" to see the query steps and (secondly) "set noexec on" to avoid interfering with other users and/or databases on the same SQL Server.

If the SQL runs quickly in the Query Analyzer then test the same query from the website. If the site is significantly slower, the likely culprit is ADO. In a case like this, you may need to create a view on the SQL server side, to ensure all database joining, filtering etc. is being performed on the DB server, not the web server. Alternatively, make use of a well tested stored procedure.

Other things that can help include removing the * from select statements and only retrieve fields you need. This can greatly improve performance if you are retrieving memo or large binary fields that you are not using.