Performance of fastRows at module db_sqlite

[alfrednewman]

Greetings,

I have a big production SQLite table with 4 Gig+ and around 12M records. (named 
mydb.db3)

The code below is looking for 10 records (of lookup_input.csv) at mydb using 
the FTS feature available at SQLite.

Given the thread 
[http://forum.nim-lang.org/t/2539#15760](http://forum.nim-lang.org///forum.nim-lang.org/t/2539#15760)
 I am using the latest DLL of SQlite in my project.

The Nim code is spending around 14 secs to do the job. My Python code does 
exactly the same spending just 9 secs.

Just some thoughts:

  * Does the Nim module db_sqlite is properly optimized ? (comparing with the 
Python one)
  * Am I doing something wrong in my Nim code ?



Best,
    
    
    import strutils, db_sqlite, streams, parsecsv, times
    
    proc lookup_match(): string =
      var
        p: CsvParser
        company : string
        lookup_id = ""
        lookup_name_source = ""
      
      let mydb = open(r"C:\Users\AlfredN\mydb.db3", nil, nil, nil)
      p.open(r"C:\Users\AlfredN\lookup_input.csv")
      p.readHeaderRow()
      
      while p.readRow():
        for col in items(p.headers):
          lookup_id = p.rowEntry(col).split('|')[0]
          lookup_name_source = p.rowEntry(col).split('|')[1].toUpperAscii()
          for row in mydb.fastRows(sql"select count(*) from tb_reference_pj 
where company match ?", (lookup_name_source)):
            if row[0] != "":
              for r in mydb.fastRows(sql"select * from tb_reference_pj where 
company match ?", (lookup_name_source)):
                if len(r[1]) <= 80:
                  company = r[1]
                  if lookup_name_source == company:
                    echo company
      
      mydb.close()
      p.close()
    
    when isMainModule:
      let t0 = cpuTime()
      echo lookup_match()
      let t1 = cpuTime()
      echo "* Time elapsed *: ", $(t1 - t0)