I have a form for assigning jobs to engineers, this is based on a list box that displays all the jobs awaiting scheduling, and a second list box showing jobs currently assigned to the engineer for the engineer and date selected.
When I double click on the top list box (jobs waiting) it assigns is to the engineer selected, requires both list boxes and the job moves from one to the other.
I have had this up and running for about 8 months now and all has been well.
Recently we moved the BE from and Access BE to a MySQL BE on a dedicated server. The server spec is good (not sure precise spec) but it is out main DB server for the product we have, so no concerns on the server side.
Now all of a sudden list box for jobs waiting to be assigned is taking an absolute age to populate or refresh, I am waiting 60 + seconds for it to populate since we moved to the MySQL BE from Access. A couple of things have changed in the code, but this would affect the results been displayed, as it is slow even before any of the code has started to be run.
The only thing I can think of, is the customer and site table has now increased in size massively form what it was, its gone from having 8 clients in it to several hundred, and the site table has thousand's of sites in it now, where previously it was about 1500, as now I use the main Client and Site DB tables rather than just ones for the product as it did previously.
I would expect that to slow it down slightly but not to this degree.
If I copy the SQL for the list box out and run it in SqlYog direct to the MySQL BE, with only slightly modification to add the DB name for where the tables are, but not changing the structure of the QRY, it returns the same results (989 presently) in 0.27 seconds.
QRY is below, can anyone suggest anything I can do to speed it up slightly. When it was on an Access BE I managed to schedule 1000 jobs across 17 engineers in about 3-4 hours, yesterday with it how it is, it took me 2.5 hours to schedule 215 jobs for 6 engineers, its TOO slow.
I have had a quick play, and rather than having it based on a Qry I based the list box on a value list, so it only populated the first time, then just add and remove items from the list box there after, but with 989 items in there it was quite sluggish when scrolling through the list, and took a while to populate in the first instance.
I've tried it without the temp var in there as a filter as well, and it makes no difference at all.
Qry:
When I double click on the top list box (jobs waiting) it assigns is to the engineer selected, requires both list boxes and the job moves from one to the other.
I have had this up and running for about 8 months now and all has been well.
Recently we moved the BE from and Access BE to a MySQL BE on a dedicated server. The server spec is good (not sure precise spec) but it is out main DB server for the product we have, so no concerns on the server side.
Now all of a sudden list box for jobs waiting to be assigned is taking an absolute age to populate or refresh, I am waiting 60 + seconds for it to populate since we moved to the MySQL BE from Access. A couple of things have changed in the code, but this would affect the results been displayed, as it is slow even before any of the code has started to be run.
The only thing I can think of, is the customer and site table has now increased in size massively form what it was, its gone from having 8 clients in it to several hundred, and the site table has thousand's of sites in it now, where previously it was about 1500, as now I use the main Client and Site DB tables rather than just ones for the product as it did previously.
I would expect that to slow it down slightly but not to this degree.
If I copy the SQL for the list box out and run it in SqlYog direct to the MySQL BE, with only slightly modification to add the DB name for where the tables are, but not changing the structure of the QRY, it returns the same results (989 presently) in 0.27 seconds.
QRY is below, can anyone suggest anything I can do to speed it up slightly. When it was on an Access BE I managed to schedule 1000 jobs across 17 engineers in about 3-4 hours, yesterday with it how it is, it took me 2.5 hours to schedule 215 jobs for 6 engineers, its TOO slow.
I have had a quick play, and rather than having it based on a Qry I based the list box on a value list, so it only populated the first time, then just add and remove items from the list box there after, but with 989 items in there it was quite sluggish when scrolling through the list, and took a while to populate in the first instance.
I've tried it without the temp var in there as a filter as well, and it makes no difference at all.
Qry:
Code:
SELECT tblfieldworks.FieldWorksID AS ID, tblfieldjobtype.FieldJobType AS JobType, tblclientdata.ShortName AS Client, site.ExternalRef AS [Site Ref], site.SiteName AS [Site Name]
FROM tblclientdata INNER JOIN (tblfieldjobtype INNER JOIN (tblfieldworks INNER JOIN (site INNER JOIN customer ON site.CustomerID = customer.CustomerID) ON tblfieldworks.SiteID = site.SiteID) ON tblfieldjobtype.FieldJobTypeID = tblfieldworks.FieldJobTypeID) ON tblclientdata.CustomerID = customer.CustomerID
WHERE (((tblfieldworks.FieldStatusID)=1) AND ((tblfieldworks.FieldStageID)=1) AND ((tblfieldworks.FieldJobTypeID) Like [TempVars]![tmpJobID]))
ORDER BY tblfieldworks.FieldWorksID;
