ODBC:Need to sort, but can't use ORDER BY because of Progress bug - How to use index?

[Benny.Giesbers]

One of our clients wants to get data from their database into Excel sorted on a specific column. Since the installation of OpenEdge 10.1B03, this is causing their Microsoft Query to crash with a 'Socket closed. Server crash likely' error.

The Progress Knowledge Base is very clear about this: It is caused by a bug and the only thing we can do is upgrade to OpenEdge 10.1C. For more details, see ID P129270. Unfortunately, this upgrade is not an option for us at the moment, so we have to look for another solution.

Now we're trying to get the data from their database in a sorted way. The WHERE of our query contains 4 criteria. These 4 fields are in an index where the 5th (and last) field is the field we want to sort on. One would expect that the result of the query is sorted on this 5th field, but that's not the case.

Is there any way to let ODBC sort our data? USE INDEX is not a part of SQL-92, right?

 

[Casper]

Re: ODBC:Need to sort, but can't use ORDER BY because of Progress bug - How to use in

The WHERE of our query contains 4 criteria. These 4 fields are in an index where the 5th (and last) field is the field we want to sort on. One would expect that the result of the query is sorted on this 5th field, but that's not the case.

Sorting on the 5th field is different then sorting by field1 by field2 by field3 by field4 by field 5....

If the 5th field isnt the first field of an index, then it can be pretty expensive to sort on this field.
If they want to import it in Excel then they can retrieve the data without the order-by and in Excel order by the 5th column.

Casper

 

[Benny.Giesbers]

Re: ODBC:Need to sort, but can't use ORDER BY because of Progress bug - How to use in

Sorting on the 5th field is different then sorting by field1 by field2 by field3 by field4 by field 5....

True, but if my query has a selection on the first 4 fields of an index, then my result should be sorted on the 5th field of that index. Isn't it like that in Progress 4GL?

The client doesn't want to let Excel do the sorting afterwards, because it's giving them problems with a vertical lookup function in Excel.

 

[Benny.Giesbers]

Re: ODBC:Need to sort, but can't use ORDER BY because of Progress bug - How to use in

We've looked into it a bit further and it seems that both 4GL and OpenEdge are using another index than the one we're hoping for. Instead of taking the 5-field index that has the 4 fields of my WHERE-clause, it combines 2 indexes: one on the 1st, 3rd and 4th field and another one on the 2nd field. That's why our result looks unsorted.

Isn't it strange that it prefers to combine 2 indexes? The only reason we can come up with is that it thinks it's better to use 2 indexes for the full 100% than use 1 index for 80%. But this is the wrong decision in our case.

In the Progress Knowledge Base, we've found a page that describes the usage of index hints. It's ID P117826.

ID: P117826
Title: "SQL: What is the correct syntax for INDEX HINT in OpenEdge SQL?"
Created: 08/07/2006 Last Modified: 11/03/2008
Status: Verified


Goals:
# SQL: What is the correct syntax for INDEX HINT in OpenEdge SQL?
# Why does the syntax of the Index Hint feature in the SQL Development Guide for OpenEdge 10.1A NOT work?


Facts:
# All Supported Operating Systems
# Progress 9.1x
# OpenEdge 10.x


Fixes:

The correct syntax for INDEX HINT in OpenEdge SQL is to use the WITH INDEX option as follows:

SELECT column_list
FROM table_name [ [ AS ] table_alias ]
[ WITH (INDEX ( index_val ))] , ...
WHERE ...

The syntax that appears on page 10-8 of the OpenEdge 10.1A SQL Development Guide does NOT work because it is incorrect.


Notes:

References to Written Documentation:

OpenEdge Data Management: SQL Development, Chapter 12: "Optimizing Query Performance".

With this information, we've managed to sort our resultset.

 

[FrancoisL]

Re: ODBC:Need to sort, but can't use ORDER BY because of Progress bug - How to use in

Just a note on this bug . Only the server needs to be updated to 10.1C to fix this . The 10.1B workstation will work fine and the bug is located server side.