nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

Find more posts tagged with

Accepted answers

DarinaTchountcheva

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!

All comments

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.

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,

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

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 David,

Thank you for your reply!

I think, I found a solution!

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

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!

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.