Are personal geodatabases better suited for quickly querying indexed attributes than file geodatabases?

Question

I'm preparing data for an ArcGIS Engine application that queries the data to search for an address. Sometimes we search just on the street name field, just on the house number field, or both. When using personal geodatabases or SDE geodatabases, one can add a multi-column attribute index in addition to single-column indexes. For some reason, according to the Creating attribute indexes ESRI article, multi-column attribute indexes are not possible when using file geodatabases. They don't mention why this is the case - maybe file geodatabases don't need them for some reason?

A multi-column index on the house number field and the street name field should theoretically improve my query performance when searching both fields at once, but is it worth switching over to using a personal geodatabase? I have a feeling that downsides of using a personal geodatabase might negate the benefits of the multi-column index.

I have been under the impression that Esri wants us to move away from personal geodatabases, but is this a case where personal geodatabases are the better option? If you have any experience with this, I'd love to know.

Answer 1

To answer the first part of your question, I think it helps to look at the additional text in the Creating Attribute Indexes help file about Multi-column indexes.

The order in which fields appear in a multicolumn index is important. In a multicolumn index with column A preceding column B, column A will be used to conduct the initial search. Also, such an index will be much more useful for queries involving column A only than it will be for queries involving column B only.
Create a multicolumn index on A and B. This index would typically be more efficient for queries involving both columns. For queries involving only A, this index would be slower than an index on A alone. This index would be of little use for queries involving only B. To compensate, you could create an additional index on B.

Both of these passages show that multi-column indexes are better for specialized use. Further, using such an index to sort on only one of the included columns, could actually hurt performance. For this reason, it is likely that individual column indexes are going to be necessary for each of the attributes included in a multi-column index.

I found a link to an old, but interesting document by ESRI stating the 9 reasons to choose a File over a Personal GDB. It is interesting in that it specifically calls out performance as being one reason. Part of this performance gain is due to the file based storage system. I think this could also play into the lack of multi-column support. Unlike in the Personal GDB, which is a single file, an index in a File GDB is stored as a separate file in the GDB structure. This means that the index file and the attribute file for a particular featureclass will have to be linked and accessed together. I could see where a multi-column index would lead to jumping back and forth between the index and attribute files, and potentially causing a performance hit that outweighs the indexing performance gain.

Since there are already significant performance gains with the File GDB over the Personal GDB, it was probably not worth implementing the multi-column index.

In my experience working with both GDB types, I have seen the Personal GDB running about 50% larger than the file. Based on the data you provided regarding your File GDB, if you were to convert to a PGDB, you would probably end up with a ~300MB Personal GDB. From what I've seen, working with MS Access databases, both within ESRI products, and separately, is that you start to see performance degradation once the ".mdb" files increase significantly over 100MB in size.

The other issue would likely be that even if you could speed up your attribute searches, you would see a large performance hit related to moving about in the data frame, and refreshing the view. The layer simply wouldn't draw as fast if it were in a PGDB. This article comparing the Types of Geodatabases gives more information on the performance differences.

As with a lot of things, the best choice will ultimately boil down to what your use case is. If there are a lot of database specific operations that you would like to perform, like queries and updating, that you can do in the Access interface, then the Personal GDB may be better. If you only plan on doing some querying, but will primarily be visualizing the spatial data, then the performance definitely falls on the side of the File GDB.

Answer 2

There are at least 9 top reasons to use File Geodatabase over Personal Geodatabase. Unfortunately, there are still a lot more reasons to keep the old PGDB around; your dilemma being one of them. (no ESRI publication on this topic)

I believe the primary purpose of FGDB over PGDB is storage capacity and performance of spatial data (drawing speed, retrieval, spatial indexing, spatial querying, etc.) rather than functionality such as multi-column "attribute" indexes and other advanced SQL functions which are normally such an integral part of any DBMS. (Which MS Access based PGDB is and the ESRI native FGDB is not) As a side note; The maximum file size limit of an MS Access database is 2GB which is also the maximum size of any single PGDB. In contrast, the FGDB file size limit is 1TB expendable to 256TB.

ESRI also states that: The syntax you use to build a SQL expression differs depending on the data source. This is because although SQL is a standard, not all database software implements the same dialect of SQL. and To query file-based data, including file geodatabases, coverages, shapefiles, INFO tables, dBASE tables, CAD, and VPF data, you use a dialect of SQL implemented within ArcGIS that supports a subset of the features and functions available in personal and ArcSDE geodatabases.

In other words (and the PGDB and ArcSDE GDB is a proof of that) if the geodatabase underlying DBMS supports this functionality then it should be available. This is likely why you are able to create a multi-column index in a PGDB that has an underlying MS Access database. Same with any ArcSDE geodatabase with an underlying DBMS which supports this functionality.

As for File Geodabase; at the 9.2 FGDB release ESRI insinuated that some of these features and functions might be added in future FGDB releases, quoting; "File geodatabases do not support all the features and functions available for personal geodatabases. At ArcGIS 9.2, the most commonly used functions not supported by file geodatabases include DISTINCT, GROUP BY, and ORDER BY, and the set functions AVG, COUNT, MIN, MAX, and SUM are not supported outside subqueries. Support for some of these is likely to be added in future releases. "

Four years later at version 10 none of these functions and features are available. (List of available functions)

It seems that FGDB is a work in progress and it needs multi-column indexing capabilities as much as it needs all the necessary SQL DBMS functions. I guess we will be stuck with PGDB until ESRI developers decide that it is important to extend its functionality to the FGDB.

Answer 3

Reviving this thread/issue, I found it can be usefull to combine, where possible, FGDB and PGDB. For instance, make a scratch-geodatabase a PGDB greatly helped performance of queries. The size of the PGDB should not increase too much, as mentioned above.