Access Database Too Slow - Make Faster!

How To Make Access Database Faster

This summary provides Microsoft Access consultant tips and recommendations for making Microsoft Access databases faster. This information, combined with other pages on the site, will help the novice MS Access consulting professional to become and expert at building better, faster, more efficient databases.  Prospective clients can get an idea of what we know and how we design databases to be fast and reliable.

These techniques, combined with our Table Design Recommendations, give us the MS Access consulting tools to create databases with good performance.  Some of these recommendations are simple to implement and some are more difficult but they all speed up your Microsoft Access Database.  If you don't have the time to invest in these techniques or your current developer isn't able to implement them then please give us a call. 

1.	Table design considerations if you Access database is too slow
	a.	Lean towards several long tables rather than one wide (many fields) table.  Normalizing the tables will tend to lead you to this goal.
	b.	Whenever possible, use lookup lists for text fields.  Design the lookup lists with an auto number field to link a lookup table with the main table.  See our Access consultants
	c.	Do not use memo fields if at all possible - they can cause Access databases to crash under some circumstances.  Free-text data should be as short as possible to convey the information! Abbr's work great IMHO :)
	d.	Create an autonumber field for tables linking to subordinate tables.  Index this field with no duplicates.
	e.	Always try to use one or more data fields (not autonumber field) to create the primary key for a table.  See more about creating primary keys.
	f.	Generally, don't store calculated values as fields in the table - calculate them on the fly as needed.
	g.	Normalize the table but don't over normalize it - there can be too much of a good thing in sometimes.
	h.	Indexes on other fields will be discussed in section 3. below
	i.	All text fields longer than a few characters should have Unicode Compression set to Yes.
	j.	If you have master/detail relationships defined in your database then consider changing the table property: SubDataSheet Name from AUTO to NONE.  This property can be seen in design view of the table.  This prevents Access from dragging around the subdatasheets (detail tables) when you open a linked database
	k.	If you absolutely need top performance then consider optimizing the data types used to store data in the tables.  For example: if you have a lookup list that will NEVER have more than 255 entries then consider using a Byte field as the ID - you won't be able to use autonumbers and you'll have to increment the IDs programmatically. See techniques specific to speeding up slow MS Access forms. This technique works best for static lists.  Next is to change the 8-byte date fields to simple integer fields (2 bytes) by calculating days since some date, such as 1980.  You then use the dateadd function to display the date in normal format.  This saves 6 bytes for every date field in a table.  Also, sorting and where criteria can usually work directly off of the integer date - saving even more calculation time.  End result could be doubling or tripling the speed of table access for some tables.
2.	Table relationships
	a.	Create table relationships using the autonumber field from the master table to the corresponding long integer field in the subordinate table
	b.	Use the relationships screen (tools... relationships...) to relate a master table with a subordinate (detail) table.
	c.	Enforce referential integrity in the relationships definition popup whenever possible.
	d.	Always check the 'cascade delete related records' attribute in the relationship setup screen whenever possible - you may not be able to do this when relating lookup tables because then field in the main table can never be blank; lookup fields are sometimes not filled in at time of record creation.
	e.	When using our table design recommendations you should never check the 'cascade update related fields' attribute
3.	Query considerations and tips to make Access faster
	a.	Always index fields that you put in the criteria section of the query design screen.  Or, in other words, index all fields on the right side of a where clause.
	b.	If all else fails: split the query into two or more queries.  Restrict as many records as possible in the first query and use the query as input to the next query.
	c.	If b. fails too, then make the 1st query a make table query to store intermediate results in a table.
	d.	Use stored queries only.  Don't create a form that has a select statement as the record source - the query will either not be optimized or optimized every time it runs.
