Re: [sqlite] Pragma Synchronous=OFF is not working

[Simon Slavin]

On 28 Aug 2012, at 5:34pm, kritesh tripathi  wrote:

> Is performance due to hardware like SD card
> speed or Filesystem ?

SD cards are very slow to read and write to.  They have to use almost no power 
at all.  I think any PRAGMA settings you may do are being drowned by how slow 
it is to access the SD card.

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

Re: [sqlite] Pragma Synchronous=OFF is not working

[Black, Michael (IS)]

And...once you confirm an in-memory database helps you, then you can try WAL 
mode and keep the DB on the SD card and see how that works for you.

I don't know what you're trying to do since loading the images is already 66% 
of your time.  I guess that's OK with your application?

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, August 28, 2012 11:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working

Hi,

Sorry but i am not using memory database since sqlite version -3.6.4 does
not support i guess ..I am not indexing any data and i am sure that all
insertion ,   i am doing under one transaction . Do you think creating
index or i n memory database or binding the values using sqlite_binding
will effect the performance .. Is performance due to hardware like SD card
speed or Filesystem ?

Cheers
kritesh





On Tue, Aug 28, 2012 at 9:55 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:

> So you're already doing the smart thinggoodand I believe you did
> say you're using a memory database, right?  Not storing it on the SD card?
>
> Are you able to run your timing test on a standard PC?
>
> 50 inserts/sec is definitely NOT fast (that's your .02 number).
> Thousands/sec is more like it on a standard PC.
>
> Did you create any indexes on your data?
>
> Are you sure you have the entire load process in one transaction?
>
> Care to show us your code?
>
> Another thing you can do is output SQL statements to stdout so you can run
> those inserts inside an sqlite3 shell and test different configurations
> that way.
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> Sent: Tuesday, August 28, 2012 11:09 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
>
> HI Michael,
>
> I am not stroing the video data in the table .For example -Suppose i have
> one image - ABC.jpg  in the folder contains in SD Card
> 1- First i am parsing the image
> 2- Second Get the values like - Fullpath (Wht is the exact path of image in
> SD Card),file Size and create unique id .
> 3-Third , I am inserting these values in Image table .
>
>
> Executing the same steps for all images (folder where all images stored )
> in  SD card .
>
> Total time to  parsing and insert the 500 image  =42 sec
> Time to insert one image in table after parsing = .02 Sec
> Time to parsing one images  = .04 sec
>
> Cheers
> kritesh
>
> On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) <
> michael.bla...@ngc.com
> > wrote:
>
> > I think the first thing you'll hear is to NOT store the video data in the
> > database.
> > Just store a file path.  That is much faster and should complete a lot
> > faster than your expectations.
> >
> > Is there some specific reason why you want the blob data in your
> database?
> >
> > How long does it take you just to read and parse the images and skip the
> > database insert?
> >
> > What exactly are you inserting in those 3 tables?  Sounds like some big
> > data.
> >
> >
> > Michael D. Black
> > Senior Scientist
> > Advanced Analytics Directorate
> > Advanced GEOINT Solutions Operating Unit
> > Northrop Grumman Information Systems
> >
> > ____________
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> > on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> > Sent: Tuesday, August 28, 2012 10:31 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
> >
> > Hi Michael,
> >
> > Righ Now i am inserting only 500 records in  three different tables in 42
> > sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am
> > parsing and then inserting one by one in the video table or image table
> > . I am expecting this  in between 10-20 sec . Presently i am using the
> > Micro itron embedded RTOS in my device .
> >
> > ___
> > sqlite-users mailing list
> &

Re: [sqlite] Pragma Synchronous=OFF is not working

[kritesh tripathi]

Thanks alot Michael .lets upgrade it and hope i need your help in case
getting stuck:)

Cheers
Kritesh

On Tue, Aug 28, 2012 at 10:05 PM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

