Is it more efficient to read the parent record in a process

BLOMASKY

Assuming I have a process that has a bunch of lines like the following:

Quotes.price = Quotes.ob_Items.avgPrice * Quotes.qty
...
...
...

is it more efficient to have a line at the top that is:
Find Items where Items = Quotes.ob_Items
then have all my logic just:
Items.avgPrice vs. Quotes.ob_Items.avgPrice

thanks
Bruce

aware_support

You cannot use Quote.Items.Price if Items is a multiple reference (which I assume it is). Aware IM will not set the price of all items if you use it. So the ONLY correct syntax is:

FIND Item WHERE Item IN Quote.Items
Item.Price = ...

If Items were a single reference, then using Quote.Item.Price would work and it would be more efficient than using FIND.

BLOMASKY

Sorry, I asked the question poorly. Assume that the Quotes BO is passed to the process, what I was asking is it better to write:
FIND Items WHERE Items = Quotes.ob_Items
Items.attr1 = 1
Items.attr2 = 2
Items.attr3 = 3
.....

Skip the Find and instead just write:
Quotes.ob_Items.attr1 = 1
Quotes.ob_Items.attr2 = 2
Quotes.ob_Items.attr3 = 3
.....

customaware

I still don't "get it"

Are Items part of a Quote?

How can the ob_ prefix be multiple.

Please show all the attributes of Quotes and Items

Jaymer

Bruce, I understood exactly what you meant when you posted this.

Since we don't know exactly whats going on behind the scenes, we're trying to figure out which is more efficient.

In Bruce's last post, he wrote this:
FIND Items WHERE Items = Quotes.ob_Items
Items.attr1 = 1
Items.attr2 = 2
Items.attr3 = 3
.....
or
Skip the Find and instead just write:
Quotes.ob_Items.attr1 = 1
Quotes.ob_Items.attr2 = 2
Quotes.ob_Items.attr3 = 3

So, "behind the scenes", is it possible that for each of these:
Quotes.ob_Items.attr1 = 1
Quotes.ob_Items.attr2 = 2
Quotes.ob_Items.attr3 = 3
AIM is causing 3 reads of the database? ... or at least 'x' amount of overhead.

And instead, if Bruce reads that Items record ONCE (FIND Items WHERE Items = Quotes.ob_Items), then he has 3 simple assignment statements, which would seem straightforward (ie. cheap from a database perspective).

So the question is whats the cheapest "cost" doing these 2 methods?

JonP

My bet is that skipping the FIND action (SELECT query) is more efficient since the changes would translate into one or more UPDATE queries and an UPDATE query is essentially a SELECT query with an extra action built-in. So that FIND action is essentially redundant.

My question is this: Is AIM clever-enough to combine multiple updates to the same record into one query? If not, would it be more efficient to use the SET action with a text attribute from a non-persisting BO to achieve that purpose?

BLOMASKY

What I think I was really asking, is when aware is doing the update, they could be doing a join so the SQL could look like:

Update Items set Attr1 = 1
from Items I Inner join Quotes Q on I.ID = Q.ob_Items_RID
where Quotes.ID = ???

vs. just the Update Items set attr1 = 1 where Items.ID = ???

bruce

Jaymer

exactly!
and... i think i had mentioned this before, since you're on MS SQL, you can see all this happening in the SQL Profiler.
It'd be easy to try it both ways to see the exact statements going to the server.

aware_support

First of all, this is not valid: Quote.Items.attr = value if Items is a multiple reference.
Second of all, all update operations happen in memory and do not involve SQL at all.

So, again, if Items were a single reference (and so the Quote.Item.attr=value operation is valid) it is much more efficient to just use Quote.Item.attr = value than adding an extra FIND.

However, if Items is a multiple reference and you want to change ALL items, then FIND is inevitable and the question about efficiency is moot.

JonP

First of all, this is not valid: Quote.Items.attr = value if Items is a multiple reference.

It's been established through a follow-up post that items is a single reference. The author confirmed that the original question was poorly-asked and I'm of the opinion that the plural naming convention used creates confusion and is not best-practice.

Second of all, all update operations happen in memory and do not involve SQL at all.

Are you bypassing SQL to write to a database? I would think at some point one or more UPDATE queries would be generated. Am I wrong about that?

Nevertheless the heart of my question is the same: When making multiple updates to the same record, is it more efficient to use a SET action or does AIM combine those multiple actions into one when writing to the database (via SQL or not)? This would seem to be the natural approach since "SET" is a reserved word in SQL to indicate what data gets written where in an UPDATE query, so I figured it was a wink to us.

aware_support

When you update an attribute:

Object.Attribute = value

there is NO SQL involved - everything happens in memory (in the cache). When the transaction is committed, then, of course, the entire record is written into the database using the UPDATE SQL statement. However, as a developer, you cannot control how the transaction is committed and it does not matter how many update statements there were before the transaction is committed.

Note, though, that with each update statement RULES attached to the object are executed and if your object has many rules, using the SET action can be more efficient than executing several update statements because in this case rules will be executed only once.

JonP

That's very helpful to know. The impact on rules never occured to me. Thanks for the tip!