EXISTS or FIND, which is more efficient

BenHayat

I can use EXISTS command to look for the existence of an object or I can do a FIND & SEARCH_COUNT combo.

My question is, which one is more efficient when it comes to hitting the DB.

Or is there special rules or conditions that I should use either one?
Thanks!

kklosson

My understanding is that you should minimize aggregate functions since they have a greater overhead. I go with EXISTS where possible.

BenHayat

kklosson wrote
My understanding is that you should minimize aggregate functions since they have a greater overhead. I go with EXISTS where possible.

Good point. I had forgotten about the Aggregate function on SEARCH_COUNT.
Thank you!!!

aware_support

If your rule looks something like this:

IF EXISTS (Condition) THEN
FIND SameCondition
DO Something

It is much more efficient to do this:

FIND Condition
IF SEARCH_COUNT > 0 THEN
DO Something

Otherwise, use EXISTS

BenHayat

aware_support wrote
If your rule looks something like this:

IF EXISTS (Condition) THEN
FIND SameCondition
DO Something

It is much more efficient to do this:

FIND Condition
IF SEARCH_COUNT > 0 THEN
DO Something

Otherwise, use EXISTS

Thank you Support greatly for your input.

BenHayat

I'm so glad I asked and got the answer. Just did a search in IDE for SEARCH_COUNT and found a LOT that I can convert to EXISTS.

BLOMASKY

I am just "guessing" here, but I would think it depends on how selective your find statement is. And, of course, if your BO has only a few hundred records, then it won't really make a difference, but lets go to extremes, assume you have a large bo, with a field called SEX (err, do I have to say GENDER now) and it has only two choices, M or F if you do FIND BO where sex = 'M' you will get 1/2 of the table returned
if you do Exists BO where sex = 'M' then the sql engine should be able to short circut the query and once the 1st M is found, it will return true

Bruce

BenHayat

BLOMASKY wrote
if you do Exists BO where sex = 'M' then the sql engine should be able to short circut the query and once the 1st M is found, it will return true

Bruce

That's what my objective is to stop looking any further as soon as the first record is found, and I hope that's what EXISTS does.

RentProperty

I Was also wondering about the efficiency of these ops. Especially when you have large databases. I always used
FIND & IF SEARCH_COUNT>0
BUT... Ran into problems when the Number of rows found is > 1000, since aware IM only processes thousand rows at a time. In the logs it showed 0 or 1 even though there were many rows...

So after that we changed and started using EXISTS & then FIND... However, I Always wonder if the resources get double taxed because the same query is basically ran twice.

We Also started using LIST_SIZE... Which I think work quite well for smaller lists... But not sure if this is the Sage as Aggregate Functions?
So Typically I would do:

IF LIST_SIZE(Property.Owners)>0 THEN
FIND Owner WHERE Owner IN Property.Owner
MyProcess

I Know this MO can't always be applied, but works very well when we Write our API's and need to return Nested objects in our JSON responses. For example where Property has many Owners... And Owners has many Contacts... Or something like that. 🙂