> Oh yeah...you need to upgrade your sqlite.
>
> You are hitting the SD card which is dog slow.
>
> Keeping your DB in memory may solve all of your database speed concerns.
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> Sent: Tuesday, August 28, 2012 11:34 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
>
> Hi,
>
> Sorry but i am not using memory database since sqlite version -3.6.4 does
> not support i guess ..I am not indexing any data and i am sure that all
> insertion ,   i am doing under one transaction . Do you think creating
> index or i n memory database or binding the values using sqlite_binding
> will effect the performance .. Is performance due to hardware like SD card
> speed or Filesystem ?
>
> Cheers
> kritesh
>
>
>
>
>
> On Tue, Aug 28, 2012 at 9:55 PM, Black, Michael (IS) <
> michael.bla...@ngc.com
> > wrote:
>
> > So you're already doing the smart thinggoodand I believe you did
> > say you're using a memory database, right?  Not storing it on the SD
> card?
> >
> > Are you able to run your timing test on a standard PC?
> >
> > 50 inserts/sec is definitely NOT fast (that's your .02 number).
> > Thousands/sec is more like it on a standard PC.
> >
> > Did you create any indexes on your data?
> >
> > Are you sure you have the entire load process in one transaction?
> >
> > Care to show us your code?
> >
> > Another thing you can do is output SQL statements to stdout so you can
> run
> > those inserts inside an sqlite3 shell and test different configurations
> > that way.
> >
> >
> >
> > Michael D. Black
> > Senior Scientist
> > Advanced Analytics Directorate
> > Advanced GEOINT Solutions Operating Unit
> > Northrop Grumman Information Systems
> >
> > ________________
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> > on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> > Sent: Tuesday, August 28, 2012 11:09 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
> >
> > HI Michael,
> >
> > I am not stroing the video data in the table .For example -Suppose i have
> > one image - ABC.jpg  in the folder contains in SD Card
> > 1- First i am parsing the image
> > 2- Second Get the values like - Fullpath (Wht is the exact path of image
> in
> > SD Card),file Size and create unique id .
> > 3-Third , I am inserting these values in Image table .
> >
> >
> > Executing the same steps for all images (folder where all images stored )
> > in  SD card .
> >
> > Total time to  parsing and insert the 500 image  =42 sec
> > Time to insert one image in table after parsing = .02 Sec
> > Time to parsing one images  = .04 sec
> >
> > Cheers
> > kritesh
> >
> > On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) <
> > michael.bla...@ngc.com
> > > wrote:
> >
> > > I think the first thing you'll hear is to NOT store the video data in
> the
> > > database.
> > > Just store a file path.  That is much faster and should complete a lot
> > > faster than your expectations.
> > >
> > > Is there some specific reason why you want the blob data in your
> > database?
> > >
> > > How long does it take you just to read and parse the images and skip
> the
> > > database insert?
> > >
> > > What exactly are you inserting in those 3 tables?  Sounds like some big
> > > data.
> > >
> > >
> > > Michael D. Black
> > > Senior Scientist
> > > Advanced Analytics Directorate
> > > Advanced GEOINT Solutions Operating Unit
> > > Northrop Grumman Information Systems
> > >
> > > 
> > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org
> ]
> > > on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> > > 

Re: [sqlite] Pragma Synchronous=OFF is not working

[Black, Michael (IS)]

Oh yeah...you need to upgrade your sqlite.

You are hitting the SD card which is dog slow.

Keeping your DB in memory may solve all of your database speed concerns.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, August 28, 2012 11:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working

Hi,

Sorry but i am not using memory database since sqlite version -3.6.4 does
not support i guess ..I am not indexing any data and i am sure that all
insertion ,   i am doing under one transaction . Do you think creating
index or i n memory database or binding the values using sqlite_binding
will effect the performance .. Is performance due to hardware like SD card
speed or Filesystem ?

Cheers
kritesh





On Tue, Aug 28, 2012 at 9:55 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:

