Memory Efficient building and access of a large collection of data
I am writing a script in Python to QC data in a database table.  The database table is in an ESRI ArcGIS recognizable format (File Geodatabase, SDE Geodatabase, or dbf table).  I am using ESRI's ArcPy package to access each individual record with arcpy.SearchCursor.  The SearchCursor is the only way to access each individual record in the ESRI formats.

As I scroll through each record of the tables, I do multiple QC checks to validate specific business logic.  One of those checks is looking for duplicate data in particular fields.  One of those fields may be geometry.  I have done this by creating an empty container object for each of those fields and as I check each record I use the following logic.

    for field in dupCheckFields:
        if row.getValue(field) in fieldValues[field]: dupValues.add(row.getValue(idField))
        else: fieldValues[field].append(row.getValue(field))

The above code is an example of the basic logic I use.  Where I am running into trouble is the fact that each of these tables may contain anywhere from 5000 records to 10 million records.  I either run out of memory or the performance grinds to a halt.

I have tried the following container types: sets, lists, dictionaries, ZODB + BList, and Shelve.

With the in-memory types (sets, lists, dictionaries) the process is very fast at the start, but as it progresses it gets much slower.  We these types, if I have many records in the table I will run out of memory.  With the persistent data types, I don't run out of memory, but it takes a very long time to process.

Question: Is there a better container type out there to provide low-memory storage of lots of data without a large cost in performance when accessing the data?

System: Win7 64-bit, Python 2.6.5 32-bit, 4gb RAM

Thanks in advance for your help.


Memory Efficient building and access of a large collection of data
===

I am writing a script in Python to QC data in a database table.  The database table is in an ESRI ArcGIS recognizable format (File Geodatabase, SDE Geodatabase, or dbf table).  I am using ESRI's ArcPy package to access each individual record with arcpy.SearchCursor.  The SearchCursor is the only way to access each individual record in the ESRI formats.

As I scroll through each record of the tables, I do multiple QC checks to validate specific business logic.  One of those checks is looking for duplicate data in particular fields.  One of those fields may be geometry.  I have done this by creating an empty container object for each of those fields and as I check each record I use the following logic.

    for field in dupCheckFields:
        if row.getValue(field) in fieldValues[field]: dupValues.add(row.getValue(idField))
        else: fieldValues[field].append(row.getValue(field))

The above code is an example of the basic logic I use.  Where I am running into trouble is the fact that each of these tables may contain anywhere from 5000 records to 10 million records.  I either run out of memory or the performance grinds to a halt.

I have tried the following container types: sets, lists, dictionaries, ZODB + BList, and Shelve.

With the in-memory types (sets, lists, dictionaries) the process is very fast at the start, but as it progresses it gets much slower.  We these types, if I have many records in the table I will run out of memory.  With the persistent data types, I don't run out of memory, but it takes a very long time to process.

Question: Is there a better container type out there to provide low-memory storage of lots of data without a large cost in performance when accessing the data?

System: Win7 64-bit, Python 2.6.5 32-bit, 4gb RAM

Thanks in advance for your help.

