[Geoserver-users] Oracle NG and maxFeatures

GeoServer GeoServer User
1

Hi all,

I want to get rid of the rownum <= 999999 construct in my queries.

I don’t have a lot of data so this is not needed for me.

I thought it was related to the maxfeatures setting in services.xml (which is set to 1000000) so I removed this but now ended up in

Rownum = 2147483646 (1 less then Integer.MAX_VALUE).

I can see code that checks if limits can be set that checks if maxfeatures is set. This code checks against Integer.MAX_VALUE.

I tried setting the services.xml value to 2147483648 but that gives an exception on reloading the settings. (not an integer, which is correct ;-))

I think the oracle NG plugin is also creating extra queries (GT_BOUNDS and GT_COUNT) dependant of this maxfeatures setting.

I see the select count(*) …. GT_COUNT_

Then the normal select query to get the features

And then an select sdo_aggr_mbr ……… which aggregates the geometrys

How can I get rid of these extra queries, or are they needed for some special functionality.

Cheers

Kris Geusebroek

Consultant



cid:image001.jpg@anonymised.com

Email: kgeusebroek@anonymised.com



Tel: +31 (0)35 538 1921



Fax: +31 (0)35 538 1922



Mobile: +31 (0)6 30 697 223



http://www.xebia.com

Utrechtseweg 49



1213 TL Hilversum



The Netherlands

Xebia Blog ! http://blog.xebia.com/

Xebia Podcast! http://podcast.xebia.com/

2

Kris Geusebroek ha scritto:

Hi all,

I want to get rid of the rownum <= 999999 construct in my queries.

I don’t have a lot of data so this is not needed for me.

Have you checked, does it negatively affect the performance
of your queries? Given the time we spend doing just xml encoding
I would be surprise it has any user noticeable effect.

I thought it was related to the maxfeatures setting in services.xml (which is set to 1000000) so I removed this but now ended up in

Rownum = 2147483646 (1 less then Integer.MAX_VALUE).

I can see code that checks if limits can be set that checks if maxfeatures is set. This code checks against Integer.MAX_VALUE.

I tried setting the services.xml value to 2147483648 but that gives an exception on reloading the settings. (not an integer, which is correct ;-))

Have you tried with MAX_VALUE exactly? 2147483647
Anyways, can you open a feature request on jira.codehaus.org
to have the maxFeature setting easier to disable (like, a checkbox to
control if maxFeature is set at all?)?

I think the oracle NG plugin is also creating extra queries (GT_BOUNDS and GT_COUNT) dependant of this maxfeatures setting.
I see the select count(*) …. GT_COUNT_

Then the normal select query to get the features

And then an select sdo_aggr_mbr …….. which aggregates the geometrys
How can I get rid of these extra queries, or are they needed for some special functionality.

These are issued to generate the feature collection bounds and count
for WFS 1.1 request (WFS 1.0 requests should issue only the bounds one).
And the bounds are generated only if the WFS configuration says so, try
to disable "feature bounding" and those queries should go away in
the WFS 1.0 case (the count one will remain for the WFS 1.1 case).

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

3

Hi,

You're right about the effect on performance the rownum query isn't
noticeably slower (I think because of oracle optimalization)

I tried the MAX_VALUE, but since the rownum is set to be <= (then the
value set minus 1) I'm left with the rownum <= 2147483646, just like
when I removed the maxfeatures from settings.xml completely.
I will create a issue for the enabling/disabling of the maxfeatures
setting

I didn't have the featurebounds setting in services.xml, but even when
setting it explicitly to false I keep getting the sdo_agg_mbr queries
executed after the feature select queries.
I'm issueing a request with version=1.0.0 so it suproses me to see the
count query at all.

And since the maxfeatures cannot be disabled right now I've set them to
1000 and I see the normal query for getting the features uses rownum <=
1000
But the sdo_agr_mbr one uses rownum <= 988

Question remains how to disable the bounds query since I don't need the
bounds in my result
And I can imagine the performance gain when getting rid of these 2 extra
queries.

Thanks in advance

