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

 

[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.)