OR operator in SQL statement (1 Viewer)

Babycat

Member
Local time
Today, 13:11
Joined
Mar 31, 2020
Messages
275
If Q_UserFilter() = True -> everything passes thru
If Q_UserFilter() = False -> only Deleted records from P, U and V get thru

AND (Q_UserFilter() OR (Iif(P.Deleted, False, True) AND Iif(U.Deleted, False, True) AND Iif(V.Deleted, False, True)))
Yes, this is simple logic AB + AC = A(B+C).
Thank for saving my code
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:11
Joined
Feb 19, 2002
Messages
42,981
@isladogs , I don't think the "AND" join can be implemented with a union but maybe you join on the one field that will reduce the recordset the most and use a WHERE for the other columns. To emulate the "OR" join, you would use Union because you want to keep only unique records. Union ALL would create duplicates if multiple fields satisfied the ORs since it returns all rows from all sets and doesn't summarize.

@the doc man, It looks like the poster's question came right out of the article. It was not his own so he can't explain the reason for the OR. And looking at the column names chosen for the article it sure does look like bad data and/or a bad design.
 

Babycat

Member
Local time
Today, 13:11
Joined
Mar 31, 2020
Messages
275
Thinking about this more--you don't even need to call Q_UserFilter() from the query at all---use it in VBA:

Code:
if Q_UserFilter() =False then User_Criteria = " AND (P.Deleted = False) AND (U.Deleted = False) AND (V.Deleted = False); "

If its true--don't even use User_Criteria
Yes, I have changed my VBA code, life is simpler than what i think... :)
 

Babycat

Member
Local time
Today, 13:11
Joined
Mar 31, 2020
Messages
275
@isladogs , I don't think the "AND" join can be implemented with a union but maybe you join on the one field that will reduce the recordset the most and use a WHERE for the other columns. To emulate the "OR" join, you would use Union because you want to keep only unique records. Union ALL would create duplicates if multiple fields satisfied the ORs since it returns all rows from all sets and doesn't summarize.

@the doc man, It looks like the poster's question came right out of the article. It was not his own so he can't explain the reason for the OR. And looking at the column names chosen for the article it sure does look like bad data and/or a bad design.
Yes bro, I had been noticed ealier that the "OR" operator of article is on table JOINs, while my code uses "OR" in WHERE clause (I think I have emphasized it in my first post). I was not sure about OR operator performance no matter where it is being used. Until now, from your posts I am pretty sure and feel safe when using OR in WHERE clause.
About using OR in table JOINs, it seems out of my spoce. However it is interesting, at least I learnt from article that I should use UNION instead of OR in table JOINs.

Thank everyone again!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:11
Joined
Feb 19, 2002
Messages
42,981
You're welcome. OR's in the WHERE clause are fine. It is important to optimize indexes so index fields you search on but don't go crazy because updating the indexes is expensive.
 

isladogs

MVP / VIP
Local time
Today, 06:11
Joined
Jan 14, 2017
Messages
18,186
@isladogs , I don't think the "AND" join can be implemented with a union but maybe you join on the one field that will reduce the recordset the most and use a WHERE for the other columns. To emulate the "OR" join, you would use Union because you want to keep only unique records. Union ALL would create duplicates if multiple fields satisfied the ORs since it returns all rows from all sets and doesn't summarize.
Yes, I was already aware of all that!
1. The direct comparison is UNION vs OR join. In this example, UNION was faster - that may not always be the case
2. I never suggested an AND join could be simulated with a UNION - it was included just to compare the speed with an OR join
3. I only included UNION ALL to compare the speed - apologies if that was misleading

The queries used above were very simple. I may retest the relative speeds of OR join vs UNION for more complex queries
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:11
Joined
Feb 19, 2002
Messages
42,981
Thanks for testing the timing Colin. I was just trying to clarify.
 

isladogs

MVP / VIP
Local time
Today, 06:11
Joined
Jan 14, 2017
Messages
18,186
No problem.
I'm also planning to look at the JET ShowPlan output for OR join vs UNION
 

isladogs

MVP / VIP
Local time
Today, 06:11
Joined
Jan 14, 2017
Messages
18,186
I've retested the first 3 queries from post #17 but this time using JET ShowPlan to see how Access handles each one.
The two fields used in the joins are both indexed but neither is a PK field

First of all (purely for the purpose of comparison), the standard double join query: qryJoinAND:

1631471952358.png


SQL:
SELECT tblInstalledVoices.*, tblLanguages.UseAltVoice, tblLanguages.AltVoice, tblLanguages.UseDefaultVoice
FROM tblInstalledVoices INNER JOIN tblLanguages ON (tblInstalledVoices.Language = tblLanguages.Language) AND (tblInstalledVoices.Code = tblLanguages.Code);

The ShowPlan output for this query clearly shows that indexes are used and that each join is tested in succession.
Rushmore optimisation isn't available in this example:

1631472226937.png


Now compare with the OR join query:
SQL:
SELECT tblInstalledVoices.*, tblLanguages.UseAltVoice, tblLanguages.AltVoice, tblLanguages.UseDefaultVoice
FROM tblInstalledVoices INNER JOIN tblLanguages ON (tblInstalledVoices.Language = tblLanguages.Language) OR (tblInstalledVoices.Code = tblLanguages.Code);

1631472367977.png

Although the ShowPlan entry is shorter, indexing is not used.
Worse still, the 'using X-Prod join' means that Access treats the OR join as a Cartesian join (or Cross-join) which inevitably is slower

