SQL Query uses a lot of resources with no results

Question

Good day everyone,

I recently had to move to the UcanAccess' engine from the Access JET Engine and I am not very familiar with "standard" SQL queries, not using the "INNER JOIN" function. I wrote the following SQL query based on one of my previous answers I got from a question regarding a DELETE clause, but this query:

SELECT TreatmentRecords.DateGiven, TreatmentRecords.TimeGiven, SInformation.Surname, SInformation.FirstNames, SInformation.CampusCode, TreatmentRecords.Treatment, TreatmentRecords.[AmountGiven], TreatmentRecords.Diagnosis
FROM TreatmentRecords, SInformation
WHERE (((YEAR(TreatmentRecords.DateGiven)) =2015) AND ((MONTH(TreatmentRecords.DateGiven))=03) AND ((TreatmentRecords.SID)<= 70000))
GROUP BY TreatmentRecords.DateGiven, TreatmentRecords.TimeGiven, SInformation.Surname, SInformation.FirstNames, SInformation.CampusCode, TreatmentRecords.Treatment, TreatmentRecords.[AmountGiven], TreatmentRecords.Diagnosis
ORDER BY TreatmentRecords.DateGiven, SInformation.Surname, SInformation.FirstNames;

appears to do nothing. What I do find is that it revs my CPU to 96% and my RAM to over 1GB, but it is like a recursive loop.

I would like to please know

a) What is wrong with the query b) Under which circumstances will a query do the abovementioned to your processor and memory

This query (in JET format), runs perfectly fine and the whole query is only supposed to return 100-200 results.

Any help will be appreciated. Thanks

Does `TreatmentRecords.SID` contain an ID value corresponding to one of the columns in `SInformation` (i.e., a matching ID value in `SInformation` that uniquely identifies each row in the `SInformation` table)? — Gord Thompson, Mar 22 '15 at 10:23
So, you can simply add TreatmentRecords.SID=SInformation.SID to the conditions in AND of the WHERE clause:... WHERE TreatmentRecords.SID=SInformation.SID and (((YEAR(TreatmentRecords.DateGiven)) =2015) AND... — jamadei, Mar 22 '15 at 14:01

jamadei · Answer 1 · 2015-03-22T14:03:00.383

Your query performs a Cartesian product (CROSS JOIN) BEFORE THE GROUPING, so it may ELABORATE an huge number of occurences, because you don't specify any join condition between TreatmentRecords and SInformation.

For example, if you have 10000 SInformation records and 1000 TreatmentRecords referring to March 2015 with SID<70000, 10 milion of record will be elaborated for the GROUP BY. Obviously this doesn't make sense.

Even if the Jet engine may interpret your intentions looking at the final grouping, and implement an alternative strategy and execution plan by not performing the whole Cartesian product, YOUR QUERY IS SOMETHING TERRIBLE for most of the DBMS(i.e. Hsqldb).

Re-writing this bad query, will have added value.

Gord Thompson · Accepted Answer · 2015-03-22T14:13:10.273

Further to the answer from jamadei, try this query that uses an INNER JOIN to avoid the cartesian product:

SELECT 
    TreatmentRecords.DateGiven, TreatmentRecords.TimeGiven, 
    SInformation.Surname, SInformation.FirstNames, SInformation.CampusCode, 
    TreatmentRecords.Treatment, TreatmentRecords.[AmountGiven], TreatmentRecords.Diagnosis
FROM TreatmentRecords INNER JOIN SInformation
    ON TreatmentRecords.SID = SInformation.SID
WHERE TreatmentRecords.DateGiven >= #2015-03-01# 
    AND TreatmentRecords.DateGiven < #2015-04-01# 
    AND TreatmentRecords.SID <= 70000
GROUP BY 
    TreatmentRecords.DateGiven, TreatmentRecords.TimeGiven, 
    SInformation.Surname, SInformation.FirstNames, SInformation.CampusCode, 
    TreatmentRecords.Treatment, TreatmentRecords.[AmountGiven], TreatmentRecords.Diagnosis
ORDER BY 
    TreatmentRecords.DateGiven, SInformation.Surname, SInformation.FirstNames;

It also uses sargable WHERE conditions on TreatmentRecords.DateGiven that could avoid a table scan if there is an index on that column.

SQL Query uses a lot of resources with no results

2 Answers2