Is there an index on pos where ppos is the left-most field (or the only field) in the index? What is the column affinity of ppos? Of the fiold you are passing as a parameter? Is ppos unique?
If you CREATE [UNIQUE] INDEX searchindex ON pos (ppos, pnum); then your query can be satisfied only using the searchindex covering index. If there is not an index on ppos, then you will be wasting time recreating the index for each query. You will probably need to increase the cache size beyond the paltry default in order for the entire btree structures to be cached in RAM -- you probably want to make it as big as you can. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Kevin O'Gorman >Sent: Saturday, 25 November, 2017 20:14 >To: sqlite-users >Subject: [sqlite] Simple read-only program very slow > >I'm pretty new at SQLite, so this may seem obvious to you. Be kind. >I'm using Python on Ubuntu Linux 16.04 LTS, and the sqlite that is >built >into Python. The database >is using WAL. > >I've got a database of some 100 million records, and a file of just >over >300 thousand that I want represented in it. I wanted to check how >much >difference it was going to make, so I wrote a super >simple program to the read the file and count how many records are >already >there. I got impatient waiting for it so I killed the process and >added an >output of one dot (".") per 1000 records. It went very fast for what >I >estimate was around 200 dots and hit a wall. It made progress, but >very >very slowly. > >So I killed it again and added a commit() call every time it output a >dot. >It didn't hit a wall, just some rough road (that is, it slowed down >at >about the same spot but not nearly so drastically). > >The code makes to changes to the database at all. Why does commit() >make a >difference? What else should I learn from this? > >The field being used for the lookup has an index. > >++ kevin > >Code follows: >#!/usr/bin/env python3 >"""Count the number of records that represent rows in the database >'pos' >table. >The database is not modified. > > Last Modified: Sat Nov 25 18:56:49 PST 2017 >""" > >import os.path # >https://docs.python.org/3.5/library/os.path.html >import sys # >https://docs.python.org/3.5/library/sys.html >import argparse # >https://docs.python.org/3.5/library/argparse.html >import sqlite3 # >https://docs.python.org/3.5/library/sqlite3.html >import re # https://docs.python.org/3.5/library/re.html > ># from /usr/local/lib/python3.5/dist-packages >import qcreate >from qerror import * >import myparser > >if __name__ == '__main__': > parser = argparse.ArgumentParser(description="""A program to read >positions and count how many are > in the database""",) > parser.add_argument("--dbname", default=None, > help="name of the database to work on (overrides >qubic.ini >file)") > parser.add_argument("file", nargs='?', >type=argparse.FileType('r'), >default=sys.stdin, > help="file containing the qsearch results (default >stdin)") > args=parser.parse_args() > infile = args.file > > if args.dbname is None: > here=os.path.split(os.path.realpath('.'))[1] > for confdir in >".",os.path.join(os.environ["HOME"],".config"),"/etc/xdg/": > f = os.path.join(confdir, "qubic.ini") > if os.path.exists(f): > args.dbname = myparser.parse(f, here, "dbname") > if args.dbname is not None: > break > if args.dbname is None: > print(" *** ERROR: no database name provided and none >found in >qubic.ini files") > sys.exit(1) > > present = missing = lines = 0 > with sqlite3.connect(args.dbname) as conn: > for line in infile: > fields = line.split() > pos = fields[0] > if len(pos) != 64: # Important test to catch grep without >--no-filename > raise InputError(" ERROR: input line has wrong-sized >position: " + line) > > pnum = None > for row in conn.execute(""" > SELECT pnum > FROM pos > WHERE ppos=? > """,(pos,)): > pnum = row[0] > break > if pnum is None: > missing += 1 > else: > present += 1 > lines += 1 > if lines % 1000 == 0: > print(".",flush=True,end="") > conn.commit() > print("there were",present,"records on file and",missing," were >missing") > print("out of a total of", lines, "records.") > > > >-- >word of the year: *kakistocracy* >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
