Very simple update query running super slow!

[Guest]

I have two tables: Rugman and PDrive
The table look like this:
Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
PDrive -- ID (Auto/key) / Field1 (text!)

The reason I had to use text for Number and Field1, is because there are a
few lines what have a number like: 1234-2

I just want to check the Number in table Rugman and see if it does exist in
the table PDrive... If it does: field test must be changed to "done" for that
record where the numer exists in the other table...
Don't get why it's running so slow.
I have created also other update queries for the same tables and those
complete good within seconds!
Look for example at this one, that completes perfect within 15 seconds:

 

[Allen Browne]

1. Instead of a WHERE clause, JOIN the 2 tables.

2. In table design view, make sure both fields are indexed.

Number is a reserved word in JET, so not a good name for a field. I doubt
that's the cause of the performance problem but you may like to avoid these
field names:
http://allenbrowne.com/AppIssueBadWord.html

There is also a problem with fields containing a dash, so you may want to
avoid that if possible:
http://support.microsoft.com/kb/271661/en-us

 

[Guest]

Hi Allen,
I was indeed aware of the Number name... Even tried changing it...
Both fields are indexed (no duplicates) and the dash in the field, i'm
afraid I cannot avoid that in this case...

1. Instead of a WHERE clause, JOIN the 2 tables.

2. In table design view, make sure both fields are indexed.

Number is a reserved word in JET, so not a good name for a field. I doubt
that's the cause of the performance problem but you may like to avoid these
field names:
http://allenbrowne.com/AppIssueBadWord.html

There is also a problem with fields containing a dash, so you may want to
avoid that if possible:
http://support.microsoft.com/kb/271661/en-us

 

[Guest]

Issue is resolved...

Hi Allen,
I was indeed aware of the Number name... Even tried changing it...
Both fields are indexed (no duplicates) and the dash in the field, i'm
afraid I cannot avoid that in this case...

1. Instead of a WHERE clause, JOIN the 2 tables.

2. In table design view, make sure both fields are indexed.

Number is a reserved word in JET, so not a good name for a field. I doubt
that's the cause of the performance problem but you may like to avoid these
field names:
http://allenbrowne.com/AppIssueBadWord.html

There is also a problem with fields containing a dash, so you may want to
avoid that if possible:
http://support.microsoft.com/kb/271661/en-us

 

[John W. Vinson]

I have two tables: Rugman and PDrive
The table look like this:
Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
PDrive -- ID (Auto/key) / Field1 (text!)

The reason I had to use text for Number and Field1, is because there are a
few lines what have a number like: 1234-2

I just want to check the Number in table Rugman and see if it does exist in
the table PDrive... If it does: field test must be changed to "done" for that
record where the numer exists in the other table...
Don't get why it's running so slow.
I have created also other update queries for the same tables and those
complete good within seconds!
Look for example at this one, that completes perfect within 15 seconds:
---
UPDATE PDrive, RugMan SET RugMan.test = "GOOD"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
(([RugMan]![ImageFile])=[PDrive]![Field1]));
--

Replace the ! with . for one thing - ! is for Object references (such as a
form control); . is the appropriate delimiter for table.field.

More importantly - make sure that there is an Index on [Number] and on
[Field1]. If [Number] is unique in the table [RugMan] make it a unique index;
you may not be able to do the update if it's not. Then use a JOIN rather than
a WHERE clause:

UPDATE PDrive INNER JOIN Rugman
ON Pdrive.Field1 = Rugman.[Number]
SET Rugman.Text = "GOOD";

I have NO idea what ImageFile has to do with anything. Your WHERE clause
implies that it's equal to the value of Field1, which would mean that it's
redundant - do ImageFile and Number contain the same information!?

John W. Vinson [MVP]