Speed Up Large Database

[LadyDi]

I have a very large database (over a million records). As a result of the size of this database, it is very slow. I've done everthing I can think of to speed up this database, and nothing seems to work. I've compacted and repaired it. I deleted any fields that were not used. I made sure I had indexes and relationships set up in every table. Still, my database is slow. Some people recommend putting the back end of the database on an SQL server, others say that isn't a good idea. What do you recommend?

 

[shadow9449]

Is the application in a shared environment, in which case I would assume that it's split?

If yes, is there a persistent connection to the back end? Also, what is the network speed? I've seen big improvements using 1 GBPS over the old 100 MBPS.

SHADOW

 

[Pat Hartman]

Jet is very fast. Faster even than SQL Server when you can run the database locally. Once a LAN gets into the picture, you need to worry about how much data you are pulling over the wire. Because of the way Jet/ACE work, Access pulls lots of data over the wire so the larger your tables get, the slower the application becomes and there really isn't much you can do about it so at that point you need to look at alternatives such as SQL Server.

Simply converting your tables to SQL Server and not optimizing your app for client/server will probably make the app even slower. Once you make the decision to upsize, you need to make sure you take advantage of SQL Server's ability to run queries on the server and send back down the wire only the data requested. So the first step is to make sure that all forms are bound to queries with selection criteria that severely limits the rows returned. This means switching from using the filter property of the form to having search textboxes or combos in your form headers. As you change the search criteria, you requery the form and that sends the query to the server so the requested rows can be returned. You may also have to modify individual queries to remove any UDF or VBA functions that cannot be sent to the server for processing thereby forcing Access to request full tables to be sent down to the app for local processing. VBA functions and UDF in the Select clause are not a problem. Access just strips them out and then runs them on the recordset returned by the server. In other clauses, they can affect the rows that are selected so if of Moses can't go to the mountain, the mountain has to come to Moses. There are a number of good articles on optimizing Access for client/server which you should read before you begin.

Almost all of my apps end up with non-Jet/ACE BEs at some point in their lives so I develop my apps from day one with a client/server mindset so I can upsize any of my apps in under an hour when the time comes. Once you understand what you need to avoid, it becomes second nature and doesn't adversly impact apps that never get upsized.

 

[Galaxiom]

Be careful with indexes because they will slow down the appending of new records. Putting them on fields that are not used as criteria in queries is an unnecessary overhead.

As Pat says, optimising recordsources makes a huge difference.

I have had Access tables with a many as ten million records (and fifteen fields) still working fast on Selects. However I certainly didn't have any recordsources based directly on that table.

Sometimes a query can be vastly improved by restructuring it to reduce the number of records on one side of a join.

For example, the second query below can be much faster because it uses a subquery to reduce the number of records in BigTable that are being joined.

SELECT * 
FROM Bigtable 
INNER JOIN LittleTable 
ON BigTable.Field1 = LittleTable.Field1
WHERE BigTable.Field2 BETWEEN #1/1/2013# AND #1/31/2013#;
 
------------------------------------
 
SELECT *
FROM
     (SELECT *
      FROM BigTable
      WHERE Field2 BETWEEN #1/1/2013# AND #1/31/2013#
      ) AS Big
INNER JOIN LittleTable 
ON Big.Field1 = LittleTable.Field1;

(Note: This is a simplistic example to demonstrate the idea. The engine might even be able to optimise around it but I know from experience that this kind of change can make a very big difference on more complex queries.)