Had similar issue a few years ago; we were using the SQLite3.exe. We
recompiled the exe as 64 bit which allowed it to use more than 4 GB of ram,
loaded the machine with as much memory as we could. Voila, super fast
Can the Python libraries be made 64 bit some how?
On Thu, Nov 30, 2017 at 7:01 PM Keith Medcalf <kmedc...@dessus.com> wrote:
> 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
> 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
> >into Python. The database
> >is using WAL.
> >I've got a database of some 100 million records, and a file of just
> >300 thousand that I want represented in it. I wanted to check how
> >difference it was going to make, so I wrote a super
> >simple program to the read the file and count how many records are
> >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
> >estimate was around 200 dots and hit a wall. It made progress, but
> >very slowly.
> >So I killed it again and added a commit() call every time it output a
> >It didn't hit a wall, just some rough road (that is, it slowed down
> >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
> >The database is not modified.
> > Last Modified: Sat Nov 25 18:56:49 PST 2017
> >import os.path #
> >import sys #
> >import argparse #
> >import sqlite3 #
> >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
> > parser.add_argument("file", nargs='?',
> > help="file containing the qsearch results (default
> > args=parser.parse_args()
> > infile = args.file
> > if args.dbname is None:
> > here=os.path.split(os.path.realpath('.'))
> > for confdir in
> > 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
> > if len(pos) != 64: # Important test to catch grep without
> > 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
> > 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
> > print("out of a total of", lines, "records.")
> >word of the year: *kakistocracy*
> >sqlite-users mailing list
> sqlite-users mailing list
sqlite-users mailing list