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

Answer 1

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.