MySQL vs Progress

[kolonuk]

OK, probably the wrong section, so sorry if it is...

We have been playing around with external reporting tools using the progress ODBC drivers, and came across one bit of software that would only connect to MySQL. So we dumped a stats table and loaded it into MySQL, and did some analysis.

We connected to the MySQL database, and found that results were coming back rather fast. After checking that we had actually loaded all the data, we were a little confused.

We found FlySpeed SQL Query, and connected to both the MySQL and Progress databases via ODBC, and ran some queries. We then ran the same query back at the Progress Procedure editor.

Then, we wondered why we use Progress at all, as the un-indexed table on the "out-of-the-box" configuration of MySQL was about 2-3 times faster than an indexed table running as a tuned Progress service.

I found this http://www.progresstalk.com/showthread.php?t=112541 and wondered about indexs, so created a new database, dumped and reloaded the data with no indexes, just to see what happened. It was much slower as you would expect, so that didn't work.

I contacted Progress tech support to ask what the difference was, and they just batted it off to their consultancy service for database tuning, saying that this wasn't in the scope of tech support.

Does anyone have any ideas why a tuned commercial product like progress is so ludicrously slower than a free, open-source, "out-of-the-box" product like MySQL?

 

[TomBascom]

Maybe it's because a product like Progress has lots of integrity checks built in to make sure that your data is not only fast but accurate?

Or a variation on the above -- maybe you're connecting to your MySQL db no-lock and your Progress db with locking enabled?

Or maybe you're comparing some ancient unsupported release with really bad ODBC drivers like Progress 9.1C to the latest and greatest MySQL?

Or perhaps it is because you haven't run update statistics?

 

[kolonuk]

Thanks Tom, I'll look into that. One thing I do know is that we are using 10.1b progress, and version 4.something mysql... ;-)

 

[palthe]

What kind of query did you use? Because it's known that table scans actually are a lot faster on an sql or oracle database (correct me if i'm wrong).

 

[kolonuk]

Yes, it was a table scan... I assume progress is slower due to hitting indexes and lock tables etc...?

Apparently v10.2 is out in Nov/Dec that has some speed improvements (so say progress tech support) so we'll see what happens...

 

[palthe]

hmm yes, I'd like to hear the seniors about this, but it has come to my attention that SQL is "resultset-minded" and Progress is "record-minded".

But I have to pass on the in-depth explanation on that one

 

[TomBascom]

The Progress SQL-92 engine can do a table scan that bypasses indexes. This is said to be significantly faster. I have not tested it myself but it makes sense.

The 4GL will, however, always use an index. There have been many requests made of product management to support a NO-INDEX option on 4GL queries. SFAIK it is not in 10.2 but I'd love to be wrong. Contact your Progress sales person, tech support and svi@progress.com to let them know that this is important to you.

 

[kolonuk]

I'm not sure that is what they meant by faster, but a NO-INDEX would be nice to have for these purposes maybe. I'll get my boss to flirt with our rep a bit more...

 

[palthe]

Hmm Tom can you be more explanatory about that?
I mean, let's say we do a count. How can this be faster without an index than when we use an index? I mean, indices are only useful for one thing I was led to believe, and that is speed! So how can it be that a NO-INDEX is faster? It has to pass by all the records in stead of just the indexed fields...

This is confusing to me

 

[kolonuk]

ideally, when you do totalling/counting using all the records, you don't want to be wasting time trying to find the next record by consulting the index everytime, you just want next, next, next, next. Progress always uses an index, no matter what. To speed up a progress statement, it would be nice to have a no-index option...

 

[Casper]

If you do a table scan then you can just 'flush' the clusters in a type II storage area. Progress only has to follow the links to the next cluster.
By using an index it has to consult the index after each fetched record, and retrieve the next, which could be in another block or another cluster. This adds up to a significantly difference in time for large tables.

Casper.

 

[TomBascom]

Hmm Tom can you be more explanatory about that?
I mean, let's say we do a count. How can this be faster without an index than when we use an index? I mean, indices are only useful for one thing I was led to believe, and that is speed! So how can it be that a NO-INDEX is faster? It has to pass by all the records in stead of just the indexed fields...

This is confusing to me

Indexes do several things:

1) Indexes provide (or support) sort ordering. That's very handy in many cases.

2) Indexes can enforce constraints like uniqueness.

3) They are make access much faster when the records needed are a subset of the records available. You wouldn't want to read a million records off disk in order to find 10 that you are looking for. On the other hand if you really do need to process a large percentage of the available records then you don't want to do 2 IO operations for every record read (1 IO to read the index, another to read the data). The break-even point is fuzzy but Oracle people often claim that it is around 10%.

 

[all6string]

The answer is basically that MySQL is generally multiple times faster. Period. It is built for speed. It has different storage engines, MyISAM being fastest I believe, but even InnoDB engine that handles transactional SQL is extremely fast. So to answer original posters question about how a commercial advanced system like Progress could be outpaced by MySQL, answer is that MySQL is faster and has far greater market penetration worldwide, largely as it is deployed most often as free open source. Is is better than Progress? No, it is just different and being part of the Progress family has a lot of big time benefits too.

 

[Rob Fitzpatrick]

The answer is basically that MySQL is generally multiple times faster. Period. It is built for speed. It has different storage engines, MyISAM being fastest I believe, but even InnoDB engine that handles transactional SQL is extremely fast. So to answer original posters question about how a commercial advanced system like Progress could be outpaced by MySQL, answer is that MySQL is faster and has far greater market penetration worldwide, largely as it is deployed most often as free open source. Is is better than Progress? No, it is just different and being part of the Progress family has a lot of big time benefits too.

You're aware that you're answering a five-year-old thread?