Access Query w/Access Table & SQL Table VERY SLOW

[accvbalearner]

Hello out there, need some advice on a query that has been giving me fits.

I have a SQL Backend and an Access 2016 (*.accdb) Front End.

I have a table in SQL with 24 rows.
I have a table in Access with a few thousand rows.

There are two common fields in both tables [HOP1] & [HOPD], both tables have the [HOP1] field entered.

The SQL table has [HOPD] entered complete also. I want to add the [HOPD] from the SQL table into the Access table using an Update Query in Access.

The query looks like this in SQLView from Access:

UPDATE SQLTbl INNER JOIN ACCTbl ON SQLTbl.HOP1 = ACCTbl.HOP1
SET ACCTbl.HOPD = [SQLTbl].[HOPD]
WHERE (((ACCTbl.HOPD) Is Null));

When I try to run the above it takes about 15 to 20 minutes before I just give up, the Running Query bar at the bottom just keeps starting over.

So I opened SQLTbl in Access, copied and pasted it into a new table in Access and ran the same query using both tables above physically in Access and it worked in less than one second!

WHAT IS THE ISSUE? This same behavior is happening all of the time between SQL and Access and it ONLY occurs when:
1) Trying to add data from a form in Access into a Table in SQL

2) When trying to run an Update Query in Access to update an SQL Table with an Access Table or Query Source or an SQL Table - the tables are in the exact same SQL database!

3) Trying to run an Update Query to update an Access Table with an SQL Table

The only time things work ok is:

A) If I write a Pass-through query, All query returns are superfast, just a few seconds. Update queries are real fast, they're finished by the time I move the mouse off of the Run button. But requires me to load everything into the SQL database, because I don't know how to pass Access Table, Query or Form data to the SQL Database

B) Opening a SQL Table/View in Access

C) Running a report in Access that has NO Calculations in Access. If I do a calculation in the Access Report, it lags a 10-15 seconds on output. If I put the calculation in a SQL View then it is an almost immediate report from Access.

I have MSSQL Server 2008 Enterprise and Office365 MSAccess 2016.

At the beginning, all of my databases were in *.mdb format and I was using the SQL Native Client Driver with a DSNLess Connection. I updated the SQL Server 2008 to the latest SPs, etc. and changed the DSNLess connection to use SQL Server Native Client 11.0 and things really sped up on A, B and C above but 1, 2 & 3 still stink.

Any Ideas on what I am doing wrong?

Thanks in advance for any advice you have to offer!

 

[isladogs]

First of all, why are you trying to replicate data from your SQL table to a local Access table? Normally data should be stored once only.

Secondly, do both tables have a primary key field?

Thirdly, are the two fields HOP1 and HOPD primary key fields or indexed fields in each table as that will dramatically speed up queries

 

[accvbalearner]

ridders,

Thanks for your quick reply.

First Answer: I'm replicating because the data needs to be together to export to Excel for a client to review, so I need a piece (HOPD) of data from the SQL table added to it in order to send. The time problem that I described is even worse when I try to export a query to Excel from Access using SQL Data. So what I do is get it into an Access Table then export it to Excel. I know that I can probably put it together in SQL and pull it straight into Excel from there, but I'm not so familiar with that scenario. I am much more comfortable with Access, but it seems that nothing I do with Access 2016 works like it used to with SQL!

Second Answer: Both tables (access and SQL) have primary keys, but neither table has HOP1 of HOPD as a Primary Key, each table has a number column as an ID. In Access it is Autonumber, in SQL it is int with a seed. It is possible in the future that HOP1 or HOPD may be repeated as the project develops, so I can't have them as a primary key, there are other columns in the SQL table I didn't mention in the original thread.

Third Answer: Indexed? In the Table Design in SQL, Access or Both? I never really thought about that because both are repeated in the Access Table. You've brought a good point here I think.

Can you tell me more about how Index effects the speed of the queries? When should I use Index on a Field in a Table?
When should I NOT us Index on a Field in a Table?

I'm used to being able to run Update queries from Access on a SQL Table without any issues (especially timing) at all. From my perspective it just seems like Access 2016 is causing all the problems, but I can't go back!

Thanks for your help, advice and quick reply!

 

[isladogs]

Have you considered exporting a query partly or wholly based on your SQL table? You may find that will work well.

This article explains how indexing works and when you should use it
https://support.office.com/en-us/article/Create-and-use-an-index-to-improve-performance-0A8E2AA6-735C-4C3A-9DDA-38C6C4F1A0CE
There are many other examples online.

Not all fields should be indexed.
Fields used in join clauses of select queries or for sorting or as filter criteria should often be indexed. Indexing may assist in update queries BUT it may also make them slower. In your case where the query hangs without completing, it's certainly worth trying...but it may not help.

 

[Pat Hartman]

The fact that you can join a Jet/ACE table to a table in a remote RDBMS and actually update one side or the other is nothing short of a miracle. But just because you can do something doesn't make it efficient. Since the SQL Server table is very small, simply download the data to a temp table and do the update by joining two Jet/ACE tables. If it were the Jet/ACE table that was tiny, you would upload that table to SQL Server and do the update there.

Sometimes you just know a better way to do something more efficiently than Access does and so you have to force Access to do it your way.

PS - always make unique indexes/primary keys for all tables.

 

[jleach]

>> But just because you can do something doesn't make it efficient <<

Great point. AFIAK, any time you have a heterogeneous query between Access (JET/ACE) and another ODBC datasource (SQL Server, MySQL, etc), it tends to become a headache for the driver to resolve and results in a huge performance hit. If you happen to be working over a WAN it becomes even more pronounced, and the possible lack of indexes are certainly contributing.

When I first started working with remote databases on Azure I used to try to sync things locally like this, and realized that it's (usually*) easier and faster to pull the whole table from SQL into a local temp/cache and go from there.

*sometimes you want to go with a LastUpdated timestamp on the server and record your local sync times so you can implement incremental loads of new data, but I find that we don't usually have to worry about that until we're measuring rows around 50k+

 

[The_Doc_Man]

UPDATE SQLTbl INNER JOIN ACCTbl ON SQLTbl.HOP1 = ACCTbl.HOP1
SET ACCTbl.HOPD = [SQLTbl].[HOPD]
[COLOR="Red"]WHERE (((ACCTbl.HOPD) Is Null[/COLOR]));

While we are at it, there is a superfluous element here. Why bother with the WHERE clause? You are ALREADY forcing this query to visit every possible record anyway (by having the WHERE clause touch the Access table's HOPD field). Is there any possibility that a case would occur where the HOP1 fields were the same but the HOPD fields were different? (Doesn't look that way from the description.) I am sure that the local Access engine (ACE) is working its buns off because you CAN'T execute this particular query as a pure back-end operation. The BE can't see the Access table, only Access can see both tables.

This is NOT to in anyway disparage or minimize the other suggestions regarding indexing or just making a local copy of the info for a quick & dirty update. But I wouldn't want to even slightly guess as to what hoops the ACE engine has to jump through to execute that query.

One last thought: IF you downloaded the SQL table that has 24 entries in it, you could just drive whatever it is you are doing with a JOIN query and not CARE about what was in the Access table anyway.