Performance issues with registered database views published to Server/Portal?

[ShelbyZelonisRoberson]

I have a new 10.6 Enterprise deployment that I am setting up and I need to publish some point feature layers to my Portal. The data that I need to publish resides in an enterprise geodatabase (SQL Server 2016) as database views (spatially-enabled tables with SQL geometry data type). I have registered the views as point feature classes with the enterprise geodatabase using ArcGIS Pro. 

Here is my issue. When I publish these layers as feature layers that reference registered data, the performance in the Portal is extremely slow. For example - 100 point features take minutes to load or never load at all. I get an error that there are too many features display even though the max record count is set to 2000. Interestingly, the problem only exists with the Feature layers - I do not have performance issues with the Map image layers of the same services.  

Other things of note: Instead of using database views, I've also tried to publish query layers of the same data and experience the same slow results. I do not have any display issues in Pro (data loads immediately) so it is not a problem with the database view performance and how the queries are written. Also, when I publish the layers as hosted (copy all data) there are no performance issues. 

I have an Esri support ticket open but was just wondering if anyone out there has had the same problem or has any advice? I've tried creating a new enterprise geodatabase and set different permissions/created new logins to the SQL database - all to no avail.

Thanks in advance!

[Anonymous User]

There're a few things I'd be interested in finding out in your situation:

1. What is the purpose of sharing a view as a feature service? Normally FS are used for editing, but unless you've enabled view editing manually through SQL (which it doesn't sound like you have), views cannot be edited in the esri ecosystem. There're valid other reasons for using a FS, but I'd be interested to know yours specifically.
2. If you go to the Feature Service's REST endpoint and try to execute a simple WHERE: 1=1, Out Fields = * query, does the server return results quickly?
3. What is your AGS logging level set to? Set it to 'Info,' try to add the layer to the Web Map. When it "freaks out" check the error logs. What do they say?
4. Log into your portaladmin endpoint and check the logs there. What do they say?

That'll get us movin' in the right direction

[ShelbyZelonisRoberson]

1. I'm assuming you mean why am I using a feature service as opposed to a map service? For one, some of the layers I want to publish eventually are larger (~2000 points) and I want to be able to symbolize them using the heat map styling. Can't do that on a map service. Plus - there's obviously something wrong that I'd like to figure out just for sanity's sake!

2. The simple query returned the results in about 3 seconds, so yes quickly.

3. Logging level is set to verbose. After setting it to Info and adding one of the problem layers to the web map, the most prevalent error is listed as SEVERE and says "Error performing query operation Wait time of the request to the service 'Shortname_Thefts.MapServer' has expired". The full log from the last 15 mins is attached.

4. Portal admin logs don't have anything to do with the service or slowness. One INFO about me updating the log settings to info level but that's it.

Thanks very much!

[Anonymous User]

Well I have good news for you (in terms of solutions -- haven't reviewed the logs yet): you can use a MapService for a heat map! 

If you add the indexed layer to the map directly, you can modify the symbology just like a feature service. For instance, if I add https://sampleserver6.arcgisonline.com/arcgis/rest/services/Hurricanes/MapServer  to my map I can't symbolize it as a heat map (sad!). 

But if I add https://sampleserver6.arcgisonline.com/arcgis/rest/services/Hurricanes/MapServer/0  with the indexed layer at the end of the URL (i.e. ...MapServer/0 rather than .../MapServer), I can configure the heat-map symbology on the data.

I'll review the logs now -- but that should get the job done in terms of completing the task!

[Anonymous User]

Oh, and just in case: can you confirm you're not having any http - https conflicts? If your web map is https, make sure the service URL you're trying to add is also https!

[ShelbyZelonisRoberson]

Yep both https 

[Anonymous User]

Great -- and were you able to add the .../MapServer/n URL to the web map and symbolize as a heat-map?

[ShelbyZelonisRoberson]

So, interestingly when I add the /0 map server endpoint - now it doesn't draw! Takes forever and I get the same error about too many features. 

[Anonymous User]

Interesting -- and what did you set as the maximum number of records returned setting for the service?

Esri has a 1,000 feature limit for rendering that might be giving us a snag.

Can you clear you browser cache, zoom into a small area in the map, and then attempt to add the indexed layer, does the same error get thrown?

[ShelbyZelonisRoberson]

I set the max records as 2000 but there are only 100 features. Still slow to load even when I clear cache and zoom in. With only 100 records I would expect an extremely fast draw time (like with the hosted version)!