3

Is there another way to write the following poorly performing in Access (Jet) SQL?

SELECT *
FROM
  Det left join 
  (select * from Inv inner join Dep on Inv.SN=Dep.SN) as mass ON
    (Det.RecSN = mass.SelfID) and (Det.DDTime between mass.Start and mass.End)

eliminating the sub-select and doing a direct join does not work, giving a JOIN expression not supported Error in the following snippet

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

The following produces a result, but suppresses Det records that don't match up to one or the other of the inner tables

SELECT *
FROM
  Det inner join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

adding another set of parentheses doesn't help other than helping Access highlight the join clause that's problematic

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    ((Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End))

dropping the between clause to a where condition results in no left join characteristics (DDTime isn't between NULL and NULL) and can lead to too many entries being held in memory while other joins are being performed to this whole mess, and can make it more logically difficult to add other where conditionals.

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID 
WHERE
  Det.DDTime between Dep.Start and Dep.End

Adding a clause to make left join behavior re-emerge (but only for the portion in the ON clause) makes the world slow to a crawl, I think because Access doesn't know how to handle OR clauses without essentially running a union all in the background for each of the sides of the OR clause with the rest of the query.

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID 
WHERE
  Det.DDTime between Dep.Start and Dep.End
  OR Dep.Start is NULL

My current work-around is to use the inner join method, then have another query that queries this one ("qCombo") and compares against the Det table for entries in Det, but not in qCombo using either a left join or where not exists. But this solution is just as inefficient (if not more so) than the sub-select method at top.

SELECT 'qCombo' as QueryName, *
FROM
  Det inner join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

Select Det.*
FROM
  Det LEFT JOIN
  qCombo ON
    (Det.DDTime=qCombo.DDTime) and
    (Det.OtherFieldsInUniqueIdx=qCombo.OtherFieldsInUniqueIdx)
WHERE
  qCombo.QueryName IS NULL

Other possibilities?

2
  • just found A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins. on msdn.microsoft.com/en-us/library/office/… – mpag Jul 19 '18 at 23:41
  • Your comment is correct. The problem is not the multiple ON conditions but the a LEFT JOIN (b INNER JOIN c) structure (omitting the ONs for clarity). – ypercubeᵀᴹ May 21 at 23:28
0

Are the tables you are querying indexed?

If not, I would add an index to the "Inv.SN=Dep.SN"columns , " Det.RecSN = Inv.SelfID" and lastly the dates "Det.DDTime, Dep.Start".

NOTE: Be careful when adding indexes. Specially if you are working in your production database. Do your research first on creating indexes on transactional tables, to avoid locks.

If you already have these indexed, then how many records do you have on each table?

Suggestion: I would Separate the OR conditions in different queries and save into a temporary table, so that I can analyze the data sets and results. Ultimately, querying the temporary tables for the final join.

Rule of thumb:

query just one record Id, and see the performance. Always check if index exists.

There may be various issues, and multiple approaches.

1
  • Briefly, Det has roughly 1 million entries (and 6? fields), Dep has about 50 entries (and maybe 20 fields), Inv has about 40 entries * 10 fields. As no one will be doing inserts/updates once produced, adding indexes shouldn't have any negative repercussions. I'll check on the indexes on Dep, but at least the Det and Inv tables are indexed as indicated. – mpag Jul 19 '18 at 0:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.