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!

[ShelbyZelonisRoberson]

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.  

[Anonymous User]

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

[ShelbyZelonisRoberson]

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.

[Anonymous User]

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?

[JonathanQuinn]

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.

[ShelbyZelonisRoberson]

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. 

[Anonymous User · Accepted solution]

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

[DaveThompson101]

@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.

[ShelbyZelonisRoberson]

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.