One thing we've discovered is that the network library SQL Server uses can be a factor. In several client locations we've discovered that using the TCP/IP Sockets library instead of Named Pipes has sped the data access up dramatically. I suppose it could be because Named Pipes requires authentication and TCP/IP Sockets doesn't, but for whatever reason, it worked better.
In addition to Josh's excellent advice on indexes and such, remember that SQL Server's optimizer looks for an index containing as many of the fields in your WHERE clause as it can, and that the index fields have to be in the same order as the fields in the WHERE clause for the optimizer to use that index. Also, never concatenate fields in a WHERE clause, e.g. WHERE field1+field2+field3 = value. This kills the optimizer.
>Thanks for the reply! We have an app that is using SPT and it still seems slow. I suspect it is the network setup. Do you have any recommendations on how to speed up the app?
"It is an important and popular fact that things are not always what they seem. For instance, on the planet Earth, man had always assumed that he was more intelligent than dolphins because he had achieved so much -- the wheel, New York, wars and so on -- whilst all the dolphins had ever done was muck about in the water having a good time. But conversely, the dolphins had always believed that they were far more intelligent than man -- for precisely the same reasons." - Douglas Adams
