Progress Compiler Vs Index

[catch.saravana]

Did progress tighten their screws in picking index on new version? Intention of this question is as we are migrating from 9.1E to 11.6 will there be any difference saying 'Oh the same query picked index1 in 9.1E and picks index7 in 11.6'?

I took a complete XREF of 9.1E and 11.6 and did comparison on the index that progress picks (SEARCH keyword on XREF) and didn't see any difference but wanted to get a confirmation from the experts as well.

The only change that I saw on XREF was if I have a query on TEMP-TABLE that doesn't have proper index then on 9.1e it doesn't say WHOLE-INDEX after temptable keyword whereas in 11.6 it shows it as WHOLE-INDEX.

e.g.
9.1E -> ./ab/sample.p ./include/test.i 733 SEARCH tt-RD memRole TEMPTABLE
11.6 -> ./ab/sample.p ./include/test.i 733 SEARCH tt-RD memRole TEMPTABLE WHOLE-INDEX

 

[TomBascom]

The compiler's index selection rules have not changed.

 

[catch.saravana]

Thanks for confirming, Tom!

 

[Rob Fitzpatrick]

Do you have SQL clients?

 

[catch.saravana]

Not right now, Rob.

Out of curiosity - You mean the SQL queries that gets translated to Progress will have impact on index selection?

 

[Rob Fitzpatrick]

The reason for the question is the difference between the two query engines. The 4GL (ABL) query engine uses rule-based index selection, as Tom indicated. The (increasingly-misnamed) "SQL-92" query engine uses a completely different method. It has a cost-based query optimizer which makes query plans with the help of metadata that is manually updated in the database by the DBA via UPDATE STATISTICS commands.

So if you have SQL clients and never update the statistics, the query optimizer will be unable to reason about data in the tables and will create unoptimized query plans.

SQL queries that gets translated to Progress

If you mean SQL queries that are run in the procedure editor, that is the "SQL-89" interface. It has been deprecated for years and should not be used. Those queries are translated to ABL and executed by the 4GL engine.

 

[Rob Fitzpatrick]

Now I'm confused. In the thread Odbc For Progress 11.6 you asked about ODBC client connectivity. Is that a separate application from this one?

 

[catch.saravana]

Yes Rob, we have 2 different applications running on Progress; one is a very small application with hardly 100 GB of database size whereas the other one that am actively working on migrating is 2 .5 TB database.

We wanted to get the 2.5 TB DB App migrated first, so that migrating wouldn't be difficult considering the size of the other database. Though we didn't start to actively work on the other DB there was couple of questions raised in our team meeting regarding the ODBC drivers in 11.6 vs Data Direct and its licensing cost. May be I should have given more details when I posted the other question - sorry for the inconvenience.

 

[Rob Fitzpatrick]

No problem. Be sure to update your statistics in the database with ODBC clients once it is running on 11.6.

 

[catch.saravana]

Sure Rob.