Improve performance of Ruby script processing CSV

I wrote a Ruby script to do the following:

• Read very large (2GB / 12,500,000 rows) CSV in SQLite3
• Request db
• Output to new CSV

In my opinion, this is perhaps the easiest and most logical way to do it. This process will need to be tweaked and repeated periodically, hence Script. I use SQLite because the data will always come in CSV format (no access to the original DB) and it's just easier to offload processing into a (easily modifiable) SQL statement.

The problem is that steps 1 and 2 are taking such a long time. I was looking for ways to improve SQLite performance . I have implemented some of these suggestions with limited success.

• Inside SQLite3 memory
• Use transaction (around step 1)
• Use a prepared statement
• PRAGMA synchronous = OFF
  
  
• PRAGMA journal_mode = MEMORY
  
  (not sure if this will help when using an in-memory DB)

After all this, I get the following points:

• Reading time: 17m 28s
• Request execution time: 14 m 26 s
• Recording time: 0m 4s
• Elapsed time: 31m 58s

Suppose I am using a different language for the above post and there are differences such as compiled / interpreted, however the insertion time is around 79,000 vs 12,000 records per second - which is 6x slower.

I have also tried indexing some (or all) of the fields. This actually has the opposite effect. Indexing takes so long that any improvement in query time is completely overshadowed by indexing time. Also, running this DB in memory results in an out of memory error due to the extra space required.

Is SQLite3 not the right DB for this amount of data? I tried the same using MySQL, but the performance was even worse.

Finally, here's a clipped version of the code (some unnecessary subtleties removed).

require 'csv'
require 'sqlite3'

inputFile = ARGV[0]
outputFile = ARGV[1]
criteria1 = ARGV[2]
criteria2 = ARGV[3]
criteria3 = ARGV[4]

begin
    memDb = SQLite3::Database.new ":memory:"
    memDb.execute "PRAGMA synchronous = OFF"
    memDb.execute "PRAGMA journal_mode = MEMORY"

    memDb.execute "DROP TABLE IF EXISTS Area"
    memDb.execute "CREATE TABLE IF NOT EXISTS Area (StreetName TEXT, StreetType TEXT, Locality TEXT, State TEXT, PostCode INTEGER, Criteria1 REAL, Criteria2 REAL, Criteria3 REAL)" 
    insertStmt = memDb.prepare "INSERT INTO Area VALUES(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)"

    # Read values from file
    readCounter = 0
    memDb.execute "BEGIN TRANSACTION"
    blockReadTime = Time.now
    CSV.foreach(inputFile) { |line|

        readCounter += 1
        break if readCounter > 100000
        if readCounter % 10000 == 0
            formattedReadCounter = readCounter.to_s.reverse.gsub(/...(?=.)/,'\&,').reverse
            print "\rReading line #{formattedReadCounter} (#{Time.now - blockReadTime}s)     " 
            STDOUT.flush
            blockReadTime = Time.now
        end

        insertStmt.execute (line[6]||"").gsub("'", "''"), (line[7]||"").gsub("'", "''"), (line[9]||"").gsub("'", "''"), line[10], line[11], line[12], line[13], line[14]
    }
    memDb.execute "END TRANSACTION"
    insertStmt.close

    # Process values
    sqlQuery = <<eos
    SELECT DISTINCT
        '*',
        '*',
        Locality,
        State,
        PostCode
    FROM
        Area
    GROUP BY
        Locality,
        State,
        PostCode
    HAVING
        MAX(Criteria1) <= #{criteria1}
        AND
        MAX(Criteria2) <= #{criteria2}
        AND
        MAX(Criteria3) <= #{criteria3}
    UNION
    SELECT DISTINCT
        StreetName,
        StreetType,
        Locality,
        State,
        PostCode
    FROM
        Area
    WHERE
        Locality NOT IN (
            SELECT
                Locality
            FROM
                Area
            GROUP BY
                Locality
            HAVING
                MAX(Criteria1) <= #{criteria1}
                AND
                MAX(Criteria2) <= #{criteria2}
                AND
                MAX(Criteria3) <= #{criteria3}
            )
    GROUP BY
        StreetName,
        StreetType,
        Locality,
        State,
        PostCode
    HAVING
        MAX(Criteria1) <= #{criteria1}
        AND
        MAX(Criteria2) <= #{criteria2}
        AND
        MAX(Criteria3) <= #{criteria3}
eos
    statement = memDb.prepare sqlQuery

    # Output to CSV
    csvFile = CSV.open(outputFile, "wb")
    resultSet = statement.execute
    resultSet.each { |row|  csvFile << row}
    csvFile.close

rescue SQLite3::Exception => ex
    puts "Excepion occurred: #{ex}"
ensure
    statement.close if statement
    memDb.close if memDb
end

      

        
        
        
      

    

Please feel free to poke fun at my naive Ruby encoding - which doesn't kill me, we hope to make me a stronger coder.