Need help in query optimization when updating column values

[usertest]

Hi,


Below is the scenario.
I am tyring to updates the column values in my access table by matching the ID from SQL tables. If it matches pull records from table 1 or else table 2
Somehow this query is taking 10 mins to refresh 440 records into the access table.
AccessTable A has only 1 column (col_pk) while I'm tyring to match the same column from other tables, and if it matches then add new column and its respective records.

Do I have any levy of optimizing the query?

strSQL = "SELECT * FROM AccessTable A;"
'440 records currently
X.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


Do While X.EOF = False


strSQL = "SELECT Col1, Col12, NAME AS Col13, Col14, Col15 FROM dbo.SQL_Table WHERE REP_ID = '" & X("Col_PK") & "';"
' 
Y.Open strSQL, DC.BODS_REP, adOpenKeyset, adLockOptimistic


If Y.RecordCount <> 0 Then
X("Col1") = Y("Col1")
X("Col12") = Y("Col12")
X("Col13") = Y("Col13")
X("Col14") = Y("Col14")
X("Col15") = Y("Col15")
X.Update
Y.Close




Else
Y.Close


strSQL = "SELECT MgrID AS Col1, RepStatus As Col12, RepType As Col14 from dbo.SQL_Table2 WHERE RepID = '" & X("Col_PK") & "';"
Y.Open strSQL, DC.BODS_REP, adOpenKeyset, adLockOptimistic
X("Col1") = Y("Col1")
X("Col12") = Y("Col12")
X("Col14") = Y("Col14")
X.Update
Y.Close


End If
X.MoveNext
Loop
X.Close

Record count (SQL_Table)-128,813
Record count (SQL_Table2)-204,218
I think it is a simple query. nothing complex going on. But any ideas/input would be appreciated.

Thanks!

[Welshgasman]

Not queries though, but recordsets, which I think they call RBAR (row by agonising row)

[usertest]

Sorry, I didnt understand your response. Would you mind elaborating?

Not queries though, but recordsets, which I think they call RBAR (row by agonising row)

[Welshgasman]

You are not using queries directly but via recordsets, which are generally slower in a great deal.

A proper Update query would be in this syntax
https://learn.microsoft.com/en-us/of...oft-access-sql

[usertest]

Quick clarification:
in thebelow code, there is only 1 column, Col_PK

SELECT * FROM AccessTable A;"

then based on the matching of Col_pk with REP_ID, I will be inserting new columns and records associated with it.

Would the update syntax still be applicable in this case?

You are not using queries directly but via recordsets, which are generally slower in a great deal.

A proper Update query would be in this syntax
https://learn.microsoft.com/en-us/of...oft-access-sql

[Welshgasman]

Well I have never been a query expert, I have to experiment, but you seem to want to update from table1 if it has matching records in A, else table2.
Could you have data in both for an id?
Why select * if you are just using one field? That would slow it down as well.

[usertest]

yes correct. If ID matches with Table 1 then add those new columns from Table 1 into Table A else it matches with Table 2 and add those columns into Table A.

The select * is basically select ID from table.
There is only 1 column in table A. I think it doesnt matter if I do select * or that column name

Well I have never been a query expert, I have to experiment, but you seem to want to update from table1 if it has matching records in A, else table2.
Could you have data in both for an id?
Why select * if you are just using one field? That would slow it down as well.

[Welshgasman]

Well if the ID can only be in one or the other, I might just run two queries regardless, knowing one will never produce any data, as opposed to checking which table to use?

[zpy2]

https://answers.microsoft.com/en-us/...9-5051006fa485

[Micron]

You might want to look at what's commonly referred to as an "upsert" query, although it's not an official Access thing.
What I have to wonder about is the notion of adding columns in such a manner. It indicates a poorly normalized db, and that is the cause for many db issues, including slowness. That assumes there is nothing wrong with your network load at the time you run that code.