topic Re: Performance issues with registered database views published to Server/Portal? in ArcGIS Enterprise Questions

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

ShelbyZelonisRoberson — Wed, 06 Jun 2018 16:56:59 GMT

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!

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

Anonymous User — Thu, 07 Jun 2018 10:24:01 GMT

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

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.
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?
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?
Log into your portaladmin endpoint and check the logs there. What do they say?

That'll get us movin' in the right direction

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

ShelbyZelonisRoberson — Thu, 07 Jun 2018 12:29:42 GMT

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!

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

Anonymous User — Thu, 07 Jun 2018 13:04:49 GMT

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!

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

Anonymous User — Thu, 07 Jun 2018 13:07:22 GMT

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!

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

ShelbyZelonisRoberson — Thu, 07 Jun 2018 13:11:08 GMT

Yep both https

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

Anonymous User — Thu, 07 Jun 2018 13:14:59 GMT

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

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

ShelbyZelonisRoberson — Thu, 07 Jun 2018 13:15:30 GMT

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.

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

Anonymous User — Thu, 07 Jun 2018 13:18:27 GMT

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?

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

ShelbyZelonisRoberson — Thu, 07 Jun 2018 13:23:41 GMT

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)!

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

ShelbyZelonisRoberson — Thu, 07 Jun 2018 13:27:12 GMT

Also I should note that it does eventually draw, but if I zoom out again or pan around it has to re-load. The lag is definitely too long to expect someone to wait if they were using this in an application. Especially since this server is only 100 features and there are others that are closer to 2 or 3,000 that I need to use as well.

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

Anonymous User — Thu, 07 Jun 2018 13:28:48 GMT

Yeah, hosting will always be the fastest as the data is locally copied.

Are indexes built on the underlying datasets that are used within the view? Additionally, if you go to the REST endpoint and execute a 1=1 query with the option to 'Return Count Only' set to true (one of the radio buttons on the item's REST endpoint, do you get the expected 100 records?

Also, can you share the expression used to create the view? Lastly, can you confirm that the view only uses data from a single source (i.e. the view isn't made up of datasets from different databases)?

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

ShelbyZelonisRoberson — Thu, 07 Jun 2018 13:39:25 GMT

So - the views are made of up datasets coming from multiple datasets/databases. In my enterprise geodatabase, some of the views are views of views on other servers... e.g., Select * from [View on one Database] as [New view in Enterprise Geodatabase]. I have issues regardless of whether they are views directly from the data sources or views of views.

I need to check w/ our DBA to see if indexes are built on the data sources. That's one thing Esri was interested in. BUT -- the views all display lightening fast in Pro which makes me think that their performance isn't part of the problem. Also - the very first service that I created from one of these views a couple weeks ago has no performance issues, it's just every one I've tried since then that is having a problem. I also tried to create a true feature class inside the geodatabase (polygon - 3 simple features, nothing related to a database view), and even those 3 polygons had a serious lag in Portal.

As for the Return Count Only query - I instantaneously do get the actual count expected, both for the service with only 100 features and for one of my larger ones with 2600.

Thanks again for your help.

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

Anonymous User — Thu, 07 Jun 2018 13:42:01 GMT

Gotcha -- I have a few other things I wanna ask, but it's probably better to do them over email and I'll circle back and post the 'solution' when we get it back to here.

Can you email me at Andrew.Valenski@charlottenc.gov?

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

JonathanQuinn — Thu, 07 Jun 2018 18:04:37 GMT

Take a look at enabling an SDEINTERCEPT which can help diagnose performance related problems.

Where are Server and Pro/ArcMap in relation to the database? Is the Server further away from the database than your own machine/desktop client? You can install ArcMap or Pro on the Server machine itself and see the performance of the view there. If it's slow there as well, then it's likely network related.

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

ShelbyZelonisRoberson — Thu, 07 Jun 2018 18:11:45 GMT

Thanks. My DBA is doing some tracing on his end to see if it's related to the views too.

Server is on one machine, Database is on another, Pro is on my desktop PC. Unfortunately since the servers are in the DMZ I do not have access to the Server or Database machines. I'll only be able to test anything related to these machines as a last resort with my IT team. However - I'm having absolutely no issues displaying the views in Pro on my machine, they load immediately, even ones with 3,000 features. The issue is only when they are published to the Portal.

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

Anonymous User — Tue, 12 Jun 2018 11:03:05 GMT

Shelby and I found that the issue was related to the database and not necessarily the server. There appeared to be performance issued associated with the database that the DBA is working to address (at a minimum through both spatial and non-spatial indices, database statistics and query optimization -- but may extend to pagination and more nuanced database tuning).

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

DaveThompson101 — Wed, 29 Dec 2021 21:01:19 GMT

@Anonymous User @ShelbyZelonisRoberson

Could you provide more detail about what fixed this issue? I'm having the same issue - view works fine in Pro, but published and in the web environment, it draws very inconsistently/incomplete and also shows features as duplicate.

Appreciate any insight you can provide.

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

ShelbyZelonisRoberson — Mon, 03 Jan 2022 18:23:47 GMT

I ended up working with Esri tech support for a while....two major solutions were that 1) I upgraded our ArcGIS Server machine from 4 to 12 CPUs, and 2) Upgraded Enterprise to the latest version at the time.