SQL tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Patrick Salsich - 10 Jan 2006 16:37 GMT

I have an access mdb with ODBC links to a SQL table on a remote server. I
can update records in the SQL table by just typing. I also wrote some
update queries using joins between local table and the SQL table. Works OK
but slow. So now I want a fast and easy way to update larger groups of
records in the SQL table based on matching records in the local table. Need
speed for this. Don;t want to use a stored procedure. Any ideas? Thanks

Patrick

Reply to this Message

Pat Hartman(MVP) - 11 Jan 2006 03:35 GMT

When you join a linked table to a local table, Jet brings the data from the
linked table to the client and processes it there. If your linked table is
SQL Server, Oracle, etc and the table is larger than a few thousand rows,
this process can be a little slow.

If the only way you have to control the selection is with a local table, I
would transfer the local table to the server. Since the join will be of
homogeneous tables, the processing will happen on the server and only the
results will be returned to the client.

If you can't create a server table on the fly, create a permanent table and
append to it as necessary. If you have multi-users who might be doing this
at the same time, include the use as part of the data so you can use that as
criteria to control which rows to append, delete, or join to the other
table.

>I have an access mdb with ODBC links to a SQL table on a remote server. I
> can update records in the SQL table by just typing. I also wrote some
[quoted text clipped - 6 lines]
>
> Patrick

Reply to this Message

Rick Brandt - 11 Jan 2006 14:21 GMT

> When you join a linked table to a local table, Jet brings the data
> from the linked table to the client and processes it there. If your
> linked table is SQL Server, Oracle, etc and the table is larger than
> a few thousand rows, this process can be a little slow.

While a agree with the precaution that this "could" happen, it certainly
does not always happen. Often the join values from the local table are sent
to the server for selection processing on the server. A test I just did
easily demonstrates this.

I linked to a SQL Server table with approximately 500,000 rows and then
created a local table with one row and one field. In that field I inserted
a value that I knew should match with 50 or so rows in approximately the
middle of the SQL Server table. When I created an Access query joining the
local table to the link I got the 50 rows returned instantaneously. Clearly
in this case the entire SQL Server table was not pulled over the LAN so the
join could be processed locally.

I have done similar operations in my production apps where I insert a
(relatively) small number of rows in a local table and then use a join to a
link strictly for the purpose of limiting the rows that I get back from the
server. I have always seen virtually instant response times when doing
this.

There might be a threshold on the number of local rows in the join that does
cause the server table to be pulled over, but IME this is not usually the
case.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Pat Hartman(MVP) - 15 Jan 2006 02:45 GMT

I'm glad to hear that Jet is smart enough to send the local table. I don't
think it has always been that smart. I had a lot of trouble a few years ago
with this situation and ended up having to create a stored procedure to
solve the problem.

>> When you join a linked table to a local table, Jet brings the data
>> from the linked table to the client and processes it there. If your
[quoted text clipped - 23 lines]
> does cause the server table to be pulled over, but IME this is not usually
> the case.

Reply to this Message

Patrick Salsich - 11 Jan 2006 17:31 GMT

So an append query running from the client Access to the temptable on the
server will go quickly?

Thanks for your help

> When you join a linked table to a local table, Jet brings the data from the
> linked table to the client and processes it there. If your linked table is
[quoted text clipped - 22 lines]
> >
> > Patrick

Reply to this Message

Pat Hartman(MVP) - 15 Jan 2006 02:47 GMT

Since your current method is slow, why not try the method I suggested and
let us know if it is an improvement.

> So an append query running from the client Access to the temptable on the
> server will go quickly?
[quoted text clipped - 36 lines]
>> >
>> > Patrick

Reply to this Message