Hi George, On Sat, Oct 03, 2009 at 01:50:31PM -0700, George Hartzell wrote: > > Hi all, > > I use an SQLite database w/ the rtree extension to hold information > about genetic polymorphism (snp's), based on UCSC's mapping work and > their mysql table dumps. My database is write-once, read from then > on. > > One of the tables has almost 19 million rows. This is the table on > which I build my 2-D rtree index. > > I read the data from tab delimited mysql dumps into the basic tables, > then run a query that pulls the data I want to rtree-index out of it's > table, cleans up one of the columns, the inserts it into the rtree > index. Finally I add a set of indices to several columns in the big > table. > > I tried using .import to move the data into the tables, but gave up > after it ran well past the time it took to do the inserts. That > didn't seem to help with the index/rtree creation time either. > > I'm turning synchronous off and doing the inserts inside of a > transaction. > > I was wondering if there was some way to populate the database inside > a ':memory:' database and then dump/copy/... the results into a file. > I've seen posts that suggest that I can select from the memory tables > and insert into the persistent ones, but that seems like it'd take > more work to get the indexes. > > I'd be interested in any suggestions for making my loading go faster.
Have you tried working with the backup API? If all the data will fit into memory, do all the work in a memory database and then use the backup api to dump it to disk. http://sqlite.org/backup.html enjoy, -jeremy -- ======================================================================== Jeremy Hinegardner jer...@hinegardner.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
