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.

  • You didn't provide your table schema along with indexes it has created and which DBMS you are using. – Kamil Gosciminski Oct 23 '14 at 8:31
  • @ConsiderMe I edited my question. – Martin Oct 23 '14 at 8:38
  • You should create a separate table with all distinct IDs corresponding with max date2 and JOIN on that. – Mihai Oct 23 '14 at 8:40
  • @Mihai do you mean soemthing like SELECT distinct ID,FID, KeyValue1, KeyValue2, Date1 INTO Final Table HAVING DATE1<>'' ... ? But how to select the max(Date2) for each ID if i do it like this? – Martin Oct 23 '14 at 8:53
  • I dont know access syntax but in mysql for example would be CREATE TABLE distinctIDS as SELECT ID,MAX(date2) FROM InputTable WHERE.... GROUP BY ID and you now JOIN this table ON id with InputTable – Mihai Oct 23 '14 at 8:59
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

  • Yes, it works fine. I tried both options, yours and the one with the JOIN from @Mihai. Both give the same output and are considerably faster than my version. But yours is faster :) Thank you – Martin Oct 28 '14 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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