My database is very slow.

[xcrx]

After a little research this morning I have realized I can't run a pass though query because my "Inventory_qry" is based of several other queries.

I think I am going to have to look into running things though views in mysql. Does anybody have any experience with views? I am wondering how many levels I can go before I run in to a bad slow down.

 

[GinaWhipp]

Okay, let me go look at this query and see if there is anything that can be done to help it along...

 

[GinaWhipp]

It is definitely the query which is based on several GROUP and TOTALS queries nestled within GROUP and TOTALS queries. No matter what this is going to slow down the query.

Each time that query is run it opens the individual tables held within which totals 8 tables and then it has to group and/or total those results prior to giving the results. In it's present state (and not knowing enough about your database) no matter where you process the data it is going to take alot.

Perhaps you could consider opening a blank form and only pulling data when a slection is made from a combo box. That way you are opening filtered information which *might* load quicker.

 

[xcrx]

That is what I was afraid of. When I filter that recordset it is still quite slow. You can try it and see what you think. On the "Inventory_frm" there is a box at the bottom to filter by description. I am going to keep going with converting all the queries to mysql views to reduce constantly pulling data from the server. That might help some.

 

[GinaWhipp]

On converting them to VIEWS...

That would be a *big* maybe because you are *still* opening quite a few tables. There are a few changes I would make to the tables which *might* result in cleaner queries but the chance is so small not worth rewriting the tables to test the theory, unless you have some spare time!

 

[xcrx]

I am open to suggestions.

 

[GinaWhipp]

Is this databse in production? Because being open to suggestions is one thing but trying to fix a database in production is not an easy task.

 

[xcrx]

The database is in production but I keep a test server running a duplicate so I can work on improving it without risking the data.

 

[GinaWhipp]

Oh, no I don't think you understand... it's the tables that should be modified. After the modification the database has to come off line for the data to be moved. This is why developers spend so much time to get the tables correct because this is not a task for the faint of heart.

Now, if you are will first thing to do is explain each table because some of these make no sense.

 

[xcrx]

No I knew what you meant . I am not faint of heart but before anything in production changes it will be changed on my test server to see if it is worth it or not. I will write up what each table is for on monday and post it if you are interested in understanding how I put it together. I am sure there are problems with my methods.

 

[GinaWhipp]

I'll be waiting!

 

[xcrx]

Below is a description of my tables and columns. I wasn't sure what all info you needed so let me know if I have forgotten anything. I also attached a text file with the same information for easier reading.

Assem_Parts_tbl
Assem_Part_ID - Unique Idetifier
Part_ID - Referanced to Part_ID.Parts_tbl
Assem_ID - Referanced to Assem_ID.Assem_tbl
Assem_Part_qty - Parts per Assembly

Assem_tbl
Assem_ID - Unique Idetifier
Assem_num - Engineering number for Assembly
Assem_Desc - Description of Assembly
Assem_Date - Date Assembly was added

Delivery_Detail_tbl
DD_ID - Unique Idetifier
Delivery_ID - Referanced to Delivery_Order_tbl
Tracking_num - Referanced to Work_Details_tbl

Delivery_Order_tbl
Delivery_ID - Unique Idetifier
Ddate - Date Created
User_ID - User that Created Record

Destination_tbl
Dest_ID - Unique Idetifier
Dest_Desc - Description of Destination
User_ID - User in charge of destination

Machine_tbl
Machine_ID - Unique Idetifier
Machine_Desc - Machine Description
Service_num - Phone Number for Machine Service

Material_tbl
Mat_ID - Unique Idetifier
Mat_Desc - Description of Material
mat_thk - Material Thickness
mat_type - Type of Steel

OD_Sin_tbl
Tracking_num - Unique Idetifier
Order_num - referanced to Orders_tbl
Part_ID - Referanced to Parts_tbl
Order_qty - Quantity of part on order
Hot - Part is urgent

