topic Re: Spatial View so slow in Data Management Questions

Spatial View so slow

HaniuHokkaido — Fri, 05 Jul 2013 08:33:57 GMT

Dear experts,

I have a spatial view with 25 columns in the database server that looks like this:

SELECT a.OBJECTID, a.Shape, a.KEYID, a.ROTATION, a.LAND_COVER, a.LAND_USE, a.COMPANY, b.Species, b.SpeciesGroup, c.Tipe
FROM     sde.LANDUSE AS a LEFT OUTER JOIN
               gdb.dbo.StandReg AS b ON a.KEYID = b.KEYID LEFT OUTER JOIN
                   (SELECT Dist, created_by, updated_by
                    FROM     gdb.dbo.Petak_PSDS
                    WHERE (Dist = 'District 1')) AS c ON a.KEYID = c.cKeyId

(the query has been truncated, not all columns are mentioned)

This View comprised of 3 tables. The view has 7600 data in it. I open it in my PC using arcmap / arccatalog and it takes around 4 minutes to load up. I have used analyze and update_dbms_stat and its still the same. Also there are no errors spatially.
How can i make it load faster ?

BTW, is view replicate-able using One-Way replication ?

Thanks

[arcsde 10.0, sql server 2005]

Re: Spatial View so slow

VinceAngelo — Fri, 05 Jul 2013 10:33:51 GMT

Since you're using SQL-Server 2005, the view has five tables. I doubt there's
any way, beyond making sure all the indexes are present, to improve that query.
It might be much faster with SQL-Server 2008 and a native geometry type, but
that's probably something to take up with Microsoft (if they're even still supporting
2005 at this late date).

- V

BTW: If you do upgrade to a modern database, be sure sure to follow best
practice, and load the data tables using some owner other than SDE -- the
SDE user should be reserved for instance administration.

Re: Spatial View so slow

MarcoBoeringa — Fri, 05 Jul 2013 10:40:46 GMT

Dear experts,

I have a spatial view with 25 columns in the database server that looks like this:
...
BTW, is view replicate-able using One-Way replication ?
[arcsde 10.0, sql server 2005]

A view is a view, that is, it doesn't contain data by itself, only references existing data in other tables. If you replicate the Feature Classes and Tables used in your view, and copy the view definition to the child geodatabase, you should have the most up-to-date "view" of the database after a synchronization.

There are some specific options for / things to take notice of with Relationship Classes and replication, see the Help for that:

Replicating related data
Synchronizing with filters and related data

Re: Spatial View so slow

KeithAdams — Mon, 15 Jul 2013 16:56:08 GMT

Hi-

We are finding storing the shape data in SQL Server GEOMETRY format has a HUGE negative impact on performance. (I just replied to your thread about that migration process.) If you are doing that, that would be one place to look. Did the views exist prior to your conversion to GEOMETRY? If so, how was the performance then?

For a polygon feature class that has about 25 attribute columns, we are getting data copy rates of only 7 to 9 features a second whether we use Feature Class to Feature Class, Copy Features, or Append. The polygons are based on 1:100,000 scale data at the county-level size (average of about 2,100 vertices / feature) to give you a sense of the level of detail.

It does seem to us, at least anecdotally, that virtually ALL database operations are slower at 10.1 than they were at 10.0 We sit and sit and sit just waiting for ArcCatalog to connect to the geodatabase before we try to do anything else. It also seemed, again only at the anecdotal evidence level, as if spatial views actually drew faster than the underlying spatial table (again, using tables that store feature data in SQL Server GEOMETRY as opposed to SDE_BINARY format).

A couple of other threads I have read suggest that there is a patch for SQL Server 2012, but we're still only at 2008R2. They also say that the "solution" is to go back to SDE_BINARY instead of using GEOMETRY, but that creates other problems for us (our spatial views inexplicably stopped working correctly, which was why we started down the GEOMETRY road in the first place) so it's not a viable alternative.

Of course there is the basic issue of whether you have the business key data on which you are doing the SQL joins indexed properly- that can have an enormous impact on performance, particularly when you have thousands or tens of thousands of featured involved.

Re: Spatial View so slow

MarcoBoeringa — Mon, 15 Jul 2013 20:05:24 GMT

For a polygon feature class that has about 25 attribute columns, we are getting data copy rates of only 7 to 9 features a second whether we use Feature Class to Feature Class, Copy Features, or Append. The polygons are based on 1:100,000 scale data at the county-level size (average of about 2,100 vertices / feature) to give you a sense of the level of detail.

...

