Using SQL Server views from a non-geodabase in ArcMap 10.6.1 is pretty much impossible if the underlying joined tables are large. Have you found a workaround?

[DarinaTchountcheva]

This is the technical article describing the problem:
Problem: Performance of SQL Server views degrades after upgrading to ArcGIS 10.4 

At version 10.6.1 this is still the same.

We have number of views coming from a non-spatial SQL Server 2016 database that we publish to ArcGIS server. Some of the views join multiple tables with millions of records. The final record count of the views is 100K-500K.

Adding one of these views to ArcMap is fairly quick, but displaying as XY event layer takes hours if the application doesn't crash. If layer is successfully added, then publishing runs for a long time (30 minutes +) and fails. The tables have been indexed. I added more indexes and made sure I only have "Index Seeks". Still it took 30 minutes to create the XY layer with the view with the least records.

I have opened a case with ESRI, and so far the solutions I have been given are to enable that database as a geodatabase or export the data to a geodatabase (which took 45 minutes). None of these are possible, so I am turning to you, guys, for help.

I am currently testing the same scenario in ArcGIS Pro 2.3, and it has been running for hour and a half the process of creating an XY event layer from the view with 100K.

We have been holding on to 10.3.1 for dear life, but with the upgrade of the OS we have to move on, and it doesn't look pretty.

If you have found a solution, please, advice!

Thank you!

[DEWright_CA]

Sadly there is not a great answer for this issue. You are correct in noting that the default workaround from #ESRI is to export the view to a FeatureClass so you take out the performance hit of the view/joins to smooth things. One way I mitigated this in MSSQL was to build a Spatial-Index on my view; this help immensely.

The other option I would recommend for you is to throw as much RAM at the SQL server as possible; as the Server instance caches data in memory; the more memory it has available to keep from paging back out will help you in the long run.

[DarinaTchountcheva · Accepted solution]

Hi David, 

Thank you for your reply!

I think, I found a solution!

As the ESRI representative was suggesting things to try and asking questions after I created a trace for her to see what is going on, one of her questions was about SDE related tables inquiries in the trace. And the database is not a geodatabase. 

And it hit me, maybe I should try an OLE DB connection as opposed to the .sde connections that I was using. 

I created an OLE DB connection using the option "Microsoft OLE DB Provider for SQL Server" (the SQL Server Native Client option didn't work, it wouldn't show any tables). Once the connection was created, I had to close and open ArcCatalog to see it and rename it. 

Then I used this .odc connection in ArcMap to add the tables with XY columns and create XY event layers. This time, the creation of the layers was instantaneous, a few seconds. Data drew very fast (with all these Index Seeks ). 

I registered the new connection in the ArcGIS Server Data Store and published the .mxd. Publishing took not more than 30 seconds. 

I tested the service in one of our web mapping applications by adding the service URL to the configuration file, and I was able to query and display the data on the map. 

I am scared to even think that this will work across all databases and ArcGIS servers. Cross my fingers!

Hope this helps somebody else!

Good Luck!

[DawnMcCall]

Hello David and Darina,

I hope it is okay to jump into this thread with a related event theme question.

I am trying to design a dataset where the information will be stored with XY and a small amount of attributes in an external database that I will get feeds from to my SDE.  This data will ultimately be in a MapService. Currently I am running 10.3.1 but will be upgrading in the next 6 months or so.

From what I am seeing, storing this data as points that are updated by a table is the most efficient from a service prospective.  However, if the XY data  for the event theme is in the SDE with no joins, will this really cause performance issues?  Also, the xy data will most likely be straight GPS dump of WGS84.  We store our data in a stateplane dataset and then reproject on the fly to Webmercator Auxiliary sphere.  Would it help performance if multiple XY coordinates were stored for each projection?

Thanks

Dawn

[DarinaTchountcheva]

Hi Dawn,

I am not sure what your GIS architecture and workflows are but if you can feed the map service directly from the SDE, then you should that. The ArcGIS software is optimized to work well with SDE databases, and there is no performance issue there. If you data is stored as a feature class in SDE and you are allowed to connect to it, then that is your best option. Use that feature class in your map document to publish to ArcGIS server. Even if it is just a table with XYs, use it from SDE. 

I haven't tested the impact of re-projecting on the fly to Web Mercator, so I can't comment on that. I am personally not a fan of having multiple copies of data in different places, and try to avoid it when possible. 

If you are not allowed to use the data from SDE in a map service, then you can definitely use it from another non-SDE database as an XY event table. In this case, since you already will be manipulating the data and copying it to another place, I would calculate the XYs in state plane and in Web Mercator to avoid re-projecting on the fly. Then in the map document (.mxd) for the map service, I would use the instructions in my answer above to connect to the non-SDE database and will display the XY table by using the web mercator XYs and setting the web mercator as the coordinate system of the event layer. 

Hope that helps you a little! 

Darina

[DawnMcCall]

Thank you so much Darina!

The data will be in SDE before going to the MapService.  I found a 2014 UC presentation that stated that the event class ran slower then actual points, buy that was 2014!  Tech support this morning was not really helpful!  This is what I needed!  

I hope you have a great day.

Dawn

[DarinaTchountcheva]

Hi Dawn

If by actual points you mean feature class, it is probably correct that they perform better in ArcMap. But I wouldn't convert an XY table into a feature class, unless performance is really tanking. I doubt that you will notice the difference in a map service. 

But you can always test. Create a feature class from the XY table in the SDE, add it to a map document. Then add the XY table to that document and create and XY event layer. Publish the service and test the performance.

Have a great day!

Darina

[DawnMcCall]

Awesome!  Thank you again

[TanuHoque]

Personally this is the approach I'd do.

Since most (if not all) databases support spatial data type,

• I'd have added a geometry field in table
• Computed geometries based on x,y coordinates and stored them in the geometry field
• Created spatial index using the database function
• Added that table to ArcGIS Pro or ArcMap
  you can drag-n-drop or for joins you can Create a Query Layer and provide the sql with joins as the query layer's definition
• for cases when data gets updated, I'd have had database triggers to auto-populate/update geometries based on values from x & y columns.