> So you're already doing the smart thinggoodand I believe you did
> say you're using a memory database, right?  Not storing it on the SD card?
>
> Are you able to run your timing test on a standard PC?
>
> 50 inserts/sec is definitely NOT fast (that's your .02 number).
> Thousands/sec is more like it on a standard PC.
>
> Did you create any indexes on your data?
>
> Are you sure you have the entire load process in one transaction?
>
> Care to show us your code?
>
> Another thing you can do is output SQL statements to stdout so you can run
> those inserts inside an sqlite3 shell and test different configurations
> that way.
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> Sent: Tuesday, August 28, 2012 11:09 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
>
> HI Michael,
>
> I am not stroing the video data in the table .For example -Suppose i have
> one image - ABC.jpg  in the folder contains in SD Card
> 1- First i am parsing the image
> 2- Second Get the values like - Fullpath (Wht is the exact path of image in
> SD Card),file Size and create unique id .
> 3-Third , I am inserting these values in Image table .
>
>
> Executing the same steps for all images (folder where all images stored )
> in  SD card .
>
> Total time to  parsing and insert the 500 image  =42 sec
> Time to insert one image in table after parsing = .02 Sec
> Time to parsing one images  = .04 sec
>
> Cheers
> kritesh
>
> On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) <
> michael.bla...@ngc.com
> > wrote:
>
> > I think the first thing you'll hear is to NOT store the video data in the
> > database.
> > Just store a file path.  That is much faster and should complete a lot
> > faster than your expectations.
> >
> > Is there some specific reason why you want the blob data in your
> database?
> >
> > How long does it take you just to read and parse the images and skip the
> > database insert?
> >
> > What exactly are you inserting in those 3 tables?  Sounds like some big
> > data.
> >
> >
> > Michael D. Black
> > Senior Scientist
> > Advanced Analytics Directorate
> > Advanced GEOINT Solutions Operating Unit
> > Northrop Grumman Information Systems
> >
> > ____________
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> > on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> > Sent: Tuesday, August 28, 2012 10:31 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
> >
> > Hi Michael,
> >
> > Righ Now i am inserting only 500 records in  three different tables in 42
> > sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am
> > parsing and then inserting one by one in the video table or image table
> > . I am expecting this  in between 10-20 sec . Presently i am using the
> > Micro itron embedded RTOS in my device .
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sql

Re: [sqlite] Pragma Synchronous=OFF is not working

[kritesh tripathi]

Hi,

Sorry but i am not using memory database since sqlite version -3.6.4 does
not support i guess ..I am not indexing any data and i am sure that all
insertion ,   i am doing under one transaction . Do you think creating
index or i n memory database or binding the values using sqlite_binding
will effect the performance .. Is performance due to hardware like SD card
speed or Filesystem ?

Cheers
kritesh





On Tue, Aug 28, 2012 at 9:55 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:

> So you're already doing the smart thinggoodand I believe you did
> say you're using a memory database, right?  Not storing it on the SD card?
>
> Are you able to run your timing test on a standard PC?
>
> 50 inserts/sec is definitely NOT fast (that's your .02 number).
> Thousands/sec is more like it on a standard PC.
>
> Did you create any indexes on your data?
>
> Are you sure you have the entire load process in one transaction?
>
> Care to show us your code?
>
> Another thing you can do is output SQL statements to stdout so you can run
> those inserts inside an sqlite3 shell and test different configurations
> that way.
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> Sent: Tuesday, August 28, 2012 11:09 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
>
> HI Michael,
>
> I am not stroing the video data in the table .For example -Suppose i have
> one image - ABC.jpg  in the folder contains in SD Card
> 1- First i am parsing the image
> 2- Second Get the values like - Fullpath (Wht is the exact path of image in
> SD Card),file Size and create unique id .
> 3-Third , I am inserting these values in Image table .
>
>
> Executing the same steps for all images (folder where all images stored )
> in  SD card .
>
> Total time to  parsing and insert the 500 image  =42 sec
> Time to insert one image in table after parsing = .02 Sec
> Time to parsing one images  = .04 sec
>
> Cheers
> kritesh
>
> On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) <
> michael.bla...@ngc.com
> > wrote:
>
> > I think the first thing you'll hear is to NOT store the video data in the
> > database.
> > Just store a file path.  That is much faster and should complete a lot
> > faster than your expectations.
> >
> > Is there some specific reason why you want the blob data in your
> database?
> >
> > How long does it take you just to read and parse the images and skip the
> > database insert?
> >
> > What exactly are you inserting in those 3 tables?  Sounds like some big
> > data.
> >
> >
> > Michael D. Black
> > Senior Scientist
> > Advanced Analytics Directorate
> > Advanced GEOINT Solutions Operating Unit
> > Northrop Grumman Information Systems
> >
> > ____________
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> > on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> > Sent: Tuesday, August 28, 2012 10:31 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
> >
> > Hi Michael,
> >
> > Righ Now i am inserting only 500 records in  three different tables in 42
> > sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am
> > parsing and then inserting one by one in the video table or image table
> > . I am expecting this  in between 10-20 sec . Presently i am using the
> > Micro itron embedded RTOS in my device .
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Regards
> kritesh tripathi
> ___
> 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
>



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

Re: [sqlite] Pragma Synchronous=OFF is not working

[Black, Michael (IS)]

