SQL indexes - How does the DB decide which one to use?



Legendary Poster
Hello there!

I've been given the job of seeing why certain reports take an eon to run.

Looking in the log there are a load of SQLs over F0901 and F0902.

How can I tell by looking at an SQL statement which indexes should be on a table or not?

EG: SELECT * FROM PRODDTA.F0902 WHERE ( GBAID = '04847745' AND GBSBL = '00999954' AND GBLT = 'AU' AND GBFY > 5.000000 ) ORDER BY GBFY ASC

Will this use an index on where statement or the Order by part?

Also am I right in thinking the order in which we write our Set Data Selections in a UBE has nothing to do with the ORDER in which the SQL will be generated?

i.e If I have a UBE and I say Set Data Selection on table x = column A then Column C then Column B
my SQL will be
select from table X where A = C = B =

But my index on table X is ABC......should I put my ER code as A B C?

I know JDE table I/O asks you for the index but I want to know where the index will come from on a UBE when all you've specified is the BSVW

Hope this make sense lol

I'm just asking for help on looking at SQLs and seeing which index should be there. Which is more important, the Order by or the actual where = columns

And I'd like to do this outside the SQL Enterprise Manager SQL Analyse tool just by looking at an SQL

thanks guys

The index selected when creating the BSVW or when defining the I/O will always be used.
As far as I know, JDE can tell the DBMS which index to use, but there would be situations where the DBMS wouldn't be able to use that index. For example, if the index isn't there or if you don't set selection criteria for the first column in the index.

The only way to really tell which indices it is using is to look at the SQL plan, which the SQL programming tool of your choice (Microsoft's is decent) can give you.

In a simple query, you might be able to look at the construction of the SQL and find out which index it is using, but a quality DBMS can and will change it's execution plan for a query based on statistics that it knows about the tables and indices that it has available (that is, how unique an index is, how many rows are in each table, etc.).

Usually JDEdwards queries are just not that complicated though. Most are only one table and rarely more than 3. So if you're having a problem, you can usually sniff it out pretty quickly by looking at the execution plan. If you see a "full scan" of a table, then you know you're on the scent.

I'll stop while I have some hope of being helpful.

That's quite a loaded question. One that you should also try posting on the Apps forum as there are some DBA's that troll that site also. I'll offer my two cents anyway.

I was always under the impression that the WHERE clause is what drives the index that will be used. It is data selection that a DB has the most trouble with. Sequencing is 'easy' once the the data is selected. Now, I learned this from listening to a lecture given by the great John Sears (AS/400 DB Guru!). It was some 12 years ago, however, so there is some chance DB design has changed. And who knows if DB400 works the same internally as the other DB's.

The index you choose in the JDE Table I/O may or may not be the one the DB ultimately chooses when it executes the SQL.

Ever since I heard that Sears lecture, I always try to order my data selection to match an existing index (or as close as I can). What you can try is issue a SQL statement that tests your ABC vs ACB from a query tool (query analyzer) and look at the execution path to see how the engine resolves both examples. I'd like to hear back from you about the results.

Good luck.
As far as I know, JDE can tell the DBMS which index to use

[/ QUOTE ]

FYI, JDE *could* tell the DBMS which index to use, but as far as I know JDE doesn't give any index hints to the underlying DBMS.

When we have poorly performing queries in the past, we have done as others have suggested and looked the the execution plan. Sometimes we have gone in and created DBMS only indexes (created in the DB but not defined in JDE). Later on, simply for the sake of consistency, if those indexes work, we add it to the JDE table definition as well.
The magic word here is "statistics", but it's really a subject for a few days of talking, not a single post ;-)

The problem is that any such short post will always be of varying degree of incompleteness / incorrectness and will likely be based on a lot of assumptions, which may not apply or be plain incorrect. Hence, if you want to get to the bottom of this, you should get a DBA on-site for a few days. Otherwise you will really never know.

You will be ill advised to make any conclusions based on these short posts alone. I.e. the first reply you received is just answering a different question...
Besides analyzing the SQL statements you might try running it with logs on and using the performance workbench to see where the problem might be.

Wow thanks guys.

Can of worms here

I can confirm that using ER in set data selection = ABC DOES result in a different SQL being generated

So ER in order ABC will do just. ER in order CBA will do that also.

If I dont specify sequence by in the UBE it uses the main index on the first table in the BSVW.

I'm kind of answering my own question but I'm not and fishing in the dark.

I'd love to know how or why the DB decides on what index to use when it executes selects.

Would it get it suggested on the select order (in ER) or would it do it based on our set sequence setup in the UBE section.

Thanks for all your help so far

John said "I'd love to know how or why the DB decides on what index to use when it executes selects."

The answer is . . . Black Magic.
Database engines such as SQL Server and Oracle use "Cost Based Optimizers" to "try" to determine the least expensive method to retrieve and serve up the data. Applications can influence how the db engine does its determination via where and order by clauses, however as Boster said, without the ability to insert hints into the generated SQL you as a developer cannot control what the database engine does. A DBA though is another story . . .

Net, net I think Alex gave the best advice . . .
I'm posting this to help anyone reading this in the future for reference

Index A B C on table X will still be used even if your SQL says B then C then A.
It's clever enough to realise it can use index ABC

Your ER doesn't have to be in the order of the index columns
Yes, in SQL Server it likely will, but it can also use an index on CDE instead, even if there're both ABC and BCA ones present, if it feels so inclined.

Plus, it will likely use different indexes for different runs of this same kind of SQL statement. If you collect logs, you will see that sometimes the statement takes very short time and sometimes very long, even with no other activity on the server - all depending on which index it picks up for a given run. ;-)

I can talk about the nuances for days. It's not a matter of a simple rule of thumb at all. It's a whole science in itself ;-)

In fact, even what you just said, may not be correct in some cases...
OK, I've only been working with JDE for about three months, but I had the same issue with a report where a query for a huge table took about an hour. Even optimizing the query didn't bring the time down. Our dba went into the server management application (we are using AS/400) and viewed the query in history. This allowed us to see the index used and even a recommended index. We were able to create that index on the server side without modifying the table indexes in JDE. Simply doing that brought the time to about ten minutes for the query, and moving the report to our Production server made it even faster.

So, based on that, I'm inclined to believe that the JDE index used in the event rules don't tell the server anything. The way it appears is that the server software determines based on the "where" statement which index to use, and creating the index in JDE simply tells the server which index to use, as well as tell JDE which fields to use when you prepare a query.

Hope that explains it better.
Gary -

One must understand the query optimizer on iSeries to fully understand what occurs. There are also several differences in how queries are handled in V5R3 and V5R4 with regards to CQE and SQE.

Jeremy -

You may want to research IBM's definition of perfect indexes to update your methodology.