Query Time and Network Traffic

I've got a pretty large db (150 gig -- 150mil records) and I've been struggling to write queries that run in a reasonable amount of time. Well, after doing everything I know to do (analyzing the queries, properly indexing fields, etc.) I've managed to get some pretty fast, efficient queries done. When I first took responsibility of the db, the queries I was writing were running in like 30 minutes. So, I've managed to cut them down to under a minute on average. That's all great, but I was messing around last night (when nobody else is at work...we have quite a large network) and all my apps using this db were running queries INSTANTLY. I mean REALLY fast! So, do you think it's safe to say that I've just done a really good job, and there isn't really any more I can do as far as query time when there is a lot of traffic on the network?

It never really occured to me that network traffic might slow down your queries. (Not that much anyway) Is this something anyone else has experienced before, or am I just crazy?As far as optimization of queries by applying indexing strategies, you probably exhausted your options. The next step will be to see if some denormalization can be introduced to minimize number of joins per query. Of course all this is only if users are not returning the world back. You're absolutely right when suspecting the network traffic. It's especially true when large resultsets are floating around.|||Network traffic won't slow down SQL Server's processing of your queries, but it will definitley slow down the return of large result sets.

But low network traffic is probably not the only factor affecting the speed of your queries after-hours. It is also likely that there were few if any other users accessing your database tables and locking resources for inserts, updates, and deletes. If your database where you are running these queries (they sound like OLAP queries) is also used for transaction processing, then these two functionalities my frequently fight for resources. Consider creating a copy of your database (use replication to keep it synchronized) and thus split the processing load between two servers.

blindman