Order of precedence on constraints?

robbie.macintosh · 2024-07-19T15:29:17.944Z

Me again and I’ve been thinking this for a while and wondered if anyone knows the answer?

is there a benefit in writing the constraints when doing a search for to reduce the workload and effort?

ie if I’m looking for a group that is within say a customer or customer and segment would it be more beneficial to constrain by customer, then segment then group?

in my example, the group is unique to the customer and the segment, but the groups are in the 1000’s were as limiting by customer may cut that doesn’t significantly to say 20 in some instances?

this is more of a generic question than a specific problem to resolve I’m just looking at optimising things like workload and making things more performant?

thanks

boston85719 · 2024-07-20T14:40:11.857Z

robbie.macintosh:

if I’m looking for a group that is within say a customer or customer and segment would it be more beneficial to constrain by customer, then segment then group?

Not really much of any discernible difference until you begin to know how many records are there and how many will be returned…if you will have the same number of records returned regardless of your constraints used, it makes no difference which constraints are used as the results are the same.

robbie.macintosh · 2024-07-20T14:59:51.088Z

Hi @boston85719

in my examples the number of returned records will always be 1.

lets say I’ve got 7512 records to start with, if I first split by say customer it may go down to 12 records to then use the 2nd constraint to find the exact record

one customer may have 1000’s of records and another has 1 or 2.

so by way of example

Customer 1 has 3000 groups
Customer 2 has 12 groups
Customer 3 has 4500 groups

my search is to get to a single group, so instead of doing a search for the group and searching for the group in 7512 records by a single constraint.

If however I constrain by say customer 2 first I then only search 12 records to find the group I’m wanting.

I don’t see anything in the documentation on this, and… I’m no database expert either.

thanks

Robbie

rico.trevisan · 2024-07-20T16:55:48.912Z

I always thought that 1 search is better than 2. I wonder if Mr. Documentation / Optimization himself – @petter – might have some better ideas.

boston85719 · 2024-07-20T17:42:57.625Z

rico.trevisan:

I always thought that 1 search is better than 2

I think for the OP there is some confusion on performing more than one search versus having more than one constraint on a single search. In these hypotheticals, and in most any instances of data retrieval, there will be no need for more than one search, but may need more than one constraint.

@robbie.macintosh the difference in the amount of time the server required to parse through a list of 100,000 records or 1 million records is minimal and doesn’t really affect too much the total time for the data to be fetched and returned. The amount of data to be returned can impact the speed at which the data is returned from the server and then populated in the clients device (ie: being shown on screen).

So, what are you actually searching, is that a Msg Template, a group, a customer or a segment?

What is the structure of your database and how are fields related to other data types?

robbie.macintosh:

ie if I’m looking for a group that is within say a customer or customer and segment would it be more beneficial to constrain by customer, then segment then group?

I believe you may be on a path of expecting a need to put multiple filters, using the :filtered operator, which is not necessary.

If you are searching for a group, and the group data type has a field that is related to customer (can be list of customers or single customer - doesn’t make a difference) you can have a constraint that the group’s customer field either contains or is equal to the customer you are using as a constraint value. Also, you add another constraint that will constrain the search by segment, so long as the Group data type has a field that is related to a segment.

Lastly, since in the example quoted, you are asking if looking for a group, you do not need to constrain by group because you are searching for groups, however, if you already know which group it is, there is no reason to search with constraints for customer or segment and can just show the group data if it were already known.

robbie.macintosh:

in my example, the group is unique to the customer and the segment

Then search for group, with constraints for the customer and the segment…but if you have multiple groups that will belong to the same customer and segment, you will have more than one value returned.

robbie.macintosh · 2024-07-20T18:12:34.377Z

let me tidy up my poor use of language…

it’s one search with multiple constraints…

9000 groups over 3 customers (so to speak),

customer 1 4500 groups
customer 2 3000 groups
customer 3 12 groups…

lets say I ultimately want to retrieve a group312 that is within customer 3’s records…

so if I do a search for the group312 - I’m scanning 1,000,000 records?

if I so a search for customer 3 and group312, is that more efficient…

I think my Saturday evening is going to be doing some experimenting with

one single constraint
2 constraints in any order
2 constraints in an order that would reduce the number of records (if you think like excel filtering).

I’ll post the results.

system · 2024-09-27T15:29:48.153Z

This topic was automatically closed after 70 days. New replies are no longer allowed.