TomBascom said:
That's an interesting thread but it's a little light on details. I would be interested in a repeatable test case. Especially one that can be run against both the 4gl and the SQL side of the database.
I freely and happily admit that other databases do the things mentioned in the lead post of that thread better than Progress and that if you need these features then the Progress database may not be the right choice for you at this time. That's no surprise and has been known for a long time. On the other hand Progress has its own strengths and many of those are the reasons that we are all here.
i'm your typical beer loving 4bl programmer, and i don't have any plans to change soon, so i'm only interested in progress.
imho, there are 2 chronic 4bl quering issues which i believe have the biggest real life impact on performance and to me seem like natural progressions, advancements of any querying language even before fast table scans.
and as you've mentioned they've already implemented them in sql (that's maybe another advantage to following a standard).
1. cost-based optimizer. take for example the fact that the join order is static.
if for example we were using a dynamic query and adding criteria to the prepare-string. but the example applies just as well to static for each statement.
with the following join order:
for each order no-lock, each orderline of order no-lock, each item of orderline no-lock:
if the filter was for a single order then the query will only fetch the data needed i.e. the order it's lines and items, great, perfect!
but if the filter was by item then the query will go thru all the data i.e. all the orders and their orderlines!!
these sort of cases are happening everywhere all the time. ymmv
there are also plenty of examples that using general rules for selecting the indices to scan can be far from a good option to go with. just the inherited limitations of a rules-based optimizer.
maybe managing statistics for an optimizer might be a problem for an embedded usually dbaless rdbms but it can also be optional.
some time ago you and i had a discussion @peg on using b-trees to size up brackets and follow their progress.
2. clients translating joins into multiple nested single-table queries. i'm just baffled how gus didn't see this one coming, and even worse that it's still here maybe a decade later.
just try running a query with many-to-one joins (e.g. for each orderline no-lock, first order of orderline no-lock) on a wan remote client.
even though we can get bye without a remote connection over a wan with n-tier arch. i'd argue becuase of that same reason splitting agent and database over several machines isn't possible for queries with high or even adequate performance.
it's a very big handicap, even if you are aware of it, it pretty much limits you to a single machine! (well, in many cases)
and again, alot of people don't even realize why their queries are taking 20x more time or come up with some pretty crazy ideas trying to get around them.
you mentioned a "use case" maybe a picture is worth a thousand words. i recall seeing query benchmarks on your site, well, maybe benchmarks of queries with multiple joins on a wan/lan remote connection can do alot to help, i think, would settle the point.
come to think of it we haven't seen much advancements in queries since the advent of query objects. i mean this should be right up our alley before anything else. that's one of the reasons for using progress, right ? we should atleast be able to create 4bl reports/queries with descent performance.
to me a long needed querying strategy would provide a strong foundation and a much wiser investment then the new gui.
i think that another thing we're looking for is leadership. personally i don't put much stock in gus or sadd john, sorry.
TomBascom said:
I am unconvinced, but open to evidence, that Progress' SQL implementation acts any differently than the 4GL does.
the reason, i think, there are two (almost separate) database engines, is because there are 2 of them running.
the 4bl that is part of the client (or a server process spawned by the broker that only exec single-table queries) that i guess is written in c
and the sql server, maybe written in java ?
i'd be really interested if you can cite any books maybe some of dan's, exchange presentations etc. on the storage engine, database engine arch etc. tia !
tom if theres anything i can do to help i'd be honored. i can wash your car, anything
come to think of it, do you have an intern program ?