SQL Spatial view using a Query Layer in ArcMap 10 is SLOW

Question

We have a number of SQL Spatial views each made up of approximately 10 tables (so relatively complex views) and each including a table with a SQL Geometry column. A view could return up to 300-400 thousand records. No SDE involved. We are using a Query Layer (QL) for each spatial view (Select * from the view) to display in ArcMap. Each spatial table has a primary key, spatial grids and uses an SRID of 0. Just to load a QL might take 10-15 seconds. To display with a 100k - 250k data view (maybe 100s of features) takes 10-20 seconds. We previous had these as SDE views (same underlying SQL views w/o the SQL spatial column) in Arc 10 and they loaded and displayed in 1-3 seconds.

Anyone have ideas about why these are SO MUCH SLOWER without using SDE?

Answer 1

I don't know if this could help but when using Query Layers on ORACLE Views with an SDO column, we had to create Spatial Index on all tables containing Spatial information, and then create a spatial index on the view.

• Michael Ciscell: Thanks for the quick response and a good idea. We have indices on the spatial tables but not on the views. In fact we were talking around this point this afternoon. I'll put an index on one of the larger views first thing Monday and let you know.
• Dom: @MichaelCiscell were you able to create the index on your View?
• Michael Ciscell: No. Sorry fro being away from this so long. We're working with ESRI now on the issue. Our views had issues an Indexed View didn't allow.

Answer 2

First, it's slower than SDE because ArcMap has no information about the extent and other information that SDE stores and uses in the back-end of a SQL Server database to store such information and use it to speed rendering (did you get the 'calculating extent' dialogue when you tried adding it to the ArcMap pane?)

Second, have you tried accessing the view from a direct connection to your SQL Server database, and consuming it through that connection, rather than building another layer of SQL on top of your existing layer of SQL (ie. the view)? Sounds a bit redundant...

We build (ie. create) spatial views into the database, including ID's, and consume them from the Database Connection tree in Catalog, rather than using query layers.

All in all, using a query layer on top of a query and lack of optimization information used by SDE sounds like the culprit...but we have experienced slow performance due to the latter issue in ArcMap as well.

Answer 3

My suggestion would be cut down on fields you are selecting instead of

Select * from the view

which gives you all the rows and all the columns of the tables in question cut down on what you are selecting:

Select Fruit,Tree,Branch From Orchard Where Tree = 'Apple'