January 27, 2004 at 11:44 am
Hi All, I have few access forms which is using store procedures as recordsources. Every time I open the form at the first time, I notice that the performance is really slow, but when I open it the second or third time, it will run a litterbit faster. Is there a way to improve this? It takes my store procedures 2s to finhish if I run them directly from the QA, but somehow it takes more than 10s from Access forms. Please advice. Thanks Minh Vu |
January 28, 2004 at 3:11 pm
Yes, I am using sql pass through query through ODBC.
Minh Vu
January 28, 2004 at 4:10 pm
I assume that it is the first pass through query that is slowest, not just this one.
What happens is that Access has to connect to the server when you first run the query. It takes time to establish that connection. After that the connection is established on the server, it doesn't have to do that anymore. It then runs faster.
I can't say for sure, but when you connect to your server using query analyzer, there may be a delay. Then you enter your query and it runs faster than the Access.
To compare speed, you have to include the Query analyzer connection time to the time running the query.
What I do is to "seed" the connection. I run a query that has the same connection string as the one in the form. Create a second pass through query with something like: "select top 1 * from Information_Schema.Tables" for its text. In code, you open a recordset with that query as its source. This will set up your connection.
Good luck.
Russ Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply