Thread: Re: TYPE_SCROLL_XXX and fetch size.
On Thu, 29 Jan 2004, [ISO-8859-2] Fischer Kriszti�n wrote: > Hi Kris! > > Kris Jurka wrote: > > The problem is that the fetch size is ignored when using a ResultSet of > > TYPE_SCROLL_INSENSITIVE. This only works for TYPE_FORWARD_ONLY. > > Do you plan to implement this feature for TYPE_SCROLL_XXX ? Our system > depends on this feature, and we have to port it to PostreSQL. So we > either have to rewrite a part of the system, or wait till it's included > in the driver. Currently, I'm trying to collect some information to be > able to decide which way we should go... > The setFetchSize directive to limit the number of rows returned is implemented by rewriting the SQL statement to use a cursor in the backend. To allow scrolling within a cursor you must specify the SCROLL option when creating it. The problem with this is that certain query plans don't allow scrolling by default, so the backend is forced to retrieve all of the results and store them on the server and implement the scrolling there. So while it is possible to implement this the performance advantage won't necessarily be seen because the server must still retrieve all rows from the query and store them. There are certainly some advantages to doing this, namely the server can spool to a file for large resultsets so it won't get the dreaded java out of memory error, but the complexity of implementing this combined with the potentially low return value has kept this item off of the top of people's todo lists. With the above restrictions I've mentioned is this still something that would be useful to you? Kris Jurka
Kris Jurka wrote: > The setFetchSize directive to limit the number of rows returned is > implemented by rewriting the SQL statement to use a cursor in the backend. > To allow scrolling within a cursor you must specify the SCROLL option when > creating it. The problem with this is that certain query plans don't > allow scrolling by default, so the backend is forced to retrieve all of > the results and store them on the server and implement the scrolling > there. Could you explain what kind of queries are these? I've read the PostgreSQL documentation, and I've found the following: " The SCROLL option should be specified when defining a cursor that will be used to fetch backwards. This is required by the SQL standard. However, for compatibility with earlier versions, PostgreSQL will allow backward fetches without SCROLL, if the cursor's query plan is simple enough that no extra overhead is needed to support it. However, application developers are advised not to rely on using backward fetches from a cursor that has not been created with SCROLL. If NO SCROLL is specified, then backward fetches are disallowed in any case. " (from http://www.postgresql.org/docs/current/static/sql-declare.html) But it's not the problem you mentioned, right?! Could you give me some related links? > So while it is possible to implement this the performance > advantage won't necessarily be seen because the server must still > retrieve all rows from the query and store them. There are certainly > some advantages to doing this, namely the server can spool to a file for > large resultsets so it won't get the dreaded java out of memory error, > but the complexity of implementing this combined with the potentially low > return value has kept this item off of the top of people's todo lists. > With the above restrictions I've mentioned is this still something that > would be useful to you? Depends on the duration of executeQuery() on a relatively large table. If it's within an acceptable range (from a client application's point of view), it would be useful. I think an ineffective implementation is better than an OutOfMemoryError (which is in deed much better than an UnsupportedOperationException). :) How much effort does it take to implement it? I could offer some programming capacity to help you (if there's a need for it, of course). I think it's better for both of us if we enhance the driver. We make no work-arounds in our system, and everybody will profit from a public available driver. Thanks, Chris -- Fischer Krisztián <fischer@borganization.com> Tel: (+36)70/3843835, (+36)1/3360547 Borganization Kft.
On Thu, 29 Jan 2004, [ISO-8859-2] Fischer Kriszti�n wrote: > Kris Jurka wrote: > > The setFetchSize directive to limit the number of rows returned is > > implemented by rewriting the SQL statement to use a cursor in the > backend. > > To allow scrolling within a cursor you must specify the SCROLL option > when > > creating it. The problem with this is that certain query plans don't > > allow scrolling by default, so the backend is forced to retrieve all of > > the results and store them on the server and implement the scrolling > > there. > > Could you explain what kind of queries are these? I've read the > PostgreSQL documentation, and I've found the following: > > " The SCROLL option should be specified when defining a cursor that will > be used to fetch backwards. This is required by the SQL standard. > However, for compatibility with earlier versions, PostgreSQL will allow > backward fetches without SCROLL, if the cursor's query plan is simple > enough that no extra overhead is needed to support it. However, > application developers are advised not to rely on using backward fetches > from a cursor that has not been created with SCROLL. If NO SCROLL is > specified, then backward fetches are disallowed in any case. " > (from http://www.postgresql.org/docs/current/static/sql-declare.html) > > But it's not the problem you mentioned, right?! Could you give me some > related links? This is about the only documentation there is, on the same page under the description of the SCROLL / NO SCROLL option is also mentioned "Depending upon the complexity of the query's execution plan, specifying SCROLL may impose a performance penalty on the query's execution time." This is a pretty vague description, to see what's really going on we'll have to consult the archives. Here is the original complaint about backwards scrolling not working, towards the end of the thread Tom Lane mentions which types of query plans can support a backwards fetch with no additional overhead. http://archives.postgresql.org/pgsql-hackers/2002-12/msg00454.php Here is a message describing the method of storing the query results on the server to all the scroll. http://archives.postgresql.org/pgsql-hackers/2003-03/msg00318.php > > > So while it is possible to implement this the performance > > advantage won't necessarily be seen because the server must still > > retrieve all rows from the query and store them. There are certainly > > some advantages to doing this, namely the server can spool to a file for > > large resultsets so it won't get the dreaded java out of memory error, > > but the complexity of implementing this combined with the potentially low > > return value has kept this item off of the top of people's todo lists. > > With the above restrictions I've mentioned is this still something that > > would be useful to you? > > Depends on the duration of executeQuery() on a relatively large table. > If it's within an acceptable range (from a client application's point of > view), it would be useful. I think an ineffective implementation is > better than an OutOfMemoryError (which is in deed much better than an > UnsupportedOperationException). :) How much effort does it take to > implement it? I could offer some programming capacity to help you (if > there's a need for it, of course). I think it's better for both of us if > we enhance the driver. We make no work-arounds in our system, and > everybody will profit from a public available driver. The driver solution shouldn't be a large amount of code, it is just tricky to make sure you get all the transitions correct when moving out of the subset of results the driver has currently cached. Kris Jurka