Pro*C and Multi-threading on Solaris 2.51: Performance problem - sample~1.gz (0/1)

From: PHILIPS TOM <PHILIPST_at_BTMAA.BEL.ALCATEL.BE>
Date: 1997/10/23
Message-ID: <344f1d83.9504960@news>#1/1

Dear all,

I am currently involved in de development of database applications under Solaris 2.5 using the PRO*C embedded SQL pre-compiler using Multi-threaded access.

I am currently using the multi-threaded PRO*C directives to access a database, which are :

EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT ALLOCATE :ctx;
EXEC SQL CONTEXT USE :ctx;
EXEC SQL CONTEXT FREE :ctx;

The problem is that performance is terrible. I wrote a little test program (single threaded) which uses the above directives and does a very simple database manipulation.

This is a simple select on a single table using a key. The test program source code, makefile and sql script to create the table are attached to this mail (gzip + tar).

The .pc file is compiled with option threads=yes to enable the above directives.

The problems seems to be the 'EXEC SQL ENABLE THREADS' line. When I comment this out (together with the CONTEXT lines) and compile with threads=no, the programs runs 100 times faster !@#$%

This means I would have to startup at least 100 threads to get the same performance as with one thread without the thread directives.

Another thing I tried is compiling with threads=no and putting in the 'EXEC SQL ENABLE THREADS' while commenting out the
'CONTEXT' lines.

The sample program runs slow!!!! So it is realy this
'EXEC SQL ENABLE THREADS' line that screws up everything.

The CPU is not working at all. So my client ap. is just blocking 99% of the time.

Since it is a single threaded sample application, the problem (in my humble oppinion) has nothing to do with Multi-Threading as such.

Another (minor) problem is that the PRO*C compiler crashes when I pre-compile with option threads=yes and none of the above directives are present in the code (all commented out). But this isn't the real problem ofcourse.  

Question:

---

Does anybody of you, hardworking people, have any ideas how to increase the performance of this thing?
Is there any patch available?
Is there any other (fast) method that I can use.

Remarks:

---

I've tried using multi-threading NOT using the runtime contexts. Each thread logs on to the database seperatly. Problem: As soon as two threads access the database at the same time, the whole lot just crashes. Talk about thread safety.

When I compile with threads=no, the supposed thread save functions are also used (ending with a 't').

I've tried the sample application under Windows-NT, and there the problem does NOT exist!!!
Under NT, when I don't use the runtime contexts, EVERYTHING WORKS FINE (no crashes)!!!
When I do, it runs at the same speed as without. So under NT, you don't HAVE to use these g.d. contexts.

Some info:

---

Solaris version: 2.5.1 on Ultra1
Oracle version: 7.3.2.3.0
PRO*C version: 2.2.2.0.0 Received on Thu Oct 23 1997 - 00:00:00 CDT