I'm trying to load grid data for a map of about 300x300 grids. Each grid has attributes such as the material in it etc.
I have the data in flat text files and I decided to load it into a sqlite db for queries and fast lookups.
I started with a straight-forward read and load into sqlite using Python's built in sqlite module. The load time was quite large as can be expected for some 3 million rows. I unfortunately don't have the exact numbers, but the total load was within about 30 mins.
Since I need different views of the data and for various other reasons I decided to switch to using SQLAlchemy to access the data.
While SQLAlchemy allowed me to quickly setup the configuration and define my data access classes, the load itself is taking forever. I am writing now even as it is loading, and it seems that the load will take something like 4-6 hours.
I must be doing something differently in SQLAlchemy vs sqlite for there to be such a big difference. I can't believe that an ORM layer like SQLAlchemy would add such a big overhead for inserting data into tables.
More updates when I figure out whats going on.
0 blog comments below