An update query takes very long time (1 Viewer)

gsrai31

Registered User.
Local time
Today, 22:58
Joined
Aug 30, 2003
Messages
44
Hi, can someone please help. This update query takes very long time to run. Just wondering if this can be achieved with VBA perhaps it will be quicker to update customer field based on criteria in query below.

UPDATE tblFinal INNER JOIN tblList ON tblFinal.Type = tblList.Range SET tblFinal.Customer = tblList!Customer
WHERE (((tblFinal.CodeNumber)>=[tblList]![StartRange] And (tblFinal.CodeNumber)<=[tblList]![EndRange]));

Thanks in advance
GR
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:58
Joined
Jan 23, 2006
Messages
15,364
How many records are involved?
What are the datatypes of tblFinal.CodeNumber, [tblList]![StartRange] and [tblList]![EndRange]?
What is your environment/technical set up? multi user/single database?

Can you post a copy of your test database?
 

isladogs

MVP / VIP
Local time
Today, 22:58
Joined
Jan 14, 2017
Messages
18,186
Hi, can someone please help. This update query takes very long time to run. Just wondering if this can be achieved with VBA perhaps it will be quicker to update customer field based on criteria in query below.

UPDATE tblFinal INNER JOIN tblList ON tblFinal.Type = tblList.Range SET tblFinal.Customer = tblList!Customer
WHERE (((tblFinal.CodeNumber)>=[tblList]![StartRange] And (tblFinal.CodeNumber)<=[tblList]![EndRange]));

Thanks in advance
GR

Unless you have a very large number of records, this should be fast
I doubt it will be quicker using code.
Try this slightly modified version of your query where bangs replaced with dots. Also Between used instead of >= ... And <=

Code:
UPDATE tblFinal INNER JOIN tblList ON tblFinal.Type = tblList.Range SET tblFinal.Customer = tblList.Customer
WHERE (((tblFinal.CodeNumber) Between [tblList].[StartRange] And [tblList].[EndRange]));
 

Cronk

Registered User.
Local time
Tomorrow, 09:58
Joined
Jul 4, 2013
Messages
2,770
Indexing appropriate fields may improve performance.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:58
Joined
May 7, 2009
Messages
19,169
You can also get rid of the where clause and move it as joining fields.
 

gsrai31

Registered User.
Local time
Today, 22:58
Joined
Aug 30, 2003
Messages
44
Thanks all, I will try between and indexing as suggested. @jdraw - datatyoes for tblFinal.CodeNumber, [tblList]![StartRange] and [tblList]![EndRange] are number. It is a single user file database on window 7 PC in Access 2013. Number of records can be between 800k to 1million apx.
Thanks for your help.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:58
Joined
Jan 23, 2006
Messages
15,364
Can you tell us in plain, simple terms what the application is?
I agree with the other comments re indexing, and the Between/AND construct.
But it seems to me you are updating tblFinal.Customer every time a CodeNumber is within a Range of values. I don't know how many unique Customers you have, nor how many "transactions" (list) you have, so it seems you could be repeatedly updating the same record. Nothing wrong with that necessarily, but depending on your intent there may be way(s) to reduce the volume of updates.

Again, this is speculation on my part because we don't really know the details of the application.
 

isladogs

MVP / VIP
Local time
Today, 22:58
Joined
Jan 14, 2017
Messages
18,186
Adding to the previous comment, please also indicate exactly how long its taking to run the updates

As a comparison, I have a table with 2.6 million records including a boolean field.
I just tested updating the boolean field to true for all records.
It took 13 seconds on my 6 year old & fairly slow PC.
It would be nice if it was faster, but I think this is reasonable for that situation

However in a practical situation I would only update the current record or a limited number of records at a time which would be much faster
 

gsrai31

Registered User.
Local time
Today, 22:58
Joined
Aug 30, 2003
Messages
44
Hi all thanks for your comments. It is an access 2013 database designed to process some raw data. The query takes about 25 to 30 minutes run. The query is updating tblFinal.Customer field every time a CodeNumber is within a Range of values. There about 10000 customers in the tblList. My aim is to update customer name in tblFinal where CodeNumber is within StartRange and EndRange. Hope this is helps.
 

isladogs

MVP / VIP
Local time
Today, 22:58
Joined
Jan 14, 2017
Messages
18,186
OK that is slow....
If the previous suggestions about modifying the SQL & indexing don't help, I suggest you post a stripped down copy of your db with the 2 tables, the query & anything else needed to test it

NOTE: The fields to index are:
tblList: Range, Customer, StartRange, EndRange
tblFinal: Type, Customer

Some further points:
1. Do you have a primary key field in BOTH tables.
If so, is the PK field Customer?
If its something else, can you join by the PK fields instead?
2. Is this a 1 to 1 join or 1 to many?
 

gsrai31

