ODBC Performance

[agriffin02]

I have a c#.net windows application that I have developed that is accessing the Progress 9.1D Database using the Merant ODBC driver and running SQL92 statements. Performance seems pretty crummy. It appears that the performance on a particular query is faster after it runs the first time, but it is still too slow. Does it cache the query on the server? I have made sure that I am using all columns that belong to an index.

Are there other options that would be better for accessing the data?

If it helps anyone to understand what I am working with, I am working with Epicor's Vantage ERP system.

 

[Casper]

there have been many improvements with regard to performance and functionality since version 9.
Make sure update statistics has been run on the database for table, index and column statistics.


With version 9 also:

• don't use group by
• don't use subqueries
• install sp9
• use the datadirect 4.1 driver which is in sp9

if all criteria above have been met then check the query plan and the performance is still poor:

SELECT SUBSTRING("_Description",1,70)
FROM pub."_Sql_Qplan"
WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" )
FROM pub."_Sql_Qplan"
WHERE "_Ptype" > 0 );

and see if the right indexes are used and the right joins are made.

Upgrade to 10.1C or 10.2A would also improve performance a lot btw

Regards,

Casper.