Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration - Mailing list pgsql-hackers

On Wed, 2002-03-13 at 21:18, Jean-Paul ARGUDO wrote:
> Hi all,
> 
> 
> Here are the results of our survey on a migration from Oracle 8.0 / W$
> NT4 SP5 to PostgreSQL 7.2 / Red Hat 7.2.
> 
> You'll probably remember of a thread I initiated in this list a couple
> of weeks ago, this is the same survey for the same customer. Now, the
> survey is finished.
> 
> So, we migrated all Oracle's specific syntaxes succesfully, including
> CONNECT BY statements (thanks to all hackers at OpenACS project (visit
> http://www.openacs.org) for the good code!).

Could you elaborate here ?

I know they do some of it using triggers and bitmap indexes, do you mean
this ?

> We migrated succesfully Oracle Pro*C thanks to fantastic ECPG (Thanks
> Michael).
> 
> The overall performance of PostgreSQL, is 33% slower than the Oracle/Nt
> solution. One must say we faced a well tuned Oracle, tuned for best
> performance. Even SQL queries were very well tuned, using Oracle
> pragmas for example (ex: %USE HASH foobar%).
> 
> Since our customer accepted up to 50%, this is a success for us,
> technicaly on this point.
> 
> BUT, we faced a real problem. On some batches, in ECPG, Pro*C
> structures uses intensively CURSORs loops. In Oracle, CURSORs
> can be PREPARED. Thus, it seems Oracle only computes once the query plan
> for the cursor, even if it is closed and re-opened. Maybe some kind of
> stored query plan / caching / whatever makes it possible.

What kind of work do you do in these cursors ?

Is it inserts, updates, deletes, complicated selects ...

> This seems not be the case in ECPG. In each COMMIT, the cursors are
> closed (they dont even need to close cursors in Oracle!). And at each
> BEGIN TRANSACTION PostgreSQL seems to compute again parsing and query
> plan..
> 
> So this finaly makes the batch work taking 300% the time Oracle needs.
> We clearly see our ECPG programs waits for PostgreSQL in the functions
> were CURSORs are opened. Then, we know the problem is not in ECPG but in
> PG backend.

Could you make ona sample test case with minimal schema/data that
demonstrates this behaviour so I can try to optimise it ?

> This is unaceptable for our customer. Many batches are launched during
> the night and have to be completed in 5h (between 0h and 5h). With a
> ratio of 3, this is not worth think about migration anymore :-(
> 
> We know we could have much better performances with something else than
> ECPG, for example, using C or TCL stored procedures, placing the
> SQL work wuch closer from the PG backend, using SPI, etc...

Did you do any tests ?

How much faster did it get ?

> But this is 
> not possible. We have to make it under ECPG, there are tons of Pro*C 
> code to migrate, and we must make it the same. With ECPG/Pro*C compiled 
> programs, we can stop executions, renice programs, etc, what we would 
> loose putting work in stored procedures.

AFAIK some SQL/C type precompilers and other frontend tools for other
databases do generate stored procedures for PREPAREd CURSORs.

I'm afraid ECPG does not :(

But making ECPG do it might be one way to fix this until real prepared
queries will be available to frontend.

> So, I'd really like some of you validate this thing about cursor. We
> have a strange feeling blended of pride for only a 1,33 ratio face to
> the giant Oracle, and a feeling of something unfinished, because only 
> of a feature not yet implemented...
> 
> I read many times the current TODO list. I think our problem is
> somewhere between the CURSOR thread and the CACHE thread in the TODO.
> 
> We would really appreciate some of you validate this behaviour about
> CURSORs, this would validate we didn't spent 40 day/man for nothing, and
> that we reached a certain good explanation of the problem, that we have
> not dug just next to the treasure.

The treasure is currently locked up in backend behind FE/BE protocol 

---------------------
Hannu