SELECT COUNT in File Geodatabase

[RobinSyllwasschy]

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

[VinceAngelo]

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

- V

[RobinSyllwasschy]

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

- Robin

[BruceHarold]

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