Does Progress/ODBC function in the real world?

[mra]

Hi Peggers!

Progress 9.1A, HPUX11, Merrant ODBC-driver on NT.


I've started experimenting with SQL-92/ODBC and I'm very puzzled with the performance I'm getting.

I have a table a table which looks like this.
trip_date as date
car_number as int
tke_recnr as int
company_number as int
batch_number as int.
Trip_type as int

<Some other fields>

and two indices
trip_date, company_number, car_number, tke_recnr
batch_number, trip_type

If I query like this:
select car_number, batch_number from <MYTABLE> where batch_number >99 and batch_number < 101 and car_number = 123

Performs fine using SQL/89 - The procedure editor, but really sucks if I use ODBC/SQL-92

WHY?????? What am I doing wrong

Is the query optimizer different from SQL-89 to SQL-92??
Where can I see the rules for choosing index in SQL-92?

If what I see is true, then I can't understand how Progress/ODBC could function I the real world.


Regards
Mike

 

[Garyk]

I don't know if I can answer you question. However I have had some recent experience with ODBC and VB. I am writing an application in VB that needs to sync data with a Progress DB. I found that the Progress/Merant ODBC has many bugs! This forced me to purchase Merant's ADO to Progress software. So far all the problems that I had with the Progress ODBC works correctly wth the Seuel Link ADO. Hopfully Progress will get these issues resolved. I hate paying for the same product twice.

 

[Unregistered]

Progress has a lot of issues with anything relating to ODBC or JDBC for that matter. They claim ODBC is bad, but its nice and fast with Our DB2 and SQL DB's.

 

[ChezJfrey]

I have had similar experiences. It appears that finding records with fields in the where clause (or table joins without every field of the primary index) not contained in an index seem inordinately slow. Try adding a trip_type to your query, or add an index that contains just the batch_number or car_number.

 

[cup99]

You might want to consider using the Openlink driver to connect to a progress database.

We have used both Merant & Openlink and found that the Merant driver does not perform as well as the Openlink driver.

The Merant driver might be easier to setup than Openlink but it is slower and has some bugs in it.

 

[mra]

Hi All - Thanks for your answers!

I've looked into the matter my self, and found out a lot of things.

First of all -
The ODBC server (Not Open Interface, but SQL-92 ODBC server) DOES NOT WORK until 9.1C - Progress told me this.
The query optimizer and the server is buggy.
9.1C works - as far as I can tell.

2.
The server, query optimizer and everything else is separate from their 4GL counterparts, this makes it unfair to compare the two approaches (but does not excuse Progress ).

3.
The JDBC, and most likely the ODBC drivers are type 2 drivers made partly in Java and partly in C, not the fastest combination.

4.
Stored procedures in SQL-92 are no different than any other JDBC client made in Java, they connect and retrieve data through the network layer (I'm shocked ) - and performs similar.


We've run some tests.
A program queries one table for approx. 1500 rows, for every row check another table for related data (0 or 1 row), update the row retrieved in the query.

Client side 4GL: 8 Sec.
Java Stored procedure: 18 Sec.
ODBC/C++ client: 21 Sec.
ESQLC Client: 10 Sec.

If the ESQLC client is rewritten
to update every row in one
single SQL: 5 Sec.

4GL running on the server: 3 Sec.

In Stored procedures and ODBC clients, most of the time is spend parsing requests, NOT performing the query/update.

Conclusion: Progress ODBC is SLOW due to overhead in parsing ODBC requests.


Anyway - We've decided to go ahead with our ODBC project, and maybe code the critical parts in ESQLC on the server.


Regards
Mike

 

[kidehen]

ODBC Drivers for Progress

You can obtain ODBC Drivers for Progress that support the SQL-89 and SQL-92 Engines from OpenLink Software at: http://www.openlinksw.com

These Drivers are available in two formats (Single Tier and Multi-Tier), the Single Tier Drivers can be installed on your Windows or UNIX Client Machines (the machine from which you will be using ODBC) without the need to install any additional server components from OpenLink. The Multi-Tier Drivers on the other hand are equipped with a database independent communications layer which requires additional software to be installed on either the application server or database server machine.

The choice is yours to make, the guaranty that I give you is that either product works. We have been delivering ODBC drivers that support PROGRESS since 1993.

 

[tsspdx]

OpenLink Software claims in this thread that their ODBC drivers work with Progress. Technically, they do. We use them with Crystal Report Writer and Progress 7/8/9 on HP/UX 10/11 in situations where speed isn't a requirement.

What OpenLink ODBC doesn't do is work with Microsoft Office 2002. So it's not too useful if your goal is, say, to let users draw data out of Progress into Excel.

So caveat emptor. Try out whatever ODBC solution you're considering in your exact environment, and don't assume it will continue to work when you upgrade anything.

 

[Unregistered]

Are the performance and various other issues with ODBC and JDBC with Progress due to the fact that Progress is not a true SQL driven DB.

On DB2 using Merant directlink ODBC the performance is very good.

 

[mra]

Probably!

Progress did not have native SQL support until version 9. Queries is working fine, but parsing the ODBC calls takes time.

They're working on it.

Regards
Mike

 

[pauli]

ODBC Drivers for Progress/ASP

Just a few comments...

I've been curious about the ability of using ASP scripts against a Progress DB, so this thread is very interesting to me. I was also told by Progress not to even attempt anything except with 9.1C.

I don't know if anyone has used my envisioned system in the "real world" (Progress DB/ODBC/IIS/ASP scripts in browser) if by the "real world" you mean running queries in an internet application available to the public. However, I know for a fact that there are some companies out there who've developed apps who they've marketed to there client base which do exactly that. (I'd call up the one company I know that does, but I don't think they'd be very forthcoming with info since I'm a former employee and a potential competitor!...)

Anyway, from reading posts on the PEG list (www.peg.com) it seems like more and more people are interested in using ODBC with Progress, maybe because of the low cost compared to buying WebSpeed. Flame away...

Regards,

 

[sjackson]

I've been working for the past year on a development project using ASP, IIS and Merant ODBC on Progress 8.3D.

The first thing that we found was that getting the connectivity working was a real pain. We got it working mainly from Geoff Crawford ODBC FAQ (Thanks Geoff ) http://www.innov8cs.com/odbchtm.htm, and from other information posted on the web.

After developing our ASP application to connect to multiple Progress databases and open them out to our Intranet, we then needed to put more advanced logic into the application.

Based on existing experience of using Triggers with Oracle and SQL Server, we then moved on to design a database level trigger to run some Progress code after INSERTS were made on a particular table.

NOW A WARNING. The trigger implementation on Progress just about works on ODBC, but I would strongly reccomend that people stay well away from using Triggers and ODBC. There are various settings that need to be made to the ODBC client, and also I believe that under Progress 9 these triggers are not always fired depending on which type of client is using the database.

We have recently upgraded our database to Progress 9.1C, and we are now working through the issues following the changes to the SQL89/SQL92 connectivity and the revised security model.

 

[Jan Doggen]

We gave up accessing Progress database from a Delphi application through ODBC somewhere along 9.x.
There were just too much errors in the returned results.

Too bad.
Jan