Of course there is the basic issue of whether you have the business key data on which you are doing the SQL joins indexed properly- that can have an enormous impact on performance, particularly when you have thousands or tens of thousands of featured involved.

So you're saying you have 10K+ features comprising an average of 2100 vertices per feature at scale 1:100.000??? :eek:

Is this data stream-digitized from imagery, with never a proper weeding out of unnecessary vertices? I can't imagine any polygon / line needing up to 2100 individual vertices to define its form at scale 1:100.000. Even at scale 1:1000, it would be highly undesirable to have such huge amounts of vertices per feature...

For a highly detailed and high quality 1:1000 base map here in the Netherlands of the national highway system, where I was involved in the re-design of the photogrammetry based workflow and database migration some ten years ago, a test dataset had an average of about 27 vertices per feature..., minimum 4, maximum 1026, but that was a rare exception.

Writing to an old, but dedicated and further unused Sun Sparc Ultra-2 single 100 MHz processor Unix server, and storing in SDE_BINARY, resulted in data load speeds of about 120 features / second, so about 27*120=3240 vertices / second. (Oracle 😎

Again, we're talking >10 years ago here...

Your data loads at 14700 to 18900 vertices per second. I leave it to others to comment if that is a normal speed right now with the configuration details you posted... but you really may need to reconsider your workflow for collecting and storing this data...

Re: Spatial View so slow

VinceAngelo — Mon, 15 Jul 2013 23:49:56 GMT

Vertices per second is a very misleading metric. I wouldn't trust it for much.

Databases do work in transactions. Each ROW is processed in total, which includes
LOBs and strings. A one-vertex GeoNames point could have 8-10k of data in a single row,
while a dense 2k vertex polygon without attributes could easily use less. It is critical to make
sure all comparisons are for roughly equal row contents, otherwise the difference in real work
(I/O) processing each row will warp your expectations.

There are many other factors at play during loading as well -- disk contention in file groups,
number an complexity of indexes, the number of fileds with data in them, high-avaliability
synchronization overhead, replication, other databases in the same server competing for
compute and IO cycles... The list is endless. Rather than comparing a system against another
one, I always compare it against itself.

- V

Re: Spatial View so slow

MarcoBoeringa — Tue, 16 Jul 2013 08:45:54 GMT

Vertices per second is a very misleading metric. I wouldn't trust it for much.

Databases do work in transactions. Each ROW is processed in total, which includes
LOBs and strings. A one-vertex GeoNames point could have 8-10k of data in a single row,
while a dense 2k vertex polygon without attributes could easily use less. It is critical to make
sure all comparisons are for roughly equal row contents, otherwise the difference in real work
(I/O) processing each row will warp your expectations.- V

I fully realized that, but rows / features by itself is also a bad metric, as your results show:

1-vertex point features load over 15x faster per feature than huge 2100-vertex pseudo random circles...

Not taking into account the rare possibility of users storing whole images, Office documents or whatever in LOBs in a row of a geographic feature, that is a significant difference not to be ignored either...

Saying you have bad loading speeds of just a few features per second, while omitting the fact that each polygon has thousands of vertices, is therefore also not a very consistent question.

Like you say, it is mixture of a whole bunch of factors at play, including feature count and size of features in terms of number of vertices.

I really think Keith still needs to answer the question why they have average 2100 vertex polygons in their datasets... Unless this data is from extensive boundaries of big counties, and actually represents 1:1000 or 1:5000 detail in reality, and in practice must be combined in geographic overlays and other geoprocessing etc. with similar high quality, large scale data, I see no reason to maintain such huge features at scale 1:100.000. The data could be generalized.

I don't think having 2100 vertex polygons by itself is impossible or bad practice, there may be good reasons to maintain it like that, but it sure isn't standard, especially not in the context of datasets having "millions" of polygons.

I just ran some statistics again against a sample of the large scale photogrammetry based datasets I wrote about in the other thread. This is well maintained real world data:

Polygons: 18968

Vertex count
Total: 535334
Average: 28.22
Min: 4
Max: 2106

If you look at the graph below, most of the polygons have <100 vertices (vertical "Frequency"). The other image shows an example of the data, including some extra line layers, to give some impression of the level of detail.

[ATTACH=CONFIG]25957[/ATTACH][ATTACH=CONFIG]25956[/ATTACH]

Re: Spatial View so slow

MarcoBoeringa — Tue, 16 Jul 2013 12:37:03 GMT

Just for the sake of it, I decided to do a little test. I densified the dataset I had by using the Densify tool to create a dataset with a vertex every 0.5 meter. I guessed this would get me close to Keith's dataset, but I still lag behind with an average 540 vertices per feature:

Results for densified layer

Polygons: 18968

Vertex count
Total: 10247488
Average: 540.25
Min: 4
Max: 18956

I will also copy the original dataset, which had the properties as listed in the previous post:

Original dataset

Polygons: 18968

Vertex count
Total: 535334
Average: 28.22
Min: 4
Max: 2106

Once the densification process itself was finished, I used ArcGIS 10.1 and the Copy Features tool to create a new Feature Class from the existing densified one, curious to know how much time that would take, and compare this with the writing of the data during the densification step, and the copying of the original dataset:

Copying of the original non-densified dataset:
18968 polygons densified in 18 sec, so 18968 / 18 = 1035.8 features written per second, and 535334 / 18 = 29741 vertices written per second.

Results for Densification step:
18968 polygons densified in 9 min 2 sec, so 18968 / 542 = 35.0 features written per second, and 10247488 / 542 = 18906 vertices written per second.

Results for Copy Features step of densified dataset:
18968 polygons copied in 1 min 54 sec, so 18968 / 114 = 166.4 features written per second, and 10247488 / 114 = 89890 vertices written per second.

The test configuration was:
Acer Veriton desktop:
- Core i5 2320 3.00GHz quad core processor
- 6 GB RAM (never used completely during the process)
- 1TB harddrive capable of up to 180 MB/s, connected on SATAII, but never or rarely exceeded read/write IO of over 10 MB/s during the process
- ArcGIS Desktop 10.1 SP1
- SQL Server Express 2012 SP1
- Dataset using SQL Server GEOMETRY storage
- No versioning on the datasets written!

"CONCLUSIONS" (take with a pinch of salt ;)):
This is definitely not server hardware, but it isn't a low spec laptop either, nor is it a shared server bogged down by other processes running on it... I am surprised to see how close the Densification step is to Keith's results:

