SQL Spatial views using Query Layer load/display slow

[MichaelCiscell]

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?

[VinceAngelo]

How does the query performance compare between the SQL prompt and through Query Layers?

ArcSDE adds optimization based on layer metadata -- you might need to provide hints in your
view to achieve equivalent optimization.

- V

[FrankPotempa]

Time to display/retrieve Query Layer table is slow or will not display in ArcMAP. A SQL Server spatial geometry(point) Query Layer has been created from SSMS 2008. The layer displays without issue in ArcMAP and a user can Identify or use the HTML popup to retrieve attribute information without issue. However when the user opens the table ArcMAP slows down and the request is not completed in a timely fashion if at all.

Table is in excess of millions of records
Table and query performance in SSMS is quick.

Platform:
Client
ArcGIS Desktop 10 SP5

Server
SSMS 2008 R2 SP1
Windows 64 Bit