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.