Orders_tbl
Order_num - Unique Idetifier
User_ID - User that created Record
ODate - Date Order was created
Dest_ID - referanced to Destination_tbl
Notes - Notes

Parts_tbl
Part_ID - Unique Idetifier
Part_num - Engineering number
Part_Desc - Description of Part
Mat_ID - Referanced to Material_tbl
Part_Notes - Notes
Process_ID - Referanced to Process_tbl
Print - Name of jpg for print

Process_tbl
Process_ID - Unique Idetifier
Process_Desc - Description of Process
Process_Notes - Notes

Status_tbl
status_ID - Unique Idetifier
Status_Description - Description of status
User_ID - User that entered Status
Tracking_num - referanced to Work_Details_tbl
SDate - Date status was made
Finished - Last status for Tracking_num

Turnkleen_tbl
NOT BEING USED ATM

Updates_tbl
Update_ID - Unique Idetifier
Part_ID - Referanced to Parts_tbl
UDate - Date Update was make
Update_Desc - Description of Update
User_ID - Who Ordered Update
New - Has the Update been processed

Used_Sin_tbl
Used_ID - Unique Idetifier
Part_ID - Referanced to Parts_tbl
Used_qty - Quantity of Part_ID Used
UDate - Date Used

Users_tbl
User_ID - Unique Idetifier
Name - User Name
Phone - User Phone
Email - User Email
Password - User Password
Init - User Initials

Work_Details_tbl
Tracking_num - Unique Idetifier
Job_ID - Referanced to Work_Orders_tbl
Part_ID - Referanced to Parts_tbl
Qty - Quantity of Part_ID on Job_ID
Dest_ID - Referanced to Destination_tbl
Hot - Part_ID needs to be rushed
Plus - Quantity above original
Minus - Quantity below original

Work_Orders_tbl
Job_ID - Unique Idetifier
Job_num - Job Number for internal use
JDate - Date Job was Created
Machine_ID - Referanced to Machine_tbl
Hot - Job is a rush
User_ID - User that created Job
Notes - Notes

 

[GinaWhipp]

Okay, well this is going to take a minute...

Have no fear I'll get to it I just don't want you to think I abandoned you because no reply right away...

 

[xcrx]

lol no problem. The structure is fairly consistent all the way though but let me know if you have any questions.

 

[GinaWhipp]

What Access version is this database?

 

[GinaWhipp]

You said earlier this database is to track parts, track them to what end? Perhaps a breif description of the business issue this database resolves would help.

 

[xcrx]

The frontend of the database is written in access 2007 with the backend being mysql 5.1.

This database is for the fabrication portion of a manufacturing plant. Basically the foremen order the parts they need to build equipment using the Orders_tbl and Orders_detail_tbl. Those parts are then seen by the production manager (my boss). He schedules the parts to be fabricated by creating a work order from Work_orders_tbl and Work_details_tbl. This generates paper work that comes back to me when the part is finished to be marked as received. The foremen also mark when parts have been used so the inventory stays proper. (They aren't doing this right now, but they will be in the future). Everything else in the database is to support these functions. The 6,300+ records that load on the Inventory_frm is a count for every part we have ran though our system to date, (this goes up all the time.)

It is basically an MRP system.

 

[GinaWhipp]

Okay, thanks that helps alot...

 

[xcrx]

Well I just finished switching all my Access queries into Mysql views and the difference is amazing. Running just a basic speed test that opens my query 'Inventory_qry' and moves though all the records, I got 12 seconds using access and less than 1 second using Mysql.

The best part was that I was able to just copy and past the query definitions in most cases. Mysql doesn't support Last or First so you have to find a different way of expressing what you want there but that was the only problem I ran into. The start up time for my database went from 10 minutes on my slow computers to 10 seconds, an insane improvement.

@GinaWhipp

I would still be very interested in hearing what you have to say about my table and query structure. I have only been doing database stuff for a year and a half now and I am sure I still have lots to learn.

 

[GinaWhipp]

No worries... should be able to get to that today!