next »

[Benjamin G.]

Hello, What is the balance between a mutlti-main application design and database resources. If we want to obtain a modular application, the ideal would be to have a multitude of small "main modules" so that the user can organize these applications as he wishes. The downside is that each app consumes database connections which is not ideal. My question is therefore to know if it would not be possible in genero to share the same connection, like connection pool or passing by parameter the "connect object" so we can share one "connection" ...

Regards

[Sebastien F.]

Hello Benjamin,

You make a good point: SQL connections can have a cost (user licenses, RAM)

First, you need to consider the type of database that you use (client side AND server side):
An SQL connection to Oracle costs more resources than Informix or PostgreSQL for example.

Then, understand that the concept of connection pooling implies that you need to deal with SQL statements quite differently as today:
Behind the scene, there are still regular SQL connections that are used to implement connection pooling.
One SQL connection can be shared between programs, that requires to make it kind of stateless.
To use connection pooling, an application needs to ask to pool for a free connection and release the connection, each time it needs to do some SQL transaction.
Imagine that you would have to surround with a CONNECT TO and a DISCONNECT, each piece of code doing some SQL... ready to review all your code?
This cannot be automagically done by Genero and the ODI layer:
It's in the hands of the programmer, to decide when a connection is required and when it can be released.
Furthermore, any open cursors will have to be closed as cursors are linked to a connection and should not be shared between programs.
And what about temporary tables, that do also below to an SQL connection?
These should also be dropped, before passing the connection to another application.
What if you change some SQL connection settings/attributes as with Oracle's ALTER SESSION command?
etc...
Today you don't care about all of that because an SQL connection is reserved for each program instance, and it is state-full.

I see 2 options today to save SQL connections:

1) Have medium-sized applications grouping tasks by domain (with multiple forms)
2) Have a multi-tier architecture using Web Services, where connection pooling is kind of implicit, since each WS server program connects once to provide multiple WS clients

Seb

[Sebastien F.]

You could also consider to use modularity as much as possible with IMPORT FGL:
Implement small tasks (like parameter tables input) in a single .4gl module that can be reused by other programs, so that a main program implementing a common task (like order input), can call a function of that secondary module.
The .42m module will not be loaded until a function of that module is called. So as long as it's not used, the secondary module will not consume resources.
You could in fact write "huge" programs doing IMPORT FGL with many secondary modules.
Seb

[Benjamin G.]

Hello Sebastien,

A management application is generally structured around a menu from which programs are executed with an interactive dialogue between the user and the database. There are 2 possibilities, either you make a "CALL ..." type call or a "RUN ...".

In the first case we have a "monolithic" application with one main and a lot of functions, this design allows to have a single DB connection and to be able to share variables between programs but does not allow having several "dialogs" open in parallel which for the user is frustrating because he has to close one "dialog" to open another.

We did some tests with the "parallel dialogs" but without success.

In the second case, an application split into a multitude of "main" we gain user flexibility, it can have several dialogs in parallel, but
to the detriment of a high number of DB connections and no "direct" data sharing between the different "main". Another idea might be to be able to launch a main module not as an independent process but as a "fork" of the "main menu" this would allow to have a single connection to the DB (at least with Informix, feature is supported) and to be able to share data.

We could therefore imagine having a sort of "RUN AS CHILD" OR "FORK" in complement to "CALL" and "RUN WITHOUT WAITING" ...

Just an idea

Regards

[Sebastien F.]

Hello,

To woek efficiently, I can understand that a single user can keep 2-4 applications in parallel (implying 2-4 SQL connections)...

However, I like also the fact that in a single application, a new window/form and dialog controlling that window/form will make previous windows unusable, to have a stack of windows/forms and force the user to go backwards in that tree.

With tons of little applications running in parallel, be sure that end users will forget to close some applications and get lost in their windows.

I believe the solution is a good compromise of both...

Your suggestion of a new kind of RUN forking a new child process with all the context of the parent (and especially sharing the SQL connection) is not realistic to me: Quite too difficult regarding FGL runtime system, and actually impossible for SQL management because database client API don't allow this AFAIK... I would be interested to read some official documentation that says that an Informix client can fork a child process and reuse the same SQL connection...

In the top parent "management application" you can maybe close the SQL connection after loading all needed SQL data (app user permissions, etc) ...

Seb

[Reuben B.]

...

In the first case we have a "monolithic" application with one main and a lot of functions, this design allows to have a single DB connection and to be able to share variables between programs but does not allow having several "dialogs" open in parallel which for the user is frustrating because he has to close one "dialog" to open another.

We did some tests with the "parallel dialogs" but without success.

