Difference in execution times between SQL Server Studio and the web

[rcombs4]

Hey guys, yesterday I went back and reworked a few reports in order to increase performance. On one of the reports I was able to reduce the execution time from about 30 seconds to just a few seconds. That report ran off a stored procedure, so I was working directly in SQL Server Management Studio. I ran the SP there and got an execution time of just a few seconds. But when I ran the report out on the web the execution time was still like 30 seconds. I knew something was not right so I messed around with it for a few moments and then just went home for the day. Then this morning when I got in I ran the report and it was still really slow. I ran the SP in SQL Studio, just to make sure it was still fast, and it finished in a few seconds. Then, because I like to run my code over and over again hoping it will magically fix itself I ran the report on the web again. And it did fix itself. It finished the report in about 10 seconds.

I called it a fluke and was going to just continue to work. But now I'm experiencing the same thing. I've got a SP's execution time down to 11 seconds when I run it in SQL Studio. But when I run it on the web it is still 30 ish seconds.

So any ideas why I'm experiencing this would be helpful. The DB server and the server I'm running these reports are not the same. So I'm connecting to the DB through a web service. I've also tried connecting to the db directly and i've seen no difference. And the SP returns a 4 column table with 16 rows, so it's not like there should be a long delay for the web to actually download the results from the DB server.

Thanks!