So you're already doing the smart thinggoodand I believe you did say 
you're using a memory database, right?  Not storing it on the SD card?

Are you able to run your timing test on a standard PC?

50 inserts/sec is definitely NOT fast (that's your .02 number). Thousands/sec 
is more like it on a standard PC.

Did you create any indexes on your data?

Are you sure you have the entire load process in one transaction?

Care to show us your code?

Another thing you can do is output SQL statements to stdout so you can run 
those inserts inside an sqlite3 shell and test different configurations that 
way.



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, August 28, 2012 11:09 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working

HI Michael,

I am not stroing the video data in the table .For example -Suppose i have
one image - ABC.jpg  in the folder contains in SD Card
1- First i am parsing the image
2- Second Get the values like - Fullpath (Wht is the exact path of image in
SD Card),file Size and create unique id .
3-Third , I am inserting these values in Image table .


Executing the same steps for all images (folder where all images stored )
in  SD card .

Total time to  parsing and insert the 500 image  =42 sec
Time to insert one image in table after parsing = .02 Sec
Time to parsing one images  = .04 sec

Cheers
kritesh

On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:

> I think the first thing you'll hear is to NOT store the video data in the
> database.
> Just store a file path.  That is much faster and should complete a lot
> faster than your expectations.
>
> Is there some specific reason why you want the blob data in your database?
>
> How long does it take you just to read and parse the images and skip the
> database insert?
>
> What exactly are you inserting in those 3 tables?  Sounds like some big
> data.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> Sent: Tuesday, August 28, 2012 10:31 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
>
> Hi Michael,
>
> Righ Now i am inserting only 500 records in  three different tables in 42
> sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am
> parsing and then inserting one by one in the video table or image table
> . I am expecting this  in between 10-20 sec . Presently i am using the
> Micro itron embedded RTOS in my device .
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Regards
kritesh tripathi
___
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

Re: [sqlite] Pragma Synchronous=OFF is not working

[kritesh tripathi]

HI Michael,

I am not stroing the video data in the table .For example -Suppose i have
one image - ABC.jpg  in the folder contains in SD Card
1- First i am parsing the image
2- Second Get the values like - Fullpath (Wht is the exact path of image in
SD Card),file Size and create unique id .
3-Third , I am inserting these values in Image table .


Executing the same steps for all images (folder where all images stored )
in  SD card .

Total time to  parsing and insert the 500 image  =42 sec
Time to insert one image in table after parsing = .02 Sec
Time to parsing one images  = .04 sec

Cheers
kritesh

On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:

> I think the first thing you'll hear is to NOT store the video data in the
> database.
> Just store a file path.  That is much faster and should complete a lot
> faster than your expectations.
>
> Is there some specific reason why you want the blob data in your database?
>
> How long does it take you just to read and parse the images and skip the
> database insert?
>
> What exactly are you inserting in those 3 tables?  Sounds like some big
> data.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> Sent: Tuesday, August 28, 2012 10:31 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
>
> Hi Michael,
>
> Righ Now i am inserting only 500 records in  three different tables in 42
> sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am
> parsing and then inserting one by one in the video table or image table
> . I am expecting this  in between 10-20 sec . Presently i am using the
> Micro itron embedded RTOS in my device .
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Pragma Synchronous=OFF is not working

[Black, Michael (IS)]

I think the first thing you'll hear is to NOT store the video data in the 
database.
Just store a file path.  That is much faster and should complete a lot faster 
than your expectations.

Is there some specific reason why you want the blob data in your database?

How long does it take you just to read and parse the images and skip the 
database insert?

What exactly are you inserting in those 3 tables?  Sounds like some big data.


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, August 28, 2012 10:31 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working

Hi Michael,

Righ Now i am inserting only 500 records in  three different tables in 42
sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am
parsing and then inserting one by one in the video table or image table
. I am expecting this  in between 10-20 sec . Presently i am using the
Micro itron embedded RTOS in my device .

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

Re: [sqlite] Pragma Synchronous=OFF is not working

[kritesh tripathi]

Hi Michael,

Righ Now i am inserting only 500 records in  three different tables in 42
sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am
parsing and then inserting one by one in the video table or image table
. I am expecting this  in between 10-20 sec . Presently i am using the
Micro itron embedded RTOS in my device .

Cheers
kritesh
On Tue, Aug 28, 2012 at 8:48 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wro te:

> Tell us what kind of speed you're seeing.  And what your insert looks like.
> Then tell us what you expect.
>
> Then we can tell you if your expectations are reasonable or if you're
> already getting as much speed as one can expect.
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
> Sent: Tuesday, August 28, 2012 10:04 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working
>
> Thanks to suugestion
>
> I am using the Sqlite version -3.6.4  and support for WAL mode started from
> version 3.7.0.If i upgrade to recent  sqlite version then is any possibilty
> to increase in performance or any other good idea .
>
> Cheers
> kritesh
>
>
> On Tue, Aug 28, 2012 at 8:11 PM, Jonathan Engle <jon_en...@kace.com>
> wrote:
>
> > Try WAL mode.
> >
> > On Aug 28, 2012, at 9:38 AM, kritesh tripathi wrote:
> >
> > > Hi Pavel,
> > >
> > > Thanks for the information . I am using all insert under one
> transection
> > > hence may be the reson its not effecting much . But do you suggest me
> the
> > > way to increase more insert speed what i need to do in this scenario.
> Is
> > > same happen for all pragma like Page_Size or Journal_Mode or Cache_size
> > ? i
> > > tried to change the values of all pragma but i think no one effect the
> > > spped .
> > >
> > > In case i will bind the values and only prepare the stament once then
> do
> > > you think any improvement ?
> > >
> > > Cheers
> > > kritesh
> > >
> > > On Tue, Aug 28, 2012 at 7:43 PM, Pavel Ivanov <paiva...@gmail.com>
> > wrote:
> > >
> > >> If all your inserts are in one transaction then pragma synchronous =
> > >> OFF won't affect your transaction speed too much. To understand
> > >> whether this pragma works or not you should measure how long it takes
> > >> to execute COMMIT (just this one statement). With synchronous = OFF
> > >> COMMIT will be executed much faster.
> > >>
> > >> Pavel
> > >>
> > >>
> > >> On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh
> > >> <tripathi.krit...@gmail.com> wrote:
> > >>> Hi ,
> > >>>
> > >>> I am executing all below mentioned pragma before start the (BEGIN
> > >> --COMMIT)
> > >>> transaction in sqlite version (3.6.4)
> > >>>
> > >>> sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL,
> );
> > >>> sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL,
> > >> );
> > >>> sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL,
> > >> );
> > >>> sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL,
> > >> );
> > >>>
> > >>>
> > >>> In transaction , I am inserting the values in the table but I dnt
> know
> > >> the
> > >>> specific reason why does not pragma effecting  the insert speed .. I
> am
> > >>> getting the same speed even i use the pragma or not . please help
> > >>>
> > >>> Is these pragma effect take place in transaction ?
> > >>>
> > >>> Cheers
> > >>> kritesh
> > >>>
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> View this message in context:
> > >>
> >
> http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.html
> > >>> Sent from the SQLite mailing list archive at Nabble.com.
> > >>> ___
> > >>> sqlite-users mailing list
> > >>> sqlite-users@sqlite.org
> > >>> http://sqlite.org:8080/cgi-

