W.-H. Gu wrote:
> For (1), after I wrapped inserts into a transaction, I face an issue:
> the cpu usage is too high. I think the reason is that I use prepare
> statement to insert a row at a time, it than does sqlite3_step for
> thousands times every second.
If I'm reading this right, you're saying you're re-preparing the
statement on every row. Don't do that. Prepare it once, and only
re-bind the variables for each insert. Then finalize the statement
after your loop.
I gather that statement preparation is a non-trivial percentage of
the compute time taken to insert a single row.
> Every second I insert 9184 rows,
Your optimal insertion rates will vary strongly depending on your
hardware, your operating system, the efficiency of data generation
between calls to the sqlite api, amount of data you're inserting per
row, amount of RAM sqlite is allowed to use, any indices, triggers,
check constraints, unique constraints, primary keys, and foreign keys
you have defined, compile time flags, and dozens of other variables.
I'm no SQL or SQLite expert myself -- the gurus might add many items
to that list off the tops of their heads.
We have no way of knowing what number you should be shooting for.
> which leads to cpu usage ~30%.
>
> Is there any good way to resolve this issue?
Obviously there are many variables here. Why do you think you want low
CPU usage?
Generally, I think you *want* to be CPU-bound for the duration of your
insert batch. Anything else implies you're doing something besides
useful computation (like waiting for disk i/o (due to a cache spill or
one of many other possible reasons), or scheduled behind some other
process, or something).
At commit time, sqlite will write the new rows back to the disk, by
default blocking til the OS tells SQLite that the write is complete
(though this is configurable). During this time you expect cpu usage
to go down while disk usage goes up.
Another point is that you need to make sure the OS is really telling you
what you think it's telling you. E.g. a common mistake for a Linux user
is to think that the load average is the average CPU usage over some
period of time. It isn't.
Eric
--
Eric A. Smith
One of my most productive days was throwing away 1000 lines of code.
-- Ken Thompson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
