How does ODBC work?

  • Thread starter Thread starter Chris Delaney
  • Start date Start date
C

Chris Delaney

Does anyone know how Access ODBC data is handled? I am
running some queries with linked Oracle tables and need to
know whether Access does all the hard work or simply passes
the SQL to the Oracle server to process?

Many thanks.
 
I think what you are doing will dictate where the processing occurs.

For example, if you use DoCmd.RunSQL, I believe all processing is done client side. Whereas if you execute an sp, the processing is done server-side.



--
Cheers,


James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures
 
James Goodman said:
I think what you are doing will dictate where the processing occurs.
For example, if you use DoCmd.RunSQL, I believe all processing is done client side.
Whereas if you execute an sp, the processing is done server-side.


Not true. Jet does a pretty good job of sending the SQL to the server for
processing in most cases. Queries with complex joins and/or VBA and
User-Defined functions will increase the amount of local processing, but
the method that invokes the query shouldn't make any difference.

Pass-Throughs and Stored Procedures are ways to *guarantee* all processing
is on the server, but that doesn't mean that all processing is local when
those methods are not used.
 
If you are talking about a straight linked table. Then odbc works very well.

However, if you create two table links to oracle, and then create a simple
query on the ms-access side that joins those two tables, then a LOT of data
is dragged.

The reason for this is simple:

ODBC does not give JET a "picture" of the database, and the relations.

So, if you have two linked tables and do a sql join, the one table might be
on oracle, and the other odbc table might be a link to a telephone data
gathering system with some solar cells on top, and a 8 bit processor that
holds the table in a tiny ram chip. In other words, ms-access only sees two
tables, and NO relation between them is possible. Thus the join is going to
be done client side, and it is slow.

The solution to the above problem is simple:

1) Use a pass through query, and this will force/guarantee that the sql join
processing occurs client side. You get records ALREADY joined returned, and
thus JET/odbc has a real easy time dealing with that.

2) don't use sql joins on linked tables.

3) Always use views for multi-table joins. You the link to this view (this
is my favourite solution).

Thus, if you create the join and save it as a view, and then link to that
view, then you can EASILY use sql, and even using conditions on that view.
The JET/odbc works JUST FINE in this case!

So, just keep in mind that odbc works very good, but you need to use MUCH
caution in sql that needs tables joined to each other. Simply put, don't try
and use sql that joins linked tables. If you need such a query, then create
the view on the server side, and then create a table link to that view. As
mentioned, you can even throw up where clauses and conditions on that view,
and even when NOT using pass through queries, performance is excellent, and
only records matching the criteria are sent down the wire. This approach
gives you a lot of freedom, and in fact most of the time you can get away
not even bothering with pass-through quires at all.


So, odbc works very well, but about the only real thing to remember is that
you can't use sql to join linked tables, as then the join is done client
side.
 
Albert D. Kallal said:
If you are talking about a straight linked table. Then odbc works very well.

However, if you create two table links to oracle, and then create a simple
query on the ms-access side that joins those two tables, then a LOT of data
is dragged.

Is this strictly an Oracle phenomenon? I certainly don't see this against SQL
Server. I have seen join queries get converted into the older "Where somefield
= SELECT..." syntax, but the query analyzer in the case I was looking at
actually returned the results from that query faster than an identical
Pass-Through that used a join.
 
Rick Brandt said:
Is this strictly an Oracle phenomenon? I certainly don't see this against SQL
Server. I have seen join queries get converted into the older "Where somefield
= SELECT..." syntax, but the query analyzer in the case I was looking at
actually returned the results from that query faster than an identical
Pass-Through that used a join.

Golly, Rick, I will simply have to test this. (no, this is not just a Oracle
thing). And, no, I am not 100% sure that JET can't do a intelligent join..

However, ask your self what happens if the 2nd table is un-linked from the
sql-server, and then re-linked to another DIFFERENT sql server. Does JET
actually check, and figure out what server the ODBC file is on? My guess is
no! In other words, can JET figure out that the ODBC connection is on the
same system, and even the same database type/system, and then as result
decide to do a intelligent join? I doubt it, and as far as I know, each
table is just that, a ODBC table. However, I will test this, but my spider
sense tells me JET does not make this distinction! It should be most
interesting.

And, if anyone does know this answer for 100% sure, please fell free to jump
in and correct my above assumptions! I am not ready to start taking bets,
but after I test, then .....well, then we shall see!
 
Albert D. Kallal said:
Rick Brandt said:
Is this strictly an Oracle phenomenon? I certainly don't see this
against
SQL
Server. I have seen join queries get converted into the older "Where somefield
= SELECT..." syntax, but the query analyzer in the case I was looking at
actually returned the results from that query faster than an identical
Pass-Through that used a join.

Golly, Rick, I will simply have to test this. (no, this is not just a Oracle
thing). And, no, I am not 100% sure that JET can't do a intelligent join..

However, ask your self what happens if the 2nd table is un-linked from the
sql-server, and then re-linked to another DIFFERENT sql server. Does JET
actually check, and figure out what server the ODBC file is on? My guess is
no! [snip]

I just played with the SQL Trace tool for SS 2000 and it appears that Jet
can tell when the two tables are on the same system. The SQL passed to the
server includes both tables, but without using a JOIN. The server and
database are defined in the DSN so I don't know why Jet wouldn't be able to
tell when they are on the same system.

