12

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.

2
  • 1
    Let us know how big the database will be and how many other attributes in the table(s)? Just one table?
    – MLowry
    Jun 6, 2012 at 17:12
  • For this particular installation, the database is a 200MB file geodatabase, with 20 feature classes, and the address feature class has 27 fields and 886,000 records. However, this is for one particular client's installation - other installations of this ArcEngine application with a different client's data could have much more or much less data.
    – Tanner
    Jun 6, 2012 at 18:33

3 Answers 3

7

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.

1
  • Thank you for the in-depth analysis of the issue. I learned a lot from it. I was leaning towards sticking with the file gdb, so I think I'll stay with that for now.
    – Tanner
    Jun 6, 2012 at 20:02
6

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.

3
  • Thanks for the detailed explanation, great answer. Since my biggest concern is on drawing speed, I think I will stick with the FGDB. It is nice to know that PGDBs have more robust SQL functionality though.
    – Tanner
    Jun 6, 2012 at 20:08
  • Just another note and nothing to do with performance, I use pgdb as I can odbc into them from other applications like minitab. If you want to export your data to another application with a file gdb I find I have to faff around exporting.
    – Hornbydd
    Jun 7, 2012 at 17:56
  • good answer all round. I'm glad to see the bit about differing SQL dialects. It is a real time sink to run across that unawares (yes that's a voice from the bottom of the pit!). Jun 13, 2012 at 4:23
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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