access 97, insert from sql server slow

[lesperancer@natpro.com]

I've got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports

if the local table is part of the reporting MDB, the insert statement
(16 fields) takes less than 30secs, but because of db-bloat, I moved
the local table to a 2nd MDB

and per postings, this 2nd MDB is copied into a folder and linked as a
'temp' MDB every time I run my reporting mdb

also, per postings, my main form opens a recordset to a table in the
'temp' MDB to prevent continuous LDB activity

but the insert statement now takes 20 mins....

both the reporting MDB and the temp MDB are on the same terminal server
(on different drives) and the sql server is connected to the terminal
server via a 1-GB network

updating to the 'temp' MDB is slow with / without any indexing on the
local table

the 'temp' MDB has been compacted, no difference

I created a new temp MDB and imported the tables, no difference

what am I missing ?

[Tim Marshall]

Re: access 97, insert from sql server slow

lesperancer@nat pro.com wrote:

I've got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports

That's really not that many records. Why not just have the mdb/e run
reports against linked tables or pass through queries? It would
eliminate maintenance of a copy of your data.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[lesperancer@natpro.com]

Re: access 97, insert from sql server slow

the final report is based on a query of many tables (consisting of both
sql server and access)
with user-selected filtering

and the current sql-server app does not allowing for indexing on its
tables

and .....

Since the posting, I changed the insert query to insert just one field
(6-char text) and it's still slow

And if I open the temp MDB and look at the contents of the table, it's
empty until to 20-odd minutes are up... so it's not a data transfer /
dribble issue...

It's like the whole thing is suspended on the sql server waiting for
the query to run

I'll try the insert without using the terminal server

Tim Marshall wrote:

lesperancer@nat pro.com wrote:
>

I've got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports

>
That's really not that many records. Why not just have the mdb/e run
reports against linked tables or pass through queries? It would
eliminate maintenance of a copy of your data.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Larry Linson]

Re: access 97, insert from sql server slow

<lesperancer@na tpro.comwrote

I've got an access97 reporting mdb that pulls data
(77,000 rows) from a sql server table into a local
table to run reports

Almost certainly, a report with 77,000 rows of data is, from a usability
point of view, something that needs work. Consider creating a View on SQL
Server to ensure that the extraction and manipulation is all done
server-side, or a pass-through Query 9 (but, my experience with Access, Jet,
ODBC, and SQL Server has been that, unless your joins are what Jet considers
complex*, the SQL generated by the combination is generally efficient). If
you only bring across the network the actual data to appear on a
"human-usable" report, I'll bet you would not have performance problems
(unless there is something seriously wrong with the Table design).

* Jet's criteria for "complex" vary between versions, but
if it deems "too complex" it will bring, or try to bring,
all the underlying data to the user's machine on what
seems to be the assumption that will be more efficient
than doing it on the server.

Larry Linson
Microsoft Access MVP

[Larry Linson]

Re: access 97, insert from sql server slow

<lesperancer@na tpro.comwrote

the final report is based on a query of many tables
(consisting of both sql server and access)
with user-selected filtering

