Progress 9.1D VB.Net Query

T

tfung

New Member
Hi Everyone,

I am doing some research into Progress and would like to know if anyone has any experienece with Quering Progress by a Windows client using Native progress language (if it is doable at all) vs. quering in a SQL method via the Merant Driver. My main thinking of going to Native Progress via Windows Client is speed as I can access / specify the index whereas in SQL you can't really tell it to use index etc.

Any comments would be appericated.
 
with a self-service connection to the database, in general, the client is the
server and access the database directly from shared memory.

things turn much less efficient with a remote network connection, especially,
joins and joins with sort-access.


with distributed computing/architecture the business logic and ui are
separated to run on the server and client machines, respectively.

with the business logic running on an appserver, typically with a self-service
database connection.
 
The major reason for using Progress would be so you could write your Business Logic in the 4GL, which has no competitive equivalent in the major databases. If you are using SQL, you may as well use another DB. There are more comprehensive, faster alternatives.

A modern app would use the appserver - database code run on the server. network connections and ODBC are much slower generally.

You can see which indexes SQL uses via Query Plan, although I haven't used SQL. You are right though, Index Hint is not available.

Also, no self-respecting 4GL programmer uses 'USE-INDEX' anyway. :eek:

Lee
 
Sorry, I've just reread the title - 'VB.Net' doesn't appear in the main text.

OpenEdge 10 has interfacing with .Net and other environments at the core of it's raison d'etre.

Use that if you can.
 
Lee Curzon said:
no self-respecting 4GL programmer uses 'USE-INDEX' anyway. :eek:
Lee
Click to expand...

hi Lee :)

