Num records in table

[joey.jeremiah]

on the other hand the 4bl write operation have always been worked and improved on
somewhat of an obsession of gus

and it's always been emphasized in the guys benchmark bunkers

ok, so you can have high volume of transactions in 4bl
and from what i know over power the sql engine by far

though 4 digit transactions per second have never been an issue for me
and i'd guess the majority workgroup installations

on the other hand, for me, reports/queries could never be fast enough
if it's a matter of short'er term priority i guess it makes sense

 

[TomBascom]

The db engine is capable of doing a scan with no index. The SQL language interface to the db engine implements the feature by way of its cost-based optimizer. The 4gl language interface uses a static optimizer and has not yet implemented the feature.

They "just" need to add a keyword to the 4gl to tell the compiler that you want to access records without an index. The obvious approach would be to create a new keyword, NO-INDEX, and to allow it to be used anywhere that USE-INDEX can be used.

As enhancements go this isn't very complicated. The database group has already done their part (2+ years ago now...) It is the responsibility of the 4gl product group to make it visible in the language.

It is essentially the same story as the DateTime saga.

 

[joey.jeremiah]

physically, aren't there 2 almost separate database engines ?

isn't the sql engine running on the java process, maybe it's just for executing java triggers, stored-procedures etc. ??

the 4bl, either embedded in the client for self-serving connection or the server processes that only executes single-table queries in remote connection.

if there are wouldn't it require a reimplementation of these features ? though i don't believe it would require a rethink of design or anything that extreme.

 

[TomBascom]

physically, aren't there 2 almost separate database engines ?

No. There is just one storage engine (sometimes referred to as "rocket"). It currently has two language interfaces -- one for 4gl and one for SQL-92.

isn't the sql engine running on the java process, maybe it's just for executing java triggers, stored-procedures etc. ??

Correct. Java is required for SQL.

the 4bl, either embedded in the client for self-serving connection or the server processes that only executes single-table queries in remote connection.

There is no single table query restriction.

 

[joey.jeremiah]

No. There is just one storage engine (sometimes referred to as "rocket"). It currently has two language interfaces -- one for 4gl and one for SQL-92.

"rocket" i liked that

storage would have to be unified. it depends on what one would call an interface but as far as queries go what common ground are there between 4bl and sql ?

especially due to that 4bl client/agents encapsulate these routines and access the database directly thru shared mem and not a server process in a self-serving connection.

There is no single table query restriction.

from the monographs

<quote>
PROGRESS translates joins into multiple single-table queries, which are the only types of queries the server can execute today.
</quote>

even though almost a decade has past, last i checked joins still cause network messages to sky rocket exponentially.


heres a real life impact with just a single join on a lan

http://www.peg.com/lists/peg/history/200408/msg01138.html

<quote>
In query 1 the necessary time is appr 100 ms. In query 2 then time 2 s (20x situation1). Adding another join takes 2 seconds more.
</quote>

the worst thing about it is that it's a surprise, but not like the happy birthday ones. you'd practically have to be an expert to know about it, afaik it's not even in the docs. imo that's the definition of frustration, that it doesn't work the way you'd expect it to.

tia Tom

 

[joey.jeremiah]

... (sometimes referred to as "rocket").

sorry Tom, couldn't help my self

http://www.ess.washington.edu/Space/ESS102/CO2rocket.car.jpg

 

[TomBascom]

"rocket" i liked that

storage would have to be unified. it depends on what one would call an interface but as far as queries go what common ground are there between 4bl and sql ?

especially due to that 4bl client/agents encapsulate these routines and access the database directly thru shared mem and not a server process in a self-serving connection.

I'm not sure why you think that it matters but 4gl clients can do either self-service or remote server access to the db. SQL clients only work through remote servers.

What they have in common is that they both manipulate the same stuff in memory and on disk. How they organize those manipulations is of course different -- that's why they are two different languages.

As for "single table" -- I thought you were saying something else entirely. In any event... I haven't spent any time looking into it but that quote from the monographs doesn't say that Progress' SQL engine does joins any differently. At some level, any sort of join for either language is going to have to be split into multiple single table queries and then "joined"; that's sort of what the term means. The rules regarding how efficient it is and how much traffic it generates are "complex".

 

[joey.jeremiah]

As for "single table" -- I thought you were saying something else entirely. In any event... I haven't spent any time looking into it but that quote from the monographs doesn't say that Progress' SQL engine does joins any differently. At some level, any sort of join for either language is going to have to be split into multiple single table queries and then "joined"; that's sort of what the term means. The rules regarding how efficient it is and how much traffic it generates are "complex".