- Keith had 7-9 "2100-vertex" polygons written per second, with 14700 to 18900 vertices per second.
- The Densification step is 35.0 "540-vertex" features written per second, and 18906 vertices per second
- The Copy Features step is 166.4 "540-vertex" features written per second, and 89890 vertices per second
- Original dataset copy is 1035.8 "28-vertex" features written per second, and 29741 vertices per second
- Vince achieved 113 "2100-vertex" pseudo random circles written per second, and +/- 240K vertices per second

Of course, the simple "Copy Features" step outperformed the Densification by a factor 4-5, but it still shows there is a real penalty in writing big polygons with many vertices, especially if additional computational or IO steps are involved, and it may actually be that Keith's hardware is simply running at its max when importing this data...

Vince's results on his laptop were even better than the Copy Features results on my desktop writing his pseudo random "2100-vertex" circles (in terms of vertices, not in terms of records / features), but that was using the se_toolkit. It may be that the overhead of ArcGIS is the problem here, and in addition my real world dataset contains a couple of extra non-system text fields with data in it, needing to be written too.

PS:
1) Writing to SDEBINARY using the Copy Features tool gave the following results:
18968 polygons copied in 1 min 18 sec, so 18968 / 78 = 243.2 features written per second, and 10247488 / 78 = +/- 131K vertices written per second.

2) I now also attempted writing to GEOMETRY using Copy Features, to copy to a Feature Dataset using another Coordinate System and even another Datum, thus forcing re-projection.
Results are:
18968 polygons copied in 3 min 51 sec, so 18968 / 231 = 82.1 features written per second, and 10247488 / 231 = 44361 vertices written per second.
As you can see, the re-projection slows the copying down by about 1/2 of the original speed compared to copying without re-projection (166.4 features / 89890 vertices per second). This again makes it likely the figures Keith supplied may actually not be that abnormal... although still on the low side of things.

Re: Spatial View so slow

KeithAdams — Tue, 16 Jul 2013 18:52:43 GMT

Hi-

Perhaps what I was doing was exposing my naivete and relative inexperience when dealing with spatial data in "hard core" fashion. Here's how I got my numbers:

I queried SDE_LAYERS to get the layer_id for the feature class I wanted to examine, then I simply queried the corresponding f table to take the average of numofpts column. That is the number I reported as being the average number of vertices for a feature in that class. Did I misunderstand the content of the f table?

I have the following stats for the layer in question:

366k features
numofpts: avg 2127, min 13, max 144,969 (!!!), standard deviation about 5086, median 1023.

The feature with 145k vertices is Monroe County, FL.

All of our data are vector, no raster.

-= Keith Adams =-

For what it's worth, this is a feature class built by use a merge and dissolve process. In addition to the business data, the three feature class inputs to the merge are what were represented to me as 1:100,000 scale county, census tract, and county subdivision boundaries.

