[plug] optimizing transactions on postgres thru unixODBC was: database replicatoron postgres

[Ariz Jacinto]

running transactions on postgres remotely can be
really slow (no wonder they have an elephant for
a logo) especially when you are accessing it thru
ODBC (it's like an thai elephant carrying a log).


but try fetching/executing the sql statements in
this fashion:

while(odbc_fetch())
strcat(sqlstr, field(sqlstr); // sqlstr = sql1;sql2;sql3;.......
odbc_query(sqlstr); // on remote host


rather than thru typical iterations, you will
experience a siginificant increase on speed.
that's what i did and i've lost some code structure
in trade of the speed (anyway, it's commented).

yeah, i know it's old school and superficial
but from our experience jumping from a
10 mins per 10000 rows x 100 cols to
15 secs per 10000 rows x 100 cols is awesome.
it even run faster than our (unaltered) libpq-based
code. (cant wait to apply it on libpq)

yes, it has some drawbacks that needed some
code tweaking, and debugging the sql transactions
can be a pain or impossible.

i.e. (parse error: near INSERT) and there are 10000
INSERTs for you to skim thru as they are executed
in a single line, hehehe.


but on our case, the replication server only logs the
transactional queries that are error free thus
the debugging problem mentioned, wont even exist.


HTH





_
Philippine Linux Users Group. Web site and archives at http://plug.linux.org.ph
To leave: send "unsubscribe" in the body to [EMAIL PROTECTED]

Fully Searchable Archives With Friendly Web Interface at http://marc.free.net.ph

To subscribe to the Linux Newbies' List: send "subscribe" in the body to [EMAIL PROTECTED]