[AccessD] Optimizing subqueries

Thanks Gustav, but neither of those options work for me. I am building
all of the SQL in code.  Fields, criteria and tables can all be
variable.  So I can't store them in saved queries.  And since this is a
process that will possibly run quite frequently, I don't want to use
temp tables. (bloat, though I could get around that using a separate
table...but it's not necessary with what I have done putting the
subqueries in the FROM statement)

I am using a stored query to join the subqueries too, which is saving a
bit of time and clutter in the SQL.  My main problem right now, is that
I am looking at a variable number of joins, and I'm getting a little
goofed as to the separators and structure required to join one table to
the next, to the next, to the next, etc.  I've got it partially working,
but it's late here, going to crash and look at it again in the morning.
So far, the initial test (that I got to work (where I am joining 2
subqueries to the main query)) works like a champ, returns the necessary
19 records instantenously.

I posted a link to this system a month or so ago, but there were several
features not in place.  When I get this join issue solved, I'm going to
clean up the look of the system, and I'll post it again so everyone can
play around in it if they'd like.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, June 03, 2008 1:28 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Optimizing subqueries

Hi Drew

Good example.
At least two variations exist.

One variation on this in Access is to move the subquery "tables" to
separate queries. In most cases - also yours - you already have created
and tested the subquery on its own to verify that the output is as
expected. Then leave that as a saved query, here it could by
qdyExtraData:

  SELECT 
    PrimaryKey 
  FROM 
    tblExtraData 
  WHERE 
    SomeOtherKey=1 
    AND
    SomeCriteriaField="Hello World"

and the main query will now read:

  SELECT 
    tblPrimaryData.PrimaryKey, 
    MorePrimaryData
  FROM 
    qdyExtraData
  INNER JOIN 
    tblPrimaryData ON
      qdyExtraData.PrimaryKey=tblPrimaryData.PrimaryKey

The advantage is that it is much easier to read and debug and that the
"subquery" can be reused in other queries.

The other variation is that from the "Old SQL School": Temporary tables.
In some cases - indeed for update and delete queries - this is so much
faster that it is hard to believe but many Access developers have no
tradition for this technique.
In your case you would write the output of the helper query to the temp
table, tblExtraData, and then use this as a source:

  SELECT 
    tblPrimaryData.PrimaryKey, 
    MorePrimaryData
  FROM 
    tblExtraData
  INNER JOIN 
    tblPrimaryData ON
      tblExtraData.PrimaryKey=tblPrimaryData.PrimaryKey

/gustav


The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.