Re: [sqlite] Pragma Synchronous=OFF is not working

[Black, Michael (IS)]

Tell us what kind of speed you're seeing.  And what your insert looks like.
Then tell us what you expect.

Then we can tell you if your expectations are reasonable or if you're already 
getting as much speed as one can expect.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, August 28, 2012 10:04 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working

Thanks to suugestion

I am using the Sqlite version -3.6.4  and support for WAL mode started from
version 3.7.0.If i upgrade to recent  sqlite version then is any possibilty
to increase in performance or any other good idea .

Cheers
kritesh


On Tue, Aug 28, 2012 at 8:11 PM, Jonathan Engle <jon_en...@kace.com> wrote:

> Try WAL mode.
>
> On Aug 28, 2012, at 9:38 AM, kritesh tripathi wrote:
>
> > Hi Pavel,
> >
> > Thanks for the information . I am using all insert under one transection
> > hence may be the reson its not effecting much . But do you suggest me the
> > way to increase more insert speed what i need to do in this scenario. Is
> > same happen for all pragma like Page_Size or Journal_Mode or Cache_size
> ? i
> > tried to change the values of all pragma but i think no one effect the
> > spped .
> >
> > In case i will bind the values and only prepare the stament once then do
> > you think any improvement ?
> >
> > Cheers
> > kritesh
> >
> > On Tue, Aug 28, 2012 at 7:43 PM, Pavel Ivanov <paiva...@gmail.com>
> wrote:
> >
> >> If all your inserts are in one transaction then pragma synchronous =
> >> OFF won't affect your transaction speed too much. To understand
> >> whether this pragma works or not you should measure how long it takes
> >> to execute COMMIT (just this one statement). With synchronous = OFF
> >> COMMIT will be executed much faster.
> >>
> >> Pavel
> >>
> >>
> >> On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh
> >> <tripathi.krit...@gmail.com> wrote:
> >>> Hi ,
> >>>
> >>> I am executing all below mentioned pragma before start the (BEGIN
> >> --COMMIT)
> >>> transaction in sqlite version (3.6.4)
> >>>
> >>> sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL, );
> >>> sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL,
> >> );
> >>> sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL,
> >> );
> >>> sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL,
> >> );
> >>>
> >>>
> >>> In transaction , I am inserting the values in the table but I dnt know
> >> the
> >>> specific reason why does not pragma effecting  the insert speed .. I am
> >>> getting the same speed even i use the pragma or not . please help
> >>>
> >>> Is these pragma effect take place in transaction ?
> >>>
> >>> Cheers
> >>> kritesh
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> View this message in context:
> >>
> http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.html
> >>> Sent from the SQLite mailing list archive at Nabble.com.
> >>> ___
> >>> 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
> >>
> >
> >
> >
> > --
> > Regards
> > kritesh tripathi
> > ___
> > 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
>