Kris

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Wednesday, March 25, 2009 11:22 AM
To: Kris Geusebroek
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Oracle NG and maxFeatures

Kris Geusebroek ha scritto:

Hi all,

I want to get rid of the rownum <= 999999 construct in my queries.

I don't have a lot of data so this is not needed for me.

Have you checked, does it negatively affect the performance
of your queries? Given the time we spend doing just xml encoding
I would be surprise it has any user noticeable effect.

I thought it was related to the maxfeatures setting in services.xml
(which is set to 1000000) so I removed this but now ended up in

Rownum = 2147483646 (1 less then Integer.MAX_VALUE).

I can see code that checks if limits can be set that checks if
maxfeatures is set. This code checks against Integer.MAX_VALUE.

I tried setting the services.xml value to 2147483648 but that gives an

exception on reloading the settings. (not an integer, which is correct

;-))

Have you tried with MAX_VALUE exactly? 2147483647
Anyways, can you open a feature request on jira.codehaus.org
to have the maxFeature setting easier to disable (like, a checkbox to
control if maxFeature is set at all?)?

I think the oracle NG plugin is also creating extra queries (GT_BOUNDS

and GT_COUNT) dependant of this maxfeatures setting.
I see the select count(*) .... GT_COUNT_

Then the normal select query to get the features

And then an select sdo_aggr_mbr ........ which aggregates the

geometrys

How can I get rid of these extra queries, or are they needed for some
special functionality.

These are issued to generate the feature collection bounds and count
for WFS 1.1 request (WFS 1.0 requests should issue only the bounds one).
And the bounds are generated only if the WFS configuration says so, try
to disable "feature bounding" and those queries should go away in
the WFS 1.0 case (the count one will remain for the WFS 1.1 case).

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

4

Hi,

Noticed something more:
In my wfs request if use two layers (so typename=layer1,layer2)

When setting maxfeatures to Integer.MAX_VALUE (eq 2147483647)
I see the first query not using rownum at all and the second one uses
rownum <= 2147483646

So somewhere down the line the value of maxfeatures is decreased with 1)

The same goes for the two sdo_agr_mbr queries

So disabling the maxfeatures can be done by setting it to
Integer.MAX_VALUE.
Should I still create an issue for this??

I also noticed the count statements are from a getServiceInfo request.
What's that??

Cheers

Kris

-----Original Message-----
From: Kris Geusebroek [mailto:kgeusebroek@anonymised.com]
Sent: Wednesday, March 25, 2009 11:39 AM
To: Andrea Aime
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Oracle NG and maxFeatures

Hi,

You're right about the effect on performance the rownum query isn't
noticeably slower (I think because of oracle optimalization)

I tried the MAX_VALUE, but since the rownum is set to be <= (then the
value set minus 1) I'm left with the rownum <= 2147483646, just like
when I removed the maxfeatures from settings.xml completely.
I will create a issue for the enabling/disabling of the maxfeatures
setting

I didn't have the featurebounds setting in services.xml, but even when
setting it explicitly to false I keep getting the sdo_agg_mbr queries
executed after the feature select queries.
I'm issueing a request with version=1.0.0 so it suproses me to see the
count query at all.

And since the maxfeatures cannot be disabled right now I've set them to
1000 and I see the normal query for getting the features uses rownum <=
1000
But the sdo_agr_mbr one uses rownum <= 988

Question remains how to disable the bounds query since I don't need the
bounds in my result
And I can imagine the performance gain when getting rid of these 2 extra
queries.

Thanks in advance

Kris

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Wednesday, March 25, 2009 11:22 AM
To: Kris Geusebroek
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Oracle NG and maxFeatures

Kris Geusebroek ha scritto:

Hi all,

I want to get rid of the rownum <= 999999 construct in my queries.

I don't have a lot of data so this is not needed for me.

Have you checked, does it negatively affect the performance
of your queries? Given the time we spend doing just xml encoding
I would be surprise it has any user noticeable effect.

I thought it was related to the maxfeatures setting in services.xml
(which is set to 1000000) so I removed this but now ended up in

