Florian G. Pflug wrote:
< snipped code of stored procedure >
Are you aware of the "insert into <table> (<field1>, ..., <fieldn>)
select <val1>, .., <valn> from ...."
command? It'd be much faster to use that it it's possible...
greetings, Florian Pflug
It did faster. Thank you Florian. Could you hint me why "insert into
.. select " is faster than a cursor transaction please?
Well, you're avoiding a lot of overhead. "insert into ... select from .."
is just one sql-statement. Of course, postgres internally does
something similar to your stored procedure, but it's all compiled
C code now (instead of interpreted plpgsql). Additionally, postgres
might be able to optimize this more than you could from plpgsql, because
you're restricted to the api that is exposed to plpgsql, while the
backend-code
might be able to "pull a few more tricks".
In general, if you have the choice between looping over a large result
in a stored procedure (or, even worse, in a client app) and letting the
backend do the looping, then letting the backend handle it is nearly
always
faster.
The information are very helpful! Thank you again Florian.
If now, I have a series of queries to be run:
1. "insert into t1... (select .. from ...left join ... .. where ....) "
2. "insert into t2 ... the same sub-query as in 1 "
3. "update t3 set ... from ( the same sub-query as in 1) AS X where
t3.pk = X.pk "
4. "update t4 set ... from ( the same sub-query as in 1) AS X where
t4.pk = X.pk"
. the subquery (select .. from ...left join ... .. where ....) is two
big tables doing left join
Will there be a better way between
a. put all there 4 queries into one function
in perl or java, just call this function
b. in perl / java, write and run the 4 queries independently
The pl/pgsql function does not allow commit. So, in the function , if
any step went wrong, all 4 steps rollback. While in java, after every
query, I can do commit. May java speed up all four updates?
- Ying