Registered User.
Local time
Today, 22:58
Joined
Aug 30, 2003
Messages
44
I have tried the query with Indexing fields as suggested. To be honest it hasn't really made any difference. There is no PK in both tables, and nothing else I can join by. It is a 1 to 1 join. If I were to try VBA code, can someone please help me with writing it for me? thanks
 

isladogs

MVP / VIP
Local time
Today, 22:58
Joined
Jan 14, 2017
Messages
18,186
I'm surprised the update query ever completes without PK fields
As a matter of course, ALL tables should normally have a primary key field.

It is ESSENTIAL for all tables used in ACTION queries so Access can identify the correct records to modify

You need to go through each table in your db & either convert an existing field to be a PK field (it needs to be unique for each record) or add an autonumber field
e.g. tblList: ListID; tblFinal: FinalID

If the Customer field is UNIQUE and each record contains a customer in both tables, then that MIGHT be suitable fr these two tables

Once you've done that & indexed other fields as already discussed, run your query & look in wonder as it completes in a few seconds
 

gsrai31

Registered User.
Local time
Today, 22:58
Joined
Aug 30, 2003
Messages
44
Okay Thank you very much for your help. I will work on it as you suggested.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:58
Joined
May 7, 2009
Messages
19,169
here is your VBA equivalent.
syntax: Call fncUpdateTBLFinal()
Code:
Public Function fncUpdateTBLFinal()

    Const TARGET_TABLE As String = "tblFinal"
    Const SOURCE_TABLE As String = "tblList"
    
    Dim rsTarget As DAO.Recordset
    Dim rsSource As DAO.Recordset
    
    Dim db As DAO.Database
    
    Set db = CurrentDb
    Set rsTarget = db.OpenRecordset(TARGET_TABLE, dbOpenDynaset)
    With rsTarget
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Set rsSource = db.OpenRecordset( _
                "SELECT Customer FROM " & SOURCE_TABLE & " " & _
                    "WHERE #" & Format(Nz(!CodeNumber, 0), "mm/dd/yyyy") & "# " & _
                        "BETWEEN [StartRange] AND [EndRange];", dbOpenSnapshot)
            If Not (rsSource.BOF And rsSource.EOF) Then
                rsSource.MoveFirst
                .Edit
                !Customer = rsSource!Customer
                .Update
            End If
            rsSource.Close
            Set rsSource = Nothing
	    DoEvents
            .MoveNext
        Wend
        .Close
    End With
    Set rsTarget = Nothing
    Set db = Nothing
End Function
 

gsrai31

Registered User.
Local time
Today, 22:58
Joined
Aug 30, 2003
Messages
44
Hi arnelgp, thank you very much, really appreciate that. I will try it tomorrow when back in office. Thanks again
 

isladogs

MVP / VIP
Local time
Today, 22:58
Joined
Jan 14, 2017
Messages
18,186
@gsari31
Can I strongly recommend you do not use the recordset code that arnelgp provided. Looping through 10000 records one at a time will not be anywhere near as fast as using an update query once the issues already described have been resolved.

@arnelgp
You are fully aware that update queries are far faster than updating using a recordset. Why have you complicated the issue by providing totally unnecessary code?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:58
Joined
May 7, 2009
Messages
19,169
See post #11 ridder, the Op asked for it.
It will be out to test tomorrow.

We can only suggest.

Op, if you want the code to run a little faster, suggest you use Transaction.
 

Mark_

Longboard on the internet
Local time
Today, 15:58
Joined
Sep 12, 2017
Messages
2,111
Thinking about this, you have 10000 records in table A.

You should be working out a query (Query A)to identify the 10000 (max) records in table B that match back to A that are in your date range.

Then you make an update query based on your Table A and your Query A. Hopefully that reduces the number of records and speeds things up.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:58
Joined
Jan 23, 2006
Messages
15,364
I'm not sure he is working with dates-- he said all fields were numbers?

?? The OP is updating the Customer field every time the CodeNumber is within a Range of values. What does that mean exactly?
Perhaps I'm missing something basic.

Consider:
Suppose I had a list of Customers and a bunch of records with zip codes. I update the Customer every time I get a zip code within a list of zip codes by State.
Am I not updating the Customer unnecessarily after the first assignment?


Now my analogy may be way off base, but I think it meets the OP's description of his issue.

@gsrai31

Here is a link to Database Principles. Every table should have a primary key that uniquely identifies each record in that table.

Perhaps you could tell us more about your application.
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 15:58
Joined
Sep 12, 2017
Messages
2,111
jdraw,

To me it seems less intuitive.

He is going through TblList based on [CodeNumber] between a start and end range.
For each entry he is then updating all TblFinal records that match on [Customer] by setting their [Type] = [Range] in TlbList.

It seems he would be much better off limiting what he is looking at in TblList to be only one record per [Customer] FIRST, then work through how he wants to update. There is nothing in his query to identify when [Range] is different for the same [Customer], thus giving him a... well... random value in [Type].
 
Last edited:

Users who are viewing this thread

Top Bottom