The first test I did was a simple two table query Parent/Child. Roughly...

SELECT Field1, Field2, Field3
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1

I did this with a local Access query against links to the tables and again
as a Pass-Through.

In this case the SQL passed to the server by the local query was pretty
ugly. It appeared to create a Statement defining variables for the
matching fields (enough for however many rows it would initially pull in)
and then substitute the actual values pulled from Table1 for that many
rows. I suppose as the user would scroll to pull in more records that this
would be repeated. I'm likely not describing it very well, but I'm
wondering if this is what you are describing.

It looked much better when a WHERE clause was added. A query of...

SELECT Field1, Field2, Field3
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1
WHERE Table1.Field1 = 123456

Was processed on the server as...

SELECT Field1, Field2, Field3
FROM Table1, Table2
WHERE Table1.Field1 = Table2.Field1
AND Table1.Field1 = 123456

While using this older syntax instead of a JOIN, the number of reads to
execute was very close to that of the Pass-Through. Then I discovered
that it wasn't really the WHERE clause that made the difference

Any time I simply opened the query datasheet when it was going to return
more rows than could be displayed then the server did the weird, ugly
looking process which seems to be JET's way of saying "return another
handful of rows". As soon as I pressed the Go-To-Last button on the sheet
then the server executed the syntax above which seems pretty
straightforward.

I'll have to play around with this some more because now I am curious what
the process is when opening and navigating in forms and such. At any rate,
I don't pretend to understand how Jet and the server optimizer decide what
to do, but I can say that even the convoluted looking stuff seems to run at
about the same speed as the Pass-Throughs. These days perhaps the
differences are masked by the sheer brute force of the hardware available.
Our first SQL Server was a dual Pentium Pro (I want to say 60Mhz?), maybe
256MB of RAM. Makes you wonder how it got anything done compared to what
you can buy now.
 
-----Original Message-----
Does anyone know how Access ODBC data is handled? I am
running some queries with linked Oracle tables and need to
know whether Access does all the hard work or simply passes
the SQL to the Oracle server to process?

Many thanks.
.

Sorry guys, I seem to have opened up a can of worms here.
Let me be a little clearer. I am attempting to work out
what is wrong with my Oracle system. Using the Oracle
interface, searches using a given table have recently
become very slow. I have tried doing queries via an Access
ODBC linked table and queries are much quicker. Is this
because the data is simply pulled straight to Access and
the query is performed there on-the-fly?

Cheers

Chris
 
Using the Oracle
interface, searches using a given table have recently
become very slow. I have tried doing queries via an Access
ODBC linked table and queries are much quicker.

The above a real surprise! Useally people wonder why ms-access is so slow!

Perahps the reason why ms-access *can* be faster is that ms-access often
will chache (hold in memory) some of the reocrds neeed for procesing.
Perahps those reocrds are thus held in memory, and you see a speed increase.

Also, often we are dealing with a perception, or what we all latencry. So,
the time to retive 5000 reocrds might be identical in both cases, but if one
starts displaying reords right away, while the other one simply waits untill
ALL 5000 records are retived AND THEN displays the first reocrd of the found
matches. In both cases, the record retival rate is the same, but the user
percetipion is compility different, and the 2nd system will seem slower. So,
it could be my comments aabout JET and it re-using reocrds is all wrong, and
you are dealing with somting that waits untill all work is done, and in the
other case, it begins dispalying data right away.

However, either way, it is not the fact of the query being perforanced on
the fly.

However, if a view is created on the oracle side, or even a stored
procedure, then perfoance should most certanly rather good. I mean, we are
talking about oracle here, and it is rather capabile data engine, and thus
if it is slow, then you must be lifting a lot of data.

To really get speed out of those database engines, you need to have some
experience as to what works, and what does not. However, generally, if you
are not dealing with large datasets, then speed with such nice systems at
your disposal should not be a problem at all. You might poke around, and ask
some questions in a Oracle group, and see what comes up.

For just regular queries, the speed of that ODBC query to oracle should be
no different then using whatever "client" tools you are using to work with
oracle. As mentioned, I do suggest avoiding joins on the ms-access side, and
using linked views, as that works about the best.
 
Using the Oracle
interface, searches using a given table have recently
become very slow.

So the slowdown is on one table and over time?

have you checked your indexes? Periodically analysing your indexes
help Oracle know what is the most effective query plan.

Also use Oracle's explain plan to check out how oracle is running the
query.

I too have noticed Oracle linked ODBC tables when joined are SLOOOW
compared to a passthrough query or executing the SQL in SQLPlus or
TOAD. This is especially apparent when you start doing stuff with
tables with over 500K records. I'll do some comparisons on Monday
cause this issue has been bugging me too.

I also notice that different ODBC versions support different
functionality (8 supports DSN less connections to Oracle, I couldn't
get it to work with 7) so maybe this is a factor in performance too.

When you say the Oracle interface do you mean the SQLPlus Windows
interface? Ditch it and use TOAD, it's the best GUI for Oracle that
I've found is a little bit of off topic advice.

www.toadsoft.com for a free trial version (60 days) - and I just
noticed a freeware version for SQL Server too...

Peter
 
Back
Top