The join of the combination of SQL Server and Jet tables forces much more
data to be retrieved from SQL Server, because the join can ONLY be done on
the machine where Jet is being processed (the user's machine). For example,
suppose that your SQL Server table has 100,000 records, and the Jet table
only has 100 records... you'll be retrieving all 100,000 records from SQL
Server, because only after the join will the 99,900 unused records be
determined.

and the current sql-server app does not allowing
for indexing on its tables

Are you interfacing with a commercial application that your company is
using? If not, if it is your company's application, you should have a chat
with the database administrator (DBA) to get the DBA's advice, and perhaps
the DBA will offer the solution of indexing, or creating Views, or maybe
even moving some of your Jet tables to SQL Server, so the big report query
join can be accomplished server-side without bringing all that extra data
across the network.

Larry Linson
Microsoft Access MVP

[Rick Brandt]

Re: access 97, insert from sql server slow

"Larry Linson" <bouncer@localh ost.notwrote in message
news:rZEmh.1728 $IT2.610@trnddc 06...

<lesperancer@na tpro.comwrote
>

the final report is based on a query of many tables
(consisting of both sql server and access)
with user-selected filtering

>
The join of the combination of SQL Server and Jet tables forces much more data
to be retrieved from SQL Server, because the join can ONLY be done on the
machine where Jet is being processed (the user's machine). For example,
suppose that your SQL Server table has 100,000 records, and the Jet table only
has 100 records... you'll be retrieving all 100,000 records from SQL Server,
because only after the join will the 99,900 unused records be determined.

Actually, in my testing that is not the case. What is sent to the server is a
WHERE clause with a 100 value OR criteria on the field used in the join. Still
a lousy way to run a query, but not as bad as pulling 100,000 rows.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[lesperancer@natpro.com]

Re: access 97, insert from sql server slow

that's why I'm inserting the sql server table data into an ms-access
table
so I don't run into the restrictions you mention
I'm working with ms-access data exclusively for reporting purposes

and this is a commercial application, which has a newer version that
allows indices to be created, but the upgrade to this version won't
happen till the 2nd half of 2007, because there's a need to update
hardware / OS

Larry Linson wrote:

<lesperancer@na tpro.comwrote
>

the final report is based on a query of many tables
(consisting of both sql server and access)
with user-selected filtering

>
The join of the combination of SQL Server and Jet tables forces much more
data to be retrieved from SQL Server, because the join can ONLY be done on
the machine where Jet is being processed (the user's machine). For example,
suppose that your SQL Server table has 100,000 records, and the Jet table
only has 100 records... you'll be retrieving all 100,000 records from SQL
Server, because only after the join will the 99,900 unused records be
determined.
>

and the current sql-server app does not allowing
for indexing on its tables

>
Are you interfacing with a commercial application that your company is
using? If not, if it is your company's application, you should have a chat
with the database administrator (DBA) to get the DBA's advice, and perhaps
the DBA will offer the solution of indexing, or creating Views, or maybe
even moving some of your Jet tables to SQL Server, so the big report query
join can be accomplished server-side without bringing all that extra data
across the network.
>
Larry Linson
Microsoft Access MVP

[lesperancer@natpro.com]

Re: access 97, insert from sql server slow

the report, is summarizing these 77000 rows of data, so it is only
about 17 pages long
the 77000 rows are further processed based on business rules, to obtain
the final table that is used for reporting purposes

the rules and filtering options are such that I can not preload the
data in a data warehouse format

I eventually will move the ms-access tables to sql-server and will used
stored procedures to implement the business rules and will run the
reports from the sql-server tables - but that won't happen until after
we upgrade to the latest OS, sql-server and application versions as
mentioned in another response

I just can't understand why inserting one field from 77000 sql-server
rows into an ms-access table takes 30 secs is the ms-access table is
local, but 20-mins if it's linked to another MDB on the same server


Larry Linson wrote:

<lesperancer@na tpro.comwrote
>

I've got an access97 reporting mdb that pulls data
(77,000 rows) from a sql server table into a local
table to run reports

>
Almost certainly, a report with 77,000 rows of data is, from a usability
point of view, something that needs work. Consider creating a View on SQL
Server to ensure that the extraction and manipulation is all done
server-side, or a pass-through Query 9 (but, my experience with Access, Jet,
ODBC, and SQL Server has been that, unless your joins are what Jet considers
complex*, the SQL generated by the combination is generally efficient). If
you only bring across the network the actual data to appear on a
"human-usable" report, I'll bet you would not have performance problems
(unless there is something seriously wrong with the Table design).
>
* Jet's criteria for "complex" vary between versions, but
if it deems "too complex" it will bring, or try to bring,
all the underlying data to the user's machine on what
seems to be the assumption that will be more efficient
than doing it on the server.
>
Larry Linson
Microsoft Access MVP

[Larry Linson]

Re: access 97, insert from sql server slow

"Rick Brandt" <rickbrandt2@ho tmail.comwrote

the final report is based on a query of many tables
(consisting of both sql server and access)
with user-selected filtering

>>
>The join of the combination of SQL Server and Jet tables forces much

more data

>to be retrieved from SQL Server, because the join can ONLY be done on

the

>machine where Jet is being processed (the user's machine). For example,
>suppose that your SQL Server table has 100,000 records, and the Jet

table only

>has 100 records... you'll be retrieving all 100,000 records from SQL

Server,

>because only after the join will the 99,900 unused records be

determined.

>
Actually, in my testing that is not the case. What is sent to the server

is a

WHERE clause with a 100 value OR criteria on the field used in the join.

Still

a lousy way to run a query, but not as bad as pulling 100,000 rows.

Are we talking about the same thing?

If I have a Query with an Access Table of 100 Records, with a field called
SerialNumber, and an SQL Server Table with a field called SerialNumber, and,
in this Query, I join the Serial Number fields in the two Tables, do you
mean that Jet-ODBC is going to send to the server 100 queries with a WHERE
clause instead of the Join, or a WHERE clause with the 100 values for Serial
Number that pre-retrieved from the local Jet Table?

Suppose it was 1,000 Records? 10,000 Records? 50,000?

Larry Linson
Microsoft Access MVP

[Larry Linson]

Re: access 97, insert from sql server slow

Have you looked at the articles, and examples, at MVP Tony Toews site,
http://www.granite.ab.ca/accsmstr.htm on performance?

Apparently, I was in error about how much information is retrieved -- see
Rick's post in this thread. It has, for certain, been several versions ago
when I last looked at what was transmitted/received from Access client to
Server database, and there was no similar situation in that database
application.

And, if the application is commercial, it's likely that you cannot create
Views in that database, nor stored procedures -- approaches frequently used
to improve performance. But, it is probable that you could use passthrough
Queries to send Queries that aren't altered or modified, but executed
exactly as you wrote them, on the server. On the other hand, passthrough
Queries will only have access to the SQL Server tables, so aren't any help
in the problem of joining local and server Tables.

Larry Linson
Microsoft Access MVP

<lesperancer@na tpro.comwrote in message
news:1167797957 .688410.81630@n 51g2000cwc.goog legroups.com...

the report, is summarizing these 77000 rows of data, so it is only
about 17 pages long
the 77000 rows are further processed based on business rules, to obtain
the final table that is used for reporting purposes
>
the rules and filtering options are such that I can not preload the
data in a data warehouse format
>
I eventually will move the ms-access tables to sql-server and will used
stored procedures to implement the business rules and will run the
reports from the sql-server tables - but that won't happen until after
we upgrade to the latest OS, sql-server and application versions as
mentioned in another response
>
I just can't understand why inserting one field from 77000 sql-server
rows into an ms-access table takes 30 secs is the ms-access table is
local, but 20-mins if it's linked to another MDB on the same server
>
>
Larry Linson wrote:

><lesperancer@n atpro.comwrote
>>

> I've got an access97 reporting mdb that pulls data
> (77,000 rows) from a sql server table into a local
> table to run reports

>>
>Almost certainly, a report with 77,000 rows of data is, from a usability
>point of view, something that needs work. Consider creating a View on
>SQL
>Server to ensure that the extraction and manipulation is all done
>server-side, or a pass-through Query 9 (but, my experience with Access,
>Jet,
>ODBC, and SQL Server has been that, unless your joins are what Jet
>considers
>complex*, the SQL generated by the combination is generally efficient).
>If
>you only bring across the network the actual data to appear on a
>"human-usable" report, I'll bet you would not have performance problems
>(unless there is something seriously wrong with the Table design).
>>
> * Jet's criteria for "complex" vary between versions, but
> if it deems "too complex" it will bring, or try to bring,
> all the underlying data to the user's machine on what
> seems to be the assumption that will be more efficient
> than doing it on the server.
>>
> Larry Linson
> Microsoft Access MVP

>

[Rick Brandt]

Re: access 97, insert from sql server slow

Larry Linson wrote:

Are we talking about the same thing?
>
If I have a Query with an Access Table of 100 Records, with a field
called SerialNumber, and an SQL Server Table with a field called
SerialNumber, and, in this Query, I join the Serial Number fields in
the two Tables, do you mean that Jet-ODBC is going to send to the
server 100 queries with a WHERE clause instead of the Join, or a
WHERE clause with the 100 values for Serial Number that pre-retrieved
from the local Jet Table?
Suppose it was 1,000 Records? 10,000 Records? 50,000?

A single SELECT statement with criteria of...

WHERE SerNum = This
Or SerNum = That
Or SerNum = ...

I don't know if there is a number of local rows where the behavior changes, but
I suspect that to be the case. I only know that if the local table is
relatively small performance is too good for it to be pulling all records for a
local join.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Albert D. Kallal]

Re: access 97, insert from sql server slow

Try a persistent connection..... ..


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[lesperancer@natpro.com]

Re: access 97, insert from sql server slow

I have a persistent connection
and right now I have 2 identical queries, consisting of
INSERT INTO tblLineBooking ( slsNbr )
SELECT slsNbr
FROM tblLineBooking_ v
WHERE transDate Between #4/1/2006# And #12/31/2006#;

one query takes 15secs (which is correct)
the other takes 20mins

then I shutdown the MDB and reopen it, now both queries take 20
mins....

so I'm not sure what's happening


Albert D. Kallal wrote:

Try a persistent connection..... ..
>
>
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Larry Linson]

Re: access 97, insert from sql server slow


"Rick Brandt" <rickbrandt2@ho tmail.comwrote in message
news:lgNmh.1872 $ji1.1722@newss vr12.news.prodi gy.net...

Larry Linson wrote:

>Are we talking about the same thing?
>>
>If I have a Query with an Access Table of 100 Records, with a field
>called SerialNumber, and an SQL Server Table with a field called
>SerialNumber , and, in this Query, I join the Serial Number fields in
>the two Tables, do you mean that Jet-ODBC is going to send to the
>server 100 queries with a WHERE clause instead of the Join, or a
>WHERE clause with the 100 values for Serial Number that pre-retrieved
>from the local Jet Table?
>Suppose it was 1,000 Records? 10,000 Records? 50,000?

>
A single SELECT statement with criteria of...
>
WHERE SerNum = This
Or SerNum = That
Or SerNum = ...
>
I don't know if there is a number of local rows where the behavior
changes, but I suspect that to be the case. I only know that if the local
table is relatively small performance is too good for it to be pulling all
records for a local join.

Thanks, Rick. There's always something to learn about our favorite database
product, no matter how long we've been using it, isn't there?

Larry Linson
Microsoft Access MVP

[Chuck Grimsby]

Re: access 97, insert from sql server slow


Is this "temp" table deleted before each time you fill it up? If so,
consider creating a *new* table each time you fill it up. You may
want to do this in a second "temp data" mdb. Feel free to create any
indexes you want, the jet engine will create them as the data goes
into the table.

Remember that both the front-end (the mdb that is running the query)
and the data mdb need to be on the computer's local drive. (Having
them both on the same drive is "better".)

Is this a MS SQL Server you're pulling the data from? If not, we need
to know that. I know it sounds silly, but different SQL Servers
sometimes require different techniques.

As Larry suggested, if you can create a view, do so. Views can
rapidly speed up the retrieval process. (If this is a MS SQL Server,
running a view repeatedly will cause MS SQL Server to sort of create
it's own indexes, regardless of what's created/allowed, which also
speeds things up.)

Also, make sure you have plenty of free space on the local computer
this is running on. Queries against a (non-MS) SQL Database can eat
memory like crazy.

You mentioned Terminal Server.... That itself can cause problems if
it's not optimized correctly. Especially when it's a Citrix set-up.
There are times when it's *very* intelligent to hire a Citrix Guru to
set the Terminal Server up properly. They're expensive in the short
run, but the employee time saved over a few days can offset it rather
rapidly. (No, I'm not a Citrix Guru, but I've seen the results of
their work, and it's danged impressive!)


On 2 Jan 2007 14:52:00 -0800, lesperancer@nat pro.com wrote:

>I've got an access97 reporting mdb that pulls data (77,000 rows) from a
>sql server table into a local table to run reports
>
>if the local table is part of the reporting MDB, the insert statement
>(16 fields) takes less than 30secs, but because of db-bloat, I moved
>the local table to a 2nd MDB
>
>and per postings, this 2nd MDB is copied into a folder and linked as a
>'temp' MDB every time I run my reporting mdb
>
>also, per postings, my main form opens a recordset to a table in the
>'temp' MDB to prevent continuous LDB activity
>
>but the insert statement now takes 20 mins....
>
>both the reporting MDB and the temp MDB are on the same terminal server
>(on different drives) and the sql server is connected to the terminal
>server via a 1-GB network
>
>updating to the 'temp' MDB is slow with / without any indexing on the
>local table
>
>the 'temp' MDB has been compacted, no difference
>
>I created a new temp MDB and imported the tables, no difference
>
>what am I missing ?

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing