Query with OR massively slower than two queries?

UI Builder
1

I’m trying to make my code more efficient, especially considering the 100 concurrent request limit. I thought a single query with an OR would require fewer resources and not be any slower. however, the OR query takes 10-15 times longer than two queries.

Other than testing every single query in multiple variations for performance, is there a best practice? It seems like going to the DB a bunch of times is faster, which is counter-intuitive to everything I’ve ever known.

Run 1
OR Query - 2706 ms
Two Queries - 163 ms

Run 2
OR Query - 2343 ms
Two Queries - 270 ms

Run 3
OR Query - 2246 ms
Two Queries - 162 ms

OR Query
Table: Orders
“Where”: “Fundraisers[Orders].objectId = ‘B53D5852-CDC4-4C23-AA1F-816A5F19165C’ OR SellersFundraisers[Orders].Fundraisers.objectId = ‘B53D5852-CDC4-4C23-AA1F-816A5F19165C’”
Properties: SUM(SubTotal) AS sales

Test code -

image
image1288×896 69.9 KB

Two Queries
Table: Orders
“Where1”: “Fundraisers[Orders].objectId = ‘B53D5852-CDC4-4C23-AA1F-816A5F19165C’”,
“Where2”: “SellersFundraisers[Orders].Fundraisers.objectId = ‘B53D5852-CDC4-4C23-AA1F-816A5F19165C’”,
Properties (for both queries): SUM(SubTotal) AS sales

Test code -

image
image918×860 61.4 KB

2

Testing every single query is the best practice.

The more records the database has to go through to identify the data set to return, the slower the query will be. The OR query is a great example of that.

Regards,
Mark

3

I hear you on testing but having some best practices is a nice starting point. I’m guessing I’m not alone in heavily relying on StackOverflow for references. Not having that with BEL often offset the speed of snapping together (is that what you call it?) the code.

Your videos are a great entry point to the concepts, but once those are understood, practical implementation examples are very helpful. My unsolicited $0.02.

Tim