topic Re: SELECT COUNT in File Geodatabase in Data Management Questions

SELECT COUNT in File Geodatabase

RobinSyllwasschy — Wed, 21 Sep 2011 08:25:32 GMT

Hi,

I'm trying to query a file geodatabase for the equivalent of
"select count(*) from table_xy" without having to iterate over the result of an IQueryDef.
Using ITable.Rowcount is not an option, as the queries have to join over several tables.

This is performance critical, the tables contain millions of rows, so doing a full table scan in ArcObjects is not an option.

So far, the only workaround I have found is to create a table "numbers" with integers from 1 to 10000 (MAXINT?) and then doing an IQueryDef-Search like this:
SELECT num FROM numbers WHERE num=(SELECT count(*) FROM table_xy)

While yielding the correct result, for obvious reasons this is not an acceptable solution.

The next thing I tried was executing this statement:
UPDATE numbers SET numbers.num =(select count(*) from table_xy)

This is syntactically accepted by the File Geodatabase and executed, but the result is always 0.

So while obviously the FGDB _can_ calculate the correct values, there doesn't seem to be a convenient way to retrieve it.

Can anyone give a better (an still fast) solution for this problem?

Regards,
Robin Syllwasschy

Re: SELECT COUNT in File Geodatabase

VinceAngelo — Wed, 21 Sep 2011 11:22:27 GMT

There won't be a solution for this until 10.1 is released.

- V

Re: SELECT COUNT in File Geodatabase

RobinSyllwasschy — Wed, 21 Sep 2011 11:48:03 GMT

Thanks for this clarification, Vince.
I just hoped there might be some way to work around this limitation in 10.0.

- Robin

Re: SELECT COUNT in File Geodatabase

BruceHarold — Thu, 22 Sep 2011 14:44:16 GMT

Hi

I might be missing something here with respect to the programming environment you might be tied to, but the geoprocessing environment has the Get Count tool, and if you really need to do this with SQL, and your OID values are a sequence, then select something like OID = (select max(OID) from table_xy) is fast.

Regards