On 8/5/06, <b class="gmail_sendername">Alan Knight</b> <<a href="mailto:knight@acm.org">knight@acm.org</a>> wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
OK, I've joined this list now. I'll respond to a couple of comments in various messages.</blockquote><div><br>Cool! :-)<br><br><br> <br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
---------------------<br>In a different part of the thread, Jeremy Shute shutej at <a href="http://crazilocks.com">crazilocks.com</a> wrote:<br><br><br>But, looking at the process browser, the messages in the GLORP-operating
<br>thread seem to relate to the assembly of queries. The cursor would<br>eliminate the READ queries, but the WRITE queries are still necessary,<br>requiring assembly one-at-a-time, unless GLORP is smart enough to use a<br>
global prepared statement underneath for insertions. I see no reason why it<br>shouldn't be -- insertions are very homogenous operations, unlike WHERE<br>clauses. Perhaps a cursor WOULD solve the problem?<br>-----------------
<br><br>Glorp does group insertions together. This can give some very large performance improvements, e.g. if it can use Oracle array binding over a high-latency connection. However, for Postgresql there isn't any directly analogous mechanism. The mechanisms I saw available were a) just concatenating the inserts together in a single large statement with semicolons separating them. This helps with network traffic, but doesn't really tell the database it's doing many similar inserts. b) The bulk load mechanism. This seemed very much aimed at loading data from files, and looked like it might be possible to use more directly, but quite awkward. I haven't done this.
</blockquote><div><br>Yes, neither option seems to be very fun. I wonder if there's a way to hack up the internal Postgres mechanisms to talk more directly to Squeak, like an INSERT INTO with a subselect clause, and provide a veneer where Squeak offers the contents of the subselect over the wire? It would be a monstrous, nasty, warty thing, but at least it could take advantage of the homogeneous nature of the data. I know some people have written code using remote tables, if you could trick Postgres into thinking the running Squeak image was a remote table WITHOUT hacking a plugin, you could probably get more expedient bulk inserts...
<br><br><br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">So right now on Postgresql (and others) Glorp will just concatenate the inserts together (up to a limit). One thing to watch out for from a performance perspective is to turn binding off for Postgresql. At least in the VW driver, binding was implemented by taking the queries, splitting them up into strings, and then concatenating the string representation of the bound values into the query. If you have a query with a very long string (
e.g. because it's a large number of inserts being done all at once) then this became a huge performance bottleneck, and doesn't even seem to offer any of the benefits of binding in terms of not needing to convert back and forth between string representations.
</blockquote><div><br>Are you saying turning binding off will help my GLORP performance?<br><br>I'm a little dense - I read this and I think you're saying "binding" is tokenizing the query, hunting for '?', and performing string concatenation if you're issuing a prepared statement. Also, does the driver do piecemeal ',' concatenation, or StringBuffer-like appends?
<br><br><br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Glorp will also keep a cache of prepared statements and re-use them if the hooks are in place, but I wasn't aware of how to do that for Postgresql.
</blockquote><div><br>This sentence is what's confusing me about the above... It makes it sound like GLORP does not use prepared statements on Postgres, in which case, my understanding of turning off "binding" must be askew... If you're not issuing prepared statements, why would turning off "binding" help?
<br><br><br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> I suspect it's less important in such a situation, in that you'd have to have binding done better in order to effectively reuse prepared statements. I also have a feeling, although I haven't measured, that Postgresql is more tolerant of dynamic SQL for performance than, say, Oracle, which really really wants you to use prepared statements.
</blockquote></div><br>Still, I think of the prepared statement as a way to construct a partial abstract syntax tree on the server side, once, and plug in missing sections. In turn, this means you ship a handle to this server-side object instead of a much larger stream of characters (or boxed symbols and strings, I don't know how the driver works), which has to get parsed into an abstract syntax tree at the server for every inserted row. Even on localhost over a pipe, a prepared statement must be preferable for any RDBMS, right? (As a side note, do you know WHY the difference on Oracle so dramatic -- are they doing less optimization in the driver than Postgres and therefore shipping more data?)
<br><br><br><br>Jeremy<br>