Sunday, October 13, 2013

gdx2sqlite

SQLITE (http://www.sqlite.org/) seems to be a reasonable alternative to MS Access as a file based database system. Basically we want to use a database rather than a spreadsheet to disseminate model results (the results are too big to comfortably fit in a spreadsheet). One of the advantages of Sqlite is that it can handle files that exceed 2 gb. In the GAMS tool GDX2ACCESS I tried to be smart and do the bulk inserts as  fast as I could (often that means using a csv file in between). In Sqlite there are no special facilities to do bulk inserts (but of course we can be judicious with using transactions). Here are comparisons with some test data:

image

Even though I use just INSERT statements, SQLITE is pretty fast, and produces smaller files than MS Access.

image