Well, not so very rich experience. I'm more like forced somewhat to make sure SQL on Progress (oops OpenEdge) database works, since lots of our customers use reporting tools on our database.
Well, lets start with the positive news:
In OpenEdge 10.1B almost all our problems we had with version 9 have disappeared. We experienced lots of problems with hanging _sqlsrv2 processes for instance.
Furhermore all our customers experienced a very significant performance increase with all their queries.
So regarding these things everything has become lots and lots better.
There are still strange things happening with SQL:
For instance if you have a query on 2 tables with a nice indexed join. Sometimes extending the where clause with some OR construction on some unindexed fields in the second table causes the join to disappear when you look at the query plan. So you must be very carefull how the query is written. Update all column statistics can overcome this problem.
Another example is that if you have a bad performing query then it sometimes just stops executing, without any error the proces dies. (I hate this word....sometimes...
).
There are 2 important things to do:
- run dbtool on a daily basis
- run update statistics on a regular basis
If we use nice indexed joins and group-by then everything works pretty fast.
Are we there yet?
Well If I said we where there I probably get a big argument with our friends BCM and RHI so I wont say that.
(select * from ... where 1 <> 1 still takes ages on a big table
)
Regarding to the features missing, I hope RHI and BCM have something to say about that, they have more knowledge of SQL then I have.
But for the purpose we use it it for (reporting) it is mighty fast and depending on the type of information gathered it can outperform ABL.
Oh and another nice feature I think is being able to connect to more then 1 database at the same time (yes I know only one can be updated, no distributed transactios).
Casper.