topic Re: Performance issues on ArcGIS 10.4 and SQLServer Express in Geodatabase Questions

Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Wed, 14 Mar 2018 12:28:04 GMT

Hi everyone

I would like to know if there are some reporting about performance issues on ArcGIS Server 10.4 Workgroup and SQL Server Express 2012 SP4. The server machine specification is showed below:

We have a polygon layer (parcel) with numbers of about 200.000 features. This layer has many relationship classes (something around 6 tables with 200,000-300,000 records each).

We did all spacial and traditional indexes and nothing solved the performance issues.

Our time response revolves arround 5-7s on each pan on the map.

Sincerely

Ezequias

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

JakeSkinner — Wed, 14 Mar 2018 14:18:47 GMT

Hi Ezequias,

The poor performance could be due to a number of factors. One thing to check is when the last time the geodatabase was compressed. If the geodatabase is edited often, you will want to compress the database on a regular basis.

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Wed, 14 Mar 2018 14:27:20 GMT

We did the compress recently. The editing process will not be performed briefly.

Thanks anyway.

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

JakeSkinner — Thu, 15 Mar 2018 12:45:12 GMT

What is the geometry type of the feature class? You can check this by right-clicking on the feature class in Catalog window > Properties > General tab. Under Geometry Properties it will state the type (i..e Geometry). If it is using Geometry I would recommend copying the feature class to SDEBINARY. I've had better performance with SDEBINARY compared to the native Geometry storage type. Below was a test I performed. I used PerfQAnalyzer to compare rendering times. The PA_Streets layer is stored with GEOMETRY storage, and PA_Streets_SDEBINARY is stored with SDEBINARY.

The feature class contains over 1.1 million records. You can see the SDEBINARY layer only took 12 seconds to render compared to 306 seconds for the layer stored with GEOMETRY.

To change the geometry of the feature class you can do a copy/paste into the geodatabase and choose the keyword SDEBINARY.

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Thu, 15 Mar 2018 19:39:45 GMT

Thank you so much for this suggestion. We are performing more tests now with PerfQAnalyzer. We are identifying the layers with problem. The first tests are on polygons (parcel) and Building (with 2mi of vertices).

We also updated the SQL Server 2012 to SQL Server 2014 and the performance is getting better.

I will keep you informed.

Sincerely

Ezequias Rocha

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Thu, 15 Mar 2018 20:28:29 GMT

Dear Jake

PerfQAnalyzer 10.4 is not considering decimal places markers on countries that switches "," and "." positioning it as decimal place separator and thousands separator (IE: Brazil).

We found this problem on the log file. We did a work around by changing regional definitions.

Best regards

Ezequias

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Fri, 16 Mar 2018 11:48:31 GMT

Dear Jake Skinner

Some numbers to consider:

Buildings (polygon) : 2,389,643 vertices 35.94666s

Parcel (polygon) : 402,431 vertices 19.68752s

Sincerely

Ezequias

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

JakeSkinner — Fri, 16 Mar 2018 11:52:47 GMT

What geometry type are these feature classes stored in? If they are using GEOMETRY, can you test with SDEBINARY?

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Fri, 16 Mar 2018 11:57:28 GMT

Could you tell me how?

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

George_Thompson — Fri, 16 Mar 2018 12:10:36 GMT

Hi Ezequias - One thing to consider when using workgroup geodatabases is related to the SQL Server Express.

Microsoft limits the use of SQL Server Express to one CPU (or core within a socket) and 1GB of RAM. The maximum database size for SQL Server 2005 or 2008 Express is limited to 4GB. The maximum database size for SQL Server 2008 Express R2 is 10GB. (Consult your Esri license agreement for specific information on the instance size limitations for your implementation.)

Types of geodatabases—ArcGIS Help | ArcGIS Desktop

Here is the Microsoft documentation on the limitations: Compute Capacity Limits by Edition of SQL Server

https://community.esri.com/groups/geodatabase?sr=search&searchId=63411611-3a1b-48fb-8b32-c2af8515830a&searchIndex=0‌

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Fri, 16 Mar 2018 12:14:29 GMT

You mentioned:

"To change the geometry of the feature class you can do a copy/paste into the geodatabase and choose the keyword SDEBINARY."

What I see was in the moment of creation of a new feature class into geodatabase.

Am I correct?

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

TinaMorgan1 — Fri, 16 Mar 2018 19:00:39 GMT

First, check what type of geometry your slow feature has. In ArcCatalog open the feature class properties and check the general tab. See what type of 'Storage' you are using in the Geometry Properties section. It will say something like Esri binary (aka sdebinary) or Geometry.

To create a new feature using the alternative storage stype just copy and paste the feature class back into the same geodatabase. When you paste the feature class into your geodatabase you will see a drop down list for Configuration Keywords in the Data Transfer window. You will choose the alternative storage type here.

This pasted data will not be versioned, but should maintain related data (things will be pasted in with the _1 naming convention). It may be worth it to add this unversioned data to ArcMap and test the performance first. Once you have observed the behavior of this new unversioned data, register it as versioned and test again.

A lot of performance issues have to be tested in a manner that incrementally adds on functionality, with testing in between each workflow. It is tedious, but worth it if you want to find the root cause.

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Sun, 18 Mar 2018 13:18:08 GMT

Hi Tina Morgan‌

By reading this document Output CONFIG Keyword (Environment setting)—Geoprocessing | ArcGIS Desktop I just noticed that set any blob keyword are not available on Workgroup and personal geodatabases. That is just my case.

My assumption is right?

I am spending too much time trying to import a workgroup featureclass (with something about 300,000 records) to my own geodatabase. Is that a correct procedure within a Workgroup geodatabase (on ArcMap)?

What is any correlate property to SDEBINARY on ArcGIS Pro?

Sincerely

Ezequias

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

deleted-user-0W0-oLHxDjCX — Wed, 21 Mar 2018 13:09:05 GMT

TMorgan-esristaff‌ and jskinner-esristaff‌ thank you for your replies.

The performance gets better. It's not as fast as we encounter on PostgreSQL/PostGIS but I think it is a problem is related to some express memory management limitation (we are allocating 2gb just for the Express).

Sincerely

Ezequias

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

MalcolmMeyer2 — Tue, 15 Oct 2019 14:51:05 GMT

I realize this is over a year later, but am wondering if you stuck with SQL Express or switched to Enterprise and Postgres? I have just migrated several FGDB to SQL Express and am experiencing performance issues that I did not expect, such as lagging on initial selection of features. After reading through here this might be normal and I either need to live with it or upgrade to Enterprise.

Re: Performance issues on ArcGIS 10.4 and SQLServer Express

George_Thompson — Tue, 15 Oct 2019 15:39:03 GMT

Please review the post below on Mar. 16, 2018 that I made. It could be that you are hitting the limitations of SQL Express and the performance. I would recommend testing to an Enterprise RDBMS if the performance (and you have ArcGIS Enterprise licensing) is not improved.