I am a full-time consultant and provide services related to the design, implementation and deployment of mathematical programming, optimization and data-science applications. I also teach courses and workshops. Usually I cannot blog about projects I am doing, but there are many technical notes I'd like to share. Not in the least so I have an easy way to search and find them again myself. You can reach me at erwin@amsterdamoptimization.com.

Sunday, October 13, 2013

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:

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