--
Regards
kritesh tripathi
___
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

Re: [sqlite] Pragma Synchronous=OFF is not working

[kritesh tripathi]

Thanks to suugestion

I am using the Sqlite version -3.6.4  and support for WAL mode started from
version 3.7.0.If i upgrade to recent  sqlite version then is any possibilty
to increase in performance or any other good idea .

Cheers
kritesh


On Tue, Aug 28, 2012 at 8:11 PM, Jonathan Engle  wrote:

> Try WAL mode.
>
> On Aug 28, 2012, at 9:38 AM, kritesh tripathi wrote:
>
> > Hi Pavel,
> >
> > Thanks for the information . I am using all insert under one transection
> > hence may be the reson its not effecting much . But do you suggest me the
> > way to increase more insert speed what i need to do in this scenario. Is
> > same happen for all pragma like Page_Size or Journal_Mode or Cache_size
> ? i
> > tried to change the values of all pragma but i think no one effect the
> > spped .
> >
> > In case i will bind the values and only prepare the stament once then do
> > you think any improvement ?
> >
> > Cheers
> > kritesh
> >
> > On Tue, Aug 28, 2012 at 7:43 PM, Pavel Ivanov 
> wrote:
> >
> >> If all your inserts are in one transaction then pragma synchronous =
> >> OFF won't affect your transaction speed too much. To understand
> >> whether this pragma works or not you should measure how long it takes
> >> to execute COMMIT (just this one statement). With synchronous = OFF
> >> COMMIT will be executed much faster.
> >>
> >> Pavel
> >>
> >>
> >> On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh
> >>  wrote:
> >>> Hi ,
> >>>
> >>> I am executing all below mentioned pragma before start the (BEGIN
> >> --COMMIT)
> >>> transaction in sqlite version (3.6.4)
> >>>
> >>> sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL, );
> >>> sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL,
> >> );
> >>> sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL,
> >> );
> >>> sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL,
> >> );
> >>>
> >>>
> >>> In transaction , I am inserting the values in the table but I dnt know
> >> the
> >>> specific reason why does not pragma effecting  the insert speed .. I am
> >>> getting the same speed even i use the pragma or not . please help
> >>>
> >>> Is these pragma effect take place in transaction ?
> >>>
> >>> Cheers
> >>> kritesh
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> View this message in context:
> >>
> http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.html
> >>> Sent from the SQLite mailing list archive at Nabble.com.
> >>> ___
> >>> 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
> >>
> >
> >
> >
> > --
> > Regards
> > kritesh tripathi
> > ___
> > 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
>



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

Re: [sqlite] Pragma Synchronous=OFF is not working

[Jonathan Engle]

Try WAL mode.

On Aug 28, 2012, at 9:38 AM, kritesh tripathi wrote:

