Database Size

Question

Database Size

Flinn, Randal J 281

I currently have a database that I have split. Queries, reports, etc. reside in a datase named FCA Analysis and is 1,152 KB. Tables reside in FAC Analysis_be and is 1,451,532 KB. The database is extremely slow (I am assuming due to the size). Are there efficiencies I can use to improve the overall performance? I am perfroming a Compact/Repair on close.

Thanks

1 answer

Your answer

Answer 1

Albert Kallal 5,586

Well, remember, if you compact + repair the applcation part (your front end with linked tables), that does NOT compact the back end database file.

So, I would double (maybe triple) check, and do a compact + repair on the back end database. It might shrink a lot. And you can't do that data file compact + repair while users are working (and you wonder why so many developers are night owls!!! - we often have to do tasks that require no one else is working on or using the data!!!).

So, check the above, and try a compact + repair on the back end.

Since compact on close will ONLY deal with the front end?

Then you have to have some type of schedule to do this task. It is possible to do this say by the task manager, and you setting up some kind of routine to do this. (maybe during the nightly backup).

Performance:

As a general rule, even as a access database grows, it should not effect performance "huge". However, the art of performance tuning is a BIG topic - one far beyond say this simple question + answer forum.

However, there are a few things you can try, or do:

First up, without question, try the compact + repair of the back end.

