A colleague stops by to tell me their web app is running slow, some days are slower than others. I know the app a little bit and also know that he is right, it is slow in spots. So, I offer to take a look. I happen to know of a few stored procedures it uses, so i execute them and return results immediately. After poking around a bit more, I decide to fire up profiler and tune the settings to make certain only my connection is being traced.

Then, I go to the page that is causing the problem. The trace shows...nothing.

No activity for almost one full minute. Then, a bunch of lines, each query taking about a half second, and there are a few dozen of them, followed by more queries except these appear to be the same statements run three times, each taking a half second. then, and only then, do i see the procs called and the final results come back.

And what are all those lines? Well, they go something like this:

SELECT last_name FROM linkedserver_connection WHERE NAME = 'domain\person'
SELECT first_name FROM linkedserver_connection WHERE NAME = 'domain\person'
SELECT full_name FROM linkedserver_connection WHERE NAME = 'domain\person'

SELECT last_name FROM linkedserver_connection WHERE NAME = 'domain\person1'
SELECT first_name FROM linkedserver_connection WHERE NAME = 'domain\person1'
SELECT full_name FROM linkedserver_connection WHERE NAME = 'domain\person1'

and so on. This page today was going to display a dozen people, but some days it could be a lot more. The more people, the slower. Keep in mind, that for some reason the app did not even connect to the database for almost a full minute before this crap was even executed.

So, my friend wants to know what I can do to speed things up. My response? "Fire the person that built this, hire a six year old and a chimpanzee and have them work in an agile structure for about a week."

He did not seem to think that would help, but I do not see how it could be any worse than what they have.