Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 226E0476AF2 for ; Mon, 13 Jan 2003 08:08:16 -0500 (EST) Received: from clearmetrix.com (unknown [209.92.142.67]) by postgresql.org (Postfix) with ESMTP id 1880E476AA2 for ; Mon, 13 Jan 2003 08:08:15 -0500 (EST) Received: from clearmetrix.com (chw.muvpn.clearmetrix.com [172.16.1.3]) by clearmetrix.com (8.11.6/linuxconf) with ESMTP id h0DD8DM29519; Mon, 13 Jan 2003 08:08:13 -0500 Message-ID: <3E22BA32.3030905@clearmetrix.com> Date: Mon, 13 Jan 2003 08:08:02 -0500 From: "Charles H. Woloszynski" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Neil Conway Subject: Re: [PERFORM] join over 12 tables takes 3 secs to plan References: <3E160BC0.3020900@clearmetrix.com> <1042333405.386.49.camel@tokyo> <3E218F2B.8030107@clearmetrix.com> <1042399760.362.26.camel@tokyo> In-Reply-To: <1042399760.362.26.camel@tokyo> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/144 X-Sequence-Number: 5994 Neil: Thanks for the feedback. I've attached my original text to this note and re-posted it back to pgsql-jdbc to make sure that they are aware of them. I look forward to this new and improved server-side preparation. Charlie Neil Conway wrote: >On Sun, 2003-01-12 at 10:52, Charles H. Woloszynski wrote: > > >>As I understand it, the functions I am waiting for are targeted into 7.4 >>(but I'd love to see them early and do some testing of those for the >>community). >> >> > >Ok -- those are pretty much all features on the JDBC side of things (not >the backend implementation of PREPARE/EXECUTE). I'm not sure how much of >that is planned for 7.4: if you haven't talked to the JDBC guys about >it, they may not be aware of your comments. > >Cheers, > >Neil > > Charles H. Woloszynski wrote: > Neil: > > I think that general use of this feature should be enabled using the > URL, not with an API call. We use a JDBC connection pool and it will > help tremendously to have the pool set to user server-side preparing > without having to downcast the connection to a PG connection (which I > think is an issue because of the facade in our connection pool code). > The second item is that of compatibility. If the new code cannot > handle all statements (eg. something with a semi in it) and disable > the generation of a 'prepare' then we cannot count on the URL > functionality. As I understand it, the programmer is required > currently to enable/disable the server-side functionality by hand and > only when the statement to be prepared is not composite (statement1; > statement2; statement2). > > But in our real-world application space, we use a connection pool with > a facade, so getting to the actual connection to enable this is > problematic (and forces postgresql-specific coding into our framework > where it is not particularly welcome). If we overcame this issue, we > would then need to hand-manage the enable/disable to only be used when > the statement is appropriately formulated (e.g., no semicolons in the > statement). > > If we could get URL enabling and auto-detection of statements that > won't work (and hence disable the enabled function for these > functions), I think we have a solution that can be deployed into > 'generic' app server environments with just configuration changes. > That is, an operations person could enable this feature and monitor > its impact on performance to see if/how it helps. That is a BIG win > (at least to me) and a HUGE marketing item. I'd love to test MySQL > with some joins over JDBC with PostgreSQL with some joins using > prepared statements and be able to demonstrate the big improvement > that this makes. > > As I understand it, the functions I am waiting for are targeted into > 7.4 (but I'd love to see them early and do some testing of those for > the community). -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com