OR operator in SQL statement (1 Viewer)

[Babycat]

Hi everyone,

I'm not a db engineer, i have almost 0 background knowledge in this field, so sorry for my fool questions.
I have read this article :
Query optimization techniques in SQL Server: tips and tricks
It mentions that OR operator as below might leads too many table read operation,

SELECT DISTINCT
    PRODUCT.ProductID,
    PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.ProductID = DETAIL.ProductID
OR PRODUCT.rowguid = DETAIL.rowguid;

In my current Ms Access project, I have been using quite alot OR operator in "WHERE" criteria of SLQ string, such as:

    User_Criteria = " AND (P.Deleted = Q_UserFilter() Or P.Deleted = False) " _
                                & " AND (U.Deleted = Q_UserFilter() Or U.Deleted = False) " _
                                & " AND (V.Deleted = Q_UserFilter() Or V.Deleted = False); "

SQL = " SELECT ....
FROM....
WHERE something" & User_Criteria
Me.Form.Recordsource = SQL

For now, the response time is still OK with about 10K records on table, but I am not sure how it going when data grows up to 100K rows or more...
Do I over concern on this problem? I plan to put data on a network drive, thus I must optimize the response time.

There is a side question:
Q_UserFilter() is a boolean function, it checks if current logging user has admin privilege, SQL will return all record including record is marked with "deleted" flag

if user_privilege = Admin then
    Q_UserFilter() = True
else
    Q_UserFilter() = False
end if

Is Q_UserFilter() executed many times when the code I call Me.Form.Recordsource = SQL?

Hope someone can make me clear on this.

Thank you very much!

 

[theDBguy]

Hi. I don't think using OR by itself in a query criteria would be a cause for any concerns. When your data size is large enough that it causes a slower response time, you might reevaluate the query then. When do you think that will happen?

 

[pbaldy]

Your side question should be easy to test. You can put:

Debug.Print "Function called"

within the function and see how many times you see that in the Immediate window after running the code. You can also increment a global variable inside the function and see what count that ends up with.

 

[pbaldy]

By the way, since you're building that SQL in code you can simply put the function's value into a variable and then use the variable in your SQL. Then you know it's only called once.

 

[Babycat]

Hi. I don't think using OR by itself in a query criteria would be a cause for any concerns. When your data size is large enough that it causes a slower response time, you might reevaluate the query then. When do you think that will happen?

Hi DBguy

Thank for your quick response.
infact, I am not really worry about the response time, but this is also a chance for me to learn more about db world.
I am inspired with the article as they can reduce much read times.

We did scan both tables, but processing the OR took an absurd amount of computing power. 1.2 million reads were made in this effort! Considering that Product contains only 504 rows and SalesOrderDetail contains 121317 rows, we read far more data than the full contents of each of these tables. In addition, the query took about 2 seconds to execute on a relatively speedy SSD-powered desktop
.....
[changed SQL scheme with OR]

The reads have been cut down from 1.2 million to 750

Thank for your quick response

 

[Babycat]

By the way, since you're building that SQL in code you can simply put the function's value into a variable and then use the variable in your SQL. Then you know it's only called once.

Yeah, that is pretty simple. I was on the moon...
Thank you

 

[pbaldy]

Yeah, that is pretty simple. I was on the moon...
Thank you

No problem.

 

[Pat Hartman]

I've never seen an OR in a join. Must be some really bad data. Why is this necessary? Will some rows really join on ProductID and others on the GUID?

 

[theDBguy]

Hi DBguy

Thank for your quick response.
infact, I am not really worry about the response time, but this is also a chance for me to learn more about db world.
I am inspired with the article as they can reduce much read times.

We did scan both tables, but processing the OR took an absurd amount of computing power. 1.2 million reads were made in this effort! Considering that Product contains only 504 rows and SalesOrderDetail contains 121317 rows, we read far more data than the full contents of each of these tables. In addition, the query took about 2 seconds to execute on a relatively speedy SSD-powered desktop
.....
[changed SQL scheme with OR]

The reads have been cut down from 1.2 million to 750

Thank for your quick response

Okay, I wanted to understand your fascination with that article, so I tried to click on the link you posted, but it doesn't seem to be working (nothing is opening in my browser). Can you please repost the link to the same article? Thank you.

 