Finally looking at the UNION query:
SQL:
SELECT tblInstalledVoices.*, tblLanguages.UseAltVoice, tblLanguages.AltVoice, tblLanguages.UseDefaultVoice
FROM tblInstalledVoices INNER JOIN tblLanguages ON (tblInstalledVoices.Language = tblLanguages.Language)
UNION SELECT tblInstalledVoices.*, tblLanguages.UseAltVoice, tblLanguages.AltVoice, tblLanguages.UseDefaultVoice
FROM tblInstalledVoices INNER JOIN tblLanguages ON (tblInstalledVoices.Code = tblLanguages.Code);

As you would expect, Access treats this as 2 separate queries run in succession:
1631472700156.png


In terms of performance, indexing is again used (good) but the results are stored in a temporary table (bad)
Hence it is slower than the AND join but faster than the OR join (in this case)

Test1 - AND Join = 3.485 s Records = 39
Test2 - OR Join = 3.78 s Records = 39
Test3 - UNION = 3.028 s Records = 39

If anyone is interested, a zipped copy of the ShowPlan.out file is attached
 

Attachments

  • showplan.zip
    487 bytes · Views: 94
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 06:11
Joined
Sep 21, 2011
Messages
14,048
I never knew access used rushmore technology?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:11
Joined
Feb 19, 2002
Messages
42,981
I don't see the attachment. Can you please try again.

I'm surprised there s any difference with just 39 records. I would test with at least 50,000
 

isladogs

MVP / VIP
Local time
Today, 06:11
Joined
Jan 14, 2017
Messages
18,186
I never knew access used rushmore technology?

Yes. It uses Rushmore where it can do so

1631475851381.png



I don't see the attachment. Can you please try again.

I'm surprised there s any difference with just 39 records. I would test with at least 50,000
Sorry - attachment now added to previous post

I said it was a simple query. As mentioned in post #17, the tests were each run 20 times. So the time differences were very small in this case.
Nevertheless, the query optimisation plan is essentially the same whether there are 39 records or 50,000

I'm intending to redo this with a more complex query involving more joins and many more records.
However, as you would expect, if I make it too complex, Access can't handle the OR join variant.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:11
Joined
Feb 28, 2001
Messages
27,001
This is more of a question on legalities than on the tech itself. I thought Rushmore was a FoxPro exclusive. Did I miss a licensing agreement or a merger or a buyout or a corporate takeover or something?
 

isladogs

MVP / VIP
Local time
Today, 06:11
Joined
Jan 14, 2017
Messages
18,186
FoxPro became part of Microsoft back around 1992 and Rushmore was then built into Access. See Visual FoxPro - Wikipedia

Two related articles:
1. Utilising Rushmore to optimise queries in FoxPro, see Using Rushmore Query Optimization to Speed Data Access | Microsoft Docs

2. The following quote is part of an FMS article Microsoft Access Performance Increase the Speed of Your Access Databases (fmsinc.com)
Take Advantage of Rushmore Query Optimization
Microsoft Jet uses Rushmore query optimization whenever possible. Rushmore is applied to queries run on native Access data, and on linked FoxPro and dBASE tables. Other linked data types do not support Rushmore optimization. To ensure that Rushmore optimizations are used, create indexes on all fields that are used to restrict a query's output. If you have queries that don't contain indexes on fields used in the query's restriction clause, Rushmore is not used.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:11
Joined
Feb 28, 2001
Messages
27,001
I must have missed that merger/acquisition event in 1992 because I wasn't involved in the forum back then and was beating my head against the ORACLE brick wall for OpenVMS. My Access database involvedement started 9 years later. Wasn't worried about the Rushmore technology. Just thought they were legally separate - but you have explained it. Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:11
Joined
Feb 19, 2002
Messages
42,981
Thanks for the sample database.

If Rushmore is used only for local Jet (ACE?) tables, what is the point? I've never actually even seen a detailed explanation of what Rushmore does except that it involves indexes. Searching brings up old FoxPro articles. I remember at the time the rumor was that MS was buying FoxPro specifically for the Rushmore technology.
 

isladogs

MVP / VIP
Local time
Today, 06:11
Joined
Jan 14, 2017
Messages
18,186
Thanks for the sample database.

If Rushmore is used only for local Jet (ACE?) tables, what is the point? I've never actually even seen a detailed explanation of what Rushmore does except that it involves indexes. Searching brings up old FoxPro articles. I remember at the time the rumor was that MS was buying FoxPro specifically for the Rushmore technology.
I haven't uploaded a sample database 😏
It was a ShowPlan.out file

It is precisely because Rushmore works with indexed fields used to filter data that makes it very useful.
That's why fields used in WHERE clauses should be indexed wherever possible.

If you look at the quote in post #35, Rushmore also works with FoxPro and dBase linked tables.
With SQL tables, the optimisation should be done in SQL Server itself
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:11
Joined
Feb 19, 2002
Messages
42,981
Sorry, I didn't unzip the file yet. I didn't have time to look at it yesterday.

You didn't address my question about linked tables or ACE. Do you know if Rushmore is used on linked Jet/ACE tables or if it is used by ACE at all?
 

isladogs

MVP / VIP
Local time
Today, 06:11
Joined
Jan 14, 2017
Messages
18,186
Rushmore works (where applicable) with any Access tables - linked or local.
It still works with ACE as well as JET. The confusingly named JET Show Plan screenshots in posts #30 & #33 were all done from A365.
The registry key is still called JetShowPlan in current versions of Access

1631559113762.png


Having said all that, although generally very helpful in terms of query optimisation, there are times when Rushmore can be counterproductive.
See Bug Report: Rushmore Query Optimization slow down Access database performance when using network linked tables (microsoft.com)
I have tested this scenario and replicated the issue
 

Users who are viewing this thread

Top Bottom