Access SQL and pass though difference?

[TKM]

I ran the following SQL..

If the 4 tables were linked into Access and joined (below is the Access SQL)

SELECT GENE_META_SERIES.EXTERNAL_SERIES_ID,
GENE_META_SAMPLES.EXTERNAL_SAMPLE_ID, GENE_META_SAMPLES.SAMPLE_NAME,
GENE_META_SAMPLES.SAMPLE_DESCRIPTION,
GENE_META_SAMPLES.SAMPLE_CHARACTERISTICS, GENE_META_SAMPLES.SAMPLE_SOURCE,
GENE_META_SAMPLE_ANNOTATIONS.SAMPLE_TYPE,
GENE_META_SAMPLE_ANNOTATIONS.SAMPLE_PATHOLOGY,
GENE_META_SAMPLE_ANNOTATIONS.SAMPLE_NAME,
GENE_META_SAMPLE_ANNOTATIONS.PRIMARY_SITE,
GENE_META_SAMPLE_ANNOTATIONS.PRIMARY_SITE_SUBTYPE1,
GENE_META_SAMPLE_ANNOTATIONS.PRIMARY_SITE_SUBTYPE2,
GENE_META_SAMPLE_ANNOTATIONS.PRIMARY_SITE_SUBTYPE3,
GENE_META_SAMPLE_ANNOTATIONS.HISTOLOGY,
GENE_META_SAMPLE_ANNOTATIONS.HIST_SUBTYPE1,
GENE_META_SAMPLE_ANNOTATIONS.HIST_SUBTYPE2,
GENE_META_SAMPLE_ANNOTATIONS.HIST_SUBTYPE3,
GENE_META_SAMPLE_ANNOTATIONS.CURATION_STATUS,
GENE_META_SAMPLE_ANNOTATIONS.TREATMENT, GENE_META_SAMPLE_ANNOTATIONS.SPECIES,
GENE_META_SAMPLE_ANNOTATIONS.GENDER, GENE_META_SAMPLE_ANNOTATIONS.LIFE_STAGE,
GENE_META_SAMPLE_ANNOTATIONS.AGE_YRS, GENE_META_SAMPLE_ANNOTATIONS.RACE,
GENE_META_SAMPLE_ANNOTATIONS.GEO_LOC,
GENE_META_SAMPLE_ANNOTATIONS.SITE_OF_FINDING,
GENE_META_SAMPLE_ANNOTATIONS.PATIENT_DIAGNOSIS,
GENE_META_SAMPLE_ANNOTATIONS.CELLS
FROM ((GENE_META_SAMPLE_ANNOTATIONS INNER JOIN GENE_META_SAMPLES ON
GENE_META_SAMPLE_ANNOTATIONS.EXTERNAL_SAMPLE_ID =
GENE_META_SAMPLES.EXTERNAL_SAMPLE_ID) INNER JOIN
GENE_META_PLATFORMXSAMPLESXSERIES ON GENE_META_SAMPLES.SAMPLE_ID =
GENE_META_PLATFORMXSAMPLESXSERIES.SAMPLE_ID) INNER JOIN GENE_META_SERIES ON
GENE_META_PLATFORMXSAMPLESXSERIES.SERIES_ID = GENE_META_SERIES.SERIES_ID
WHERE (((GENE_META_SERIES.EXTERNAL_SERIES_ID)="NUID-0000-0077-5214"))
ORDER BY GENE_META_SAMPLE_ANNOTATIONS.PRIMARY_SITE;

I get 3 differnt speed results that differ from 2 seconds to 20 seconds. Now
if I run a pass though such as:
select count(*) from sample_annotations sa, samples sp, series se,
platformxsamplesxseries x
where sa.external_sample_id = sp.external_sample_id AND sp.sample_id =
x.sample_id AND x.series_id = se.series_id;

the speed is the same for all three macheines. Why is this? is their a
difference between a pass though and a Access / SQL?

 

[vanderghast]

Your pass-through query only returns a scalar and does not have the same
condition on EXTERNAL_SERIES_ID, so your comparison between the pass-through
and the first query is somehow irrelevant. Use the first query as a
pass-through query if you really want to compare the same thing.

A pass-through query is solved on the MS SQL Server (or other kind of
server), and the result is brought back, through the network, while a Jet
query on linked table is performed locally through Jet calls to ODBC. The
pass trough query will generally be faster if it returns a very small amount
of data from large tables, while the Jet query will probably be faster if
the tables involved are small and implied in a cross join to generate
relatively very large amount of data.

The quality of the network may greatly influence the resolution time it
takes, and that may be machine dependant (as if a PC is on the same hub, and
alone, with the server, and another PC many hops away from the server, while
one of the intermediate hub in its way is very busy due to another PC
downloading music).



Vanderghast, Access MVP