Slow query - can I make it run faster? (1 Viewer)

Morten

Registered User.
Local time
Today, 12:31
Joined
Sep 16, 2009
Messages
53
Hi

I have a database with only two linked external sources:

A spreadsheet and a SharePoint list.

I update the SharePoint list with data from the spreadsheet. A total of 22 columns will be checked for changes.

Here is a part of the SQL string I use to update the SharePoint list, so the SQL string is a bit longer with 22 columns.

Code:
UPDATE [Supplerende kreditor stamdata] 
INNER JOIN SyncSP 
ON [Supplerende kreditor stamdata].[Kreditor nummer] = SyncSP.[Kreditor nummer] SET [Supplerende kreditor stamdata].Kreditortype = [SyncSP]![Kreditortype]
WHERE [Supplerende kreditor stamdata].Kreditortype is null OR [Supplerende kreditor stamdata].Kreditortype <>[SyncSP]![Kreditortype];

My problem is the query is very slow, because it needs to check all 24.000 records for changes.

How can I speed the process up?

Best regards
Morten
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:31
Joined
May 7, 2009
Messages
19,169
You may try to create a query that is already filtered and update that query.
 

Morten

Registered User.
Local time
Today, 12:31
Joined
Sep 16, 2009
Messages
53
You may try to create a query that is already filtered and update that query.

Thank you for your reply. Do you mean filter by creating a select Query to only get the records with a change in one of the 22 columns?

Best regards
Morten
 

static

Registered User.
Local time
Today, 12:31
Joined
Nov 2, 2015
Messages
823
If both tables should be the same, delete and insert everything from scratch.


If you UNION both tables together you can check if they match by GROUPing...

Code:
select [Kreditor nummer], Sum(n) AS nsum
from (select distinct *, 1 as n FROM [Supplerende kreditor stamdata]
	 union all
	 select distinct *, 2 as n FROM [SyncSP]) 
group by [Kreditor nummer], Kreditortype, ..., ..., ... , to field 22

nsum = 3 = match, no update required
nsum 1 & 2 field mismatch
nsum 1 without 2 no matching id in SyncSP
nsum 2 without 1 no matching id in sks


Updating data will be slower than requesting it, so work out what needs to change first (use a local temp table if needs be), then run a single update.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:31
Joined
Feb 19, 2002
Messages
42,970
One of the wonderful features of Access is its ability to create heterogeneous joins that are updateable. That doesn't mean they are efficient. To join the SharePoint list to the Spreadsheet, Access copies the entire contents of both linked tables to local memory. It then performs the join and for each change it finds, it sends a separate update query to affect just that row to the SharePoint site.

If the SharePoint list is always supposed to match the spreadsheet, delete the list and then append the new values.
 

Users who are viewing this thread

Top Bottom