Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-22-2018, 03:36 AM   #1
iqkh76
Newly Registered User
 
Join Date: Apr 2011
Posts: 9
Thanks: 1
Thanked 0 Times in 0 Posts
iqkh76 is on a distinguished road
Update Query very slow

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

iqkh76 is offline   Reply With Quote
Old 02-22-2018, 03:50 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Update Query very slow

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 02-22-2018, 04:44 AM   #3
iqkh76
Newly Registered User
 
Join Date: Apr 2011
Posts: 9
Thanks: 1
Thanked 0 Times in 0 Posts
iqkh76 is on a distinguished road
Re: Update Query very slow

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.

iqkh76 is offline   Reply With Quote
Old 02-22-2018, 04:51 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,029
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Update Query very slow

ensure you have applied indexes to both your linking fields

edit. if [tmpMaster].[iCode] 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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 02-22-2018 at 04:59 AM.
CJ_London is offline   Reply With Quote
Old 02-22-2018, 05:25 AM   #5
iqkh76
Newly Registered User
 
Join Date: Apr 2011
Posts: 9
Thanks: 1
Thanked 0 Times in 0 Posts
iqkh76 is on a distinguished road
Re: Update Query very slow

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.
iqkh76 is offline   Reply With Quote
Old 02-22-2018, 06:06 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,038
Thanks: 78
Thanked 1,996 Times in 1,944 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Update Query very slow

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 02-22-2018, 06:44 AM   #7
iqkh76
Newly Registered User
 
Join Date: Apr 2011
Posts: 9
Thanks: 1
Thanked 0 Times in 0 Posts
iqkh76 is on a distinguished road
Re: Update Query very slow

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

iqkh76 is offline   Reply With Quote
Old 02-22-2018, 06:53 AM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,038
Thanks: 78
Thanked 1,996 Times in 1,944 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Update Query very slow

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"?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 02-22-2018, 07:05 AM   #9
iqkh76
Newly Registered User
 
Join Date: Apr 2011
Posts: 9
Thanks: 1
Thanked 0 Times in 0 Posts
iqkh76 is on a distinguished road
Re: Update Query very slow

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 is offline   Reply With Quote
Old 02-22-2018, 07:07 AM   #10
iqkh76
Newly Registered User
 
Join Date: Apr 2011
Posts: 9
Thanks: 1
Thanked 0 Times in 0 Posts
iqkh76 is on a distinguished road
Re: Update Query very slow

Can I split this data into multiple tables and run the query?

So can I split this in 4 tables of 25,000?
iqkh76 is offline   Reply With Quote
Old 02-22-2018, 11:04 AM   #11
fkotulak
Newly Registered User
 
Join Date: Feb 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
fkotulak is on a distinguished road
Re: Update Query very slow

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!
fkotulak is offline   Reply With Quote
Old 02-22-2018, 03:03 PM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,029
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Update Query very slow

Quote:
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
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-22-2018, 03:05 PM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,029
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Update Query very slow

Quote:
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?

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Tags
like condition , update , wild characters

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Query Referencing Linked Table - Way Too Slow vmed Queries 2 10-08-2012 10:18 AM
Update query to slow to be of any use Groundrush Queries 11 10-20-2008 08:16 AM
Update query is running too slow bugsy Queries 1 02-13-2008 09:40 AM
Very slow update query rob_levy Queries 2 11-22-2007 02:22 AM
[SOLVED] SQL UPDATE very slow via Jet? mtnorwood Modules & VBA 3 05-09-2001 08:07 AM




All times are GMT -8. The time now is 11:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World