1

I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the most recent and Date1 must exist:

For each distinct ID in InputTable (= rst(0) ) do:

INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) 
SELECT InputTable.ID, 
       InputTable.FID, 
       InputTable.KeyValue1, 
       InputTable.KeyValue2, 
       InputTable.Date1
FROM
  ( SELECT TOP 1 * 
    FROM
      ( SELECT * 
        FROM InputTable 
        WHERE ID='" & rst(0) & "' And Date1<>''
      )
    WHERE Date2 = 
          ( SELECT Max(Date2) 
            FROM InputTable 
            WHERE ID = '" & rst(0) & "' and Date1 <> ''
          )
  );

The InputTable has about 400.000 records and ~200.000 distinct IDs and only contains the 5 fields that will be in the Final Table, all of which are TEXT.

InputTable is an importet .txt file and i am working with ACCESS outside of any real database. The Result will be again written into a .txt file.

It takes about 1 second for each ID, which is too long... Is it possible to improve the performance significantly?

I really appreciate any help.

0

1 Answer 1

2

Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds:

Option Compare Database
Option Explicit

Sub UsingAggregateQuery()
    Dim cdb As DAO.Database, t0 As Single
    Set cdb = CurrentDb
    cdb.Execute "DELETE FROM FinalTable", dbFailOnError
    t0 = Timer
    cdb.Execute _
            "INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) " & _
            "SELECT InputTable.ID, " & _
            "       Last(InputTable.FID), " & _
            "       Last(InputTable.KeyValue1), " & _
            "       Last(InputTable.KeyValue2), " & _
            "       Last(InputTable.Date1) " & _
            "FROM " & _
            "    (" & _
            "        SELECT ID, FID, KeyValue1, KeyValue2, Date1 " & _
            "        FROM InputTable " & _
            "        WHERE Date1 <> '' " & _
            "        ORDER BY ID, Date2 " & _
            "    ) " & _
            "GROUP BY ID;", _
            dbFailOnError
    Debug.Print Format(Timer - t0, "0.0") & " seconds"
    Set cdb = Nothing
End Sub

Edit

I just ran another test on a sample table of 400,000 rows and the code above completed in under 7 seconds

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.