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

Roy,

If you remove the Order By clause, does the insensitive query speed up appreciably?

Barry

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

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

Barry


Nope I hadn't. I have just done so and it takes about as long and gives wrong results

Roy Lambert

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