MS Access Multiuser - SQL tables

Author

SQL tables

Patrick Salsich

2006-01-10, 11:24 am

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

Pat Hartman$MVP$

2006-01-11, 3:24 am

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.

"Patrick Salsich" <psalsich@bellsouth.net> wrote in message
news:OatHhQgFGHA.2320@TK2MSFTNGP11.phx.gbl...
>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
>
>
>

Rick Brandt

2006-01-11, 9:24 am

Pat Hartman(MVP) wrote:
> 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.

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

Patrick Salsich

2006-01-11, 1:24 pm

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

Thanks for your help

"Pat Hartman(MVP)" <patsky@NoSpam.optonline.net> wrote in message
news:eqRuW$lFGHA.2444@TK2MSFTNGP11.phx.gbl...
> 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.
>
> "Patrick Salsich" <psalsich@bellsouth.net> wrote in message
> news:OatHhQgFGHA.2320@TK2MSFTNGP11.phx.gbl...
I[color=darkred]
Thanks[color=darkred
]
>
>

Pat Hartman$MVP$

2006-01-15, 3:23 am

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.

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
news:aH8xf.17607$oW.8127@newssvr11.news.prodigy.com...
> Pat Hartman(MVP) wrote:
>
> 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.
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
>

Pat Hartman$MVP$

2006-01-15, 3:23 am

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

"Patrick Salsich" <psalsich@bellsouth.net> wrote in message
news:OaTTpTtFGHA.3936@TK2MSFTNGP12.phx.gbl...
> So an append query running from the client Access to the temptable on the
> server will go quickly?
>
> Thanks for your help
>
>
> "Pat Hartman(MVP)" <patsky@NoSpam.optonline.net> wrote in message
> news:eqRuW$lFGHA.2444@TK2MSFTNGP11.phx.gbl...
> the
> is
> and
> this
> as
> I
> Thanks
>
>

Sponsored Links