Big problem with access - very slow page load

Hi

I have an access database being loaded from an asp.net app, and the home page actually takes 16 seconds to load (20 seconds the first time its run). There are alot of queries and I am wondering if I switch to SQL Server and put those queries in Stored procedures instead will this speed things up?

Any one have any ideas?

Thanks

 

I know that Bruce is really not an Access fan, but I have had excellent results using it. Maybe you can post a little of your data access code and it will trigger some ideas.

 

It helps to speficy the fields to return in the recordset also, instead of always using 'SELECT *' for every query. Only select the fields you need.

I've also had pretty good luck with Access, too, until a half-dozen or more users tried to use the application at the same time. Then the page timed out consistently. Good code will really help. You may try looking up 'access odbc improving performance' on google or something like that to see what you could find.

I switched to SQL server and love it. No problems whatsoever.

Ben

 

name the fields in your SQL: eg. Select table.field1, table.field2, etc

create the sql in Access and save as a query: then call that query in your ASP (like a Stored Procedure)

if you are doing a loop, always use GETROWS - this makes a huge difference.

Cache the info if it doesnt update regular - eg. set it for 2 hours or 1 day, put in an application variable (only a string though)

Use Option Explicit and DIM always

DIm at top of Page or top of Procedure

Really depending on the code and database, but if you use response.buffer at the top you can also use Response.flush before the next/loop to write the code to the users page as it is created

show us some code or something.

Here are a couple worthwhile links: I know SQL server but alot of the same techniques can be used with Access:
http://www.sql-server-performance.com/asp_sql_server.asp

and for Access primarily:
http://www.blueclaw-db.com/access_consultant_rapid.htm

 

also if you have alot of data in a memo field, or using alot of memo fields that will also cause it to take longer to load.