Re: ECPG FETCH readahead

On Jun 24, 2010, at 2:13 PM, Michael Meskes wrote:

>> I think, yes, it does make sense. Because we are talking
>> about porting a whole lot of COBOL applications.
>
> COBOL???
>

yes, COBOL :).
it is much more common than people think.
it is not the first COBOL request for PostgreSQL hitting my desk.
in our concrete example we are using a C module written with ECPG which is magically attached to tons of COBOL code ...

>> The ESQL/C or ECPG connector was already written
>> the Informix quirks in mind, so it fetches only one record
>> at a time passing it to the application. And similar performance
>> is expected from ECPG - which excpectation is not fulfilled
>> currently because libecpg doesn't do the same caching as
>> ESQL/C does.
>
> Eh, you are talking about a program you wrote for your customer or they wrote
> themselves, right? I simply refuse to add this stuff only to fix this situation
> for that one customer of yours if it only hits them. Now the thing to discuss
> is how common is this situation.
>
> Michael

i think that this cursor issue is a pretty common thing for many codes.
people are usually not aware of the fact that network round trips and parsing which are naturally related to "FETCH 1" are a lot more expensive than fetching one row somewhere deep inside the DB engine.
out there there are many applications which fetch data row by row. if an app fetches data row by row in PostgreSQL it will be A LOT slower than in, say, Informix because most commercial database clients will cache data inside a cursor behind the scenes to avoid the problem we try to solve.

currently we are talking about a performance penalty of factor 5 or so. so - it is not a small thing; it is a big difference.

regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de