In the second case, an application split into a multitude of "main" we gain user flexibility, it can have several dialogs in parallel, but
to the detriment of a high number of DB connections and no "direct" data sharing between the different "main". Another idea might be to be able to launch a main module not as an independent process but as a "fork" of the "main menu" this would allow to have a single connection to the DB (at least with Informix, feature is supported) and to be able to share data.

...

I would've said that the overwhelming majority of the code I see follows the second case.  At any given point a single user may have multiple Genero programs running, each program having its own private variables and each program having its own database connections.  I can't remember when I last saw your first "monolithic" case.

A typical scenario might be that a store worker might be running
"Main Menu" - program they launch at the beginning of the day from which they launch other programs"
"Sales Entry" - a program to enter new sales
"Debtor Enquiry" -a program they can use to check debtor balances, either launched from Main Menu or from Sales Entry passing a debtor code as an argument
"Stock Enquiry" - a program they use to check stock balances, either launched from Main Menu or from Sales Entry passing a product code as an argument

so this would be 4 seperate database connections, quite possibly 4 copies of the same prepared or declared database cursor, and 4 instances of the same variables e.g. username

Over the course of the day, user might start a few more programs
"Transfer Entry" - to transfer some stock in
"Purchases Entry" - to order some stock
"Customer Maintenance" - to update customer details
etc etc

and I guess the scenario you are worried with this model is by the end of the day, user has all these program open sitting idle but consuming a database connection.

You can be smart about your database connections.  As Seb said for case of Main Menu, once the menu is loaded, do you still need the database connection?

Similarly for each program, by design encourage the user to exit the program when they have finished rather than perhaps going to a point ready to enter a new transaction and waiting, so that you don't get lots of programs sitting idle.  Use ON IDLE to exit the program etc.

This has reminded me of an early Genero transformation where the database started to slow down.  This was because the  customer had taken the opportunity to tidy up the code so that database cursors were used rather than static SQL statements preparing the same database operation multiple times.  However this resulted in hundreds of Point of Sale terminals throughout the country sitting at an INPUT login, password consuming database memory whilst waiting for the next store worker to come along and process a sale.  The solution was to make sure FREE, CLOSE were used to free up resources when program got to this point, that is did we want to hold onto these resources for  minutes to save a second or two?  15 years later, I probably also would suggest now using Web Services as a solution so ...

as Seb suggested, you might want to consider a multi-tier solution, add to your tests a third case ' an application split into a multitude of "main" we gain user flexibility,' like you had, but code it so that each application has no database connection but any database activity is done via a Web Service call.  I could see this being advantageous in a store or branch situation where you have many instances of the same program, a limited number of SQL statements executed multiple times, and so instead of having 100's, and 1000's of these programs each with their own database connection, and copies of the same database cursor, you end up with a pool (probably numbering in single digits) of web service programs running that have the database connection and cursor in memory.  You are then looking at the numbers to measure if the extra overhead in the web service call is worth it in terms of what you save in database resources.

Reuben






 

[Benjamin G.]

Hello Sebastien, Reuben

Today we have accustomed users to opening dozens of "pages" in "browsers".
I'm not sure it's a good way to work, but it's a reality.
A user wants to be able to open, for example, two "item code" to compare the differences ...

I understand that "forking" is not necessarily a solution and certainly too specific to UNIX.

The WebService solution is indeed an "elegant" solution but which has a high cost in terms of development and maintenance.
I reserve the WS when the data must undergo a pre-processing before being consumed.

There are other "problems" in not making a "monolithic" application this is the number of processes that must be managed on the server. Example, if I have 500
users and each opens say 6 "main programs" (item management, customer management, phone order entry, FAX order entry, order viewing, statistics, ...)
this already gives me 3000 processes in addition to all the other processes.

When i need to do server or DBA maintenance this high number of "processes" quickly becomes a "headache".

We use internally infomix 4gl  with "monolithic" and "multiple-main" models, but without the possibility of opening several at the same time.

The first is "very good" because the system resources are easy to control, the second is also "light" and only has the default of doing a lot of CONNECT / DISCONNECT. But these two models are "old fashion" and our users needs 2 or more logins so they can launch multiple instances of the same application. Example, a vendor needs a login to process an order entry by phone, an order one for a FAX order and an other one for "maitenance or statistics" purpose ...

This is why the concept of "parallel dialog", although we may not have understood the concept, seemed to us an excellent compromise. According to our tests the only thing that would have to be added to make it work
in order to have a "monolithic" application with "calls" is an "event" which could be triggered when
the user "activates" the dialog. This would be possible if at the moment the "windows" window changes to "foreground".
In other words, in the "active dialog" have an "ON ACTIVATE" event with in parameter the name of the dialog which returns in foreground in order to be able to
connect to the "dialog" of the window that has been chosen by the user.

Regards