[Geoserver-users] rowprefetch for Oracle connection

GeoServer GeoServer User
1

Hello everybody,
I just want to know if there is a method to change the default value for rowprefetch used for Oracle connections.
If I am right the default value is 10.
I would try with a higher value.
Is it possibile?

Many thanks

Mauro

–
Mauro Bagazzi
SardegnaIT srl
Viale Trieste 186, Cagliari
Telefono : +39-0706064365
E-mail: <mbagazzi@anonymised.com>
Skype: mauro.bagazzi

2

On Wed, Feb 20, 2013 at 12:01 PM, mbagazzi@anonymised.com <maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,
I just want to know if there is a method to change the default value for rowprefetch used for Oracle connections.
If I am right the default value is 10.
I would try with a higher value.
Is it possibile?

We don’t have a “rowprefetch” property, maybe you mean the “fetch size” which is a standard
JDBC property? The default value is 1000 and it can be set in the data store configuration
panel

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

3

yes, it is

Many thanks

Mauro

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>

On Wed, Feb 20, 2013 at 12:01 PM, mbagazzi@anonymised.com…5407… <maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,
I just want to know if there is a method to change the default value for rowprefetch used for Oracle connections.
If I am right the default value is 10.
I would try with a higher value.
Is it possibile?

We don’t have a “rowprefetch” property, maybe you mean the “fetch size” which is a standard
JDBC property? The default value is 1000 and it can be set in the data store configuration
panel

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

–
Mauro Bagazzi
SardegnaIT srl
Viale Trieste 186, Cagliari
Telefono : +39-0706064365
E-mail: <mbagazzi@anonymised.com>
Skype: mauro.bagazzi

4

Hi Andrea,

I saw that Oracle JDBC Driver have a setDefaultRowPrefetch() and
setRowPrefetch () methods

http://slowdog.net/archives/22

For the sake of curiosity, do you know if they are used in GeoTools or
could you point me to the classes where I can check?

Cheers,

Stefano

---------------------------------------------------
41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>:

On Wed, Feb 20, 2013 at 12:01 PM, mbagazzi@anonymised.com
<maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,
I just want to know if there is a method to change the default value for
rowprefetch used for Oracle connections.
If I am right the default value is 10.
I would try with a higher value.
Is it possibile?

We don't have a "rowprefetch" property, maybe you mean the "fetch size"
which is a standard
JDBC property? The default value is 1000 and it can be set in the data store
configuration
panel

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

5

I tried to look on github, maybe I am looking in the wrong place but
I would expect a source folder here

https://github.com/geoserver/geoserver/tree/master/src/extension/oracle

Am I wrong?

Cheers,

Stefano
---------------------------------------------------
41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

2013/2/20 Stefano Iacovella <stefano.iacovella@anonymised.com>:

Hi Andrea,

I saw that Oracle JDBC Driver have a setDefaultRowPrefetch() and
setRowPrefetch () methods

http://slowdog.net/archives/22

For the sake of curiosity, do you know if they are used in GeoTools or
could you point me to the classes where I can check?

Cheers,

Stefano

---------------------------------------------------
41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>:

On Wed, Feb 20, 2013 at 12:01 PM, mbagazzi@anonymised.com
<maurobagazzilavoro@anonymised.com> wrote:

Hello everybody,
I just want to know if there is a method to change the default value for
rowprefetch used for Oracle connections.
If I am right the default value is 10.
I would try with a higher value.
Is it possibile?

We don't have a "rowprefetch" property, maybe you mean the "fetch size"
which is a standard
JDBC property? The default value is 1000 and it can be set in the data store
configuration
panel

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

6

On Wed, Feb 20, 2013 at 12:26 PM, Stefano Iacovella <stefano.iacovella@anonymised.com> wrote:

Hi Andrea,

I saw that Oracle JDBC Driver have a setDefaultRowPrefetch() and
setRowPrefetch () methods

http://slowdog.net/archives/22

For the sake of curiosity, do you know if they are used in GeoTools or
could you point me to the classes where I can check?

They are not used at all, we are just setting up a DBCP connection pool
in the most generic way, there is nothing DBMS specific in it:
https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStoreFactory.java#L428

Subclasses are allowed to override those methods, but the Oracle one does not (not sure if any subclass does at all):
https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleNGDataStoreFactory.java

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

7

On Wed, Feb 20, 2013 at 12:38 PM, Stefano Iacovella <stefano.iacovella@anonymised.com> wrote:

I tried to look on github, maybe I am looking in the wrong place but
I would expect a source folder here

https://github.com/geoserver/geoserver/tree/master/src/extension/oracle

GeoServer has no code for data sources, they are all in GeoTools

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

8

2013/2/20 Andrea Aime <andrea.aime@anonymised.com>:

On Wed, Feb 20, 2013 at 12:26 PM, Stefano Iacovella
<stefano.iacovella@anonymised.com> wrote:

Hi Andrea,

I saw that Oracle JDBC Driver have a setDefaultRowPrefetch() and
setRowPrefetch () methods

http://slowdog.net/archives/22

For the sake of curiosity, do you know if they are used in GeoTools or
could you point me to the classes where I can check?

They are not used at all, we are just setting up a DBCP connection pool
in the most generic way, there is nothing DBMS specific in it:
https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStoreFactory.java#L428

Subclasses are allowed to override those methods, but the Oracle one does
not (not sure if any subclass does at all):
https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleNGDataStoreFactory.java

thank you a lot for directions.
If I understood it the default value is set in OracleNGDataStoreFactory.java:

       if (dataStore.getFetchSize() <= 0) {
            // Oracle is dead slow with the fetch size at 0, let's
have a sane default
            dataStore.setFetchSize(200);
        }

Isn't it?

Looking on the web I see that a lot of people suggest that higher
values lead to slower performances, that to me is counter-intuitive.

In your experience it is spatial data that performs differently from
other data types or also with them it is better to set lower value of
the prefetch parameter?

Cheers,

Stefano

---------------------------------------------------
41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

9

On Wed, Feb 20, 2013 at 2:13 PM, Stefano Iacovella <stefano.iacovella@anonymised.com> wrote:

In your experience it is spatial data that performs differently from
other data types or also with them it is better to set lower value of
the prefetch parameter?

My experience is that we read so many data in the typical wms/wfs request that having
a higher value is better. I distinctly remember setting values as low as 10 leading to
lower performance than 100 or 200, but I did not get any speedup past 1000.

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it