> Hi Pavel,
> 
> Thanks for the information . I am using all insert under one transection
> hence may be the reson its not effecting much . But do you suggest me the
> way to increase more insert speed what i need to do in this scenario. Is
> same happen for all pragma like Page_Size or Journal_Mode or Cache_size ? i
> tried to change the values of all pragma but i think no one effect the
> spped .
> 
> In case i will bind the values and only prepare the stament once then do
> you think any improvement ?
> 
> Cheers
> kritesh
> 
> On Tue, Aug 28, 2012 at 7:43 PM, Pavel Ivanov  wrote:
> 
>> If all your inserts are in one transaction then pragma synchronous =
>> OFF won't affect your transaction speed too much. To understand
>> whether this pragma works or not you should measure how long it takes
>> to execute COMMIT (just this one statement). With synchronous = OFF
>> COMMIT will be executed much faster.
>> 
>> Pavel
>> 
>> 
>> On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh
>>  wrote:
>>> Hi ,
>>> 
>>> I am executing all below mentioned pragma before start the (BEGIN
>> --COMMIT)
>>> transaction in sqlite version (3.6.4)
>>> 
>>> sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL, );
>>> sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL,
>> );
>>> sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL,
>> );
>>> sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL,
>> );
>>> 
>>> 
>>> In transaction , I am inserting the values in the table but I dnt know
>> the
>>> specific reason why does not pragma effecting  the insert speed .. I am
>>> getting the same speed even i use the pragma or not . please help
>>> 
>>> Is these pragma effect take place in transaction ?
>>> 
>>> Cheers
>>> kritesh
>>> 
>>> 
>>> 
>>> 
>>> --
>>> View this message in context:
>> http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.html
>>> Sent from the SQLite mailing list archive at Nabble.com.
>>> ___
>>> 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
>> 
> 
> 
> 
> -- 
> Regards
> kritesh tripathi
> ___
> 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

Re: [sqlite] Pragma Synchronous=OFF is not working

[kritesh tripathi]

Hi Pavel,

Thanks for the information . I am using all insert under one transection
hence may be the reson its not effecting much . But do you suggest me the
way to increase more insert speed what i need to do in this scenario. Is
same happen for all pragma like Page_Size or Journal_Mode or Cache_size ? i
tried to change the values of all pragma but i think no one effect the
spped .

In case i will bind the values and only prepare the stament once then do
you think any improvement ?

Cheers
kritesh

On Tue, Aug 28, 2012 at 7:43 PM, Pavel Ivanov  wrote:

> If all your inserts are in one transaction then pragma synchronous =
> OFF won't affect your transaction speed too much. To understand
> whether this pragma works or not you should measure how long it takes
> to execute COMMIT (just this one statement). With synchronous = OFF
> COMMIT will be executed much faster.
>
> Pavel
>
>
> On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh
>  wrote:
> > Hi ,
> >
> > I am executing all below mentioned pragma before start the (BEGIN
> --COMMIT)
> > transaction in sqlite version (3.6.4)
> >
> > sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL, );
> >  sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL,
> );
> >  sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL,
> );
> >  sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL,
> );
> >
> >
> > In transaction , I am inserting the values in the table but I dnt know
> the
> > specific reason why does not pragma effecting  the insert speed .. I am
> > getting the same speed even i use the pragma or not . please help
> >
> > Is these pragma effect take place in transaction ?
> >
> > Cheers
> >  kritesh
> >
> >
> >
> >
> > --
> > View this message in context:
> http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.html
> > Sent from the SQLite mailing list archive at Nabble.com.
> > ___
> > 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
>



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

Re: [sqlite] Pragma Synchronous=OFF is not working

[Pavel Ivanov]

If all your inserts are in one transaction then pragma synchronous =
OFF won't affect your transaction speed too much. To understand
whether this pragma works or not you should measure how long it takes
to execute COMMIT (just this one statement). With synchronous = OFF
COMMIT will be executed much faster.

Pavel


On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh
 wrote:
> Hi ,
>
> I am executing all below mentioned pragma before start the (BEGIN --COMMIT)
> transaction in sqlite version (3.6.4)
>
> sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL, );
>  sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL, );
>  sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL, );
>  sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL, );
>
>
> In transaction , I am inserting the values in the table but I dnt know the
> specific reason why does not pragma effecting  the insert speed .. I am
> getting the same speed even i use the pragma or not . please help
>
> Is these pragma effect take place in transaction ?
>
> Cheers
>  kritesh
>
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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