Split DB - Reports run slow

[B30]

I am in the final stages of testing and looking into ways to share the db. i tested the split db and i see the value in it, but it took forever for the reports to run (maybe a network/server issue, but that is beyond my control & over my head). I will have about 30-40 users entering data, and maybe 6-8 users running reports. the form for the data entry runs smooth enough, with only a little delay for the combo box control. But the reports take forever to run. I would not be opposed to the 6-8 users having access to the backend of the db if i could put the queries and reports there, so that the reports could run faster. Any other suggestions?

[ranman256]

I could be the server connection. I run reports off split db all the time, usu its fast, but sometimes it can be slow due to network. Or other functions in the query.

It can also be the query design. Some complex ones take a long time, but can be revised to get faster.

And you cant have multi users in the backend. Access wont let you.

[B30]

Are there any simple suggestions to help speed up the reports? The reports draw info from several different queries, so i assume that is part of the problem. but all the data is stored in 1 table. is there any benefit to having a query that retrieves all the info from the table and then all the other queries draw from that query? Would that save the fedb from making several references to the bedb for 1 report?

[aytee111]

Local tables are the fastest, querying the BE is the slowest. If it makes sense, bring a smaller part of the data from the BE and put into a temp table in the FE and use that for your queries.

[B30]

aytee11 - i dont understand? if i bring the data to the FE, what is the point of a split db? how would i bring a small portion of the data to the FE? i assume this would be done with a query?, which sounds like essentially the same thing i am doing with my reports now because when i run my reports, i am running queries to filter data based on criteria set by the user through a form. Is it possible to "split" the db differently so that only the forms are in the FE and the queries are in the BE with the data? I am just trying to imagine how the process works and thinking that this would mean that all of the commands are sent to the BE at one time, then it can run all the queries it needs to right next to the data, and then send it back to the FE at one time? reducing the number of trips it needs to make from FE to BE and back.

[ssanfu]

I will have about 30-40 users entering data, and maybe 6-8 users running reports

That is a lot of people...... is that the number of people that will be in Access at the same time?

MS has said that Access can handle 255 concurrent users.. but I have found numerous sites that suggest 15 - 25 max concurrent users.
http://stackoverflow.com/questions/3...ccess-database
(see the David-W-Fenton response)

You might be at the point it is time to convert to SQL Server Express as the BE (free), still using Access as the FE.

The more users and the more traffic on the network will result in a slowdown.

[aytee111]

I was responding to your comment "is there any benefit to having a query that retrieves all the info from the table and then all the other queries draw from that query?". The more queries the slower it is, that is why I was suggesting a table as a base, assuming that you don't need all the data all of the time. Then queries that run off a local table which is a subset of the data in the BE.

There are lots of ways to speed up a database.
Having all the data in one table is suspect, is this the correct way to do it?
Look at your table design, do you have lookup fields or formatted fields (such as Currency)? If so, remove it from the table and put the formatting in the form/report. How many fields? Are they all defined with the correct data type and field sizes?

Keep your queries as simple as possible, call as few functions as possible (anything starting with a "D", such as DLookUp), remove formatting.
Grouping and sorting can be done in a report, not in a query.

Etc.

[B30]

Thanks for the tips. the table only has 11 fields, but several of them are date formatted. Is that a possible issue? The data we are collecting is the hour meter reading from a machine at the beginning of the week and at the end of the week. The table records info such the person reporting the info, the date of the beginning reading and date of the ending reading and then the meter readings. I have a query that does the math of ending - beginning to get hours operated. The report runs a second query that filters data based on a user defined date range to return the info from the table such as dates of the readings and person reporting, and it also reports the hours operated from the first query. Does it look like i am duplicating any processes here, or unnecessarily slowing anything down? The sorting and grouping is done in the report.

[B30]

ssanfu - no i dont expect that many users at 1 time. the data entry only takes about 1-2 minutes and they will enter it only once a week. So i would be surprised if there are 5 in there at the exact same time.

[June7]

Recommend no formatting and no lookups set in table. I never use Currency type.

I don't see anything in described setup that should cause performance issue.

My split db has a couple of forms and reports with complex structure that are a bit slow to render but at most 30 seconds.

[ssanfu]

Have you been consistently doing a "Compact & Repair" during development?
And a decompile/recompile once in a while?

Slowness could be the beginnings of corruption. I had a A2000 dB that slowed down until it was taking a couple of minutes to open a form. Turns out the VBA project was corrupt. Took a couple of weeks to recover the dB. (All of the backups were also in varying stages of corruption).

Other than that, I would say it would be a network issue.

[aytee111]

I am not sure what you mean by the field on the table being date formatted. Make sure there is no formatting on your fields, just the Date/Time data type.

You don't say how many records the table contains, it sounds like a lot. The first query which is doing all the calculations does it on all the records, is it possible to apply the date filter first before you do them?

The rule is to never put calculated data into tables, but this may be one time where it would be better to do so. At the moment you are doing the calculation every time you want to report on a record. This may be a convoluted calc and time-consuming, so I would recommend doing the calc only once, when you add the record, or else sometime afterwards that makes sense. Then your reports need only to read that field. That would speed things up a lot I think.

[Xarkath]

Hello,

I have found that having too many queries and/or forms can slow Access down even if they are not all being used. What I have done is make different FE databases all linking to tables in 1 to 3 BE databases giving different departments/division access to only those tools they will need. I have found this to relieve a lot of latency issues while keeping the main data in a relatively central location. Except those caused by server/network issues.

To do this just start a new database but instead of creating new tables just link to the tables in your existing BE database. You will need to recreate or copy over the forms, queries, reports you need for the reporting functions.

To be clear, this is based on my own personal experience, and it has worked for me, but I cannot say that it is a guaranteed fix. Just that it may be worth a try.

[B30]

Thanks all for all the tips. I am going to try some of your suggestions and see how it goes. based on everything that has been said, i suspect that there was a network problem when i was testing the split db before and that was the reason for the delay. I do have another question about a split db. if someone is in the fe inputting data, and another person tries to run a report, what will happen. i realize the accuracy issue of the new record may not end up on the report depending on the timing and that is okay. but will there be any kind of performance issues?

[Xarkath]

To be honest I would play it safe and set record locking so that no other user can access a record until it has been saved and released. I took over a database that had constent corruption issues and I finally figured out that what was happening was that users were accessing records before they were fully entered and the entry process includes a few macros running updates. If someone was in such a record while the macros were running then it would corrupt the record. The last incident actually corrupted 4 records. So I set the record locking and additionally I set up the queries to better filter out unfinished records and it has now been 2 months without any corruption issues (a record from what I am told). Anyhow, I would suggest playing it safe and design some safeguards to keep the 2 processes completely separate from each other.