Board index » delphi » Fastest way to find duplicates in database?

Fastest way to find duplicates in database?

Which is the fastest way to find duplicate numbers in a unsorted field in a
paradox database.

I have tried table.fieldbyname('Cust No').asstring - but it is extremely
slow. There are about 3500 records in the database. I'm not sure how to use
findnext, etc because I don't know how to set the filters.

Please Help
Kevin

 

Re:Fastest way to find duplicates in database?


Quote
"me" <fr...@telkomsa.net> wrote in message

news:ag2892$f2r$1@ctb-nnrp2.saix.net...

Quote
> Which is the fastest way to find duplicate numbers in a unsorted field in
a
> paradox database.

> I have tried table.fieldbyname('Cust No').asstring - but it is extremely
> slow. There are about 3500 records in the database. I'm not sure how to
use
> findnext, etc because I don't know how to set the filters.

Doing lookups on field that isn't part of an index will always be quite slow
since, on average, 1/2 of all records must be examined. Since you are
looking for duplicates the entire table will have to be examined before
reporting no duplicate exists.

Re:Fastest way to find duplicates in database?


If you just want to find out if they exist:
See if you can create a unique index on the field.

To find out what they are use SQL like the following:
select cust_no, count(*)
from my_table
group by cust_no
having count(*)>1

The otherway is to sort the dataset by cust_no and then loop through
the whole
dataset checking that the current value does not equal the value in
the previous record.

Marc

Re:Fastest way to find duplicates in database?


me <fr...@telkomsa.net> wrote in <ag2892$f2...@ctb-nnrp2.saix.net>:

Quote
>Which is the fastest way to find duplicate numbers in a unsorted field in
a
>paradox database.

I don't know if the following works on a Paradox table or not.  I am using
it on a dbase Table to compare against 3 fields and it is FAST.

  // Duplicate Search using Locate Method:
 if LogTable.Locate ('CALL;FREQ;MODE',
    VarArrayOf([CallEntered.Text,FreqEntered.Text,Mode.Text]),[])
  then  // Duplicate

Other Threads