[plog]

User_Criteria = " AND (P.Deleted = Q_UserFilter() Or P.Deleted = False) " _
& " AND (U.Deleted = Q_UserFilter() Or U.Deleted = False) " _
& " AND (V.Deleted = Q_UserFilter() Or V.Deleted = False); "

Just using logic and algebraic replacement you can reduce that to 1 call.

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)))

I've never seen an OR in a join.

I saw my first one ever just last week--but it wasn't naturally occuring. It was in an interview test and on an elementary LEFT JOIN question. I just couldn't get over the OR in the JOIN and missed it. And the example table was poorly structured.

 

[theDBguy]

I did a quick search for the article. If this is the correct one, I agree with @Pat Hartman, the article seems to be discussing using OR in the table JOINs rather than using it in the WHERE clause. Did I miss it? Otherwise, those are two different subjects.

Query optimization techniques in SQL Server: tips and tricks

In this blog post we will show you step by step some tips and tricks for successful Query optimization techniques in SQL Server.

www.sqlshack.com

 

[Pat Hartman]

I saw my first one ever just last week--but it wasn't naturally occuring.

Great because in my nearly 50 years of using SQL with numerous RDBMS', it has never even occurred to me to use an OR. The fact that you can use an "AND" implies that an "OR" is possible and yet silly me, I just automatically created a union query. As I've said many times here on the forum - Just because you can do something, doesn't mean that you should Intuitively, I must have recognized how bad the performance would be. Excellent article. Thanks to theDBguy for finding it. I couldn't get the link to work either so I moved on.

 

[isladogs]

You can significantly speed up queries by ensuring all search fields are indexed and that the indexes are correctly used in the search criteria.
For more information, see my article Optimise Queries

 

[plog]

Thinking about this more--you don't even need to call Q_UserFilter() from the query at all---use it in VBA:

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

 

[isladogs]

Great because in my nearly 50 years of using SQL with numerous RDBMS', it has never even occurred to me to use an OR. The fact that you can use an "AND" implies that an "OR" is possible and yet silly me, I just automatically created a union query. As I've said many times here on the forum - Just because you can do something, doesn't mean that you should Intuitively, I must have recognized how bad the performance would be. Excellent article. Thanks to theDBguy for finding it. I couldn't get the link to work either so I moved on.

You can also use OR joins in Access queries but only in SQL view.
In fact, performance can sometimes be better than using a UNION query (though usually UNION is faster)

 

[Pat Hartman]

You can also use OR joins in Access queries but only in SQL view.

I assumed that would be how to do it. This is one I'm not going to worry about.

 

[isladogs]

Here are some quick tests using joins based on AND, OR, UNION & UNION ALL
Each query was run 20 times in succession and the total time determined

In this example, both union queries were faster than an AND join. The OR join was slowest

However that speed order isn't always the same

 

[The_Doc_Man]

With all of my distraction due to the aftermath of Hurricane Ida, I missed this thread earlier. Reading it, I suspect there is a structural error in the table's layout. I have to ask a question because I'm not sure.

When JOINing tables that could make their linkage through two different fields in the same table, I see that you are looking at linking through something called ProductID. Can your products have the same ProductID i.e. the ID is not unique? Or is that field the prime key of the table in question? Because if so, that rowguid field is doing something that it should not do - substitute for a prime key - meaning that PK doesn't uniquely specify the record. OK, I could indeed be wrong - but that idea jarred me a bit.

 

[Babycat]

I did a quick search for the article. If this is the correct one, I agree with @Pat Hartman, the article seems to be discussing using OR in the table JOINs rather than using it in the WHERE clause. Did I miss it? Otherwise, those are two different subjects.

Query optimization techniques in SQL Server: tips and tricks

In this blog post we will show you step by step some tips and tricks for successful Query optimization techniques in SQL Server.

www.sqlshack.com

Sorry, I have fixed the link.
Your searched article is correct.

Yes, you are right, the article give example using OR in table JOINs, not OR in WHERE clause.

 

[Babycat]

By the way, since you're building that SQL in code you can simply put the function's value into a variable and then use the variable in your SQL. Then you know it's only called once.

Tested. It called once only