Conference ulysse::rdb_vms_competition

1118.0. "Need some help: Ingres v. RdB performance" by NSDC::RATCLIFF (Heisenberg may have been here) Mon Mar 23 1992 21:03

I'm in a situation where some Ingres knowledge would help.

A 3rd-party sw. house has developed a banking application sw. which I have
been asked to evaluate in terms of performance (among other things).

The sw. is written in C, on VMS, using embedded SQL calls to Ingres (V6.3).
Approx. 150 relations, 400 C modules, 300 Klines of code. All access to the dbms
is done with ~150 access modules, one per relation; I don't have all the code, but
the one access module I have is quite small, 250 lines. Users complain about
performance even with very few users logged in. I've tried a demo of it, alone on
a Vax 6240, with a small database; the performance already seems lousy.

I've read reports on discussions with the 3-rd party and looked at a little
bit of the code. It seems that the features of an rdbms are not used at all.
In particular:
- update locking is done by means of a field in the relation in the application code;
- the only use of SQL seems to be select-from-where, with exact match
  or >= match;
- joins are not used, but coded in embedded read loops on cursors opened
  throughout the loop;
- the db design is not normalised at all. For instance, if some kind of transaction
  has a daily amount to be calculated through 90 days, the relation will contain
  90 fields called something like amountday01 to amountday90.

Total re-design and re-write seem to be out of the question for the time being,
but "a certain amount" of modifications would be acceptable.


Questions:

1. Would converting the software to a use of RdB with embedded C-SQL
offer a reasonable performance boost? I've read the various topics in this
conference that could provide some help in doing it. I'm worried, though,
that the poor use of an rdbms as an ISAM file system would still be a
major penalty.

2. Would a TP monitor offer a performance boost?

3. There are no start transaction calls prior to accessing Ingres; does
that mean that Ingres starts a read/write transaction every time?

4. Is Ingres' embedded SQL evaluated/parsed/whatever at run or
pre-compilation time? What does RdB do in this respect?

5. I have a meeting with the 3rd party this week; are there specific questions
I could ask?

Of course, I need the answers by yesterday... Any comments welcome.

Thanks in advance for any help, John.

    
    
    
      
    My 2 penneth -
    
Questions:

1. Would converting the software to a use of RdB with embedded C-SQL
offer a reasonable performance boost? I've read the various topics in this
conference that could provide some help in doing it. I'm worried, though,
that the poor use of an rdbms as an ISAM file system would still be a
major penalty.

    There seems to be a bit of a question mark around the performance of
    Ingres, you are not the first to complain of this. Having said that 
    the design/flat file approach etc is a little bizarre and will not 
    be helping.
    
    Rdb does offer additional features which will help with multi-user 
    performance, row level locking for instance. BTW how many concurrent 
    users are they hoping to put on the system ? 
       
    I'm sure it will be a little more complicated than just pulling Ingres
    out and pushing Rdb in. Have they stuck rigidly to ANSI-SQL and avoided
    any Ingres extensions or used any Ingres db facilities ?
    
2. Would a TP monitor offer a performance boost?

    Probably not. As I recall the Ingres architecture offers multi-server 
    support but TP1 benchmarking tests have shown that the best performance 
    is obtained with a single server. Consequently all locking/concurrency
    issues will be managed efficiently by this single multi-threaded 
    procedure avoiding the overhead of x-process communication to resolve
    lock conflicts.
    
    Also you cannot just plug ACMS in, the application has to be designed
    with ACMS in mind. Implementing it would be a major exercise. 
    
        
    
3. There are no start transaction calls prior to accessing Ingres; does
that mean that Ingres starts a read/write transaction every time?

    Dont know.
    
4. Is Ingres' embedded SQL evaluated/parsed/whatever at run or
pre-compilation time? What does RdB do in this respect?

    Rdb SQL is converted to compilable code at pre-compilation time. The
    code is the compiled and linked like any 3gl application. Queries are 
    optimised at run-time the first time they are executed.
    
5. I have a meeting with the 3rd party this week; are there specific questions
I could ask?
    
    I would be a little concerned about the viability of the application.
    Maybe there are some Ingres tricks that may help.
    
    Good luck.
     
     Charles