the diff is that in 4bl the split is on the client side of the connection.

there by generating at least a single network message for each nested query, on average 1 millisecond much slower then disk read operation regardless of whether the data is already in the buffer-pool or not

causing a break in the fabric of space and time mcfly, oops ... i've turned into doc from back to the future



things are even worse, or worst possible, in sorted access.

KB-18342: "How to improve Client Server Performance in Progress"

<quote>

FOR EACH WITH JOIN & SORT

Depends on if the sort is only on the main table or not. Can use a lot of round trips. Not recomended, use nested FOR EACH statements if at all possible.​

</quote>

doesn't that sound absurd to you ? oh, btw don't use sorted queries ( in a remote connection ) ?!



and you know whats the worst thing about it ? is that, again, it's a surprise !

just recently i had to wire 2 machines to prove that point to my progress software distributor. who innocently recommended on putting an appserver and database on diff machines to spread the load, sounds oddly logical doesn't it ?

and that's my point things should be kept simple and behave the way we expect them to, if not, again to me that's the dictionary def of frustration.



george potemkin has mentioned in more then a few occasions as the biggest performance factor. which shouldn't be one at all and is unique to progress.

this here is the reason for 100x queries, 4 hour reports being a norm, that wan connections aren't possible and i would argue the same for lan and so on.

over the years, in my short life, i've seen all kinds of "crafty" developers trying to understand and workaround this design. like loading half the database into local temp-tables and running queries off them etc.



here's the perfect example. they should teach this example at courses, exchange presentations and hang it in progress offices.

http://www.progresstalk.com/showthread.php?t=90927

just between you and me, the saddest thing about it is that he's 100% right. forget about the technicalities there's just no arguing with this result.

please don't take it as personally directed at you or progress just phisihing for an interesting discussion.

 

[joey.jeremiah]

i've got an idea for an exchange presentation


queries gone bad !

what's you gonna do, what's you gonna do when they come for you ...

 

[TomBascom]

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 am unconvinced, but open to evidence, that Progress' SQL implementation acts any differently than the 4GL does.

 

[joey.jeremiah]

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.

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 ?

 

[joey.jeremiah]

just a few things that need to be cleared up.


regarding the example i posted, i think, the situation is -

multiple join query over a remote connection, probably, lan.

and a crafty developer that decided to first load all the data for the query into temp-tables using queries for each of the query's buffers to try getting around it, one that i've seen on more then a few occasions.

not a case of spatial indices or what ever.

i don't know if alot of other people reading this post realize that the 4gl embedded sql is sql89 and is being deprecated, i know i didn't for some time until someone set me straight.

 

[Lee Curzon]

Tom, Joey,

This is a really interesting exchange for those of us who aren't hot on the db engine theory side.

Thanks.

 

[joey.jeremiah]

This is a really interesting exchange for those of us who aren't hot on the db engine theory side.

that's the thing. if gus was half as good as he likes to give himself credit for we wouldn't have to know about it.

queries would find the best access plan or atleast a decent one no matter what filter the user entered.

and people wouldn't be scratching their head wondering why queries are taking tens of times longer when adding a join (remote connection).

any kid writing a web app using asp and sql takes this features for granted.

 

[Lee Curzon]

that's the thing. if gus was half as good as he likes to give himself credit for we wouldn't have to know about it.

Are you getting worked up again?:awink:

To be fair, it tends to be the Peggers who hold him in awe, because he is the Wizard behind the scenes, and he does make the effort to descend from Bedford every so often, which is rightly appreciated.

I haven't noticed a particular arrogance on his part, apart from his geeky signature and posting style.

Also, we don't know what sort of resources or pressures Gus and his team work to.

I bow to your superior knowledge on the technical deficiencies though.

 

[joey.jeremiah]

Are you getting worked up again?:awink:

To be fair, it tends to be the Peggers who hold him in awe, because he is the Wizard behind the scenes, and he does make the effort to descend from Bedford every so often, which is rightly appreciated.

I haven't noticed a particular arrogance on his part, apart from his geeky signature and posting style.

Also, we don't know what sort of resources or pressures Gus and his team work to.

I bow to your superior knowledge on the technical deficiencies though.

[FONT=&quot]
point well taken. but gus was the engine crew and later on headed it, and i guess now oversees over it. point is he's responsible

think of how many people gus has worked up ?? i would in all fairness say made their life miserable, it really does effects peoples life. i mean we're the ones facing the customers and we committed ourselves to this tech etc.

i'm sorry if it's not exactly pc, but i'm no saint either.

