Hi,
I have developed an application in c# which connects to V10 database through the "Progress Openedge 10.1B" odbc driver and everything works well. I have been testing it with older versions of Progress and I am hitting a problem with 9.1D. Many customers still use this ( nothing to do with me !) so I need to try and find a solution.
I have noticed that joins don't use the full index if the selection fields are a mixture of db and constants e.g.
SELECT count(*) FROM pub.mytable
LEFT JOIN
pub.mytable2 on mytable2.field1 = mytable.field1
and mytable2.field2 = mytable.field2
and mytable2.field3 = mytable.field3
The above works fine and displaying the query plan shows that the index containing field1+field2+field3 is used for all 3 fields.
If one of the selection criteria is changed to a constant ( not actual query but it demonstrates the point )
SELECT count(*) FROM pub.mytable
LEFT JOIN
pub.mytable2 on mytable2.field1 = 'some constant here'
and mytable2.field2 = mytable.field2
and mytable2.field3 = mytable.field3
The query plan still shows the correct index is chosen but only field1 is used within it. When the query is run, promon shows a high record count as it trawls though the field2/field3 selections sequentially.
If the field3 selection is changed to a constant then only field1 and field2 are used within the index ( but the correct index is chosen ).
Interestingly if all three fields are constants ( not very likely in the real world ) then they are all used within the index.
So the bottom line seems to be that mixing comparisons with fields and constants prevents all the fields in the index being used. In all the examples, V10 includes all fields and works correctly.
Have I missed something really obvious or is there a way to code around this for 9.1d ?
Many thanks.
Nige.
I have developed an application in c# which connects to V10 database through the "Progress Openedge 10.1B" odbc driver and everything works well. I have been testing it with older versions of Progress and I am hitting a problem with 9.1D. Many customers still use this ( nothing to do with me !) so I need to try and find a solution.
I have noticed that joins don't use the full index if the selection fields are a mixture of db and constants e.g.
SELECT count(*) FROM pub.mytable
LEFT JOIN
pub.mytable2 on mytable2.field1 = mytable.field1
and mytable2.field2 = mytable.field2
and mytable2.field3 = mytable.field3
The above works fine and displaying the query plan shows that the index containing field1+field2+field3 is used for all 3 fields.
If one of the selection criteria is changed to a constant ( not actual query but it demonstrates the point )
SELECT count(*) FROM pub.mytable
LEFT JOIN
pub.mytable2 on mytable2.field1 = 'some constant here'
and mytable2.field2 = mytable.field2
and mytable2.field3 = mytable.field3
The query plan still shows the correct index is chosen but only field1 is used within it. When the query is run, promon shows a high record count as it trawls though the field2/field3 selections sequentially.
If the field3 selection is changed to a constant then only field1 and field2 are used within the index ( but the correct index is chosen ).
Interestingly if all three fields are constants ( not very likely in the real world ) then they are all used within the index.
So the bottom line seems to be that mixing comparisons with fields and constants prevents all the fields in the index being used. In all the examples, V10 includes all fields and works correctly.
Have I missed something really obvious or is there a way to code around this for 9.1d ?
Many thanks.
Nige.