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

Hi Pedro

So you - ahemm - were in hurry?
Using the temp table will probably bring this down to 2 1/2 second.

/gustav


> 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
>>

> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com