Re: [sqlite] quickly insert multiple rows into multiple table

[W.-H. Gu]

Hi,

   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.

   Every second I insert 9184 rows, which leads to cpu usage ~30%.

   Is there any good way to resolve this issue?

  Thanks,

   WeiHsin

On Mon, Jul 12, 2010 at 10:48 AM, Kees Nuyt <k.n...@zonnet.nl> wrote:

> 1On Mon, 12 Jul 2010 09:29:35 -0700, "W.-H. Gu"
> <weihsi...@gmail.com> wrote:
>
> >Hi,
> >
> >  I have 5 tables. Every second I want to insert 1 row into the main
> table,
> >and multiple rows into every other table. What I do right now is to
> prepare
> >5 statements for those 5 tables, where the statements for multiple row
> >insertion is like
> >
> >  INSERT INTO MyTable (FirstCol, SecondCol)
> >  SELECT 'First' ,1
> >  UNION ALL
> >  SELECT 'Second' ,2
> >  UNION ALL
> >  SELECT 'Third' ,3
> >  UNION ALL
> >  SELECT 'Fourth' ,4
> >  UNION ALL
> >  SELECT 'Fifth' ,5
> >
> >  My questions are:
> >
> >  (1) Is the above statement for multiple row insertion efficient? What is
> >the best approach?
>
> That is a very complicated way to combine multiple insert
> statements.
>
> Just wrap the INSERTs (several thousands of them is not a
> problem) into a transaction:
>
> BEGIN EXCLUSIVE TRANSACTION;
> INSERT INTO MyTable (FirstCol, SecondCol)
>   VALUES ('First' ,1);
> INSERT INTO MyTable (FirstCol, SecondCol)
>   VALUES ('Second' ,2);
> INSERT INTO MyTable (FirstCol, SecondCol)
>   VALUES ('Third' ,3);
> INSERT INTO MyTable (FirstCol, SecondCol)
>   VALUES ('Fourth' ,4);
> INSERT INTO MyTable (FirstCol, SecondCol)
>  VALUES ('Fifth' ,5);
> COMMIT;
>
> >  (2) Should I need to bind parameters every time when inserting? Or is
> >there a way to bind pointers so that I just need to do sqlite3_step every
> >time when I insert rows?
>
> I think you have to bind. You make it sound like a lot of
> work, but you only have to write the code once
>
> >  (3) Is there a way to speed up the whole process? For example, disable
> >index management first and enable it again after bulk insert, or one
> prepare
> >statement for multiple row multiple table insertion, etc..
>
> a) Normalize redundancy out of the schema.
> b) Wrap many inserts in a transaction
> c) Before bulk load, you can indeed DROP indexes and CREATE
> them again later to get some speed advantage
> d) sort the data in primary key order before inserting
> e) Give the database as much cache as you can (but not more
> than the estimated database size)
> f) fast hardware (especially disks)
>
> I think there are a few more suggestions in the wiki on
> http://www.sqlite.org
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] quickly insert multiple rows into multiple table

[W.-H. Gu]

Hi,

  I have 5 tables. Every second I want to insert 1 row into the main table,
and multiple rows into every other table. What I do right now is to prepare
5 statements for those 5 tables, where the statements for multiple row
insertion is like

  INSERT INTO MyTable (FirstCol, SecondCol)
  SELECT 'First' ,1
  UNION ALL
  SELECT 'Second' ,2
  UNION ALL
  SELECT 'Third' ,3
  UNION ALL
  SELECT 'Fourth' ,4
  UNION ALL
  SELECT 'Fifth' ,5

  My questions are:

  (1) Is the above statement for multiple row insertion efficient? What is
the best approach?
  (2) Should I need to bind parameters every time when inserting? Or is
there a way to bind pointers so that I just need to do sqlite3_step every
time when I insert rows?
  (3) Is there a way to speed up the whole process? For example, disable
index management first and enable it again after bulk insert, or one prepare
statement for multiple row multiple table insertion, etc..

  Thanks in advance,

  WeiHsin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Still have high cpu usage when deleting old data with indices

[W.-H. Gu]

Hi,

  In my C application, I create a table and insert 32 rows every second.
Each row has about 28KB. Every 5 seconds, it deletes old rows with timestamp
<= current time - PERIOD. I did create an index on the column 'timestamp,'
but I observed that every 5 seconds, cpu usage hits up to 9%. Without index,
cpu usage is just a bit more (10~11%). This doesn't happen when I disable
the delete operations.

  My question is: is there a discussion saying that delete in SQLite may use
more cpu usage than others like MySQL? is there a way to fix it? or perhaps
I am just missing something?

  Thanks,

  WeiHsin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to disable fsync() in SQLite

[W.-H. Gu]

Hi,

   Is there a way to disable fsync() in my SQLite application on Linux? I
saw a discussion of SQLite performance at
http://www.sqlite.org/speed.htmland it has some results with option
'nosync.' I am wondering if I can do the
same thing as there to see how fsync() effects the performance in my
applicaion.

   Thanks,

WeiHsin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users