Fastest way to Query Progress Database

ProgressUser732

New Member
Hello,

I have a progress open edge database and use the odbc openedge 10.1b drivers to connect and query the data.
When I use this driver, it usually takes 20 mins! to run a query with no joins that returns 5 to 10 results, why does it take so long?

What is the best driver to use?
Would learning 4GL/ABL query language speed up the query?
When I extract the same data to SQL Server and execute the query it takes 5 seconds.
How can I speed my query so that it takes the same time (or nearly the same time as SQL server does).

I am new to Progress Databases so any help would be appreciated.

Thanks.
 

tamhas

ProgressTalk.com Sponsor
While I second everything Tom said, no joins suggests to me that you are scanning all of a very large table selecting on criteria which are not indexed. If so and this is really the nature of your query, there may not be much to do except upgrading to a more recent release and/or doing tuning to improve the performance of your database generally. However, it may be that a quick study of the indexes in the dictionary will point you at a more efficient query. For example, there are a number of ABL products which use some kind of company code as the leading field in many indexes. People at companies where they are only using one code tend to think that this code is unimportant since it is the same in all tables, but suppose you are searching for orders by date and the relevant index is company code and date. If you don't supply the company code, it will scan the whole table. If you do supply the company code, it will go straight to the relevant records and be nearly instantaneous.
 

ProgressUser732

New Member
Thanks both for your replies.
I will try the UPDATE STATISTICS command on the database when I get the chance and see if it makes any difference.

Tom - What makes you say you might have to use the ABL?
Would ABL be faster than using an ODBC driver?
What about other drivers such as JDBC, Merant etc? Are these any good?
I assume the only overhead with ODBC is converting SQL to ABL which isn't much since I will be only executing uncomplicated queries that have at most 5 left joins.
Will also need to execute some correlated subqueries as well.

I am finding it really difficult to to find any abl tutorials, a search on SQL tutorials on a search engine yield 1000's of results, ABL tutorials - hardly any at all.

I have been looking at ways to execute an ABL query from external languages such as c# and php but it's impossible to find any straight forward guidelines in the progress manuals.
Any idea about how I would go about this?

Thanks for your help.
 

tamhas

ProgressTalk.com Sponsor
SQL is not converted to ABL. The SQL engine is entirely separate. There are some things SQL does better ... like rapid scan of a full table if no index is useful (new option in 11.0 for ABL though) and many things where it is just easier to specify the desired result set with ABL. ABL is mostly record oriented, not set oriented. SQL is preferred for interface to third party reporting and the like, but for actually getting most work done you will want to be using ABL.

For the current problem, do the UPDATE STATISTICS since you should be doing that on a regular basis anyway, but take a look at your query relative to the indexes on the table you reference. Chances are you will find that your criteria correspond to no index and that is your problem.

Oh, and there is a SQL forum for SQL questions.
 
Top