Calculate Field Extremely slow when done for SQL based layer

1336
5
03-21-2022 01:48 PM
nacelle_pylon
New Contributor III

Is there a preferred way to calculate a field?

I calculated two fields separately in a table in SQL via the Geoprocessing Tool ‘Calculate Field.’

Seems ridiculous to me with the processing power available that it takes 45 minutes to calculate 45K rows of 1 field with a simple conversion from a double datatype to text.

I would understand if I were calculating Einstein’s Theory of relativity on every cell.

I performed the exact same calculate with the same feature layer and data but in a File Level GDB on a file server residing 20 miles away.

It took 11 seconds!!

If my math is correct, that’s 24,545% faster.

The only thing removed from the equation was SQL. What gives? Any help or suggestions?

5 Replies
RhettZufelt
MVP Frequent Contributor

are you by chance connecting with VPN?

SQL server data is super sloooooooooooooooow in ArcGIS when trying to connect over VPN.

 

R_

0 Kudos
nacelle_pylon
New Contributor III

No vpn used. In office network

0 Kudos
KimGarbade
Occasional Contributor III

SDE has a lot more overhead.  For example if your database is versioned and you update a field with Field Calculator field calculator has to navigate the versioning tree and the delta tables to write the changes.  If you have editor tracking turned on, or attribute rules, or certain logging enabled on the database server these can slow you down, as can relationships that uses messaging or a geometric network.... when did you compress last, rebuild your indexes... On the database server side, how big are your transaction logs and what level of logging are you using and are the logs fragmented, stuff like that....  There are just a lot of things that can slow you down using enterprise SDE.  I guess some of these apply to a file gdb also... so no telling for sure... still 24,000% is a lot :-).

0 Kudos
nacelle_pylon
New Contributor III

Problem is i'm using a table that was exported into sql from a csv file. The table in sql is neither versions nor has editor tracking enabled. Also we compress every night. We don't have attribute rules but unsure about the logging on the data as the sql instance is essentially maintained by our IT department. I will look into some of things you are suggesting, as far as transactions logs and level of logging. 

0 Kudos
MichaelTorbett
Occasional Contributor II

@nacelle_pylondid you ever figure out what your issue was? I'm having the same problem using field calculator on a enterprise gdb feature class stored in SQL Azure. No VPN. Compression, statistics and rebuilding indexes are ran nightly.

0 Kudos