Performance questions

[Maury Markowitz]

We have a fund accounting package built on Pervasive SQL (formerly Btrieve).
We also keep additional information in our own system, based on SQL Server.
Every night we run a process to copy data from Pervasive to SQL Server, and
during the day we also run many queries against the Pervasive system. We do
this with various queries and code in Access, running against linked tables
to both servers.

About 1/2 of the queries against Pervasive are much, _much_, MUCH, slower
than expected. I'm trying to figure out why. Queries that run in seconds
(literally) on SQL Server can take minutes against Pervasive, yet something
that looks idential will complete in seconds. I know it's something I'm doing
(or Access really), but I don't understand what. So some questions...

1) When using a linked table, do queries against it get sent to the server?
IE, if I link to "tblLotsOfStuff" and then "select * from tblLotsOfStuff
where something = aValue", does that SQL get passed along to the remote
server? Does it "depend"? If so, on what does it depend?

2) We have found that some tables run faster if Access uses a Pass-through
query. That is, if we simply send a query to Pervasive that joins two tables
it might take a minute, but if we replace one of those linked tables with a
query that does "select * from thatTable", then it runs in seconds. Can
anyone suggest a reason for this? It would seem that this is forcing the
query to run locally on my machine, over the network.

3) If I join tables from two servers, as we do, what exactly happens? Does
Access query both and then join, or does it pull both tables across the
network and then do it internally?

If there's a good document on this, I'd love to see it. Nothing I've found
on google so far has really gone into any detail at all on how Access does
its stuff.

Maury

 

[Brian]

We have a fund accounting package built on Pervasive SQL (formerly Btrieve).
We also keep additional information in our own system, based on SQL Server.
Every night we run a process to copy data from Pervasive to SQL Server, and
during the day we also run many queries against the Pervasive system. We do
this with various queries and code in Access, running against linked tables
to both servers.

About 1/2 of the queries against Pervasive are much, _much_, MUCH, slower
than expected. I'm trying to figure out why. Queries that run in seconds
(literally) on SQL Server can take minutes against Pervasive, yet something
that looks idential will complete in seconds. I know it's something I'm doing
(or Access really), but I don't understand what. So some questions...

1) When using a linked table, do queries against it get sent to the server?
IE, if I link to "tblLotsOfStuff" and then "select * from tblLotsOfStuff
where something = aValue", does that SQL get passed along to the remote
server? Does it "depend"? If so, on what does it depend?

2) We have found that some tables run faster if Access uses a Pass-through
query. That is, if we simply send a query to Pervasive that joins two tables
it might take a minute, but if we replace one of those linked tables with a
query that does "select * from thatTable", then it runs in seconds. Can
anyone suggest a reason for this? It would seem that this is forcing the
query to run locally on my machine, over the network.

3) If I join tables from two servers, as we do, what exactly happens? Does
Access query both and then join, or does it pull both tables across the
network and then do it internally?

If there's a good document on this, I'd love to see it. Nothing I've found
on google so far has really gone into any detail at all on how Access does
its stuff.

Maury

What I normally do when a query is running slowly against SQL Server linked
tables is to use the SQL Profiler to find out exactly what SQL is arriving
at the server. Having discovered that, it is usually pretty obvious how Jet
has decided to execute the query, and how to speed it up e.g. by using a
passthrough query instead. I've no idea whether a similar tool exists for
Pervasive SQL, but you certainly need such a thing.

Although I have never used Pervasive SQL, in my experience the ODBC drivers
provided by the purveyors of Mickey Mouse database engines are often pretty
awful.

Joining tables in two different databases is always going to be slow. See
my first point about finding out exactly what SQL is arriving at each
server.

 

[Maury Markowitz]

What I normally do when a query is running slowly against SQL Server linked
tables is to use the SQL Profiler to find out exactly what SQL is arriving
at the server.

These aren't SQL Server tables though. Is this still applicable? And how do
you use Profiler to find out what's arriving at the server?

Joining tables in two different databases is always going to be slow. See
my first point about finding out exactly what SQL is arriving at each
server.

What exactly does Access do in these cases? Read both into local tables or
something?

Maury

 

[Brian]

These aren't SQL Server tables though. Is this still applicable? And how do
you use Profiler to find out what's arriving at the server?


What exactly does Access do in these cases? Read both into local tables or
something?

Maury

The SQL Profiler will only work with SQL Server. As I said, you really need
something similar for Pervasive SQL, but whether such a thing exists I
really wouldn't know.

The SQL Profiler is on the Tools menu in the SQL Server Enterprise Manager.
It's pretty straightforward to use.

I can't give you details on what Access will do in any given situation -
that's why you need a tool to tell you what's happening - but, in general,
if you create a query which the Jet database engine determines can be
performed in it's entirety by the server, then that's what will happen and
Jet will gratefully accept the results. However, if Jet concludes that the
server cannot perform the query in it's entirety (which is definitely the
case if the query involves joining tables in heterogeneous databases) then
it will have to retrieve sufficient data from the server so that it can
finish the query itself. In your case, Jet will certainly need to query
each database separately for the data it needs in order to perform the join.
It's impossible to even guess what data it will actually query from each
database without knowing anything about the query, the database structures,
the indexes and so on - and even then, the acid test would still need be an
empirical study of the actual SQL arriving at each server.