I cannot really answer the question whether there are missed optimization
opportunities in Nim's SQLite wrapper, but being a wrapper, it sounds unlikely.
There are some things about your code I can comment on:
lookup_id = p.rowEntry(col).split('|')[0]
lookup_name_source = p.rowEntry(col).split('|')[1].toUpperAscii()
This is inefficient because you are splitting the string multiple times. You
can do this instead:
let colContent = p.rowEntry(col).split('|')
template lookup_id(): untyped = colContent[0]
template lookup_name_source(): untyped = colContent[1]
But that concerns the CSV and should not be much of a performance hog (unless
your CSV is very large).
Now the actual part where you could use optimization are these lines:
for row in mydb.fastRows(sql"select count(*) from tb_reference_pj where
company match ?", (lookup_name_source)):
if row[0] != "":
What are they for? the result will never be `""`, because `count(*)` always
returns some number (correct me if I'm wrong, I haven't done SQL for some
time). You are issuing a potentially expensive query here for no reason.
Onto the next query:
for r in mydb.fastRows(sql"select * from tb_reference_pj where company
match ?", (lookup_name_source)):
You are querying every column value from a table although you are using only
one value afterwards. Okay, to be fair, the following lines are probably not
your actual code, right? Note that if you did the template thing I showed, you
have to use `lookup_name_source()` here.
Your code would probably be faster by just removing the first query.