4.	Report and form considerations - filtering records and combo boxes
	a.	Try to restrict records and perform calculations at the query level and not within the form or report (using a filter) unless absolutely necessary
	b.	When splitting a database into a front end and back end.  Put all static lookup tables in the front end - improving performance by reducing network loading and will conserve memory (so... you don't link to the back end database for these tables).
	c.	For list boxes and combo boxes use stored queries anytime the query is based on a long table or the query requires joins. (see why in 3.d.)
	d.	If you are have thousands of entries in a combo box your form may be slow to open and slow to bring up the list of values.  One trick is to have another text field for value input.  This text field is used to restrict the list of values that are returned by using the afterupdate event and dynamically assigning the source of the combo box using the first few characters typed into the additional text box.  For more information on this technique see: Access consultants make faster combo boxes (click the performance link)
	e.	If you use a switchboard menu or other menu system where there are no forms opened until a choice is selected then the performance of a linked database can degrade because the frontend database will try to delete the .ldb file on the server several times before it fails.  If you have a small form or a recordset opened all the time then the frontend will not check on the ldb file again and again and performance will be improved.
5.	Compacting Microsoft Access databases to speed up Access
	a.	Periodically, based on the amount of data added/changed, you should compact the Access database.  To do this choose 'Tools'... 'Database Utilities'... 'Compact and Repair'.
	b.	Warning! Make sure your subversion of Access is up to date otherwise you may encounter a bug in Access that causes autonumber fields to pick previously picked numbers when adding new records.  Access 2000 current subversion is 9.0.4402 SP-1 (on computers running OSs other than Windows XP).  For Windows XP computers the current sub version is 9.0.6962 SP-3.  To view the subversion pick 'Help'... 'About Microsoft Access'.  Access 2002 running Windows XP subversion is 10.4302.4219 SP-2
	c.	Do NOT set the database to compact on close. Each time the database is compacted the table statistics are refreshed.  The table statistics are used to optimize the stored queries however the optimization information is wiped out when the statistics are regenerated and the optimization is redone when each query is edited or run.  This optimization process can take up to several seconds.
6.	Jet 4.0 Engine
		Don't forget to keep your Jet4.0 database engine up to date - version 8 as of 12/01/03
7.	PC Hardware Recommendations for faster databases
		Obviously a faster pc will make Microsoft Access databases run faster.  As of October 2009 the desk top PC we recommend: 2 gig of ram, 2.0 gigahertz processor, and the fastest hard drive you can find.  For a server PC: 4-6 gigs of ram, 3.0 gigahertz processor, and multiple fast hard drives, one of which is dedicated to the back end database.  Some PC sellers sell fast processors with little memory and the slowest/cheapest hard drive they can find.
8.	Database Name and Location to improve Access performance
		When you have a split database (front end/back end) give the backend database a short name.  Also, when placing the backend on the server put it at or near the top-level folder.  Don't bury it in deeply nested folders.  Server security checking can take longer when the database is deeply nested.
9.	Virus Protection Slowing Database
		Some virus protection software will dramatically slow the opening and sometimes the functioning of an Access database.  Exclude the database folder from active virus protection scanning.  You may find the database opens 90% faster.
10.	Slow Queries: Use recordset operations with BeginTrans and CommitTrans
		If you have a query, other than a simple select query, such as an update or append query that takes too long to run then the following technique will increase the speed of your Access query from minutes to a few seconds.  The reason this works is that the record set operations are written to memory (transaction buffer) rather than directly to the hard disk. The following is a snippet of some code we use to analyze our website hits.  The key parts of the Transaction processing are bolded. Dim wksp As DAO.Workspace Dim special As String Dim visitor As String Dim visit_date As Date Dim Engine As String Dim rst As DAO.Recordset Dim rst2 As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb special = Chr(34) & "-" & Chr(34) ' making Microsoft access databases run faster with better design Set wksp = DBEngine.Workspaces(0)   ' set up a transaction buffer wksp.BeginTrans      ' all record set changes are buffered after this On Error GoTo roll0 Set rst2 = db.OpenRecordset("M_Sitestats") Set rst = db.OpenRecordset("select field1 from Sitestats") rst.MoveFirst Do While Not rst.EOF   visitor = Left(rst!Field1, InStr(rst!Field1, " ") - 1)   visit_date = CDate(Mid(rst!Field1, InStr(rst!Field1, "[") + 1, 11))   If InStr(rst!Field1, "www.google") <> 0 Then     Engine = "Google"     GoTo write_it   End If   If InStr(rst!Field1, "yahoo.com") <> 0 Then     Engine = "Yahoo"     GoTo write_it   End If write_it:   With rst2     .AddNew     !visitor = visitor     !visit_date = visit_date     !Engine = Engine     .Update   End With check_next:   rst.MoveNext Loop wksp.CommitTrans      ' here we write the changes to disk all at once GoTo finish_it roll0: If Err.Number = 3022 Then On Error GoTo roll0 Resume check_next End If MsgBox Err.Number & " " & Err.Description wksp.Rollback   ' cancel everything if unexpected error finish_it: rst.Close rst2.Close Set rst = Nothing Set rst2 = Nothing End Sub This method can speed up Access database queries many fold by allowing the transactions to be cached rather than written to disk after each transaction.  Go to our Microsoft Access Visual Basic examples to see more about Access transaction processing techniques.

If all these technique fail to speed up your Access database then pop in an SSD (Solid State Drive) for less than $100 and get a 400% improvement instantly.