topic Re: Poor performance when running a spatial only query in Data Management Questions

Poor performance when running a spatial only query

HarshaPerera — Thu, 30 Jun 2011 09:43:15 GMT

Hello,

I am using the ArcSDE Java api to perform a spatial query on a feature class containing 950k records. The query is taking 15+ seconds to run and returns a single result (this is correct). How can I improve the performance of the query? The version of ArcSDE is 9.3.1 and it is using an Oracle 11g database.

Here is the output of the si_stats command

ArcSDE 9.3.1 for Oracle11g Build 1632 Thu Feb 26 12:05:37 2009
Layer   Administration Utility
-----------------------------------------------------
Layer 126 Spatial Index Statistics:
Level 1,   Grid Size 1000
|-------------------------------------------------------------------|
| Grid Records: 1549383                                             |
| Feature Records: 953994                                           |
| Grids/Feature Ratio: 1.62                                        |
| Avg. Features per Grid: 11.72                                     |
| Max. Features per Grid: 472                                       |
| % of Features Wholly Inside 1 Grid: 70.03                         |
|-------------------------------------------------------------------|
|               Spatial Index Record Count By Group                 |
| Grids:      <=4    >4    >10    >25    >50    >100   >250   >500 |
|---------- ------ ------ ------ ------ ------ ------ ------ ------ |
| Features: 928247 25747   4359    704    248     98     29     15 |
| % Total:      97%     3%     0%     0%     0%     0%     0%     0%|
|-------------------------------------------------------------------|

Here is the output of the stats command

ArcSDE 9.3.1 for Oracle11g Build 1632 Thu Feb 26 12:05:37 2009
Layer   Administration Utility
-----------------------------------------------------

Statistics for Layer 126
--------------------------------------------------------------
Total Nil Features:           0
Total Point Features:           0
Total MultiPoint Features:          0
Total Line Features:           0
Total MultiLine Features:        437
Total SimpleLine Features:     722840
Total MultiSimpleLine Features:     230717
Total Area Features:           0
Total MultiArea Features:          0
    -----------------------
Total Features:       953994

Minimum Feature Number:           1
Maximum Feature Number:      953994
Largest Feature:        3156 Points
Smallest Feature:           2 Points
Average Feature:       22.55 Points
Minimum Line String Length:                    0.332415
Maximum Line String Length:               127282.943089
Average Line String Length:                  394.203191
Layer Envelope:
minx:     80699.01000, miny:      6441.14000
maxx:    655562.00000, maxy:    657056.00000

This is a snippet of code that performs the spatial query

HashMap<String, LinkedList<Object>> ret = new HashMap<String, LinkedList<Object>>();
SeSqlConstruct construct = new SeSqlConstruct(layerToSearch.getQualifiedName());
SeQuery query = new SeQuery(conn, columnsToReturn, construct);
SeShapeFilter filter = new SeShapeFilter(layerToSearch.getTableName(),
layerToSearch.getSpatialColumn(),
geometry, SeFilter.METHOD_ENVP, true);
query.setSpatialConstraints(SeQuery.SE_SPATIAL_FIRST, false, new SeFilter[] { filter });

query.prepareQuery();
query.execute(); // Takes 15+ seconds

The geometry is a 100m square. The se filter method (envp,ai etc) does not seem to matter.

Any help would be much appreciated.

Harsha

Re: Poor performance when running a spatial only query

VinceAngelo — Thu, 30 Jun 2011 10:20:34 GMT

If you're searching with 100 meter squares, you might want to shrink your index grid
to 300-500m, just to reduce the number of false positives.

My laptop generates 300ms results on a smilar query with the 'C' API and an application
server connection...

Are you using an application server or Direct Connect?

How many columns are you returning? What are their types? Are any large BLOBs
involved?

How long does the same query take using a 'C' client ('sdequery' of se_toolkit can
perform 'C' API searches from the command-line)?

- V

Re: Poor performance when running a spatial only query

HarshaPerera — Mon, 04 Jul 2011 13:25:33 GMT

Thanks for your reply Vince.

I've tried the following

1. Upgrading the client and the ArcSDE database to 9.3.1. SP2
2. Changing the grid size to 500.
3. Try different sized search squares e.g. 10*10, 25*25
4. Use application and direct connects.

but still get the same results.

I've also tried using the search by location function in ArcMap. This is sometimes very slow on the first search but speeds up afterwards - even when searching in a completely different area.

> Are you using an application server or Direct Connect?
I've tried both and get similar performance.

> How many columns are you returning? What are their types? Are any large BLOBs
involved?

I am only selecting two columns - the shape (ST_GEOMETRY) and an ID (string) column

I haven't tried sdequery yet. SDE is installed on Win 2008 R2. Presumably I can use the build for 64 bit Win 2003?

Can you suggest any optimizations that I can try out?

Thanks,
Harsha

Re: Poor performance when running a spatial only query

VinceAngelo — Mon, 04 Jul 2011 14:11:57 GMT

It's been years since I last used the Java API, but when I did I usually found the performance
underwhelming (I was writing an engine to process 5000 inserts with complex pre-processing
in under 10 seconds , and had gotten insert time with 'C' down to 1.5 seconds when I started
using Java -- since the initialization phase took 13-18 seconds [vice 200-300ms with 'C'], the
thought of writing several thousand lines of new Java code for the rest of the preprocessing
became too daunting).

It's difficult to recommend any optimization without learning how the 'C' API performs with
similar queries, which would look like:

sdequery -l table,shape -C shape,id -E minx,miny,maxx,maxy -N -u user -p pass

- V

Re: Poor performance when running a spatial only query

HarshaPerera — Tue, 05 Jul 2011 13:41:44 GMT

Thanks for your help Vince. I've tried running sdequery. It returns the result in 500ms (900ms if the geometry is stored as SDO_GEOMETRY). So the problem appears to be the Java API or the way that we are using it. I've written a simple test Java program (source code attached) that reproduces the problem. It takes 70+ seconds to execute the same query.

I accept that the Java API calls will be slower, but should it be this slow?

Re: Poor performance when running a spatial only query

VinceAngelo — Tue, 05 Jul 2011 16:10:28 GMT

I can't reproduce your issue with ArcSDE 10sp1 (on my 7 year old home PC to a 5 year old
ArcSDE dev box on the same net) or ArcSDE 9.3.1sp2 (on my home PC to an office Oracle
server over VPN). Both environments return results in 2-3 seconds (equivalent sdequery
invocations [with 15 fetches and 2 column display] take 150 & 810 milliseconds, respectively).

You'll need to start an incident with Tech Support to resolve this. They'll need to know which
version of Java you're using.

- V