query performance

[GShen]

Not sure where to post this. Running a report calling for 4 parameters. The adp is connected to a server where the data is not stored. The stored procedure which runs this report executes the stored procedure on the server which has the data. It only brings back 10-20 records. When I run it in studio management it takes anywhere from 5-10 seconds. From Access, it just hangs and hangs and hangs. I put a bunch of hints in the SELECT to assist with indexes but like I said, it runs from Studio fine. What in the world is causing all the overhead in Access ? Running sqlserver 2005. Access 2007.

Remember when... everything worked and there was a reason for it?

 

[ajrimmer]

Have you installed SP2? Solved a couple of performance issues for me.

 

[GShen]

Running sp2 in Access. On sql2005 running sp3. I am able to run the sp from an access db. as a stand alone query. When I run it as a stand alone on the server which is pointing to the other server, it hangs. A few weeks ago, I thought I had this fixed. Maybe the data was cached. We did indeed reset one of the views it uses (historical) a few weeks ago but like I said, it works fine in studio and in Access on the server it resides. Something is bottling this thing up.
Thanks. I will keep plugging away. Should I post this under queries ? or SQL server ?

Remember when... everything worked and there was a reason for it?