Best practices in optimizing rules and processes

[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]

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?

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.

[BenHayat]

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

@Support;
I had the same question. How about allowing us to create composite indexes in Aware, which Aware will maintain them, every time we make changes to the structure of DB. All DB maintenance should be under the umbrella of Aware, including foreign indexes for relationships.

[ACDC]

Talking about indexes, if you have a very old application that has gone through periodic updates over the years , you may find that some indexes are missing on your objects. At some time in the past (can't remember which update ) AwareIM started creating an index automatically for each SINGLE reference on each object.

I later discovered in our app that during the upgrade AwareIM failed to carry through with the creation of these indexes, so indexes were missing on many of our objects.

I believe the creation of these indexes resulted in a noticeable difference in speed of our app . I had to manually edit the DB and create each of the indexes that were missing.

So for each single reference on the object it seems you should have an Index for the relevant attributes AwareIM uses in the DB - example using a CreatedBy single reference attribute on the object :
index:
CreatedBY_RID

I am not sure if these are relevant anymore (at one time AwareIM was creating them but it appears in the CRM sample only the _RID is being created - maybe support can confirm )
CreatedBy_REN
CreatedBy_REN

If you do not have these it may be wise to create them

[Jaymer]

PERHAPS,
in the short term, an optimizer or "sniffer" could be implemented.
Its certainly easier for Vlad to analyze those hundreds of rules & processes and at least SUGGEST indexes where Aware "sees" heavy usage.
Having said that, this new tool will definitely be a work in progress.
For now, just sniff.
Later, assist in maintenance of indexes, and
even later, with intelligence built in for the selected back-end database.

On the one hand, Vlad's position is like "Look, y'all have gotten this far without it, so why invest a bunch of time on a knee-jerk reaction of the forum", combined with there's so much other [maybe] MORE IMPORTANT stuff to implement, saying YES to the forum on this opens up a HUGE can of worms... while it doesn't seem to me that a "version 0.1 sniffer" integrated into the toolkit would help a lot with a small investment.

Jaymer
20 yr Magician
consultant...
but not really of anything specific BUT I feel left out If I don't start adding a big signature line down here....
[HUGE logo
coming soon] <<<--- AdBlock in your browser helps filter these out !!!

[tford]

in the short term, an optimizer or "sniffer" could be implemented.

I had a similar thought .. something like the Integrity Checker that lists attributes with no index that are used in WHERE and SORT BY of queries. If this were shown in query format with BO name, attribute name, query name .. you could then sort and select to easily see opportunities for optimization that you might otherwise overlook.

In the even shorter term, adding Index? to the list of items on the BO Attribute summary screen would allow a quick scan of BOs.

index.jpg (73.39 KiB) Viewed 14739 times

[BLOMASKY]

Ben, the problem with that, is that each DB has some unique features / syntax.

Instead of creating indexes on the fly, using Query, You can write a script that will create all of your indexes. Then, when you go live, its easy-peasy to run that script. (and, if you have the rights to create / change the tables, then you should have the ability to drop and create indexes.

Just my 0.02 cents worth
Bruce

[kklosson]

This post prompted me to thinking... I have an object attribute attached to an object and it's very slow to display the list on the parent object form in the production environment where there is a lot of data. So I thought, maybe the index is screwed somehow. So for the first time, I looked at the indexes and I see how AwareIM indexes the Object_RID field - naturally. So on my dev system database, I see the two indexes representing the two owner objects. On the production system - no indexes where there should be two. Cool! This is the fix. I can add the indexes easily enough. Any suggestions on the index name? Does it matter?

[BenHayat]

This post prompted me to thinking... I have an object attribute attached to an object and it's very slow to display the list on the parent object form in the production environment where there is a lot of data. So I thought, maybe the index is screwed somehow. So for the first time, I looked at the indexes and I see how AwareIM indexes the Object_RID field - naturally. So on my dev system database, I see the two indexes representing the two owner objects. On the production system - no indexes where there should be two. Cool! This is the fix. I can add the indexes easily enough. Any suggestions on the index name? Does it matter?

Your post also prompted me to ask, Shouldn't Aware check/fix any indexes when a system is deployed to production? If Aware is in charge of DB management, this task should also be part if it.

[aware_support]

Code: Select all

Instead of creating indexes on the fly, using Query, You can write a script that will create all of your indexes

By the way, there is a place in your BSV where you can put all your database-specific scripts - stored procedures, scripts to create indexes etc. This place is in the Properties of a business space version - click on the Advanced button. I don't think many people know about it.

The advantage of having your scripts there are:
1) The more artefacts of your applications you have in one place the better
2) More importantly, Aware IM will AUTOMATICALLY EXECUTE the scripts once your application is deployed for the first time - can be really important for those distributing their apps to their customers

[BenHayat]

By the way, there is a place in your BSV where you can put all your database-specific scripts - stored procedures, scripts to create indexes etc. This place is in the Properties of a business space version - click on the Advanced button. I don't think many people know about it.

The advantage of having your scripts there are:
1) The more artefacts of your applications you have in one place the better
2) More importantly, Aware IM will AUTOMATICALLY EXECUTE the scripts once your application is deployed for the first time - can be really important for those distributing their apps to their customers

Wow, this "Sounds" great. I doubt that many people knew the functionality of this section. We need to explore this more. Are there any docs and samples?

[kklosson]

I'm just a bit timid in the index naming. AIM generates a unique number. What's a good practice if I create the name myself, and I presume the database will advise if the name is not unique? BTW, I am using Navicat to manage the database, which is MySQL.