he reminds me of someone. years ago when i started working in progress, we had this elder programmer, he used to read the screen with his finger was completely clueless about the lang, every small job he got he used to call a project ... you get the idea. i think he later dropped out and became a teacher

but he always had this come, relax attitude about him, never in a hurry, never lost his temper, that and in a way he even looked like gus.


in all fairness do you see gus as a visionary, and john sadd as natural author, and lets not forget the "icf" that were probably a decade worth of development gone down the drain and maybe a good reason why we're behind.

these are the guys leading us.

[/FONT]

 

[Lee Curzon]

[FONT=&quot]in all fairness do you see gus as a visionary, and john sadd as natural author, and lots not forget the "icf" that were probably a decade worth of development gone the drain and maybe a good reason why we're behind. [/FONT]

I must admit, my opinion of Gus has been influenced by how he is regarded by some on the Peg, simply because some of them know much more about the subject than me.

Also, some of his posts have a technical depth which I don't have the aptitude for (or possibly, the interest in), and I always respect those who maintain a grasp of theory (like yourself), particularly as they get older (no ageism intended).

Progress as a DB compared to Oracle/SQL Server, I have little interest in, because it can't compete in comprehensiveness as has been pointed out by emissaries from the Evil Empire elsewhere in this forum.

Progress as a GUI development system is distinctly second-rate compared to Microsoft, and only SmartObjects and the OCX (old technology!) capabilities keep me interested.

What I admire Progress/Gus for, and what keeps me here, is the 4GL, and I wish they'd make some more effort to concentrate on that, and integrating it with other systems, as they now seem to be doing with OE. The backwards compatibility obsession is aggravating to me as a programmer, but then I don't at present have to deal with legacy customers.

'Too little, too late' is the valid, constant refrain, but in truth, I don't know whether it is the technical team or management or both that are the problem.

In general, I am impressed by John Sadd's documentation, but then I am comparing it to previous Progress docs, not the millions of books available for other platforms.

The Webspeed and Smartobject docs suck.

 

[joey.jeremiah]

good point!

stop giving me hard time because i'm still green and loud ( they kinda go together, don't they ) :lol:

too bad we can't go down to the pub for a beer, see you around man.

 

[Lee Curzon]

Ok, I'll let you know when I'm in Israel next.

Logging off to go do some work.

 

[TomBascom]

Go to Exchange. Talk to Rich Banville and go to his presentations. Rich is the db guy these days and he has been for quite some time. If you can't make it to Exchange download Rich's presentations. They're quite good.

(Or go the Euro conference -- it's cheaper than the US conference and it's a little smaller which makes it easier to get access to guys like Gus and Rich. They're all very happy to talk to customers and explain these sorts of things in more detail than can possibly be covered here.)

Gus, of course, still has deep knowledge of the ins and outs of the db engine (and a whole bunch of other stuff) but his day job is much loftier these days. It is very unusual for a guy in his position to spend the effort that he does reaching out to and understanding real customers -- I don't see his peers making anything like that kind of effort.

I'd like to see the cost based optimizer used by the 4gl too. But I'm not holding my breath. It is, by all accounts, a big leap -- not for the database engine but for the 4gl. I haven't tried this but I've long thought it would be interesting to take the cost statistics that are gathered for SQL and apply them to selectively over-ride 4gl index selection. If I ever run into one of those horrible queries that you seem to so commonly find I plan to give it a whirl.

In any event -- your wrath is mis-focused. The problem is not so much the db engine as it is the 4gl product management process. The 4gl chronically lags the capabilities of the db engine (and the development world at large). IMHO this is largely due to a lack of vision by 4gl product management -- not the technical people. They (product management) have focused on immaterial 3gl GUI eye candy (that they couldn't get right) for years at the expense of important technical infrastructure like db features, date-time, OO and -- yes, I'll say it, the character mode interface (which, like or not, is still a very significant revenue generator and which will remain significant for a long time yet to come.)

This lack of vision continues to a large degree -- for instance we get "abl" instead of NO-INDEX. But there are signs of hope. We have finally got some real OO features and a real IDE. And the OERA has a lot of promise. But the momentum needs to be maintained and they must not be allowed to rest on their laurels (there aren't enough of them to make a good bed anyway).

The 4gl is where the problem is and the 4gl is where the future is. The engine crew can produce the most wonderful database engine on the planet but unless the 4gl can use it to full advantage they're wasting their time. We all became Progress users because of the 4gl. And, ultimately, it is the 4gl that keeps us Progress users. It's all about creating the world's best business applications and that is an effort that requires the world's best 4gl.