SQL Indexes can have HUGE impacts on performance

BLOMASKY

The reason for this post, is that I have a Stored Procedure (SP) that is very complex, for display a dashboard for each salesperson on his customers. Each customer has a lot of stuff computed from different tables (total A/R), Total unshipped invoices, last 12 months sales vs. prev. 12 months.
Yes, this could be run each evening, as a large batch, but it has to be up to the minute accurate. My Stored procedure took about 40 seconds to run. (some of the tables where over 500K rows). I changed one of the indexes in the table and it went from 40 seconds to < 2 seconds!

Now the details. Aware allows you to create an index on a BO attribute but you have limited options.
In addtion, each BO can have 1 CLUSTERED index. (A clustered index stores the physical records in the same order as the index. You only get one of these, and Aware creates it on the ID field (which, many times is ok)

However, my Invoice file which has 300K rows, has an attribute for Customer. I created a clustered index on Customer / ID so the physical file is in customer and oldest to newest invoice order. (ah, I didn't mention, that creating your indexes in SQL allows you to optionally have more than one attribute in an index)

Just doing this, made my SP run 20 times (yes 20!) faster.

Just saying, sometimes little tweaks to the db can have some huge performance increase

Bruce

ACDC

It does sound very interesting, although I am still a bit confused. The customer attribute on your invoice object I take it is a single reference and would be automatically indexed by Aware anyway . So using the SP method you were able to combine two attributes as an additional index - could you elaborate with some more details please, as 20x is substantial - I would love to boost some of our queries 20x - Also are you working with Mysql

BLOMASKY

You will probably not find any AWARE query will speed up by any measurable amount, since:

Aware queries are tied to only 1 BO (which is 1 SQL table) so you will not find a huge speed increase although you can save some sorting time, etc. If you DO have any aware queries that take more than 1-2 seconds to run, then there is some issue with the network / sql configuration / disks, etc.

In my example there were multiple tables joined together and one of them was a group by (Select custno, sum(invoiceamount) as totalOwed group by custno)

This was just one of the tables being used in the query, and with just the single field the query optimizer picked a poor plan which required it to pass over the table multiple times. By having the actual data clustered (ordered by) the customer in invoice # order, it was smart enough to just merge this table into the other tables.

Also, you say "The customer attribute on your invoice object I take it is a single reference and would be automatically indexed by Aware ". This is incorrect. Aware will create an index (ok, constraint, so almost the same thing) on the ID field and will create an index on any field where you select index. Many back end databases will create indexes on columns that are used often in queries. (that is why you can run a query a few times and it is slow, then like magic, it speeds up. I am attaching a screen shot showing the indexes on my Invoices table. Notice that 3 of them have funny numeric names starting with "I". These are the system created ones, The last one, "idx_invoices_shipto" is the one I created with 2 columns.

did I confuse you? and I am working with MSSQL, however the principals are similar between MSSQL and MYSQL (and with MSSQL now free and now running on Linux), I am not sure why most peeps have not converted over to it.)

Bruce

ACDC

Aware will create an index (ok, constraint, so almost the same thing) on the ID field and will create an index on any field where you select index.

Aware does create an additional index, for example on the customer reference in the Invoice object, aware creates three columns in the DB , one of them is indexed _rid ....remote id
eg: 1.customer_ren
2.customer_rid - this is indexed
3.customer_rma

Anyway thanks for the additional info, yes it was confusing

(and with MSSQL now free and now running on Linux), I am not sure why most peeps have not converted over to it.)

BTW, the last time I looked MSSQL is free as long as the DB stays under 10GB , our DB is currently 30GB so Mysql. will have to do. Imagine the surprise when an apps database gets to 10GB and then you have to start paying or downgrade- I would love to be working with MSSQL but its just to costly

johntalbott

BLOMASKY wrote
You will probably not find any AWARE query will speed up by any measurable amount, since:

Aware queries are tied to only 1 BO (which is 1 SQL table) so you will not find a huge speed increase...

Is this statement is accurate?

My assumption has been an Aware query must include JOINS if it:

Returns one or more "shortcut" fields
Contains a WHERE clause that traverses multiple BOs e.g. Where BO1.BO2.Attribute = Something

BLOMASKY

Boy, wrong in most things.

Yes, you are right (as usual ACDC) Aware does create indexes on the _RID column on all relationships. I was getting confused with the indexes the query analyzer creates.. ) (humm, that does bring up an interesting question re: performance, assuming you do NOT create the relationship in the parent, and its not an owned by relationship where there would be cascading deletes, I wonder what the purpose of these are? Of course, if you are using them in a WHERE clause then it will help, otherwise, in a OLTP (online transaction processing) system it will just slow down DB insert and update transactions. Perhaps not that much, but... but.....

And John, yes the SQL generated will perform as many joins as required, but they are always against the primary, (unique) key in a table, so there is not much you can do to affect that performance. If you are writing a SP that is using aggregates or sub queries or ?? then the choice of indexes is more "interesting"

Bruce