How does access search records in a query

[jackie]

When you join two tables, and one table has a million records while the other
has 2000, how does access search for each join type.
Which join type will run the fastest. Does access actually look through 1
million records on some of the join types.
If the data with the 1 million records is a linked table would that effect
the speed of the query.
Does access run the query on my pc or the server where the large table
resides.
The oracle tables are in another state. Will this have a significant effect
on performance.

I tried creating a pass through query, but I got a 936 error message. I
copied the sql for the pass through from a select query.
I apologize for the multiple posts on the same topic I posted recently. I
didn't know I wasn't supposed to do that.
Thanks.

 

[John W. Vinson]

When you join two tables, and one table has a million records while the other
has 2000, how does access search for each join type.

Identically across the three types, basically.

Which join type will run the fastest. Does access actually look through 1
million records on some of the join types.

Not unless you have no index on the fields; creating a relationship (of any
type) with enforced referential integrity will index the field. Of course a
Outer Join with the million-record table as the preserved table will return a
million records rather than 0 to 2000, so it will naturally be slower; but
that has to do with the join in the Query, not the (default, optional) join
specified in the relationship.

If the data with the 1 million records is a linked table would that effect
the speed of the query.

Yes. Note that you cannot enforce referential integrity between a local table
and a linked table. Are both tables stored in Oracle, or only the big one?

Does access run the query on my pc or the server where the large table
resides.
The oracle tables are in another state. Will this have a significant effect
on performance.
YES.

I tried creating a pass through query, but I got a 936 error message. I
copied the sql for the pass through from a select query.

If you could perhaps copy the SQL and the error message to a post here someone
might be able to help. The above does not contain any useful information.

 

[jackie]

Thank you for replying.
One table is an Oracle table, the other table resides in an access database
on my computer.
Here' sthe SQL

SELECT [Imcom Dodaacs].DODAAC, DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.DODAAC,
DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.CATEGORY,
DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.BB_IND
FROM [Imcom Dodaacs] LEFT JOIN DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL ON
[Imcom Dodaacs].DODAAC = DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.DODAAC
WHERE (((DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.BB_IND)="1"));


Here's the error message:
ODBC--Call Failed.
[Oracle][ODBC][ORA]ORA-00936: missing expression (#936)

Of course a

Outer Join with the million-record table as the preserved table will return a
million records rather than 0 to 2000, so it will naturally be slower; but
that has to do with the join in the Query, not the (default, optional) join
specified in the relationship.

Does this mean that if I do a left outer join with the table with the
million records on the left. Also, how does the join specified in the
relationship effect a join I create in a query. Does the query join take
precedence.

Thank you

 

[John W. Vinson]

Thank you for replying.
One table is an Oracle table, the other table resides in an access database
on my computer.
Here' sthe SQL

SELECT [Imcom Dodaacs].DODAAC, DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.DODAAC,
DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.CATEGORY,
DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.BB_IND
FROM [Imcom Dodaacs] LEFT JOIN DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL ON
[Imcom Dodaacs].DODAAC = DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.DODAAC
WHERE (((DSS_USER_V_PORTAL_F067_BASIC_Z_LOCAL.BB_IND)="1"));

[Imcom Dodaacs] is the big Oracle table I presume? Is there a unique Index
defined on DODAAC in the local table? and is BB_IND indexed?

Here's the error message:
ODBC--Call Failed.
[Oracle][ODBC][ORA]ORA-00936: missing expression (#936)

There's nothing *obvious* in the SQL that should cause that error. Can you
query [Imcom Dodaacs] directly (i.e. without a join)? Why the left join - do
you want to see all million records whether or not there is a match? If you
change LEFT to INNER does it work?

Of course a

Does this mean that if I do a left outer join with the table with the
million records on the left. Also, how does the join specified in the
relationship effect a join I create in a query. Does the query join take
precedence.

The query join takes precedence. The join type defined in the relationship is
just advisory and will be the default join type when you create a new query.
Since the two tables are in different databases, Access can't enforce
referential integrity in any case; any relationship you define in the
relationships window is, again, just advisory.