Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Sensitive vs insensitive query speed |
Tue, Jun 17 2014 2:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I think this has been covered before and I think its down to a mixture of Windows and Tim being the clever chappy that he is.
The query below takes < 0.1 seconds when sensitive but > 22 seconds when insensitive SELECT _ID AS ID, _Name AS Name, IF((SELECT COUNT(*) FROM Sites WHERE Sites._fkCompanies = Companies._ID) > 0 THEN (SELECT COUNT(*) FROM Sites WHERE Sites._fkCompanies = Companies._ID) ELSE NULL) AS Sites, IF((SELECT COUNT(*) FROM Career WHERE Career._fkCompanies = Companies._ID) > 0 THEN (SELECT COUNT(*) FROM Career WHERE Career._Left IS NULL AND Career._fkCompanies = Companies._ID) ELSE NULL) AS Employees, IF((SELECT COUNT(*) FROM OrderBook WHERE OrderBook._fkCompanies = Companies._ID) > 0 THEN (SELECT COUNT(*) FROM OrderBook WHERE OrderBook._fkCompanies = Companies._ID) ELSE NULL) AS Orders, IF((SELECT COUNT(*) FROM Invoices WHERE Invoices._fkCompanies = Companies._ID) > 0 THEN (SELECT COUNT(*) FROM Invoices WHERE Invoices._fkCompanies = Companies._ID) ELSE NULL) AS Invoices, IF((SELECT COUNT(*) FROM ELN WHERE ELN._fkCompanies = Companies._ID) > 0 THEN (SELECT COUNT(*) FROM ELN WHERE ELN._fkCompanies = Companies._ID) ELSE NULL) AS History, IF((SELECT COUNT(*) FROM Calls WHERE Calls._fkCompanies = Companies._ID) > 0 THEN (SELECT COUNT(*) FROM Calls WHERE Calls._fkCompanies = Companies._ID) ELSE NULL) AS Calls FROM Companies WHERE _Status = 'Archived' ORDER BY _Name Obviously part of the difference is caused by writing a temporary file out but for a couple of thousand rows that seems a lot of time. I seem to recall, but can no longer find, a thread saying that the engine only supplies enough for the visible bits of the result set (how it does this for filesharing I have no idea but if true then Tim does) The result set is displayed in a grid and I run through it and select (SelectedRows.CurrentRowSelected := True) for the whole dataset. If I generate the result set as sensitive it takes ages for the selecting all records, if I use an insensitive result set the generation of the result set is like watching paint dry but the selecting all records is lightning. I'm not complaining (well not really) I'd just like to understand what's happening under the bonnet here. Roy Lambert |
Thu, Jun 19 2014 10:47 AM | Permanent Link |
Barry | Roy,
If you remove the Order By clause, does the insensitive query speed up appreciably? Barry |
Fri, Jun 20 2014 8:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
I've restored the data to allow me to test this and virtually no difference in speed either with or without the ORDER BY clause. Roy Lambert |
Fri, Jun 20 2014 8:50 AM | Permanent Link |
Barry | Roy,
Have you tried something like this: SELECT _ID AS ID, _Name AS Name, count(Distinct Sites._fkCompanies) as Sites, count(Distinct Career._fkCompanies) as Employees, count(Distinct OrderBook._fkCompanies) as Orders, count(Distinct Invoices._fkCompanies) as Invoices, count(Distinct ELN._fkCompanies) as History, count(Distinct Calls._fkCompanies) as Calls from Companies left join Sites on Sites._flCompanies=Companies._Id left join Career on Career._Left=Companies._Id left join OrderBook on OrderBook._fkCompanies=Companies._Id left join Invoices on Invoices._fkCompanies=Companies._Id left join ELN on ELN._fkCompanies=Companies._Id left join Calls on Calls._fkCompanies=Companies._Id WHERE _Status = 'Archived' Group by _Name Barry |
Fri, Jun 20 2014 9:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
Nope I hadn't. I have just done so and it takes about as long and gives wrong results Roy Lambert |
Fri, Jun 20 2014 5:49 PM | Permanent Link |
Barry | Roy,
>Nope I hadn't. I have just done so and it takes about as long and gives wrong results Sure, but apart from that is was ok, right? LOL I probably should have counted the distinct primary key of each join table instead of _fkCompanies as in: count(Distinct Sites._RcdId) as Sites, The Count() aggregate function will not count NULL values so if the left join doesn't find a row, it doesn't get counted. Barry |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |