Best practices in optimizing rules and processes

tford

There have been a number of posts recently that refer to optimizing rules and processes. I'd love to hear best practice recommendations from others. My BSVs work, but I'm pretty sure they are not optimized. If anyone can shed light on general principals or examples, that would be really helpful.

weblike

Hi Tom,
I have not seen your post(this) and I have posted something like this a few seconds ago in "Poll: Concurrent users?" ..
So, I'm very interested in this too...

If we could know which processes, rules, quries are slow (running time)...would be great.

thx,

BenHayat

See, these are the type of videos we need from the source of the product, that has been requested. In order to accurately get your answer, the product's manufacture should include these information in some form of media. Otherwise it will turn into "The Elephant and the blind Men" case (). Read below...

Once an elephant came to a small town. People had read and heard of elephants but no one in the town had ever seen one. Thus, a huge crowd gathered around the elephant, and it was an occasion for great fun, especially for the children. Five blind men also lived in that town, and consequently, they also heard about the elephant. They had never seen an elephant before, and were eager to find out about elephant.

Then, someone suggested that they could go and feel the elephant with their hands. They could then get an idea of what an elephant looked like. The five blind men went to the center of the town where all the people made room for them to touch the elephant.

Later on, they sat down and began to discuss their experiences. One blind man, who had touched the trunk of the elephant, said that the elephant must be like a thick tree branch. Another who touched the tail said the elephant probably looked like a snake or rope. The third man, who touched the leg, said the shape of the elephant must be like a pillar. The fourth man, who touched the ear, said that the elephant must be like a huge fan; while the fifth, who touched the side, said it must be like a wall.

They sat for hours and argued, each one was sure that his view was correct. Obviously, they were all correct from their own point of view, but no one was quite willing to listen to the others. Finally, they decided to go to the wise man of the village and ask him who was correct. The wise man said, “Each one of you is correct; and each one of you is wrong. Because each one of you had only touched a part of the elephant’s body. Thus you only have a partial view of the animal. If you put your partial views together, you will get an idea of what an elephant looks like.”

The moral of the story is that each one of us sees things exclusively within one’s point of view. We should also try to understand other people’s points of view. This will enable us to get a proper perspective on different situations and events.

BLOMASKY

I have to say, this is the STRANGEST group of programmers I have ever met.

Some have Cows on Ice

Some are feeling up Elephants...

However, the good news, is that I don't feel like I am the strangest one here! and that is a unique feeling...

p.s. As far as optimizing rules, can I suggest that VIEW LOGGING is your best friend. I look at that, and notice that a rule, or process is called more often than I planned. Thats a great way to see whether you should change conditions on your rules to be more selective.

However, the rules engine is either fast or blindingly fast. When I see that it has evaluated 50-100 conditions after I click save, and see that the save is sub second response, I figure there is not a LOT of tuning that I need.

(of course, indexes on your SQL columns, can be a HUGE time saver.. but thats another topic)

Bruce

aware_support

There is a thread about this in a Tips and Tricks forum. Maybe one day, we'll make a video tutorial about this. Here is a summary from that thread again:

Optimisation of rules is about careful study of the LogViewer:

1) Check the Log Viewer for the execution of rules that refer to the object (cross reference rules). These may be expensive. Execution of many of them may be unnecessary. Turn them off by going to the Advanced tab of the rule and ticking "Do not execute referred" (or something like that - the last checkbox there)

2) Aggregate operations (SUM, COUNT etc) can be expensive if you have lots of data. Try not to use them if you can

3) Unconditional rules can be expensive (especially if they use aggregate operations). Make them conditional (using WAS CHANGED expression, for example)

4) Check for repetitive execution of rules. The rule engine can execute rules repetitively if the next rule triggers a condition that causes the previous rule to fire again. Optimise this carefully. Use rule priorities to make sure that expensive rules are only executed once

tford

Found the 2010 thread that support referred ... titled "general rule for best performance"

https://www.awareim.com/forum/d/4125>

tford

Also referencing this post by support from recent post about concurrent users:
https://www.awareim.com/forum/d/8622>

This has been discussed many times and it is terribly frustrating sometimes to repeat the same things over and over again. Still:

Aware IM itself imposes NO RESTRICTIONS on the number of concurrent users

However, it does not mean that your specific app will support millions of concurrent users without you having to do anything about it. The ability to support concurrent users DEPENDS ON HOW WELL YOUR APPLICATION IS STRUCTURED. Asking how many concurrent users Aware IM supports is like asking how many users C++ supports. It can support millions or it can hardly support one - depending on how you write the code.

You need to optimise every operation in your application if you are serious about supporting many concurrent users:
a) Make sure that rules are optimised - there is a thread about it on this form. Inspect a rule log and see what's going on there
b) Make sure there are no CPU extensive operations in your app. If you have to have them be smart about them.
In kklosson's app, for example, there is an operation that generates a 100 pages report! This report probably has to go through the entire database many times and is guaranteed to bring the CPU to its knees (no matter how powerful and how much memory there is). How many concurrent users do you think will be able to use his system when this report is running? The answer is zero. The right thing to do is to schedule such reports to run overnight or get a separate server to handle them on the background. This requires smart solutions on the developer's part.
c) Identify performance bottle-necks in your app and optimise them. Sometimes you may need to change rules or even replace a query with a stored procedure. This is very important for the number of concurrent users.
d) And of course add other servers if need be to scale it up. Scaling/load balancing IS supported in Aware IM.

Concurrent users will only be supported properly if the CPU does not have to work hard on any operation that the user performs there!!

Bottom line: Aware IM is not a silver bullet as far as concurrent users are concerned. Developers need to invest heavily in any serious application that intends to support many concurrent users. This involves careful planning of application behaviour, optimisation of rules, optimisation of queries and so on.

Jaymer

in that older thread support suggested:
"2) Indexing of attributes may help "

does that mean adding an index externally to Aware (ie. directly in the DB)?

kklosson

My guess is that it means you should index the attribute (a tick box in the attribute configurator) for attributes that you will perform a direct query on. For example, let's say you have an attribute called EyeColor on a person object. Mostly, it's just a descriptive attribute. But let's say you have a query or report that aggregates on that attribute, like FIND People where People.Eyecolor='Brown'. If you are going to do that, then indexing the attribute becomes a good idea. That's what I think.

aware_support

It can be both ticking "Indexed" checkbox and/or creating indexes yourself directly in the database. Sometimes ticking the "indexed" checkbox is not enough - it really depends on a specific query that takes long time to execute. So in this case you can either write a stored procedure or create indexes in the database directly or both.

tford

Can someone help me understand why there would be a difference between an index created in a database vs. an index created in AwareIM & under what circumstances would you want/need to create an index directly in the database?

baskosi

Index created in Aware IM is for one attribute only. When you create an index in database, you can create a composite index (more than one attribute).

The simplest way to create optimized composite indexes is to look at the WHERE condition of your queries and create an index for all the attributes in the WHERE clause. The sequence of the attributes in the index is also important. Start with attributes that will have exact conditions and will filter records to the smallest set.

BenHayat

baskosi wrote
Index created in Aware IM is for one attribute only. When you create an index in database, you can create a composite index (more than one attribute).

The simplest way to create optimized composite indexes is to look at the WHERE condition of your queries and create an index for all the attributes in the WHERE clause. The sequence of the attributes in the index is also important. Start with attributes that will have exact conditions and will filter records to the smallest set.

Great answer!!!
I requested for creating "Composite Index" in Aware back in 2010 and still haven't made it yet into the system.

BLOMASKY

Indexes in Database give you a couple of additional options, beside the fact you can have more than one attribute.

1) you can create covering indexes. Those are indexes which have ALL of the fields that your query needs. (of course, then you would have to write the SQL query in Aware, cause aware does a select * which normally retrieves all fields, but, for instance, if you wanted to select the State and City for all customers and had a covering index made up of both fields, then the DB will just read the index and not have to spend time reading the actual data fields.

2) You can have one index in SQL where the data is arranged in the order of the index. (This will slow down inserts, but usually you are not inserting bunches of records at once. The plus is, when you ORDER the query by this index, there is no sorting required.

3) You will also see that SQL will create a much of indexes with "funny" names after you have queried the data a lot. (that is, if there are more than a few 100 records). Sometimes there are multiple, overlapping indexes and you can create an index (or 2) that might replace multiple system created indexes.

Bruce

BenHayat

BLOMASKY wrote
3) You will also see that SQL will create a much of indexes with "funny" names after you have queried the data a lot. (that is, if there are more than a few 100 records). Sometimes there are multiple, overlapping indexes and you can create an index (or 2) that might replace multiple system created indexes.

Bruce

The big problem is, every time you make a change to that object and run Aware, all your indexes are gone, and you have to go in production server and start creating these indexes again for each object you change.
But you may send an update to a client that you don't have access to their DB server and now all indexes are gone or Aware is on a different server and DB Admin doesn't allow you to touch DB.

The best solution is for Aware to allow us to identify all the elements/attributes (and their orders) to create a composite index right in the configurator. This will resolve this manual Index administration.

@SUPPORT;
For the next update, could you please consider giving us the option to create composite indexes in Aware?

baskosi

Why wouldn't Aware IM check existing queries and create composite indexes based on them?

I know that even developers with computer education don't understand how to create indexes. Since Aware IM is used by many non-developers it would be helpful for them if the system created needed indexes for them.

BenHayat

baskosi wrote
Why wouldn't Aware IM check existing queries and create composite indexes based on them?

I know that even developers with computer education don't understand how to create indexes. Since Aware IM is used by many non-developers it would be helpful for them if the system created needed indexes for them.

I was just explaining to someone the importance of the sequence of element in the composite index to truly use a composite index with proper WHERE clause, as you stated:

The sequence of the attributes in the index is also important. Start with attributes that will have exact conditions and will filter records to the smallest set.

I have to agree with your request/suggetion, because many people/developers don't know how to create the "correct/optimized" composite index with correct elements and sequence. If Aware could do so, based on the queries given, it would make more sense. But I still would like to create my own.
So, Aware can give us and Automatic or Manual options to create composite indexes.

pureist

when Bruce wrote, "you can create covering indexes", was he referring to the composite concept mentioned? It sounded like it.
It seems like composite index functionality would inherently be part of the forthcoming functionality of Aware being compatible with tables of other DBMS and other DBMS being compatible with Aware tables.

aware_support

Creating indexes automatically is not in our plans, sorry.

It is difficult to do it automatically. Besides, indexes can be expensive, so creating an index in one place can impact performance of the system in some other place, since the database has to maintain these indexes. It should really be the developer's decision based on the performance of specific queries and the structure of the specific application.

Maybe one of these days we'll do it, but not in the near future.

pureist

Ben wrote: "Automatic or Manual". Below relates to Automatic. what about Manual, and specifically Manually defined composite indexes?

aware_support wrote
Creating indexes automatically is not in our plans, sorry.
It is difficult to do it automatically. Besides, indexes can be expensive, so creating an index in one place can impact performance of the system in some other place, since the database has to maintain these indexes. It should really be the developer's decision based on the performance of specific queries and the structure of the specific application.
Maybe one of these days we'll do it, but not in the near future.