1

I was benchmarking which method was faster for counting the number of rows in a table, when something curious happened:

import arcpy
import os
import time

test_tuple = ("path\to\connection", "feature_class_name")
fc = os.path.join(*test_tuple)

i = 0
while i < 3:
    # Method 1
    time.sleep(5)
    start_time = time.clock()
    count = int(arcpy.GetCount_management(fc).getOutput(0))
    end_time = time.clock()
    print("Method 1 ({count}) finished in {time} seconds".format(count=count, time=(end_time - start_time)))
    print("The count was: {count}".format(count=count))

    # Method 2
    time.sleep(5)  # Let the cpu/ram calm before proceeding!
    start_time = time.clock()
    query = """SELECT COUNT(*) FROM {}""".format(test_tuple[-1].upper())
    execute_object = arcpy.ArcSDESQLExecute(test_tuple[0])
    result = execute_object.execute(query)
    count = int(result)
    end_time = time.clock()
    print("Method 2 ({count}) finished in {time} seconds".format(count=count, time=(end_time - start_time)))
    print("The count was: {count}".format(count=count))
    i += 1

This gave the following results:

Method 1 finished in 5.018752999999999 seconds
The count was: 22645
Method 2 finished in 0.24905560000000015 seconds
The count was: 21473
Method 1 finished in 0.7440046999999996 seconds
The count was: 22645
Method 2 finished in 0.7023353000000014 seconds
The count was: 21473
Method 1 finished in 0.7402944999999974 seconds
The count was: 22645
Method 2 finished in 0.684015500000001 seconds
The count was: 21473

Why are the counts different between the two methods?

  • 1
    I've not personally used the arcpy.ArcSDESQLExecute method, so I don't know if this is it or not, but my gut reaction is to assume it likely has something to do with a versioned dataset and resulting discrepancies. If so, using Versioned Views rather than the dataset directly may assist. – John Oct 17 at 19:51
  • 1
    Is the feature class versioned? If so, that's your answer. – Vince Oct 17 at 20:06
  • It is versioned, but I don't understand why versioning would change counts if both methods are looking at DEFAULT. – jesnes Oct 17 at 20:09
  • 1
    Because there are additional rows in the Adds and Deletes tables which haven't yet manifested changes in the business table. Versioning 101 is the place to start. – Vince Oct 17 at 20:13
  • Does that mean that the discrepancy would go away if the database was compressed? – jesnes Oct 17 at 20:16
4

When querying the table of a versioned (or archive-enabled) feature class directly using SQL, your results would not be expected to reflect the current state of the feature class. You should use the versioned view instead. The table could contain records that have been deleted, or records that have been changed, or may be missing records that have been added.

Try changing the line:

query = """SELECT COUNT(*) FROM {}""".format(test_tuple[-1].upper())

to:

query = """SELECT COUNT(*) FROM {}_EVW""".format(test_tuple[-1].upper())

Ie, run it against the versioned view, not the table. The versioned view usually has the same name as the table but with "_evw" appended and it is what ArcGIS/arcpy would be actually using in the background and not the table directly.

If you are using versioning or archiving, you should NEVER interact directly with the database table outside of ArcGIS, but always with the versioned view. Otherwise you will get invalid data such as superceded data, deleted records, missing records, etc (and if you make edits directly to the table, you could corrupt your database, as far as ArcGIS is concerned).

ESRI have a lot of documentation on this sort of thing. Eg:

and

  • I'm glad I asked this question, because the answer is more informative than I imagined. Thanks! Also, there's a small error in where you say to add "_evw": it should be added to the line query = """SELECT COUNT(*) FROM {}""".format(test_tuple[-1].upper() + "_EVW") because that's where the view is being queried. As you have it, you are adding "_EVW" to the workspace's path. – jesnes Oct 18 at 15:42
  • 1
    Oops. Don’t know what I was thinking. I’ve fixed the post now - slightly different to how you wrote it to be a little more efficient I think. – Son of a Beach Oct 18 at 21:07
  • I wholeheartedly agree, thanks again for your informative answer. – jesnes Oct 19 at 0:39
1

In addition to using the versioned view, consideration should be made for the length of the table name. In this case, I've discovered that if adding "_EVW" to the end of the table name exceeds 30 characters, an error will be thrown. I got around this with the following:

test_tuple = ("path\to\connection", "OWNER.table_name")
own, name = test_tuple[-1].split(".")
fc = os.path.join(*test_tuple)

desc = arcpy.Describe(fc)

query_name = test_tuple[-1]
char_limit = 30  # Oracle has a 30 character limit on table names
if desc.isVersioned:
    if len(name) + 4 > char_limit:  # +4 for the addition of "_EVW"
        query_name = ".".join([own, name[:char_limit - 4]])
    query = """SELECT COUNT(*) FROM {}_EVW""".format(query_name.upper())
else:
    if len(self.name) > char_limit:
        query_name = ".".join([self.owner, self.name[:char_limit]])
    query = """SELECT COUNT(*) FROM {}""".format(query_name.upper())

execute_object = ArcSDESQLExecute(self.connection)
result = execute_object.execute(query)
count = int(result)
  • Good find. I've just checked for MS SQL Server, and the limit there is 128 characters. Less likely to be an issue, but still good to be aware of! Thanks for posting this update. – Son of a Beach Oct 22 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.