My database is very slow.

[xcrx]

I developed the attached database to track parts at my work. It has been rewritten with new features a few times but it just keeps getting slower and slower to use. I was hoping someone would take a look at it and tell me if it is just beyond the scope of access or if there is something that I need to do differently. This is shared across several computers. Some of the computers have a slower connection and it is taking upwards of 10 minutes to finish the initial query to display the first form after logging in.

The database normally runs as an access frontend with mysql as the backend. I imported all my tables into access so I could share it with you guys.

Any opinions on what I can do to speed up my database would be greatly appreciated.

you can log in using any of the accounts, all the passwords are stored as plain text in the Users_tbl.

 

[GinaWhipp]

Have a look at...

http://www.granite.ab.ca/access/performancefaq.htm

Speed up Database…
http://www.blueclaw-db.com/access_consultant_rapid.htm

 

[xcrx]

Thanks for the links. I have read quite a few things on improving database speed but I will look specificly at these and see what they have to say.

 

[GinaWhipp]

You're welcome!

 

[xcrx]

The tips on those sites were not very helpful unfortunately. I think that the main problem might just be network speed. When I use the database on my system which is connected to the server through 1 switch, it is acceptably fast. (It probably takes 15-30 seconds to load my main form). However when I use a computer in our other building it takes more like 5-10 minutes to open the main form. This other computer has to go though 2 wireless access points to reach the server. atm there isn't anyway around this.
Is there anything I can do to speed things up on the remote computers?

 

[GinaWhipp]

Ahhh, not that I know of AND wireless??? This means you have greater chance of corruption due to a hiccup in the system. I must urge you to make frequent back-ups.

 

[Beetle]

Recipe for disaster;

Step 1: Create an Access database.
Step 2: Run it through a wireless network.
Step 3: Sit back and wait for the inevitable corruption.

Is there anything I can do to speed things up on the remote computers?

See this page for a discussion on the matter.

 

[xcrx]

Maybe I didn't make it clear in my initial post. The database portion is actualy handled by a mysql server so I don't think I need worry too much about corruption but just the same I run a daily backup through a cron job.

Getting the data seems to run fast enough it is the querries that are slowing me way down.

I tried to look at that link but it failed to load.

 

[Beetle]

Maybe I didn't make it clear in my initial post. The database portion is actualy handled by a mysql server

You're right, you did say that in your initial post, I just wasn't paying attention. That link I posted was a discussion about using an Access database over WAN, so it wouldn't really apply to your situation. Here it is again anyway, if you're interested;

http://www.kallal.ca/Wan/Wans.html

I don't have experience with MySQL, but I would ask;

For your forms that need to be read/write, are you using queries that limit the retrieved record set to only those records that are absolutely necessary (i.e. you're not pulling the entire table)?

For your read only forms, and your reports, are you using pass through queries?

 

[xcrx]

It is only my main form that is a troublemaker. It is read only but it pulls data from several tables though several querries to get the end result with is a list of my inventory as calculated by the data entered. It might be the the query is just too complex. I am not sure of a way to simplify it though. My database is attached to my original post if you want to see what kind of queries I am talking about. It has been converted to access only for your viewing pleasure.

 

[Beetle]

I can't open the file you posted because I am using A2003. If you want save it as a .mdb (A2003 or older) file and upload it again I could look at it, but it would probably be tomorrow before I can do that.

 

[xcrx]

Yeah I can do that. It will be tomorrow before I can convert it as I don't run access at home.

 

[xcrx]

I have reattached my database as a mdb file. Let me know what you think.

 

[Beetle]

You have 26 queries and somewhere around 50 forms. Which query or form is the problematic one?

 

[GinaWhipp]

@Beetle

Part of the problem is the tables are set to [AUTO] insteasd of [NONE]. While that will only speed things up a little. Every little bit counts.

 

[xcrx]

@Beetle

It is 'Inventory_frm' that is the troublemaker.

@GinaWhipp

The tables don't matter. In production all the tables are in a mysql database.

 

[GinaWhipp]

@xcrx

Oh that's right, I keep forgetting that.

 

[xcrx]

No problem.

 

[Beetle]

OK, I finally got a chance to look at this after a hectic afternoon.

That query, at least as it is in the example file, is pulling 6300 records. Is that also the case in your actual application? This might work acceptably well on a LAN (although, I still wouldn't pull that many records at form load unless I absolutely had to), but it's probably going to be unacceptably slow on a WAN or wirless network no matter what you do.

The first thing I would consider is whether or not it is absolutely necessary to pull the entire record set across at form load. It looks like that form is used for searching, so you might consider having the stored query set up to initially pull no records (i.e. have it look for a value that you know will never exist in the record set). Then, after the user enters their search criteria, use code to rebuild the SQL for the record source to pull only the records they are searching for. There are a few ways this can be done using QueryDefs or RecordSets.

If you absolutely must pull all the records across, and since this is a read only record set, then i would try to use a pass through query so it processes on the server side. It will probably still be slow pulling that many records, but you may see some improvement.

Also, I don't see indexes on a lot of your table fields but I'm assuming that's just a function of you importing the tables for the purposes of the example file. I assume they are properly indexed in the actual database. Yes?

 

[xcrx]

i I will think about if i have to pull all the records at loa but first i will try the pass through query. and yes all the tables have proper indexes and relationships in mysql.