UPSERT Query Performance

[sturdyerde]

I have a large table (almost 4 million records, 12 fields) in Access
that is cleared and re-populated over a WAN connection on a daily
basis. It is time consuming. Is there any chance that an UPSERT
query might help this to go faster, or would it likely take about the
same amount of time?

Thanks,
Sam

 

[vanderghast]

If the table is first 'cleared' then synchronized, the UPSERT (MS SQL Server
2008, via a MERGE, or whatever you use with Jet) does nothing more than a
standard INSERT since no row would 'match' the data to be merged: all the
data have been cleared.

In fact, it could be faster if you export the data into a file (say, coma
delimited) on the server, compact it, then, through the WAN, transfer *that*
file to the local pc, decompact it and next, in the local db, import that
file now in the local pc, into Access.

Note that Jet does an UPSERT through a simple UPDATE over an OUTER JOIN,
thing that MS SQL Server cannot do and so, MS SQL Server has to invent a
new syntax to deal with that. But Jet does not need a new syntax.


UPDATE oldData RIGHT JOIN newData
ON oldData.primaryKey= newData.primaryKey
SET oldData.primaryKey = newData.primaryKey,
oldData.someOtherField = newData.someOtherField


is an "UPSERT", in Jet.




Vanderghast, Access MVP

 

[samuel.erde]

If the table is first 'cleared' then synchronized, the UPSERT (MS SQL Server
2008, via a MERGE, or whatever you use with Jet) does nothing more than a
standard INSERT since no row would 'match' the data to be merged: all the
data have been cleared.

In fact, it could be faster if you export the data into a file (say, coma
delimited) on the server, compact it, then, through the WAN, transfer *that*
file to the local pc, decompact it and next, in the local db, import that
file now in the local pc, into Access.

Note that Jet does an UPSERT through a simple UPDATE over an OUTER JOIN,
thing that  MS SQL Server cannot do and so, MS SQL Server has to inventa
new syntax to deal with that. But Jet does not need a new syntax.

UPDATE oldData RIGHT JOIN newData
    ON oldData.primaryKey= newData.primaryKey
SET oldData.primaryKey = newData.primaryKey,
    oldData.someOtherField = newData.someOtherField

is an "UPSERT", in Jet.

Vanderghast, Access MVP

Thank you all for your responses. I will also review those articles.
In short answer:

I am *currently* clearing and appending the table records, but would
not clear if using the UPSERT query.

I do not have direct access to the remote DB server, so I don't think
that it would be an option to export CSV, compact, transmit, and
import. Sounds like a good idea, though!

I have run a test UPSERT query once using the same syntax as above,
but it took much longer than it usually does to just clear and append.

Regards,
Sam