Update Query very slow (1 Viewer)

iqkh76

New member
Local time
Tomorrow, 00:17
Joined
Apr 13, 2011
Messages
9
I am running the below query on around 100,000 records but it is taking ages, do not know why.

Code:
UPDATE [tmpMaster] INNER JOIN iMaster ON [tmpMaster].[Name1] like "*" & iMaster.mName  & "*" SET [tmpMaster].[iCode] = [iMaster].[iCode];
 

Minty

AWF VIP
Local time
Today, 18:47
Joined
Jul 26, 2013
Messages
10,355
Using a wildcard at the beginning of (Like "*..) especially in a Join will cause the problem.

The wildcard will make it ignore any indexing on the fields, and cause it to search every one of your 100,000 records against every record in the source table. So just 10 records in the source would mean a 1,000,000 attempted matches.

If the names match then don't use the wildcard at all, use =
If the names only match after the first bit of the name remove the initial *

Both the above would significantly improve the performance.
 

iqkh76

New member
Local time
Tomorrow, 00:17
Joined
Apr 13, 2011
Messages
9
Thanks for your response.

I made the changes of removing the * before the field name but still it is slow. Since I want a approximate match hence using Like.

Also tried using % still no change.

Please advise if I can do anything else to make this faster.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:47
Joined
Feb 19, 2013
Messages
16,553
ensure you have applied indexes to both your linking fields

edit. if [tmpMaster]. is indexed, remove the index before running your update then reinstate it (this will halve the number of updates required)

concerned about your partial match requirement

if you have name1 values of say

smith
Smithson

and mName values of

smit
smith

then each of the name1 records will attempt to be updated twice, once with the icode from the smit record and once from the smith record - only one will win, but no guarantee which one or whether it will be consistent
 
Last edited:

iqkh76

New member
Local time
Tomorrow, 00:17
Joined
Apr 13, 2011
Messages
9
I will try removing the index from the icode field.

For the partial match this is something similar to vlookup with approx match which will pick up first record which matches something of lookup value, this is the requirement to update the field.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Jan 23, 2006
Messages
15,364
How would you know that the first match of many potential matches is the "best/closest" match?
Can you tell us a little more about the data involved?
 

iqkh76

New member
Local time
Tomorrow, 00:17
Joined
Apr 13, 2011
Messages
9
iMaster has list of codes for mName and I want to update the nearest first match to tmpMaster. Since first Pal has code ia123 and first Kal is id8987.

I am already running a query to get the exact match, hence only want to update the one which does not matched exactly.

iMaster
mName iCode
Pal Pal ia123
Pal A ib451
A Kal ic8987
Kal Kal id999
Kal B ie698

tmpMaster
Name1 icode
Pal ia123
Kal ic8987

I hope I am clear now!

Thanks for your response
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Jan 23, 2006
Messages
15,364
This is a sample,right? You're not really dealing with 3 char names??? and 100000 records??
How important to your business is the result of this "name matching"?
 

iqkh76

New member
Local time
Tomorrow, 00:17
Joined
Apr 13, 2011
Messages
9
The above data is sample data.

Name matching is like 50% of the data does not get updated with exact match.

I processed 200,000 records and of which 100,000 do not get updated with icode. Hence moved the records to another table and trying to use update query on that.

100,000 records is also for one of the data files I am working and there are around 100 - 200 such files but I would be processing them individually.

So this approx match is more important to get the correct output.
 

iqkh76

New member
Local time
Tomorrow, 00:17
Joined
Apr 13, 2011
Messages
9
Can I split this data into multiple tables and run the query?

So can I split this in 4 tables of 25,000?
 

fkotulak

New member
Local time
Today, 12:47
Joined
Feb 22, 2018
Messages
3
For what its worth. Certain Access versions are incompatible with Windows 10. And will cause many things to run very slow. Been there and done that. If on Windows 10 and using Access 2010 or before - don't know about 2013, that could be your problem. Switched to Access 2016 and all my slowness went away!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:47
Joined
Feb 19, 2013
Messages
16,553
Can I split this data into multiple tables and run the query?
Possibly, you would have to try it

but in your example, kal will match to kal kal id999. not A Kal.

you might be better to do this in stages

create a make table query to store name1, mname and icode based on like xxx*. Ensure name1 is indexed

then you can identify those records where there is a count of 1 - these records can be used to update your tmpmaster table and then removed from your maketable - or skip this stage and just go to the next stage

for those that are left, create another maketable, grouping on name1 and selecting first icode.

then use this table to update your tmpmaster table

the reason for the maketable is because you cannot use groupby in update queries
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:47
Joined
Feb 19, 2013
Messages
16,553
Certain Access versions are incompatible with Windows 10. And will cause many things to run very slow
news to me, can you provide examples or links to this?
 

Users who are viewing this thread

Top Bottom