Thread
-
Re: 7.3 Prepared statements
Charles H. Woloszynski <chw@clearmetrix.com> — 2003-01-03T04:18:02Z
Bruce: Do you mean that, under JDBC with PG7.3, it will use the stored execution plan, or that we should ask the JDBC driver maintainers to work to use this feature to use the stored executiong plan? I am obviously interested in helping make this happen to improve the overall PostgreSQL solution. Should I contact someone specific about this to see if there are plans to make this happen in the near future? I think I have a solid framework to test its effectiveness with decent performance monitoring tools already in place. Thanks, Charlie Bruce Momjian wrote: >Sorry, I don't know if it does that yet, but I am sure it will if it >doesn't already. > >--------------------------------------------------------------------------- > >Charles H. Woloszynski wrote: > > >>I realize that this is a bit off topic, but your answer got me thinking. >> Do JDBC Prepared statements get the same saved execution plan support? >> We currently use PreparedStatements in our framework for JDBC access. >> We currently do not retain the PreparedStatement between uses, but if >>PostreSQL caches the execution plan, we may need to change our design. >> >>Thanks, >> >>Charlie >> >> >>Bruce Momjian wrote: >> >> >> >>>A view is just syntaxic sugar added to a query that references the view. >>>A preparted statement actually saves the execution plan for repeated >>>execution. >>> >>>--------------------------------------------------------------------------- >>> >>>Francisco J Reyes wrote: >>> >>> >>> >>> >>>>On 17 Dec 2002, Neil Conway wrote: >>>> >>>> >>>> >>>> >>>> >>>>>On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: >>>>> >>>>> >>>>> >>>>> >>>>>>1. What is the lifetime of the plan created? >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>From the PREPARE reference page: >>>>> >>>>>Prepared queries are stored locally (in the current backend), and only >>>>>exist for the duration of the current database session. >>>>> >>>>> >>>>> >>>>> >>>>What is the difference/advantage between a prepared query and a view? >>>> >>>> >>>>---------------------------(end of broadcast)--------------------------- >>>>TIP 4: Don't 'kill -9' the postmaster >>>> >>>> >>>> >>>> >>>> >>> >>> >>> >>> >>-- >> >> >>Charles H. Woloszynski >> >>ClearMetrix, Inc. >>115 Research Drive >>Bethlehem, PA 18015 >> >>tel: 610-419-2210 x400 >>fax: 240-371-3256 >>web: www.clearmetrix.com >> >> >> >> >> >> >> >> > > > -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com
-
Re: [GENERAL] 7.3 Prepared statements
Bruce Momjian <pgman@candle.pha.pa.us> — 2003-01-03T04:47:05Z
I would ask on the jdbc lists. I am Cc'ing them, and removing the general list. --------------------------------------------------------------------------- Charles H. Woloszynski wrote: > Bruce: > > Do you mean that, under JDBC with PG7.3, it will use the stored > execution plan, or that we should ask the JDBC driver maintainers to > work to use this feature to use the stored executiong plan? > > I am obviously interested in helping make this happen to improve the > overall PostgreSQL solution. Should I contact someone specific about > this to see if there are plans to make this happen in the near future? > I think I have a solid framework to test its effectiveness with decent > performance monitoring tools already in place. > > Thanks, > > Charlie > > > > > Bruce Momjian wrote: > > >Sorry, I don't know if it does that yet, but I am sure it will if it > >doesn't already. > > > >--------------------------------------------------------------------------- > > > >Charles H. Woloszynski wrote: > > > > > >>I realize that this is a bit off topic, but your answer got me thinking. > >> Do JDBC Prepared statements get the same saved execution plan support? > >> We currently use PreparedStatements in our framework for JDBC access. > >> We currently do not retain the PreparedStatement between uses, but if > >>PostreSQL caches the execution plan, we may need to change our design. > >> > >>Thanks, > >> > >>Charlie > >> > >> > >>Bruce Momjian wrote: > >> > >> > >> > >>>A view is just syntaxic sugar added to a query that references the view. > >>>A preparted statement actually saves the execution plan for repeated > >>>execution. > >>> > >>>--------------------------------------------------------------------------- > >>> > >>>Francisco J Reyes wrote: > >>> > >>> > >>> > >>> > >>>>On 17 Dec 2002, Neil Conway wrote: > >>>> > >>>> > >>>> > >>>> > >>>> > >>>>>On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: > >>>>> > >>>>> > >>>>> > >>>>> > >>>>>>1. What is the lifetime of the plan created? > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>>From the PREPARE reference page: > >>>>> > >>>>>Prepared queries are stored locally (in the current backend), and only > >>>>>exist for the duration of the current database session. > >>>>> > >>>>> > >>>>> > >>>>> > >>>>What is the difference/advantage between a prepared query and a view? > >>>> > >>>> > >>>>---------------------------(end of broadcast)--------------------------- > >>>>TIP 4: Don't 'kill -9' the postmaster > >>>> > >>>> > >>>> > >>>> > >>>> > >>> > >>> > >>> > >>> > >>-- > >> > >> > >>Charles H. Woloszynski > >> > >>ClearMetrix, Inc. > >>115 Research Drive > >>Bethlehem, PA 18015 > >> > >>tel: 610-419-2210 x400 > >>fax: 240-371-3256 > >>web: www.clearmetrix.com > >> > >> > >> > >> > >> > >> > >> > >> > > > > > > > > -- > > > Charles H. Woloszynski > > ClearMetrix, Inc. > 115 Research Drive > Bethlehem, PA 18015 > > tel: 610-419-2210 x400 > fax: 240-371-3256 > web: www.clearmetrix.com > > > > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: 7.3 Prepared statements
Lincoln Yeoh <lyeoh@pop.jaring.my> — 2003-01-03T08:24:31Z
I've been wondering about that for Perl DBI/DBD too. Link. At 11:18 PM 1/2/03 -0500, Charles H. Woloszynski wrote: >Bruce: > >Do you mean that, under JDBC with PG7.3, it will use the stored execution >plan, or that we should ask the JDBC driver maintainers to work to use >this feature to use the stored executiong plan? > >I am obviously interested in helping make this happen to improve the >overall PostgreSQL solution. Should I contact someone specific about this >to see if there are plans to make this happen in the near future? I think >I have a solid framework to test its effectiveness with decent performance >monitoring tools already in place.
-
Re: [GENERAL] 7.3 Prepared statements [Viruschecked]
Patric Bechtel <bechtel@ipcon.de> — 2003-01-03T09:35:16Z
On Thu, 2 Jan 2003 23:47:05 -0500 (EST), Bruce Momjian wrote: > >I would ask on the jdbc lists. I am Cc'ing them, and removing the >general list. > >--------------------------------------------------------------------------- > >Charles H. Woloszynski wrote: >> Bruce: >> >> Do you mean that, under JDBC with PG7.3, it will use the stored >> execution plan, or that we should ask the JDBC driver maintainers to >> work to use this feature to use the stored executiong plan? >> >> I am obviously interested in helping make this happen to improve the >> overall PostgreSQL solution. Should I contact someone specific about >> this to see if there are plans to make this happen in the near future? >> I think I have a solid framework to test its effectiveness with decent >> performance monitoring tools already in place. >> >> Thanks, >> >> Charlie As I dipped my nose a bit deeper into that the last few days, I think I can tell you that the JDBC driver (7.3.1 and the current CVS) doesn't use server side prepared statements automatically. I changed that with a patch I announced yesterday. As soon as it will be accepted, it will. If you want to try it out *NOW*, you should to something like this: ((org.postgresql.jdbc1.AbstractJdbc1Statement)statement).setUseServerPrepare(true); It's kind of clumsy, and does clue your app to pgsql a bit, but for a test, it's ok. (You could also try out my patch, it's on the pgsql-patches list :-)) Don't overestimate the performance win for prepared statements, as the optimizer can NOT really calculated the perfect plan, if the statement is too unspecific. At the moment there's a bug regarding boolean values in server side prepared statements. My patch fixes this, too. I hope it will be applied soon... tia Patric
-
Re: [GENERAL] 7.3 Prepared statements
Barry Lind <blind@xythos.com> — 2003-01-03T18:30:18Z
Charles, You can have the jdbc driver use stored plans, but that is not the default behavior. There is a method on the org.postgresql.PGStatement object to enable using a stored plans. It is not the default behavior in 7.3 for the following reasons: 1) The functionality on the server is new and therefore there are probably a few bugs yet to be found. I didn't want to destabilize the overall jdbc driver by relying on new functionality. 2) The implementation on the server will not allow all current uses of JDBC PreparedStatements to work (i.e. prepared statements that do multiple sql statements in one call, ususally done to reduce number of network roundtrips when needing to issue many sql calls at the same time). 3) The current implementation on the server will cause a decrease in performance if you only use the PreparedStatement once. Since the implementation of server side prepared statements requires multiple roundtrips to prepare, execute and close, there will be more network traffic and thus decreased performance if the JDBC prepared statement is only used once. And my experience is that the vast majority of JDBC prepared statements are only used once. 4) This is version one of the functionality. We can learn from it to understand how it can be improved for 7.4. thanks, --Barry Bruce Momjian wrote: > I would ask on the jdbc lists. I am Cc'ing them, and removing the > general list. > > --------------------------------------------------------------------------- > > Charles H. Woloszynski wrote: > >>Bruce: >> >>Do you mean that, under JDBC with PG7.3, it will use the stored >>execution plan, or that we should ask the JDBC driver maintainers to >>work to use this feature to use the stored executiong plan? >> >>I am obviously interested in helping make this happen to improve the >>overall PostgreSQL solution. Should I contact someone specific about >>this to see if there are plans to make this happen in the near future? >> I think I have a solid framework to test its effectiveness with decent >>performance monitoring tools already in place. >> >>Thanks, >> >>Charlie >> >> >> >> >>Bruce Momjian wrote: >> >> >>>Sorry, I don't know if it does that yet, but I am sure it will if it >>>doesn't already. >>> >>>--------------------------------------------------------------------------- >>> >>>Charles H. Woloszynski wrote: >>> >>> >>> >>>>I realize that this is a bit off topic, but your answer got me thinking. >>>>Do JDBC Prepared statements get the same saved execution plan support? >>>>We currently use PreparedStatements in our framework for JDBC access. >>>>We currently do not retain the PreparedStatement between uses, but if >>>>PostreSQL caches the execution plan, we may need to change our design. >>>> >>>>Thanks, >>>> >>>>Charlie >>>> >>>> >>>>Bruce Momjian wrote: >>>> >>>> >>>> >>>> >>>>>A view is just syntaxic sugar added to a query that references the view. >>>>>A preparted statement actually saves the execution plan for repeated >>>>>execution. >>>>> >>>>>--------------------------------------------------------------------------- >>>>> >>>>>Francisco J Reyes wrote: >>>>> >>>>> >>>>> >>>>> >>>>> >>>>>>On 17 Dec 2002, Neil Conway wrote: >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>>On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>>>1. What is the lifetime of the plan created? >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>>>From the PREPARE reference page: >>>>>>> >>>>>>>Prepared queries are stored locally (in the current backend), and only >>>>>>>exist for the duration of the current database session. >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>>What is the difference/advantage between a prepared query and a view? >>>>>> >>>>>> >>>>>>---------------------------(end of broadcast)--------------------------- >>>>>>TIP 4: Don't 'kill -9' the postmaster >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>>-- >>>> >>>> >>>>Charles H. Woloszynski >>>> >>>>ClearMetrix, Inc. >>>>115 Research Drive >>>>Bethlehem, PA 18015 >>>> >>>>tel: 610-419-2210 x400 >>>>fax: 240-371-3256 >>>>web: www.clearmetrix.com >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>> >>> >>> >> >>-- >> >> >>Charles H. Woloszynski >> >>ClearMetrix, Inc. >>115 Research Drive >>Bethlehem, PA 18015 >> >>tel: 610-419-2210 x400 >>fax: 240-371-3256 >>web: www.clearmetrix.com >> >> >> >> >> >> > >
-
Re: [GENERAL] 7.3 Prepared statements
Charles H. Woloszynski <chw@clearmetrix.com> — 2003-01-03T19:07:42Z
Barry: Thanks for the feedback. This helps alot. Feedback/questions below. Barry Lind wrote: > Charles, > > You can have the jdbc driver use stored plans, but that is not the > default behavior. There is a method on the org.postgresql.PGStatement > object to enable using a stored plans. > > It is not the default behavior in 7.3 for the following reasons: > > 1) The functionality on the server is new and therefore there are > probably a few bugs yet to be found. I didn't want to destabilize the > overall jdbc driver by relying on new functionality. > > 2) The implementation on the server will not allow all current uses of > JDBC PreparedStatements to work (i.e. prepared statements that do > multiple sql statements in one call, ususally done to reduce number of > network roundtrips when needing to issue many sql calls at the same > time). Do you mean that the driver, in this mode, will disallow various forms of JDBC prepared statements, or that the stored plan support will not be supported if the query is of one of these forms? I hope it is the latter, as that would make porting to the new driver easier and avoid the re-port to a new driver issue. We use PreparedStatements *for everything* and it is part of the framework that we use to do it this way. I think it would make the new driver unusable to us if we could not use the PreparedStatements for certain, currently legal, SQL. > > 3) The current implementation on the server will cause a decrease in > performance if you only use the PreparedStatement once. Since the > implementation of server side prepared statements requires multiple > roundtrips to prepare, execute and close, there will be more network > traffic and thus decreased performance if the JDBC prepared statement > is only used once. And my experience is that the vast majority of > JDBC prepared statements are only used once. Is that saying that the performance is reduced with the *new functionality* more than current PreparedStatement processing, or that PreparedStatements are in genral, slower? Sounds like the former, and your observation that prepared statements are mostly used only once is kinda troublesome. Sounds like this feature will need to be enabled/disabled in the URL (I think I saw a posting about that from you), to avoid impacting applications that do not re-use prepared statements. Our framework allows for re-use, so I am looking forward to improved performance once we start in earnest the move to 7.3.x. > > 4) This is version one of the functionality. We can learn from it to > understand how it can be improved for 7.4. I hope we can help get some feedback to you on this. Do you have a timeline for 7.4 features? Thanks, Charlie
-
Re: [GENERAL] 7.3 Prepared statements
Barry Lind <blind@xythos.com> — 2003-01-03T19:33:41Z
Charles H. Woloszynski wrote: >> 2) The implementation on the server will not allow all current uses of >> JDBC PreparedStatements to work (i.e. prepared statements that do >> multiple sql statements in one call, ususally done to reduce number of >> network roundtrips when needing to issue many sql calls at the same >> time). > > > Do you mean that the driver, in this mode, will disallow various forms > of JDBC prepared statements, or that the stored plan support will not be > supported if the query is of one of these forms? I hope it is the > latter, as that would make porting to the new driver easier and avoid > the re-port to a new driver issue. We use PreparedStatements *for > everything* and it is part of the framework that we use to do it this > way. I think it would make the new driver unusable to us if we could > not use the PreparedStatements for certain, currently legal, SQL. > The implementation in the current driver is such that you need to enable the use of server side prepare on a statement by statement basis (default is not to use it). So you should only enable if for those statements for which it will work. To illustrate the problem here is an example: pstat = conn.prepareStatement("insert into foo values (?); update bar set y = ?;); When run in server prepared mode this will issue the following statements to the server: prepare abc(int, int) as insert into foo values ($1); update bar set y = ($2); which will fail since the prepare statement is ended by the first semicolon. So this would be an example of a JDBC prepared statement that works fine currently but would fail when used with server side prepared statements. >> >> 3) The current implementation on the server will cause a decrease in >> performance if you only use the PreparedStatement once. Since the >> implementation of server side prepared statements requires multiple >> roundtrips to prepare, execute and close, there will be more network >> traffic and thus decreased performance if the JDBC prepared statement >> is only used once. And my experience is that the vast majority of >> JDBC prepared statements are only used once. > > > Is that saying that the performance is reduced with the *new > functionality* more than current PreparedStatement processing, or that > PreparedStatements are in genral, slower? Sounds like the former, and > your observation that prepared statements are mostly used only once is > kinda troublesome. Sounds like this feature will need to be > enabled/disabled in the URL (I think I saw a posting about that from > you), to avoid impacting applications that do not re-use prepared > statements. Our framework allows for re-use, so I am looking forward to > improved performance once we start in earnest the move to 7.3.x. > Yes the former. That is why the use of server side prepared statements is not the default. This can hopefully be addressed by changes in the FE/BE protocol in 7.4 so that there isn't any difference in performance. >> >> 4) This is version one of the functionality. We can learn from it to >> understand how it can be improved for 7.4. > > > I hope we can help get some feedback to you on this. Do you have a > timeline for 7.4 features? > Traditionally the postgres release cycle is 6-12 months and we are about 2 months into it. > Thanks, > > Charlie > thanks, --Barry -
Re: [GENERAL] 7.3 Prepared statements
Charles H. Woloszynski <chw@clearmetrix.com> — 2003-01-03T19:53:26Z
Barry Lind wrote: > The implementation in the current driver is such that you need to > enable the use of server side prepare on a statement by statement > basis (default is not to use it). So you should only enable if for > those statements for which it will work. To illustrate the problem > here is an example: > > pstat = conn.prepareStatement("insert into foo values (?); update bar > set y = ?;); > > When run in server prepared mode this will issue the following > statements to the server: > > prepare abc(int, int) as insert into foo values ($1); update bar set y > = ($2); > > which will fail since the prepare statement is ended by the first > semicolon. So this would be an example of a JDBC prepared statement > that works fine currently but would fail when used with server side > prepared statements. Is the driver's mode set using a URL argument or an API call? I thought I saw mention of a URL argument. Is the API call something that persists between connection uses? We use a connection pool and I want to get the right semantics for the use of this feature. Personally, I'd prefer that the server scan for a non-quoted semicolon and revert to non-prepared' operation. The cost of the extra scan is likely to be minimal and would allow those folks who use a connection pool to use this feature and have it work when it can and not interfere when it might. I am guessing that you may have already parsed the statement looking for ?'s If so, wouldn't the supression of the 'prepare' be relatively straight-forward? Charlie -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com -
Re: [GENERAL] 7.3 Prepared statements
Barry Lind <blind@xythos.com> — 2003-01-03T20:10:57Z
Charles H. Woloszynski wrote: > > > Barry Lind wrote: > >> The implementation in the current driver is such that you need to >> enable the use of server side prepare on a statement by statement >> basis (default is not to use it). So you should only enable if for >> those statements for which it will work. To illustrate the problem >> here is an example: >> >> pstat = conn.prepareStatement("insert into foo values (?); update bar >> set y = ?;); >> >> When run in server prepared mode this will issue the following >> statements to the server: >> >> prepare abc(int, int) as insert into foo values ($1); update bar set y >> = ($2); >> >> which will fail since the prepare statement is ended by the first >> semicolon. So this would be an example of a JDBC prepared statement >> that works fine currently but would fail when used with server side >> prepared statements. > > > Is the driver's mode set using a URL argument or an API call? I thought > I saw mention of a URL argument. Is the API call something that > persists between connection uses? We use a connection pool and I want > to get the right semantics for the use of this feature. > Personally, I'd prefer that the server scan for a non-quoted semicolon > and revert to non-prepared' operation. The cost of the extra scan is > likely to be minimal and would allow those folks who use a connection > pool to use this feature and have it work when it can and not interfere > when it might. I am guessing that you may have already parsed the > statement looking for ?'s > If so, wouldn't the supression of the 'prepare' be relatively > straight-forward? > There is no url argument currently. There is a proposed patch that adds that functionality but it won't be there until 7.4. As far as parsing the query looking for a semi colon that is probably what will be done for 7.4, but again that isn't there for 7.3. The API call sets a member variable on the PreparedStatement object so once set it will remain set for that object until explicitly unset or the PreparedStatement is closed. --Barry