We run this process every day, generating something on the order of +/- 4,150 final perimeters and append the results to a growing "history" table that we can use for trending and historical change analysis. (I don't want to go further down the road of the hows, whys, and possible alternate strategies for doing this right now- that's neither her nor there in the context of this discussion because at the end of the day the 90+ days of historical data I have in the feature class are what has to get transferred and converted. I can thin the data some by archiving some of the days of data, and am in the process of doing so, but even so there's a big gob that has to get transferred. )

Re: Spatial View so slow

KeithAdams — Tue, 16 Jul 2013 20:26:00 GMT

So you're saying you have 10K+ features comprising an average of 2100 vertices per feature at scale 1:100.000??? :eek:

Is this data stream-digitized from imagery, with never a proper weeding out of unnecessary vertices? I can't imagine any polygon / line needing up to 2100 individual vertices to define its form at scale 1:100.000. Even at scale 1:1000, it would be highly undesirable to have such huge amounts of vertices per feature...

For a highly detailed and high quality 1:1000 base map here in the Netherlands of the national highway system, where I was involved in the re-design of the photogrammetry based workflow and database migration some ten years ago, a test dataset had an average of about 27 vertices per feature..., minimum 4, maximum 1026, but that was a rare exception.

Writing to an old, but dedicated and further unused Sun Sparc Ultra-2 single 100 MHz processor Unix server, and storing in SDE_BINARY, resulted in data load speeds of about 120 features / second, so about 27*120=3240 vertices / second. (Oracle 😎

Again, we're talking >10 years ago here...

Your data loads at 14700 to 18900 vertices per second. I leave it to others to comment if that is a normal speed right now with the configuration details you posted... but you really may need to reconsider your workflow for collecting and storing this data...

Hi-

This is initially a project to carry out a one-time data conversion / migration exercise of existing data. What happens after that remains to be seen.

We have built the data over the years, and rebuild / reload big chunks of it every night. The data are not versioned. We are in the early planning and analysis stages of redesigning the data management process, but for now that is beside the point.

At this point, the data are what they are, and the sole issue is how to get them from the old beat-up DBs that have gone through three version upgrades of both the DBMS and ArcGIS into brand shiny fresh new ones. At the end of the day, the new and old need to be indistinguishable from one another except that the new will be in GEOMETRY where the old was SDE_BINARY.

The databases are on virtual servers. The destination server is configured as two Intel E5-2260 @ 2.2 GHz CPUs, Windows Server 2008 Standard Edition, 64 bit, 16 GB RAM.

Incidentally, I checked and verified that the county, census tract, and county subdivision boundary data from which we are building the feature class came from Esri and the Census Bureau. We're just consuming them without having compiled them.

They get used for spatial analyses, so they need to be at as fine a level of detail as we can manage.

Thanks-

-= Keith Adams =-

Re: Spatial View so slow

VinceAngelo — Sat, 11 Dec 2021 13:09:58 GMT

So if these are only simple feature classes, why not just run

sdeexport -o create -f - ... | sdeimport -o create -f - -k GEOMETRY ...

for each table?

The <10 features/sec load performance is still off by an order or magnitude or two,
so you need to address that too.

- V

Re: Spatial View so slow

MarcoBoeringa — Wed, 17 Jul 2013 08:53:37 GMT

Perhaps what I was doing was exposing my naivete and relative inexperience when dealing with spatial data in "hard core" fashion. Here's how I got my numbers:

I queried SDE_LAYERS to get the layer_id for the feature class I wanted to examine, then I simply queried the corresponding f table to take the average of numofpts column. That is the number I reported as being the average number of vertices for a feature in that class. Did I misunderstand the content of the f table?

No, you didn't. You interpreted the data right. You undoubtedly know this already, but be aware that GEOMETRY and GEOGRAPHY in SQL Server don't use a f table, features are stored straight in the base table.

I have the following stats for the layer in question:

366k features
numofpts: avg 2127, min 13, max 144,969 (!!!), standard deviation about 5086, median 1023.

The feature with 145k vertices is Monroe County, FL.

Still a huge number of vertices per feature by any measure.

Re: Spatial View so slow

MarcoBoeringa — Wed, 17 Jul 2013 10:21:04 GMT

I have made one last attempt to get closer to Keith's data and working process, by densifying the features one more time, inserting a vertex each 0.1 meter. I used a selection of about 10% of the original dataset, so there is only 1806 polygons left here. This resulted in a dataset with the following characteristics.

Results for densified layer

Polygons: 1806

Vertex count
Total: 4883810
Average: 2704
Min: 23
Max: 34119
Std: 3505

As you can see, the resulting dataset now has on average even more vertices per feature than Keith's dataset (2704 versus 2127).

I than copied this data to a SDE_BINARY dataset using the Copy Features tool by setting the proper configuration keyword, and back from there to GEOMETRY. This last step is very close to what Keith is doing, as my initial tests were GEOMETRY to GEOMETRY mainly, and not specifically from SDE_BINARY to GEOMETRY. Since this step involves a conversion of data types, it might slow down things.

I also copied from GEOMETRY to GEOMETRY again for comparison:

GEOMETRY TO GEOMETRY results:

1806 polygons copied in 59 sec

1806 / 59 = 30.6 features written per second
4883810 / 59 = 82776 vertices written per second.

GEOMETRY TO SDE_BINARY results:

1806 polygons copied in 37 sec

1806 / 37 = 48.8 features written per second
4883810 / 37 = 132K vertices written per second.

SDE_BINARY TO GEOMETRY results:

1806 polygons copied in 43 sec

1806 / 43 = 42 features written per second
4883810 / 43 = 114K vertices written per second.

CONCLUSIONS:
Well, "surprise"... or maybe not so. But the writing to and from GEOMETRY to SDE_BINARY, both ways, is actually faster than from GEOMETRY to GEOMETRY, despite a needed conversion.

Anyway, looking at the figures ranging from 30.6 to 48.8 features / second max, I do have to contest Vince's "two-orders-of-a-magnitude" to low performance figures with the 7 to 9 features Keith reported. It seems likely from these figures, that a maximum gain of about 5-6x is a more realistic figure based on real world data (albeit densified). This is assuming ArcGIS and geoprocessing framework usage, not se_toolkit or ArcSDE Command Line tools, of course. And test hard-/software configuration as in this post in this same thread.

Lastly, Keith's dataset has more attribute fields than mine, that may contribute to lower insertion figures too.

The databases are on virtual servers. The destination server is configured as two Intel E5-2260 @ 2.2 GHz CPUs, Windows Server 2008 Standard Edition, 64 bit, 16 GB RAM.

I guess this needed to be Intel E5-2660 2.2 GHz. According to this benchmark page, they should be about double as fast as my Core i5 2320. That would realistically put them in the "one order of a magnitude" faster than the current loosy 7-9 features / s score, so maybe 80-100 "2000+ vertex" features / s should be achievable.

Re: Spatial View so slow

VinceAngelo — Thu, 18 Jul 2013 00:34:34 GMT

While virtual servers are great for many purposes, database server (and ArcGIS
server) is not one of them. None of my enterprise clients run databases on
virtual servers, and I've worked on benchmark teams to clearly demonstrate
the benefits of ArcGIS on physical (vice virtual) servers. We also saw a 2x
performance difference between fibre-attached disks and network shares.

Obviously, this is not to assert that all physical systems are better under all
conditions, but when I/O is a a premium (as it is in databases and mapping),
it makes sense to optimize the system for I/O performance (which includes
giving the machines a lot of RAM -- 16Gb is good but 32-64Gb is better).

- V

Re: Spatial View so slow

KeithAdams — Wed, 31 Jul 2013 19:30:46 GMT

No, you didn't. You interpreted the data right. You undoubtedly know this already, but be aware that GEOMETRY and GEOGRAPHY in SQL Server don't use a f table, features are stored straight in the base table.

Still a huge number of vertices per feature by any measure.

Glad to know I had the details right. We are looking forward to the removal of f, s, and i tables from the architecture, and also to being able to use straight SQL (plus some follow-up spatial index management) to copy features from "master" sets to subsidiary subsets, without having to go through the pain of reprocessing the data for each variation.

Since we are using county, census tract, and county subdivision boundary data obtained from outside sources (Esri and the Census) we're not predisposed to mess with them. Any idea why there might be over 145K vertices to define a single county boundary, even if that's an extreme outlier?

-= Keith =-

Re: Spatial View so slow

LeoDonahue — Wed, 31 Jul 2013 19:52:36 GMT

... beyond making sure all the indexes are present, to improve that query.

Haniu,

Did you index your join fields? I didn't see your reply to that.

Re: Spatial View so slow

MelitaKennedy — Wed, 31 Jul 2013 21:25:02 GMT

A couple of reasons for large vertex counts--

1. Part of the boundary follows a water feature
2. Even long, "straight" lines shouldn't be only two point lines if you want to reproject the features. ArcGIS doesn't densify data as it reprojects the features. Straight lines may be curves in the new coordinate system.

Melita