Query filters slower, if left open

[Frenotx]

I'm running into a very irritating problem. I've inherited a fairly awkward database, and spend a large chunk of my time optimizing it. I've been able to clean up quite a lot of it, but there is this one query (and it's attached form) that just wont play nice.

The query isn't terribly complicated. There is a workorders table, linked to a store data table, linked to a clients table. The query does retrieve a lot of fields though (51), and has a few (6-7) calculated fields. The first calculated field is just "Onefield: 1", so I'm not sure if that counts. XD

The problem that I'm running in to is this: When I first open the query (which takes a little over 10 seconds), it is very snappy and responsive. The query has all of the records loaded as soon as it comes up (>150k records). That is, immediately after the query finishes, I can jump to the last record without experiencing any delay. I can filter/sort the resulting records extremely quickly, too. Applying a filter and/or sort takes less than a second.

HOWEVER, after leaving the query open for a minute or so (either letting it idle, or constantly applying / removing filters), its response time get's extremely slow. Applying a filter or sorting goes from taking less than a second, to over 10 seconds (about as long as it takes to open the query from scratch).

My database is split into a FE and a BE, so at first I attributed this to network lag. What really throws me off though, is that if I close the query and then immediately open it back up, it reliably reverts back to its speedy state. It still takes the 10+ seconds to open, but I then get another good minute of quick filters before it slows down again.



Does anybody have any idea what could be causing this? It is a pretty significant issue, as this query feeds the form that the workers primarily interact with.

[June7]

Why are you opening the query directly? What behavior does the form using the query exhibit - slow?

Are critical fields indexed?

[Frenotx]

Why are you opening the query directly? What behavior does the form using the query exhibit - slow?

Are critical fields indexed?

I am opening the query directly because I am the developer. The form that the standard users... use, is very simple and lightweight. As such, the form's performance follows the query performance almost exactly.

To re-iterate: Once the query loads (takes ~10 seconds) it behaves wonderfully (Near-instant filtering, unfiltering, sorting, etc.)... for the first ~1 minute that it is open. After that minute, and filter application/removal or sorting starts taking ~10 seconds, as if the query is running from scratch.

The critical fields are indexed.

Note 1: I am not worried about / interested in the form- it runs smoothly enough, and serves it's purpose well. It the query that's the problem.

Note 2: If I wait until qryWorkorders (the query in question) slows down, then open an exact duplicate of the query (without closing qryWorkorders), the new query will not have that first minute of high speed- it will jump straight to being slow.

Note 3: The backend is an MDB, and the user's FE is an MDE. This behavior is observable on both the users FE, and my dev copy (an MDB).


Here is the SQL code for the query:

SELECT 1 AS OneField, [ClientAbbr] & [StoreNumber] AS CLIENTSTORE, Left([CustID],4) AS CustAbbr, tblWorkOrders.STOREID, tblStoreData.Address, tblStoreData.City, tblStoreData.State, tblStoreData.ZIP, tblStoreData.County, tblWorkOrders.SCID, tblWorkOrders.BEGINDATE, tblWorkOrders.BILLINGISSUES, tblWorkOrders.CALLSTATUS, tblWorkOrders.COMPLETIONCODE, tblWorkOrders.DESCRIPTION, tblWorkOrders.[DISPATCHED?], tblWorkOrders.GLCode, tblWorkOrders.INDATE, tblWorkOrders.[NESPROJECT?], tblWorkOrders.NOTTOEXCEED, tblWorkOrders.PROMISEDATE, tblWorkOrders.SERVICEDATE, tblWorkOrders.SERVICETYPE, tblWorkOrders.TECHNICIAN, tblWorkOrders.TIMESTAMP, tblWorkOrders.WORKORDER, tblWorkOrders.WOAck, tblWorkOrders.WOClosed, tblStoreData.Cycle, tblStoreData.GEOZONE, tblStoreData.Phone, tblStoreData.StartServiceDate, tblStoreData.EndServiceDate, IIf([EndServiceDate]<Now(),"CLOSED",IIf([StartServiceDate]>Now(),"Inactive - Future","Active")) AS StoreStatus, tblStoreData.WarrantyDate, tblStoreData.PrimaryLamp, tblStoreData.Week, tblWorkOrders.STATUS, tblStoreData.SiteNotes, tblClients.CustID, tblClients.Client, tblStoreData.LastRelampADate, tblStoreData.LastRelamp, tblStoreData.LastRetrofit, tblWorkOrders.ETA, tblStoreData.ClientID, tblWorkOrders.CALLLOCATION, Left([CustID],4) & "-" & Right([CustID],2) AS CustIID, tblStoreData.StoreNumber, [Client] & " #" & [StoreNumber] AS Store, [City] & ", " & [State] & "  " & [Zip] AS CityStateZip, tblStoreData.PreviousClientStore, tblWorkOrders.[NESPROJECT?], tblWorkOrders.SOLOMONIMPORTDATE, tblStoreData.Lat, tblStoreData.Lng, tblStoreData.Prec
FROM tblWorkOrders INNER JOIN (tblStoreData INNER JOIN tblClients ON tblStoreData.ClientID = tblClients.ClientID) ON tblWorkOrders.STOREID = tblStoreData.StoreID
ORDER BY tblWorkOrders.SCID DESC;

[Frenotx]

So nobody has any ideas on this?

[June7]

Not something I have experienced. Possibly choice of INNER JOIN is causing issue. Maybe LEFT or RIGHT join would be better. I would have to test with data.

[Frenotx]

Not something I have experienced. Possibly choice of INNER JOIN is causing issue. Maybe LEFT or RIGHT join would be better. I would have to test with data.

I tried every JOIN setup I could think of. Never was able to figure this one out... Thankfully, it is no longer relevant. Shifting all of my info to a SQL backend solved a lot of problems.