SQLite

I’ve been working with computers for over 30 years and I still am amazed at how fast they are. I do these little exercises every now and then to remind myself. I ran a Python program I wrote to create real “looking” customer data. It uses sqlite databases of first name, last name and zip codes to generate these records. It took about an hour and 15 minutes to create “one million” [places pinky in the corner of my mouth] records. This file is slightly greater than 87MB. It is a “|” delimited file. It also contains real “looking” addresses with correct city/state for the zip, and a valid 16 digit mod 10 account number. the average record size is > 90 bytes…if I pad it out to a fixed length record it’s about 140 bytes. Python is a great but interpreted language hence not the quickest. To be fair it is accessing 3 SQL databases and doing a lot of random number generating to create many of the other fields a million times.

I imported these one million records into a SQLite database and it took roughly 12 seconds, and this SQL database file created is about 131MB. During those 12 seconds it also created the Primary Key using the 16 digit Account number. I guess generating random 16 digits are enough to insure you don’t get duplicates because I didn’t get any. To double check I tried importing the records in again but immediately got “INSERT failed: UNIQUE constraint failed: customers.Account”. I can run SQL querys within the “Sqliteman” GUI program and it gives me timings of the query’s. For example, I selected all last names of Smith and sorted by Last, First and it took 0.265 seconds to return a portion of the 16,886 records. And these fields were not even indexed…Amazing! This is from a standalone SQLite program. SQLite is a great little database but doesn’t have all the bells and whistles of a “real” SQL database such as MySQL or PostgreSQL. I really love this little database and have converted many of my old MS-Access databases to Python/SQLite.