Delete Duplicates taking forever.

[ponderdj]

Hello,

I have a button on a form that deletes duplicate records. The backend was switched to Oracle and the query is now taking a very long time. I was wondering if there would be a more efficient way to write this.

    Set qd = db.CreateQueryDef("", "DELETE * FROM [tblCompletionReport] WHERE ID NOT IN " & _
    "(SELECT First([tblCompletionReport].ID) AS IDField FROM [tblCompletionReport] GROUP BY " & _
    "[tblCompletionReport].TSO, [tblCompletionReport].TSR, [tblCompletionReport].CCSD, " & _
    "[tblCompletionReport].[ABBREVCCSD], [tblCompletionReport].[ServiceType], " & _
    "[tblCompletionReport].[CompletionDate], [tblCompletionReport].[DateTimeGroup])")
    qd.Execute
    qd.Close

I have tried putting the SQL in a query and running it, and it is just as slow.

Thanks.

 

[SQLSister]

Suggest you ask this inthe Oracle forum. I know in SQL Server that the where NOt iN is the problem and I would replace it with a left join and a where clause, but I'm not sure if the Oracle delte statment supports that syntax.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ponderdj]

OK, I will repost in the Oracle forum. For the record, I think that it will work, but it is very slow. I let it run for 15 min. before I terminated the process. Thanks for the suggestion.