Rownum = 2147483646 (1 less then Integer.MAX_VALUE).

I can see code that checks if limits can be set that checks if
maxfeatures is set. This code checks against Integer.MAX_VALUE.

I tried setting the services.xml value to 2147483648 but that gives an

exception on reloading the settings. (not an integer, which is correct

;-))

Have you tried with MAX_VALUE exactly? 2147483647
Anyways, can you open a feature request on jira.codehaus.org
to have the maxFeature setting easier to disable (like, a checkbox to
control if maxFeature is set at all?)?

I think the oracle NG plugin is also creating extra queries (GT_BOUNDS

and GT_COUNT) dependant of this maxfeatures setting.
I see the select count(*) .... GT_COUNT_

Then the normal select query to get the features

And then an select sdo_aggr_mbr ........ which aggregates the

geometrys

How can I get rid of these extra queries, or are they needed for some
special functionality.

These are issued to generate the feature collection bounds and count
for WFS 1.1 request (WFS 1.0 requests should issue only the bounds one).
And the bounds are generated only if the WFS configuration says so, try
to disable "feature bounding" and those queries should go away in
the WFS 1.0 case (the count one will remain for the WFS 1.1 case).

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------
------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

5

Kris Geusebroek ha scritto:

Hi,

Noticed something more:
In my wfs request if use two layers (so typename=layer1,layer2)

Aah, two layers. In that case the query for count cannot be
bypassed as we need to know how many records the first typename
will return. The maxFeatures is global, so if the first typename
returns 100 records, and the maxFeatures is 1000, the second
one will have to be limited to 900 in order to make sure
the global count does not exceed 1000.

When setting maxfeatures to Integer.MAX_VALUE (eq 2147483647)
I see the first query not using rownum at all and the second one uses
rownum <= 2147483646

So somewhere down the line the value of maxfeatures is decreased with 1)

Probably the first feature type returns just one feature and the
code does not consider that when maxFeatures is MAX_INT the
residual maxFeature should not be decreased when moving from
one ft to the next. You can open a improvement request for that
on jira.codehaus.org.

The same goes for the two sdo_agr_mbr queries
So disabling the maxfeatures can be done by setting it to
Integer.MAX_VALUE.
Should I still create an issue for this??

I also noticed the count statements are from a getServiceInfo request.
What's that??

Hum.... I don't know? First time I hear about a GetServiceInfo request?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

6

Hi,

Issue http://jira.codehaus.org/browse/GEOS-2782 created to track the bug
of using maxfeatures equal to Integer.MAX_VALUE with multiple layers

I can see Request: getServiceInfo INFO message coming from geoserver.wfs
just before the 2 count(*) queries are issued.
Can't find this back in the code though?

Cheers Kris

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Wednesday, March 25, 2009 12:29 PM
To: Kris Geusebroek
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Oracle NG and maxFeatures

Kris Geusebroek ha scritto:

Hi,

Noticed something more:
In my wfs request if use two layers (so typename=layer1,layer2)

Aah, two layers. In that case the query for count cannot be
bypassed as we need to know how many records the first typename
will return. The maxFeatures is global, so if the first typename
returns 100 records, and the maxFeatures is 1000, the second
one will have to be limited to 900 in order to make sure
the global count does not exceed 1000.

When setting maxfeatures to Integer.MAX_VALUE (eq 2147483647)
I see the first query not using rownum at all and the second one uses
rownum <= 2147483646

So somewhere down the line the value of maxfeatures is decreased with

1)

Probably the first feature type returns just one feature and the
code does not consider that when maxFeatures is MAX_INT the
residual maxFeature should not be decreased when moving from
one ft to the next. You can open a improvement request for that
on jira.codehaus.org.

The same goes for the two sdo_agr_mbr queries
So disabling the maxfeatures can be done by setting it to
Integer.MAX_VALUE.
Should I still create an issue for this??

I also noticed the count statements are from a getServiceInfo request.
What's that??

Hum.... I don't know? First time I hear about a GetServiceInfo request?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.