CODEWITHSUNDEEP
sqlms-accessms-access-2010
Alwin
Alwin

Reputation: 117

MS Access SQL query extremely slow after converting to local table

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)

Upvotes: 2

Views: 469

Answers (1)

Andre
Andre

Reputation: 27634

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.

Upvotes: 1

Related Questions