respect is something for turkish prisons ( it's an old joke, no offence dude ) :P


the progress rules-based ( the geek term is syntactical, from syntax ) optimizer doesn't garantee the best indices to use.

how can you garantee the best access plan using general/universal rules without knowing anything about the particular cases data.

even a cost-based optimizer, though it's designed to answer that particular issue, is still just an estimate.


if you know the best access plan for a particular query why not specify it explicitly ?

i agree with you that use-index has short comings because you can't specify multiple index merges only a single index bracket.

but as you know there are ways to manipulate the optimizer to use the indices you want, well, in some cases.

and i'm also completely with you for keeping the source minimal as possible and not adding use-index where it's obvious.


tfung,

what you can do is specify the best access plan i.e. join order and indices for each particular case.

it's simpler then you might think, in general, it's a ranking of the best brackets to use


for example -

if ( there's a bracket on field1 ) then do:

{query.i "access_plan_1"}

end.

else
if ( there's a bracket on field2 ) then do:

{query.i "access_plan_2"}

end.

.
.
.

else do:

{query.i "shortest-whole-access-plan"}

end.

/* this is where it ends. don't bother with any access plan that's worst then the shortest way to go thru all the data */
 
Thanks for the reply everyone. I suppose what I am trying to get at is that we want to develop an application for the factory with some smarts in it and some GUI; Being a .NET/MSSQL programmer, I want to know whether there is an advantage (speed wise) to learn and write the application in native progress language bypassing ODBC or if the speed difference is not all that much, then whether I just stick with what I know (as the development time will increase by months if we do it natively in Progress as we have to learn it); I have done some tests where if I query a few tables and join them at the client level, then its quite quick (seconds) ... if I asks for the join (i.e. header and detail), it could take up to 3 to 5 minutes ... so I wonder if I did the same join under native progress code via VB.NET, whether it would speed it up.

Thomas
 
here's a quick test -

launch a client ( of course i mean 4gl client ) on the same machine as the database server with a self-service connection.

self-service means the clients access the database thru shared memory and not a server process.


for example - mpro -db <physical-database-name>

here's a simple static 4gl query very similar to working with arrays in other languages. fill-in the missing info and try diff variations.

<snippet>

etime( yes ). /* reset timer */

for each <header-table>
use-index <index> /* optional, i'll explain later on */
no-lock,

each <detail-table>
where <join-conditions>
use-index <index> /* optional */
no-lock:

end. /* for each */

display etime. /* display elapsed time */

</snippet>


two factors that have a substantial impact on performance and can easily effect performance by factors of 10's each are -

1. the remote vs self-service connection

2. and the order in which you scan records, let me explain.

in general, records are accessed thru indices and the index used also determines the order in which the records are accessed, right.

now, if the records are physically scattered in one order compared to contiguous in another you'll see a huge diff.

exactly the same idea as disk fragmentation as with a regular home pc.

the term is called logical scattering, fragmentation in this context means a totally diff thing, but let's leave that for now.


and that's the reason i also put the option to specify an index on the header table even though there's no filter.

usually the primary index is the least scattered. the implied meaning of primary is the index mostly used and that's why it's used for dump and load.
 
going forward - good news and bad news -

the good news is that you can stick with what you know and do best and that is .net, at least for the front end.

the bad news is that it would require release 10, but it might be well worth it in terms of results and time savings.


what i mean is that -

you can write the backend in progress using a shared memory connection ( now that's a native connection ) strong typing etc.

the application will run on an appserver and create prodatasets that will be passed back and forth to the client as ado.net

prodatasets also map to java service data objects ( sdo ) and xml incase you'd go with a diff ui or integrate with other apps etc.


there are many customers who are using this same approach, in fact it's encouraged. best of both worlds.

i think you'll find plenty of articles, videos etc. of customers using this approach.

heck, with this layered architecture you could have separate progress and .net teams working on the business logic and user interface.

i'd also recommend you take a look at the webcasts ( web events ) archive @psdn.com and since it's a serious question also bring it up @peg.com. hth
 
Thanks Everyone. I will have a look around the net for some articles; I like the .Net approach because i know its limitations so I might stick with that for the moment.

Thomas
 
joey.jeremiah said:
hi Lee :)
respect is something for turkish prisons ( it's an old joke, no offence dude ) :P
Click to expand...

Joey,

None taken. I like the quote - I've seen 'Midnight Express'. Mind you, I've also seen 'Oz', so maybe 'Respect' is a more universal prison quality. I don't aim to find out soon. I like my various orifices the size they are.

I can’t get into a debate with you for two reasons: I don’t have the time at the moment (yeah, I know, I shouldn't have made a provocative statement), and having seen some of your previous posts on the subject, I don’t have the theoretical ammunition (academic background) anyway.

But briefly: In my experience, when Progress chooses a different index to me, I’ve been wrong. When it has chosen a different index to my colleagues, they’ve been wrong.

I would be interested in you posting a couple of simple queries against the Sports database where USE-INDEX overrides the default which demonstrates your claim.

There is the other problem, that if you add another index at a later date that would serve the query better, the previous USE-INDEX clause would override it.


The only places I’ve used the clause in recent years are for FIND FIRST/LAST when trying to move to a specific position in a particular order. Other than that, I’ve avoided it – perhaps for stick-in-the-mud, prejudiced reasons – I don’t know, but I am willing to be corrected.

Lee

ps. your next post gives me some food for thought - 'logical scattering' is not something I'm familiar with, I'll look it up when I have time. I'm still sceptical though about USE-INDEX being necessary in 'normal' application programming.
 
Well,

To jump in:
In most cases use-index shouldn't be used. But on the other hand, I had to use use-index in some cases within our application to speed up things. Progress chooses the index which fits best in almost all cases. But in some cases (if you know about the contents of the table) it can be worth using use index.

For example we had a table with a lots (lots) of records and the input of the user did Progress to choose an index which was the following:
number of relation, some status code.
Unfortunately most records of this relation had the same stus code. so an almost full scan of the records belonging to this relation was made.
By using use-index I forced Progress to look into a much smaller resultset which had the effect that the query executed much (much!) faster.

Ok, I admit, you can also blame this on bad design.....

Regards,

casper.
 
:biggrin: lol, interesting option, or Danish people with sense of humour....

Casper
 
Casper,

how are you dude ?! haven't seen you around for a while, i was worried you left us.


Lee,

for what it's worth, i'll try posting a better explanation once i'll have some free time, this time i'll actually add some examples.

as for "logical scatter factor" not sure it's an official term but peggers use it all the time to describe the case.

"physical scatter factor" is in offical progress term used in the db utils etc. but it's somewhat misleading.
 
Hi Joey,

Yep, I wasn't around for a while here. --> I moved to another place in Holland.
Pretty busy at my work, but try to keep up here as much as I can... :-)

BTW: Do you mean with logical scatter factor that if a table is dumped with a certain index and is most of the time read with a different index?
Like customer table dumped with Primary key, where it's most likely read with index: name?

The physical scattering is related to the scatter factor which you see in proutil tab(db)analys. This scatter factor is an indication of the average distance between rowid's.

Greetz,

Casper.

P.S. liked the stuff with XML and word :-)
 
You must log in or register to reply here.
Back
Top