On Wed, Oct 15, 2008 at 01:38:16PM -0500, David Clark scratched on the wall:
> Ok I want to convert a large application which uses ad-hoc data
> structures and file formats to a sqlite database. But before I do that
> I am working on a proof of concept test to show that yea it will work
> once conversion is done.
>
> But alas my first test is failing miserably. Well, it works it just
> takes way too long. Like at least all night.
> I create table tbl2
> f1 varchar(30)
> f2 varchar(30)
> f3 varchar(30)
> identity integer primary index autoincrement
>
> That works I have the table.
> I go to insert into with
> insert into tbl2 values ("00000001", "00000001", "00000001", NULL);
>
> insert into tbl2 values ("00000002", "00000002", "00000002", NULL);
>
> Ok I did each insert as separate query using the 5 minute example and
> it took all night and never did complete.
You need to wrap them up in a transaction.
Also, if you want to store integers, store integers. SQLite allows
the same column to store records of different types.
Also, string-literals in SQL use single quotes.
> So then I decided...ok the fsync() for serialization is the problem.
>
> So I looked at limits.html and make a single query out of as many as
> would fit in a 1000000 byte buffer. That turned out to be
> 15625 inserts. That should be one fsync per query so should be much
> faster...but alas I don't think it is.
That won't process them as one statement. Even if you pass them into
SQLite as a big command buffer, they're still processed one at a
time.
Issue the command "BEGIN", do 100 to 10000 INSERTs, issue a "COMMIT".
You should see a very noticeable difference in speed.
> My second test would be a random query into this large table to see
> how long that took. Then random queries from a large
> number of threads. Get a query time under 1 second for pulling up a
> record...and this product is so in my application.
Depending on your query needs, you might need to look into indexes.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
