Questions about spatial view including tables from non-spatial database

[ShuhongYan]

Our company has one non spatial database A and one geodatabase B, both of which are in the same Server. 

My job is to create a spatial view in SQL Server, put the spatial view in a mxd, publish it to ArcGIS Server, and then create an online map on Portal for ArcGIS. All attribute data (including two columns Lat & Long) are from database A. There are over 180,000 records.

Currently, my action is to

      1. create a spatial view in geodatabase B joining all tables in database A and add SHAPE field based on the POINT() method;

      2. publish the spatial view to arcgis Server and create the map.

However, the problem is that all the points on the map are loaded very slowly. Also, reading the attribute table, opening the pop-up window of a point on the map are both really really slow. Is it because all the data is reading directly from database A?

Is there anyone having better suggestions to create the spatial view? I just want to create an online map using the data from the non-spatial database A.

I heard about indexed view (materialized view). Will the indexed view instead of regular view improve the loading speed of the points in the mxd or on the online map?

I would really like to provide more details about my script of creating the view or other stuff.

Any help is really appreciate!

Shuhong

[NickHarvey]

Hi Shuhong - I'm not sure why you haven't received a suggestion on this to date.......In the enterprise settings that I have worked in we have typically avoided using spatial views for direct consumption in client apps (indexing or not), including desktop, Portal/AGOL etc.  I might suggest setting up an automated/scheduled script that repetitively converts your spatial view into a read-only-purposed feature class which can then be consumed in your client app(s).   Currently we use arcpy.FeatureClassToFeatureClass_conversion (available in Toolbox) for this purpose, but the script also includes some error checking steps, db connections etc.

You may have some circumstances that require another process or another process may be more appropriate, I don't know......But what I'm suggesting is pretty common I think (and might help depending upon your needs).  Especially if for example a 24-hour data refresh rate is all that is needed for your organization, you could schedule the script to run overnight while users are disconnected...   

HTH 

-Nick 

[ShuhongYan]

Hi Nick,

Thank you so much! I re-did the whole process and talked to DBA who helped me look at the connection between Database and Server and now the map is loading the data from the database well! 

Problem solved!

Shuhong

[CraigSwadner]

We use spatail view all the time, for Desktop, Server, and Apps without issue.  Are you the database admin ?

[ShuhongYan]

No, DBA takes charge of all the database. I talked to them and fixed the problem. Thank you!