CODEWITHSUNDEEP
sqlms-accessoptimization
Mituga
Mituga

Reputation: 58

SQL subquery optimization Access

I don't know much about SQL so I hope my query below could be improved with your knowledge. The fact is my PC can't handle this query, so I'm wondering if there is a better way to do this.

My query is:

SELECT 
    v.*, 
    IIF((SELECT COUNT(v2.pk) FROM demo v2 
         WHERE v.place = v2.place 
           AND v.date = v2.date) > 1, "Si", "No")
FROM demo AS v;

The point is to flag the records that have another record with the same place and the same date.

Upvotes: 0

Views: 68

Answers (1)

HansUp
HansUp

Reputation: 97101

Start with a GROUP BY query which gives you the count for each combination of place and date ...

SELECT v2.place, v2.date, COUNT(*) AS group_count
FROM demo AS v2 
GROUP BY v2.place, v2.date

Then you can use it as a subquery which you join back to the demo table and add your IIf expression to the top-level SELECT clause ...

SELECT
    v.*, IIf(sub.group_count > 1, 'Si', 'No')
FROM
    demo AS v
    INNER JOIN
    (
        SELECT v2.place, v2.date, COUNT(*) AS group_count
        FROM demo AS v2 
        GROUP BY v2.place, v2.date
    ) AS sub
    ON v.place = sub.place AND v.date = sub.date;

Regarding performance, you should index both the place and date fields. If the demo table is really huge, this query may not be blazing fast, but it should be faster than your original version.

I suggest you also replace v.* with a list of the fields you want to see. v.* requires 2 steps: first find the field names which correspond to v.*; and second, retrieve the data for those fields. So listing the field names eliminates that first step. And if you only need to see a subset of the available fields, listing them by name means less data for the db engine to retrieve.

Upvotes: 1

Related Questions