Dynamic Queries and Indexes

With MS-SQL, I can simply do a SELECT * WHERE WHATEVER...and the DB figures out the best index combination to use for me: I don't have to tell it which indexes to use. My question is this: Does the Progress DB do that as well, or do I have to tell it which indexes to use?

Progress will also figure out which indexes to use when run your query.

You may want to check out my query manager in the "Code share" section - it'll make constructing and managing your queries a lot easier.

And welcome to Progress!

Tim Kuehn

To expand a little on Tim's statement...

In ABL, it is possible to specify an index with a USE-INDEX clause. There are a number of people who argue that this is something you should never do because it indicates that you think you are smarter than the compiler and, of course, circumstances may change and you won't rethink whether this is the right choice or not.

If you don't use that clause, then Progress will pick one or more indexes based on a set of rules which you will find documented here:

http://www.peg.com/techpapers/monographs/selection/selection.html

There are times that people think it has made the wrong choice or a less optimal choice than it could and I suppose that is probably true, but it is pretty smart and quite consistent about following the rules.

What we don't have yet in the ABL, but which is available in the SQL access to a Progress database, is a full-fledged query optimizer that takes advantage of statistics to determine the best query strategy. If you rummage around some of the recent posts on PSDN, you will find some discussion about getting this into the ABL.

In ABL, it is possible to specify an index with a USE-INDEX clause.

While it is possible, it's not generally advisable since you lose a lot of the optimizations the engine can use to satisfy a query.

There are some cases where it's useful, but they are generally few and far between.

For a review of a number of prior religious discussions on this topic, go www.peg.com and hit the "search" link.

Enjoy!

It is what it is (I think that's Pop-eye's line).

It's not just a question of simplicity and the many other features that make our life easier in SQL, like, sub queries, unions and other set operations, aggregates, views etc. etc.

In most cases a query's criteria is dynamic and so the execution plan needs to be dynamic, like, join order, indices etc.

And many other query features which are just none existent, different tables scans, different types of join etc. etc.

There are additional pitt falls to ABL queries you're sadly going to discover, like, performace issues with table joins and sorting in remote connections, things like regex and so on.

Even if the guys will believe us when we say we need those features there's alot of catching up to do and most release don't over achieve.

It really hurts me to say it, but If you need those features I would recommend you use something else instead of ABL. I wouldn't know but maybe it's more suitable for service oriented, working with XML etc. I'm not really sure.

Coming from Progress I found MySQL a god sent and there are many other great alternatives that offer the simplicity and productivity that I felt when I started with Progress many years a go.

Thanks for the responses. As a follow-up...Since the engine does do the work of selecting the correct indexes, is it better to have indexes built on single columns? In the schema I'm working with, there are a lot of indexes that are composed like:

Index1 = Column1Column2Column3

Index2 = Column1Column3Column2

Index3 = Column1+Column4

It seems there's an index for every possible "FIND" option. Seems to me that, if I need to do a find by Column1+Column3 it would be more efficient and flexible (in many ways) to have seperate indexes for each column.

At any rate: In my case I cannot change the schema...is Progress DB smart enough to find and use the correct composite index depending on my WHERE clause?

Thanks again,

Rob

FOR EACH will use multiple indexes.

FIND ... only uses one index.

so definining a single index per column may help with multi-field FOR EACH statements, but not multi-field FIND statements.

Butin the case of a "find first", you can consider using "for first" instead. As far as I know, it will also do index-optimisation.

Thanks to all who've responded.

Rob,

If you have not seen the PKB ID: 21216 - "Diagnose 4GL Query Problems: Use Parameters -zqil and -zqilv" yet it may help.