Tim.

I have a table with duplicated rows. To delete duplicates I create an autonumeric field called CLIENTES_ID and execute this SQL instruction: (CODIGO is the field duplicated)

DELETE from CLIENTES where CLIENTES_ID >
(
Select min(CLIENTES_ID) from CLIENTES Tbl1
where CLIENTES.CODIGO = Tbl1.CODIGO
)

This operation takes 1:52 minutes in a table with 100000 rows. Is wrong this instruction? because in another database the same instruction takes one second.

Thanks and regards.

Francisco,

<< have a table with duplicated rows. To delete duplicates I create an
autonumeric field called CLIENTES_ID and execute this SQL instruction:
(CODIGO is the field duplicated)

DELETE from CLIENTES where CLIENTES_ID >
(
Select min(CLIENTES_ID) from CLIENTES Tbl1
where CLIENTES.CODIGO = Tbl1.CODIGO
)

This operation takes 1:52 minutes in a table with 100000 rows. Is wrong this
instruction? because in another database the same instruction takes one
second. >>

1:52 sounds about right for 100,000 rows, depending upon the row size.  If
another database is doing it in one second, I can guarantee that a) it's
caching the entire table *prior* to the DELETE and b) it's not writing
anything to disk.

Is the CODIGO column indexed ?  If so, how is the index defined, especially
the collation (if CODIGO is a VARCHAR/CHAR) ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim.

>>Is the CODIGO column indexed ?  If so, how is the index defined, especially
>>the collation (if CODIGO is a VARCHAR/CHAR) ?

Column CODIGO is indexed COLLATE ESP but I only want to know if this instruction may be improve or are ok.

Thanks and regards.

Franisco,

<< Column CODIGO is indexed COLLATE ESP but I only want to know if this
instruction may be improve or are ok. >>

Is the CODIGO column also defined with the ESP collation ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim.

>>Is the CODIGO column also defined with the ESP collation ?

Sure.

Francisco,

<< Sure. >>

Sorry, I don't think I was specific enough.   Does the CODIGO column
have the exact collation 'ESP', and not 'ESP_CI' or some other collation
modifier ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim.

>>Sorry, I don't think I was specific enough.   Does the CODIGO column
>>have the exact collation 'ESP', and not 'ESP_CI' or some other collation
>>modifier ?

Yes, CODIGO column have collation 'ESP' and index have the same collation but don't worry, I hope don't use this instruction many times, only was curiosity because, as I said to you, call my attention that another database spend only one second in this work and I didn't know if I was doing well with EDB because the instruction is the same in two cases.

Thanks and regards.