Update query super slow...

[Guest]

I am trying to make an update query but have some performance issues...

I need to compare a folder with pictures to an excel file, to see if the
pictures exist in the excell file when they exist in the folder...
I want to create 3 different things:
1 - The image exist in the folder but not in the excel file (add txt: Add
Image)
2 - The picture exist in both (add txt: Good)
3- Picture does not exist in the folder (add txt: No Picture)..

What I have done so far:
I have send the DIR command to a txt file and imported this into MS-Access,
removing the extentions, leaving only the names. Since all names are product
numbers it will result in the following: 123456.jpg --> 123456
I have called this table PDrive with the fields ID (autonumber) and Field1
(number)

Then I imported the Excell file that was exported from our software into a
table called RMPro, with the fields: ID (autonumber), Number (number),
Imagefile (number) and Test (text)

Now I need to compare Field1 with ImageFile.. They both are modified to only
have numers, so it should be easy right?

My queries:
1- Add Image
UPDATE PDrive, RugMan SET RugMan.Test = "Add Image"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
(([RugMan]![ImageFile])<>[PDrive]![Field1]));

This one works great, no problems

2- Good
UPDATE PDrive, RugMan SET RugMan.Test = "GOOD"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
(([RugMan]![ImageFile])=[PDrive]![Field1]));

Also works without any problems.

3-No Picture
UPDATE PDrive, RugMan SET RugMan.Test = "GOOD"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
(([RugMan]![ImageFile])=[PDrive]![Field1]));

Here is the PROBLEM... This query I can let run for hours, but it never
seems to finish...

Anyone has any idea why and what the solution to this is?
Thanks!!

 

[Guest]

OOps made a mistyake in Quesry 3:
Here's the correct one -->

UPDATE PDrive, RugMan SET RugMan.Test = 'No Pic'
WHERE ((([RugMan]![Number]) Not In (SELECT [PDrive]![Field1] FROM PDrive )));

 

[John W. Vinson]

Here is the PROBLEM... This query I can let run for hours, but it never
seems to finish...

UPDATE PDrive, RugMan SET RugMan.Test = 'No Pic'
WHERE ((([RugMan]![Number]) Not In (SELECT [PDrive]![Field1] FROM PDrive )));

NOT IN queries can be really inefficient. Cartesian Join queries are even
less efficient! Try a Frustrated Outer Join unmatched query instead:

UPDATE RugMan LEFT JOIN PDrive
ON Pdrive.Field1 = Rugman.Number
SET RugMan.Test = 'No Pic'
WHERE [PDrive].[Field1] IS NULL;



John W. Vinson [MVP]

 

[Guest]

John,
This worked more then perfect!!! 3 seconds and it was done!!
Thank you so much ;o)

Here is the PROBLEM... This query I can let run for hours, but it never
seems to finish...

UPDATE PDrive, RugMan SET RugMan.Test = 'No Pic'
WHERE ((([RugMan]![Number]) Not In (SELECT [PDrive]![Field1] FROM PDrive )));

NOT IN queries can be really inefficient. Cartesian Join queries are even
less efficient! Try a Frustrated Outer Join unmatched query instead:

UPDATE RugMan LEFT JOIN PDrive
ON Pdrive.Field1 = Rugman.Number
SET RugMan.Test = 'No Pic'
WHERE [PDrive].[Field1] IS NULL;



John W. Vinson [MVP]