Re: best performance for simple dml

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">very nice pointers.
 thankyou very much!<br /><br />--- On <b>Mon, 6/27/11, Pavel Stehule <i><pavel.stehule@gmail.com></i></b>
wrote:<br/><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br
/>From:Pavel Stehule <pavel.stehule@gmail.com><br />Subject: Re: [SQL] best performance for simple dml<br />To:
"chesterc young" <chestercyoung@yahoo.com><br />Cc: pgsql-sql@postgresql.org<br />Date: Monday, June 27, 2011,
1:05AM<br /><br /><div class="plainMail">2011/6/27 chester c young <<a href="/mc/compose?to=chestercyoung@yahoo.com"
ymailto="mailto:chestercyoung@yahoo.com">chestercyoung@yahoo.com</a>><br/>><br />> two questions:<br />> I
thoughtcopy was for multiple rows - is its setup cost effective for one row?<br /><br />I expect it will be faster for
onerow too - it is not sql statement<br /><br />if you want to understand to performance issues you have to understand
to<br/><br />a) network communication costs<br />b) SQL parsing and SQL planning costs<br />c) commits costs<br />d)
othercosts - triggers, referential integrity costs<br /><br />><br />> copy would also only be good for insert or
select,not update - is this right?<br /><br />sure,<br /><br />If you need to call a lot of simple dml statement in
cycle,then<br /><br />a) try tu move it to stored function<br />b) if you can't to move it, then ensure, so statements
willbe<br />executed under outer transaction<br /><br />slow code<br /><br />for(i = 0; i < 1000; i++)<br /> 
exec("insertinto foo values($1), itoa(i));<br /><br />10x faster code<br /><br />exec('begin');<br />for(i = 0; i <
1000;i++)<br />  exec("insert into foo values($1), itoa(i));<br />exec('commit');<br /><br />Regards<br /><br />Pavel
Stehule<br/><br />><br />> --- On Mon, 6/27/11, Pavel Stehule <<a
href="/mc/compose?to=pavel.stehule@gmail.com"ymailto="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>>
wrote:<br/>><br />> From: Pavel Stehule <<a href="/mc/compose?to=pavel.stehule@gmail.com"
ymailto="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>><br/>> Subject: Re: [SQL] best performance
forsimple dml<br />> To: "chester c young" <<a href="/mc/compose?to=chestercyoung@yahoo.com"
ymailto="mailto:chestercyoung@yahoo.com">chestercyoung@yahoo.com</a>><br/>> Cc: <a
href="/mc/compose?to=pgsql-sql@postgresql.org"ymailto="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a><br
/>>Date: Monday, June 27, 2011, 12:35 AM<br />><br />> Hello<br />><br />> try it and you will see.
Dependson network speed, hw speed. But the most fast is using a COPY API<br />><br />> <a
href="http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html"
target="_blank">http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html</a><br/>><br />> Regards<br
/>><br/>> Pavel Stehule<br />><br />><br />> 2011/6/27 chester c young <<a
href="/mc/compose?to=chestercyoung@yahoo.com"
ymailto="mailto:chestercyoung@yahoo.com">chestercyoung@yahoo.com</a>><br/>><br />> what is the best
performance/ best practices for frequently-used simple dml, for example, an insert<br />> 1. fast-interface<br
/>>2. prepared statement calling "insert ..." with binary parameters<br />> 3. prepared statement calling
"myfunc(..."with binary parameters; myfunc takes its arguments and performs an insert using them<br />><br
/></div></blockquote></td></tr></table>