Try what is called a persistent connection (if you don't have one). While this sounds "fancy", it simple means that you want to "force" and "keep" open a connection from your front end applcation to the back end at all times.

A super quick and easy way to test this? Open your applcation (with the linked tables). Then open "any" linked table from the nav pane. Now, try launching your form or report. You will often see 10x the performance increase. So try this quick test. If this test DOES solve your performance issues?

Then on your main startup form, or some startup form you want to thus "force" a connection to remain open. You can do this by launching a bound form (to any back end table), and KEEP that form open. Another way is to declare a recordset variable as global, and open a table to that recordset on startup. As noted, this tip/trick does not always help, but often it can do wonders.

Check for no existing linked tables, and also for a default printer set to a network printer, or even a non existing printer. So, have your default printer set to a local printer. Often we don't have a physical printer, so make your default printer say a local PDF printer, or XPS printer (just any default printer - non network one).

This is a known issue, and when access loads a form (or report), it can often attempt to "talk" to the printer to get information about layout and printer abilities (EVEN when you not going to print anything - so this can really slow things down.

And if you loading/launching a report with say a filter of some type, check if indexing is on that column used for that criteria.

And speaking of criteria and filtering? Well, always try to ask the user for some information before launching a form.

if you have say 250,000 invoices in the system? If you prompt the user to say

What invoice to display?

Then you can launch a form this way:

 dim InvoiceNum      as long

' form or prompt for invoice

InvoiceNum = 13445

docmd.OpenForm "frmInvoices",,,"InvoiceNumber = " & InvoiceNum

So, in above, assuming we have a index on InvoiceNumber, then that form will ONLY load the one record. Even on a typical office network, and say 500,000 rows? That invoice form should load near instant, and Access will ONLY pull over the network the ONE record if you use that "where" clause of the OpenForm.

In other words, limit the data you pull into a form.

Anyway, try compacting the back end database, and try that persistent connection - that persistent connection trick tends to really help.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Flinn, Randal J 281 Reputation points

2022-04-15T16:58:40.687+00:00
Albert,

Thanks for the response. Based on your one comment, (As a general rule, even as a access database grows, it should not effect performance "huge".) I am starting to believe the issue is with my report. I am not currently concerned with the time to generate the report but issues with design, modification, etc. When I attempt to make changes to field sizes, placement, properties, etc. there is a significant time lag. This report is based on multiple queries:

Query 1 - Groups records (by Order No) from a table based on (7) Order Types (text field)

Query 2 - Selects records from a second table (where the Order No is equal to Query 1 Order No) and a field called Element is equal to (30) possible values

Query 3 - Crosstab query on results from Query 2

Again, there are a large number of records so report completion time is not an issue; however, to modify (1) field can take minutes to move, resize, etc.

Thanks
Albert Kallal 5,586 Reputation points

2022-04-15T20:57:47.587+00:00

As I noted, try the persistent connection idea. Before you open the form (or report) in design mode, try opening a linked table from the nav pane (just open any table - as long as it is linked - does not even have to be the one your form/report is using.

Try the above - see if it improves that "slow" design time issue.
Flinn, Randal J 281 Reputation points

2022-04-18T17:04:11.877+00:00

Albert,

Again, thanks for the response. Having a linked table open did not improve the performance.

Any other suggestions?

Thanks,
Albert Kallal 5,586 Reputation points

2022-04-18T17:39:41.567+00:00

Well, as noted, try setting your default printer to a non network printer.

In fact, try pulling the network plug (or disable your wi-fi). See if running disconnected helps this. As noted, in most cases that persistent connection trick can help huge. (but, not in all cases).

So, I would try running without any network connection - see if that helps.

Edit: remember, this context and discussion is about a very slow and long time delay when you attempt to open a form (or report) in design mode.
Flinn, Randal J 281 Reputation points

2022-04-19T13:29:19.223+00:00

Hello @Albert Kallal ,

So I did isolate the network connection and experienced the same slow performance in report design and modification. I also changed my default printer to a local PDF, rebooted and again saw the same slow performance.

Any other suggestions?

Thanks
Albert Kallal 5,586 Reputation points

2022-04-19T18:24:55.767+00:00

Gee, not much left to go on here. If turning off all network connections and developing 100% local and disconnected does not help?
Might be your virus software, but if pulling the network plug, and working on the front end (and back end) linked 100% local on your computer?

Gee, I assume you done a compact repair on both FE and BE. And perhaps turn of track -auto name correct. These options here:

Now, C+R, and exit, and re-start. As noted, if your working without a network connection, then you should not be seeing huge delays. Are all applications slow, or is jumping into design mode ONLY slow for this one applcation.

Edit: as noted, check all reports - make them default printer - not specific. But, as noted, try a blank database, and the one or two forms that seems slow - does that work? (and without network active).

I would create a blank new database, bring in ONLY the linked tables for that one form, and try that. If it works fast, then stray linked table that points to a non existing table in the BE, or even to a non existing back end will also exhibit this behavior.

So, give above a try - but beyond that, I can't think of anything more to suggest. I had a applcation that was slow for two years - attempted every fix. Turns out that one linked table was pointing to a non existing BE. So, even linked tables NOT part of the given form that are bad can cause this behavior.
Flinn, Randal J 281 Reputation points

2022-04-20T19:40:54.513+00:00

Thanks for your follow-up. I did create a new sample database including only the tables and queries needed. I am seeing the same result when I attempt to create a table from the Crosstab query.

Thanks
Albert Kallal 5,586 Reputation points

2022-04-21T13:41:54.317+00:00

This discussion was about the large time taken to flip into design mode for a form, or report in general.

Now we flipped the narrative 100%, and are now talking about a slow query, and a cross tab query too boot. That is HUGE different issue and narrative here.

So, now we have some complex query on query, and a cross tab query that is slow. This issue is VAST and HUGE different of a issue then that of flipping into design mode for ALL OF and EACH and of ANY form or ANY report.

So this issue is now not about a really slow switch into design mode, (for ALL forms and ALL reports, but now to some SINGLE cross tab query that is slow. So, the approach to this kind of issue and problem is going to be MUCH different of a issue. We simply may well be talking about a slow cross tab query here - not that in general all forms and reports are slow to flip into design mode.

So, now we looking at query performance here - not applcation performance, or that design mode is slow for all forms and reports.

This cross tab query may well be just slow - perhaps indexing for some of the cross tab columns is required, and may well speed up that query. The solutions to this issue and problem are not in general going to be fixed by the suggestions above so far.

But, indexing may well help this, and optimizing this ONE particular query may be possible, but unless flipping into design mode for all forms/reports is what you experience, we are now on a different issue and problem to solve.
Flinn, Randal J 281 Reputation points

2022-04-21T15:16:44.157+00:00

@Albert Kallal thanks for the response.

Sorry if I didn't make it totally clear. I did try to identify the specific issue in my response from 5 days ago. As I said at that time, my concern was with the specific report. Speed of the CrossTab is slow but I fully understand. The real issue is when I attempt to create and/or modify the report.

Regardless, I will review indexing the some of the data.

Thanks
Albert Kallal 5,586 Reputation points

2022-04-21T16:04:17.27+00:00

No problem - I somewhat miss-read the question, and assumed that all design changes are slow. The answers and information here still can help others - so this is not all a loss here.

And of course layout mode will be slow - but design mode "might" be helped by some of the suggestions here. But, with a really slow and difficult query source, then you have a challenge.

Share via

Database Size

1 answer

Your answer