VB.NET ODBC and Performance

[GX2]

I am noob with Progress so please be patient with me.

First off I am using VB.NET to connect to Progress 9.1e through ODBC using the DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E driver.

My problem is this. I am trying to get some data from a table that has an index that contains two fields. I have data for one of those fields that I using in my WHERE clause. I need to get the last entry of one of those fields in the index and so I don't have any data to supply for the second field in my index. My performance is terrible as I am not able to use the index. My question is how do I force the query to use a specific index as in the use-index statement in Progres itself? Can I do this? Are there any other methods of increasing performance?

 

[tamhas]

Let me guess that the field you have is the second one in the index, otherwise you wouldn't be having the problem. Inherently, unless you create an additional index, this means a full table scan. In a recent enough version of Progress, I believe they have implemented full table scan without index for SQL (not in ABL, grrrr) which helps since fewer records have to be read, but it still isn't going to be blistering fast ... the only way to do that is to get an index to support your query.

 

[GX2]

Thanks for the reply. Do you know if I can specify the index to use explicitly in a query like the use-index statement in Progress? Is there a resource for ODBC client that is a syntax conversion of Progress key words to SQL key words? I found the pro_element function to select the desired value in an array field.

GX2

 

[tamhas]

Look up PRO_DEFAULT_INDEX ... but I've never had to use it. If there is an index with your field as the leading component, then it should get used. If there is no such index, then you are in for a full table scan by definition.

 

[Casper]

You can give an index hint with:

select * from pub.<table> with (INDEX("<index-name"))

This will only work if the index is already considered a possibility by the sql engine.
You can check if this works by consulting the queryplan of the query.
But my feeling is that Tamhas is right and you are trying to access the second field of the index. If that is the case then the only way to speed that up is to add an index on the second field or use another index that will decrease the size of the resultset.

HTH,

Casper.

 

[ERPgal]

You can also create a new index that has the fields available to you.
Or you can try to trick the db using a LIKE command

i.e. WHERE a.myfield LIKE '%'

The former is more likely to work.