Spatial View Created w/Linked Server Very Slow Rendering??

[brettangel]

We have several spatial views that utilize tables from a linked server.  When viewed in SQL Server Management Studio the queries run no problem, but if I try viewing them in Catalog, Map or Reader they bog down and sometimes just hang until the user ends the process.  I've put in a incident with ESRI and they can't seem to locate where the problem is coming from.   This has been an ongoing issue and there doesn't seem to be a consistent pattern to the slow rendering.  We're using version 10 and SQL Server 2008 and SDE tables are compressed and analyzed nightly.  Anybody else experienced this or use a similar process?  At this point I'll take any and all suggestions.

-Brett

[VinceAngelo]

Doing a join through a linked server is usually slow, and occassionaly very, very slow,
usually because the foreign table isn't available for SQL optimization.  Oracle has some
tuning parameters that sometimes make extremely slow DBLINK accesses only painfully
slow; I haven't seen that level of control available in SQL-Server (but I haven't really
looked, either).

The only real solution is to cache all the rows locally (replicate the join table in the
local server).

- V

[brettangel]

Vince, the joined view works great in SSMS, but crawls when being viewed in any ESRI product so I believe the slow rendering is related to ESRI software.  I had ESRI poke around and run a trace, but haven't heard back from them.  I'm currently looking into the cache solution, but being a bit of a hack at this I'm not familiar with this process.  In the meantime I've modified the query and made some symbology changes in the maps and that seemed to help, but it's bothersome to not know why. 

-B

[VinceAngelo · Accepted solution]

Do you have an index on the symbology column(s)?  A compound key of the symbology
column and join key would likely help join performance.

- V

[brettangel]

I do not have a index on the symbology columns, but that sure makes sense.

EDIT:  I'm running into issues turning on schema-binding and creating an index, but will report back with results.  Are you asking about an index on the table or view?  I'm assumed the view.

-B

[VinceAngelo]

You can't index views, only tables.  Views use the indexes of the tables (a view
is basically a preparsed SELECT statement), but they generally can't use more
than one index per table (it's the optimizer's job to determine which index is used,
and the optimizer across to a linked server doesn't have the necessary statistics
to choose wisely).

- V

[CarstenVock]

Hello,
I´m also trying to create a view with a data table from a linked server.
Unfortunately it doesn´t work.
What idid:
1. creating a view using this statement:

sdetable -o create_view -T WPB_2 -t WPB_join -c "*" -i sde:sqlserver:ARCGIS2010 -u sde -p Password -D GeoDB

2. I tried to modify the view in sql server management studio using the same syntax as i used for a different view I created before:

SELECT     wpb.Element, wpb.Name, wpb.Totaal_debiet,wpb.Gemiddelde_druk, wpb.Totaal_opslag, wpb.Status,sde.WPB_join.Status AS Expr1, sde.WPB_join.LOCATIE, sde.WPB_join.PLAATS_, sde.WPB_join.Shape, sde.WPB_join.ADRES, sde.WPB_join.OBJECTID
FROM         [PI_AF_LINKEDSERVER.BrabantWater].DataT.Snapshot_WaterProductieBedrijven AS wpb INNER JOIN
                      sde.WPB_join ON wpb.Name = sde.WPB_join.ARC_Address
EDIT:
This works now and is my preffered approach too, BUT is not much faster then joining the data in ArcMap via joins and relates. It´s just a little more convinient to have the feature class as a whole in the sde with all the columns you want.
Also make sure you use non versioned tables as underlying tables.

But still if I want to create the view with sdetable tools could someone give me their syntax for creating views with data in a linked database?

EDIT:
The second approach is that I created a join in SQL Server Management Studio and then tried to register it. Prerequisite for the view is that it is schema bound otherwise the index cannot be build. When I now want to create this view and click on "bind to Schema" in SQL Management Studio I get an error message: Remote access is not allowed from within a schema-bound object.
How can I now create a view which can be registered with SDE and joines data from a Linked Server???
Thanks!

[VinceAngelo]

You cannot use ArcSDE tools to build complex views, and I doubt you ever will.  The
best way to handle this is to create empty tables with the columns you intend to join,
and use this for a simple view, then edit the view (keeping the same columns in the
same order and the same type -- to do otherwise risks unpredictable behavior).

But first you must delete all the spatial data loaded as the SDE user, create a user
to own the spatial data, and load the data again as that user (the 'sde' schema
should only ever contain the tables created by ArcSDE, and then only be used for
ArcSDE administration).

Performance in a linked database can range from poor to awful to miserable.  I've never
seen it better than poor, so I don't recommend this solution.

- V