MS Access SQL query extremely slow after converting to local table

Question

I have the following SQL query

SELECT
    pmma_vt_feldmarkposition.vtvt_recn AS [li_verträge_recn],
    pmma_vt_feldmarkposition.feldmark AS [vt_feldmark_nr_pflanze],
    switch (gbs is null, gbs_opti, gbs is not null, gbs) AS [ta_istbeitragssatz_hagel],
    NZ(pmma_vt_feldmarkposition.ebs_sturm, 0) + NZ(pmma_vt_feldmarkposition.ebs_frost, 0) + NZ(pmma_vt_feldmarkposition.ebs_wolkenbruch, 0) + NZ(pmma_vt_feldmarkposition.ebs_hochwasser, 0) + NZ(pmma_vt_feldmarkposition.ebs_trockenheit, 0) + NZ(pmma_vt_feldmarkposition.ebs_pauschal, 0) AS [ta_istbeitragssatz_elementar],
    switch (gbs is null, gbs_opti, gbs is not null, soll_gbs) AS [ta_sollbeitragssatz_hagel]    
INTO
    vrt_feldmarkpositionen
FROM 
    PMMA_VT_FELDMARKPOSITION
WHERE
    pmma_vt_feldmarkposition.lfd_nr * 1000000000 + pmma_vt_feldmarkposition.vtvt_recn 
    IN (
        SELECT MIN(pmma_vt_feldmarkposition.lfd_nr * 1000000000 + pmma_vt_feldmarkposition.vtvt_recn) AS minhelper
        FROM pmma_vt_feldmarkposition
        GROUP BY pmma_vt_feldmarkposition.vtvt_recn
    );

pmma_vt_feldmarkposition used to be linked to my ODBC database and the query was running without problems. Now, I converted the link to a local table (also named pmma_vt_feldmarkposition, removed the link to the database) to be able to work offline - but the query keeps running forever without finishing. I do not receive any kind of error message.

What could be the reason for this? Could it be because my .accdb file is 1,8GB large now? (I saved a few more local tables and already used the "compact and repair" function)

Depending on the method you used to create the local table it may be missing some indexes. Also, the WHERE clause looks like it is not sargable so you may be doing a table scan (or several table scans) that could be slowing things down. — Gord Thompson
(BTW, did you mean "ODBC", or does your data really pertain to the OECD?) — Gord Thompson
1.8 GB is close to the 2 GB limit for an Access db file. Is there even enough room remaining to accommodate the size of the new table (vrt_feldmarkpositionen) that query attempts to create? — HansUp
Try moving PMMA_VT_FELDMARKPOSITION to a separate Access database. Then, in your current database, make PMMA_VT_FELDMARKPOSITION a link to that table in the other database. — HansUp
Thanks for the suggestion, I moved all locally saved databases to seperate databases. The main files is now less than 0,5 GB. However, the problem still persists. The resulting table created by my script is not larger than 1 GB, so the size shouldn't be ab problem anymore. — Alwin

Andre Andre · Accepted Answer · 2016-03-17T00:16:41

Actually, I think the best solution will be to get rid of the calculated expression, and work with both fields in a JOIN instead.

Simplifying the first part of the query, this would be:

SELECT
    fields
FROM 
    PMMA_VT_FELDMARKPOSITION T
INNER JOIN (
    SELECT MIN(lfd_nr) AS MinLfdNr, vtvt_recn
    FROM pmma_vt_feldmarkposition
    GROUP BY vtvt_recn
) AS MinGrp
    ON  T.lfd_nr = MinGrp.MinLfdNr
    AND T.vtvt_recn = MinGrp.vtvt_recn

At least I think this should return the same result as your query.

If necessary, performance can be further improved by storing the subquery result in a temp table.

Make sure both lfd_nr and vtvt_recn are indexed.

MS Access SQL query extremely slow after converting to local table

1 Answers