Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-04-2014, 12:15 AM   #1
Acropolis
Newly Registered User
 
Join Date: Feb 2013
Location: West Midlands
Posts: 182
Thanks: 17
Thanked 14 Times in 14 Posts
Acropolis is on a distinguished road
Qry Running Very Slow

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:


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;

Acropolis is offline   Reply With Quote
The Following User Says Thank You to Acropolis For This Useful Post:
Uncle Gizmo (09-04-2014)
Old 09-04-2014, 12:52 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Qry Running Very Slow

Two thoughts: It may be worth saving the query as a view in the back end and using that as the record source, that normally speeds things up significantly when there are many joins.

If that doesn't speed things up, I would probably add a extra stage of filtering so that you select the customer then the site to restrict the records that are displayed in each combo.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Acropolis (09-04-2014)
Old 09-04-2014, 01:07 AM   #3
Acropolis
Newly Registered User
 
Join Date: Feb 2013
Location: West Midlands
Posts: 182
Thanks: 17
Thanked 14 Times in 14 Posts
Acropolis is on a distinguished road
Re: Qry Running Very Slow

Could you expand a little on using a view in the BE and the record source idea please?


With regards the filtering that wouldn't work so well, as there are many jobs, for various sites and having to work through them like that and filter it down etc would take longer than it does currently when its running slowly.


What I can't understand is why it has gone slow since moving to a MySQL BE rather than an Access BE, it used to run really quickly, almost instant.

Acropolis is offline   Reply With Quote
Old 09-04-2014, 01:40 AM   #4
Acropolis
Newly Registered User
 
Join Date: Feb 2013
Location: West Midlands
Posts: 182
Thanks: 17
Thanked 14 Times in 14 Posts
Acropolis is on a distinguished road
Re: Qry Running Very Slow

Scarp last, tried your idea of a view and calling that and it worked fantastically, info is there now by the time the form has open. Thanks
Acropolis is offline   Reply With Quote
Old 09-04-2014, 02:15 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Qry Running Very Slow

You are welcome - I suspect the slowing down is simply due to the number of records involved. if you multiply out hundreds of clients against 1000's of sites the resulting recordset is very large by comparison to what you to had before.

I've found that many complex queries grind to a halt in the front end, that SQL can handle with ease in the backend.

Minty is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Queries running slow jnolfo Queries 2 12-03-2013 10:16 AM
SQL database running slow AnnPhil SQL Server 4 03-26-2008 01:44 AM
Where exists /IN running slow meacho Queries 1 01-16-2008 11:24 AM
Slow Running samer General 1 06-24-2005 03:27 AM
I Need To Slow Down The Running Of A Macro! KIMBOZZZ General 5 02-17-2003 03:35 PM




All times are GMT -8. The time now is 03:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World