Query so slow. was; Re: [AccessD] delete duplicates

Hello Martin and Gustav,

i will try your solutions later this week. Thanks for the help.
The query did his job after about 21/2 hours on a pentium3, 2,4Ghz, 512 K
Cache and 512Mb memory.

Pedro Janssen



----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, September 16, 2003 11:05 AM
Subject: Re: Query so slow. was; Re: [AccessD] delete duplicates


> Hi Pedro
>
> I'm not sure it your queries work correctly, but if they do they will
> run very slow.
>
> You have at least two options.
>
> 1. Try this:
>
>     DELETE *
>     FROM test
>     WHERE PK NOT IN (
>       SELECT TOP 1 PK
>       FROM test AS T
>       WHERE T.FieldA = test.FieldA);
>
> 2. Write the FirstPKs of your query1 to a temporary table. Then
> include that in query2:
>
>   <query2/>
>   DELETE *
>   FROM test
>   WHERE PK Not In (Select PK from tblTemp);
>
> /gustav
>
>
> > Hello Group,
>
> > i use the query's below to delete he second or third etc duplicate from
> > fieldA.
> > It is working fine with 50 records or so. When i use it on a Table with
9000
> > records
> > query2 still runs after a few hours. What can be wrong?
>
> > TIA
>
> > Pedro Janssen
>
>
> > <query1/>
> > SELECT First(test.PK) AS FirstOfPK, test.FieldA
> >>From test
> > GROUP BY test.FieldA;
>
>
> > <query2/>
> > DELETE test.FieldA
> > FROM test
> > WHERE test.PK Not In (Select FirstOfPK from Query1);
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>