Re: [sqlite] Progress callback and nested queries

[Dan Kennedy]

On 11/03/2011 01:11 AM, Duquette, William H (318K) wrote:

I'm pretty sure I know the answer to this.

Sqlite3 allows you to define a "progress" callback, which will be called every 
so many byte-code instructions during a long-running query, so that you can update a 
progress bar or like that.

I'm assuming that querying the same database using the same handle during a 
"progress" callback would be a *bad* idea.  Am I mistaken?


The docs here say you're not supposed to (last paragraph):

  http://www.sqlite.org/c3ref/progress_handler.html

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

Re: [sqlite] problem using sqlite_prepare_v2

[Igor Tandetnik]

On 11/2/2011 2:46 PM, John Markavitch wrote:

iRes = sqlite3_step(statement);
if ((iRes ==  SQLITE_ROW) || (iRes ==  SQLITE_DONE))
{
psItemPID = sqlite3_column_text (statement, 0); 
   // column 0 item pid
psLocation = sqlite3_column_text (statement, 
7);  // location - KYTR.mp3

}
/** if I use sqlite3_finalize () here, All following sqlite3_prepare_v2
error  */
/* If I do not use sqlite3_finalize () here, sqlite3_prepare_v2 does not
error   */
//  iRes = sqlite3_finalize(statement);


psItemPID and psLocation pointers are only valid while the statement is 
a) active, and b) still positioned on the same row (by the way, you 
shouldn't be calling sqlite3_column_* after _step returns SQLITE_DONE - 
at that time, the statement is positioned past the last row). When you 
call sqlite3_finalize, the memory these pointers point to gets 
deallocated and they now point to random garbage. You then write that 
garbage into sCmd.

--
Igor Tandetnik

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

Re: [sqlite] Progress callback and nested queries

[Pavel Ivanov]

> I'm assuming that querying the same database using the same handle during a 
> "progress" callback would be a *bad* idea.  Am I mistaken?

That would deadlock on handle's internal mutex or corrupt the handle
if your SQLite is compiled without multi-threading support.


Pavel


On Wed, Nov 2, 2011 at 2:11 PM, Duquette, William H (318K)
 wrote:
> I'm pretty sure I know the answer to this.
>
> Sqlite3 allows you to define a "progress" callback, which will be called 
> every so many byte-code instructions during a long-running query, so that you 
> can update a progress bar or like that.
>
> I'm assuming that querying the same database using the same handle during a 
> "progress" callback would be a *bad* idea.  Am I mistaken?
>
> Will
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> 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] Slow INDEX

[Nico Williams]

On Wed, Nov 2, 2011 at 2:13 PM, Fabian  wrote:
> 2011/11/2 Nico Williams 
>> But note that this can still fail you when the file is larger than
>> available RAM.  In that case such a flag would be very bad.  And
>> SQLite3 can't know how much RAM is available.  The OS can know
>> (sortof) and the user can know, but SQLite3 can't.  So I take the
>> above back -- such a flag would probably result in posts about how
>> SQLite3 startup causes thrashing...
>
> If the flag would respect the cache_size pragma, it could work very well. I
> currently set the cache_size to 300MB (72k x 4096 pages), and the database
> size on disk is only 125 MB, so it would fit in perfectly. People that
> never changed the default cache_size, will never experience any trashing,
> because theyre database will not be pre-cached, since it doesnt fit into
> cache.

That's true, though the reading in of the file would have to be
delayed till you set the cache_size.  I'd be happy with such a thing.
And if the cache_size is smaller than the file size, then make this do
nothing.

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

Re: [sqlite] Slow INDEX

[Fabian]

2011/11/2 Nico Williams 

>
> Incidentally, it pays to make the SQLite3 page size match the
> filesystem preferred block size.
>
>
I already have the page_size set to 4096, which should match the default
NTFS cluster size.

But note that this can still fail you when the file is larger than
> available RAM.  In that case such a flag would be very bad.  And
> SQLite3 can't know how much RAM is available.  The OS can know
> (sortof) and the user can know, but SQLite3 can't.  So I take the
> above back -- such a flag would probably result in posts about how
> SQLite3 startup causes thrashing...
>

If the flag would respect the cache_size pragma, it could work very well. I
currently set the cache_size to 300MB (72k x 4096 pages), and the database
size on disk is only 125 MB, so it would fit in perfectly. People that
never changed the default cache_size, will never experience any trashing,
because theyre database will not be pre-cached, since it doesnt fit into
cache.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem using sqlite_prepare_v2

[John Markavitch]

Good Afternoon:

I am relatively new at sqlite and am having a problem with the
sqlite_prepare_v2() statements. My sqlite version is 3.7.8 running under
Windows 7.

After using a sqlite_prepare_v2() statement, if I do a sqlite3_finalize(),
all further sqlite_prepare_v2() calls return a SQLITE_ERROR. If I do not use
sqlite3_finalize(),further sqlite_prepare_v2() statements return SQLITE3_OK.

If I put the sqlite3_finalize() statement just before closing the database,
or do not use the sqlite3_finalize() statement, I can not delete the
database files.

I have investigated all the resources I can find and can not see a solution
to the problem.

Following is my code segment:

void CAppleDevice::GetData5 (CString strTitle, CString strArtist, CString
*strOutFile)
{
CString strLibDB;
CString strLocDB;
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char sCmd [MAX_PATH];
sqlite3_stmt *statement;
unsigned int iRes;
sqlite_uint64 uiVal;
char sLocation[MAX_PATH];
char sBaseLocation[MAX_PATH];
const unsigned char *psBaseLocationID;
const unsigned char *sBaseLocationID;
const unsigned char *psLocation;
const unsigned char *psItemPID;
CString strCmd;
char sFileName [MAX_PATH];
int iRetCode;

strTitle.Replace ("'", "''");
strArtist.Replace ("'", "''");

strLibDB.Format ("%s%s", LOCAL_DB, "MediaLibrary.sqlitedb");
rc = sqlite3_open(strLibDB, );

if( rc )
{
  MessageBox (NULL, sqlite3_errmsg(db), "Error", MB_OK);
  sqlite3_close(db);
}

else
{

sprintf (sCmd, "Select * from item_extra WHERE title = '%s'", 
strTitle);



iRes = sqlite3_prepare_v2
(
db,/* Database handle */
  sCmd,   /* SQL statement, UTF-8 encoded */
  strlen (sCmd),  /* Maximum length of zSql in 
bytes. */
  ,  /* OUT: Statement handle */
  NULL /* OUT: Pointer to unused portion of zSql */
);


if (iRes ==  SQLITE_OK)
{
iRes = sqlite3_step(statement);
if ((iRes ==  SQLITE_ROW) || (iRes ==  SQLITE_DONE))
{
psItemPID = sqlite3_column_text (statement, 0); 
   // column 0 item pid
psLocation = sqlite3_column_text (statement, 
7);  // location - KYTR.mp3

}
/** if I use sqlite3_finalize () here, All following sqlite3_prepare_v2
error  */
/* If I do not use sqlite3_finalize () here, sqlite3_prepare_v2 does not
error   */
//  iRes = sqlite3_finalize(statement);

strcpy (sCmd, "Select * from item WHERE item_pid = ");

strcat (sCmd, (char *)psItemPID);

iRes = sqlite3_prepare_v2
(
db,/* Database handle */
sCmd,   /* SQL statement, UTF-8 encoded */
strlen (sCmd),  /* Maximum length 
of zSql in bytes. */
,  /* OUT: Statement handle */
NULL /* OUT: Pointer to unused portion of 
zSql */
);


if (iRes ==  SQLITE_OK)
{
iRes = sqlite3_step(statement);
if ((iRes ==  SQLITE_ROW) || (iRes ==  
SQLITE_DONE))
{
psBaseLocationID = sqlite3_column_text 
(statement, 27);
}

else
{
strOutFile = NULL;
MessageBox (NULL, "SQL Location Error", 
"Error", MB_OK);
}
}

//  sqlite3_reset(statement);
strcpy (sCmd, "Select * from base_location WHERE 
base_location_id = ");

strcat (sCmd, (char *)psBaseLocationID);

iRes = sqlite3_prepare_v2
(
db,/* Database handle */
sCmd,   /* SQL statement, UTF-8 encoded */
strlen (sCmd),  /* Maximum length 
of zSql in bytes. */
,  /* OUT: Statement handle */
NULL /* OUT: Pointer to unused portion of 
zSql */
);


if (iRes ==  SQLITE_OK)
{
iRes = 

Re: [sqlite] Slow INDEX

[Nico Williams]

On Wed, Nov 2, 2011 at 1:20 PM, Fabian  wrote:
> Linux will not read the whole file in, but Windows eventually does. The
> inserts go progressively faster when they are reaching halfway, and Windows
> reads very large pages from disk, even if you request only 10 bytes. So in

The largest read will depend on how many contiguous blocks are on
disk.  For a file with lots of random writes that could be very low.
You'd need to get down and dirty with the filesystem to find out for
sure.  Or use DTrace (ah, but there's no DTrace on Windows).  You're
left to make assumptions, and you want to make the most optimistic
ones and feel disappointed when it turns out that those assumptions
were wrong :)

Even if the file was all contiguous on disk and the OS was smart
enough to realize that reading the whole thing in is the right thing
to do, there's a limit to how far you can take this since the file
could be too large to fit in RAM.

This isn't SQLite3's fault...

> reality a very large percentage of these 10K I/O's will come from a buffer
> (either Windows one or your harddrive's buffer), and will not result in any
> physical reads from disk. Ofcourse you're right that these random reads
> will be slower than a sequential file-copy, because they are random, and
> not large, continous blocks.

The I/Os will be blocksize I/Os, not application write size.

Incidentally, it pays to make the SQLite3 page size match the
filesystem preferred block size.

>>Actually, it might be nice if SQLite3 had a function or open
>>flag by which to request that the whole thing be read into memory,
>>because the OS certainly won't know to do it.
>
> I completely agree, because all the current methods (copy the file to
> 'null', etc.) didn't work well.

But note that this can still fail you when the file is larger than
available RAM.  In that case such a flag would be very bad.  And
SQLite3 can't know how much RAM is available.  The OS can know
(sortof) and the user can know, but SQLite3 can't.  So I take the
above back -- such a flag would probably result in posts about how
SQLite3 startup causes thrashing...

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

Re: [sqlite] Slow INDEX

[Fabian]

2011/11/2 Nico Williams 

>
> But that's NOT what SQLite3 is doing.  SQLite3 is doing random I/O.
> And the OS sees the random I/O pattern and concludes it's better to
> not read the whole file in.  So for those 10K inserts you pay -worst
> case- 10K I/Os.  At ~12ms per random I/O (likely the seek times for
> your disks) you're talking 120s, so you're actually far from the worst
> case -- even at 7ms seek time you're talking about twice the time
> you've seen in the worst case.
>
>
Linux will not read the whole file in, but Windows eventually does. The
inserts go progressively faster when they are reaching halfway, and Windows
reads very large pages from disk, even if you request only 10 bytes. So in
reality a very large percentage of these 10K I/O's will come from a buffer
(either Windows one or your harddrive's buffer), and will not result in any
physical reads from disk. Ofcourse you're right that these random reads
will be slower than a sequential file-copy, because they are random, and
not large, continous blocks.

>Actually, it might be nice if SQLite3 had a function or open
>flag by which to request that the whole thing be read into memory,
>because the OS certainly won't know to do it.

I completely agree, because all the current methods (copy the file to
'null', etc.) didn't work well.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Progress callback and nested queries

[Duquette, William H (318K)]

I'm pretty sure I know the answer to this.

Sqlite3 allows you to define a "progress" callback, which will be called every 
so many byte-code instructions during a long-running query, so that you can 
update a progress bar or like that.

I'm assuming that querying the same database using the same handle during a 
"progress" callback would be a *bad* idea.  Am I mistaken?

Will
--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Slow INDEX

[Fabian]

2011/11/2 Black, Michael (IS)

> Then, when you restart the file is on system disk and it is flushing each
> insert to system disk on the WAL file slowing things down dramaticalliy.
>

I do not use WAL, since I have turned 'journal_mode' off (to improve insert
performance), and as far as I know WAL is only usefull when you need to
keep a journal?

I also have 'synchronous' off, so SQLite shouldn't be waiting for any
filesystem flushes. I hoped it was writing all the newly inserted rows to
disk using a single operation, as soon as I call 'commit'. But I observed
it, and found out its already writing the rows as soon as soon as they are
inserted, not batching them for when I call commit.

So that could be part of the problem. I don't have a real Windows machine
at hand, so I will build one tomorrow, but if your expectations are
correct, than it will be even slower than inside a virtual machine, because
it will do individiual writes for the 1 million rows too, making
performance even worse than it is now.

Anothing thing is that I don't expect the slow performance have anything to
do with slow disk writes, only with disk reads. I know this because when I
make the index UNIQUE, and try to insert 10.000 duplicate rows (which are
all ignored), it has the same bad performance, even though there are zero
bytes written to disk. So it points in the direction of the reads making it
slow, not the writes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow INDEX

[Nico Williams]

On Wed, Nov 2, 2011 at 11:41 AM, Fabian  wrote:
> 2011/11/2 Mr. Puneet Kishor 
>> ahh, so you *are* getting expected behavior, just not what *you* expected.
>> Did you have a different number in mind instead of a factor of 300? And, if
>> so, why?
>
> To read an (un-cached) 150mb file from disk, doesn't take 30 seconds. And

But that's NOT what SQLite3 is doing.  SQLite3 is doing random I/O.
And the OS sees the random I/O pattern and concludes it's better to
not read the whole file in.  So for those 10K inserts you pay -worst
case- 10K I/Os.  At ~12ms per random I/O (likely the seek times for
your disks) you're talking 120s, so you're actually far from the worst
case -- even at 7ms seek time you're talking about twice the time
you've seen in the worst case.

What you should do, given that this one file is critical to your app,
is read the whole file into memory (if it were stored on contiguous
blocks, which it won't be, that'd make for about one second to read it
in).  Actually, it might be nice if SQLite3 had a function or open
flag by which to request that the whole thing be read into memory,
because the OS certainly won't know to do it.

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

Re: [sqlite] Slow INDEX

[Black, Michael (IS)]

What happens if you run your test outside of VirtualBox?



I'm thinking when you first write the file VirtualBox does it locally and then 
flushes the whole thing to system disk.

Then, when you restart the file is on system disk and it is flushing each 
insert to system disk on the WAL file slowing things down dramaticalliy.



If this is true your test will perform correctly on your main PC.



I've heard about VM systems having problems writing to files like this.







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 Fabian [fabianpi...@gmail.com]
Sent: Wednesday, November 02, 2011 11:57 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Slow INDEX

2011/11/2 Simon Slavin 

>
> So compare your 'restart-and-INSERT' test with one where you restart, log
> in, then do a few random operations for a minute: start your web browser
> and load a page.  Open and close a few documents or control panels.  Sit
> and do nothing for a minute.  /Then/ carry on with the test.
>
>
To rule out your suggestion of background processes slowing down the
operation, or any VirtualBox performance issues, I rebooted, and
immediately copied the DB file (125 MB) to another location, and it
completed within 5 seconds. So it doesn't seem VirtualBox or background
processes are to blame. In the past I already did some tests with waiting
for 15 minutes, and it had zero effect. Thanks for the suggestion anyway!
___
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] Slow INDEX

[Fabian]

2011/11/2 Simon Slavin 

>
> So compare your 'restart-and-INSERT' test with one where you restart, log
> in, then do a few random operations for a minute: start your web browser
> and load a page.  Open and close a few documents or control panels.  Sit
> and do nothing for a minute.  /Then/ carry on with the test.
>
>
To rule out your suggestion of background processes slowing down the
operation, or any VirtualBox performance issues, I rebooted, and
immediately copied the DB file (125 MB) to another location, and it
completed within 5 seconds. So it doesn't seem VirtualBox or background
processes are to blame. In the past I already did some tests with waiting
for 15 minutes, and it had zero effect. Thanks for the suggestion anyway!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow INDEX

[Simon Slavin]

On 2 Nov 2011, at 4:31pm, Fabian wrote:

> 2011/11/2 Mr. Puneet Kishor 
> 
>> Others will have better answers, but methinks that when you reboot the
>> computer, the operating system's caches are flushed out, which slows the
>> operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.)
>> and notice if the speed increases again to what you expect.
> 
> The reason I reboot the PC for the test, is because I want to have the
> caches flushed out, and I fully expect it to make things slower, but not by
> the degree (factor 300) i'm experiencing.

Doing lots of inserts under those conditions would require accessing almost 
every byte of the file.  In other words, after your reboot, Windows is probably 
having to load the entire file into cache again.  So let's build that into our 
expectations.  But I agree with Fabian that loading a 150Meg file into cache 
shouldn't take more than a few seconds.

One thing to test is to check to see that Windows is not still doing startup 
operations while you're doing your testing.  Windows presents itself very 
quickly to the user.  It shows a login screen then continues to start many 
processes in the background.  Then once the user is logged in it shows the user 
their desktop almost immediately, but it hasn't yet started many background 
operations.  If the user immediately starts doing things, they will be slowed 
down by the fact that the computer hasn't finished starting up yet.

So compare your 'restart-and-INSERT' test with one where you restart, log in, 
then do a few random operations for a minute: start your web browser and load a 
page.  Open and close a few documents or control panels.  Sit and do nothing 
for a minute.  /Then/ carry on with the test.

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

Re: [sqlite] Slow INDEX

[Fabian]

2011/11/2 Paul Corke 

>
> A stateful antivirus that does lots of heavy processing when you first
> open the file?
>
>
I'm running these tests on a completey clean Win7 install, without any
anti-virus (or other software) installed. I should note that it runs
virtualized (in VirtualBox) so I can quickly switch back to the clean state
every time, but if VirtualBox performance was the root cause of these
performance issues, I would expect it to slow down the initial insert (> 1M
rows) too.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow INDEX

[David Bicking]

On 11/02/2011 12:31 PM, Fabian wrote:

2011/11/2 Mr. Puneet Kishor



Others will have better answers, but methinks that when you reboot the
computer, the operating system's caches are flushed out, which slows the
operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.)
and notice if the speed increases again to what you expect.



The reason I reboot the PC for the test, is because I want to have the
caches flushed out, and I fully expect it to make things slower, but not by
the degree (factor 300) i'm experiencing.


In the past I've seen recommendations to open and read the file, perhaps 
by making a copy of the file, then try your inserts. If the time does 
goes back down, then you know it is file cache issues.


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

Re: [sqlite] Slow INDEX

[Paul Corke]

On 02 November 2011 16:42, Fabian wrote:

> Maybe there is a very simple explanation, I just can't think of any.

A stateful antivirus that does lots of heavy processing when you first
open the file?

Have you tried:

1) Reboot

2) Wait 10 minutes (don't even touch the computer)

3) Test

Paul.

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

Re: [sqlite] Slow INDEX

[Fabian]

2011/11/2 Mr. Puneet Kishor 

>
> ahh, so you *are* getting expected behavior, just not what *you* expected.
> Did you have a different number in mind instead of a factor of 300? And, if
> so, why?


To read an (un-cached) 150mb file from disk, doesn't take 30 seconds. And
SQLite shouldn't have to read the whole file (because of the INDEX), only
the relevant rows in the INDEX, so theoreticly it should even be faster
than reading the whole file. Because Windows has a aggresive caching
technique (it reads much larger blocks from disk, even if you request only
10 bytes from the file), I'm not expecting SQLite to be faster than reading
the whole file, but that it's so much slower just surprises me. Maybe there
is a very simple explanation, I just can't think of any.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow INDEX

[Mr. Puneet Kishor]

On Nov 2, 2011, at 11:31 AM, Fabian wrote:

> 2011/11/2 Mr. Puneet Kishor 
> 
>> 
>> Others will have better answers, but methinks that when you reboot the
>> computer, the operating system's caches are flushed out, which slows the
>> operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.)
>> and notice if the speed increases again to what you expect.
>> 
>> 
> The reason I reboot the PC for the test, is because I want to have the
> caches flushed out, and I fully expect it to make things slower, but not by
> the degree (factor 300) i'm experiencing.


ahh, so you *are* getting expected behavior, just not what *you* expected. Did 
you have a different number in mind instead of a factor of 300? And, if so, 
why? I am genuinely curious -- I know nothing about Windows. I rarely reboot my 
computer -- my desktop iMac hasn't been rebooted in several weeks now. I did 
reboot my MacBook Air a few days ago for a software update, but usually that 
too goes through a few weeks before it is rebooted... when I do reboot them, I 
experience everything to be slow for the first 10-15 mins or so.

--
Puneet Kishor

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

Re: [sqlite] Slow INDEX

[Don V Nielsen]

I would like to stick my neck out over the chopping block and agree.  My
experience is the opposite, but appears to support Puneet's assertion.
 With me, it takes my C# application 12 seconds to pass 103,00 records and
insert 98,000 rows into the db from it.  The next time I run the
application (which starts with a fresh db,) it takes 7 seconds or less.
 This leads me to be believe the O/S still has the original file cached, so
it's i/o performance is much improved.

dvn

On Wed, Nov 2, 2011 at 11:27 AM, Mr. Puneet Kishor wrote:

>
> On Nov 2, 2011, at 11:24 AM, Fabian wrote:
>
> > Now if I re-open the database, I can add an additional 10.000 rows very
> > fast (<1 sec). But if I reboot the (Windows) PC, and insert an additional
> > 10.000 rows, it takes at least 30 secs, which seems very slow, if I can
> add
> > the first 1 million in under 10 seconds.
>
>
> Others will have better answers, but methinks that when you reboot the
> computer, the operating system's caches are flushed out, which slows the
> operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.)
> and notice if the speed increases again to what you expect.
>
>
>
> --
> Puneet Kishor
> ___
> 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] Slow INDEX

[Fabian]

2011/11/2 Mr. Puneet Kishor 

>
> Others will have better answers, but methinks that when you reboot the
> computer, the operating system's caches are flushed out, which slows the
> operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.)
> and notice if the speed increases again to what you expect.
>
>
The reason I reboot the PC for the test, is because I want to have the
caches flushed out, and I fully expect it to make things slower, but not by
the degree (factor 300) i'm experiencing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow INDEX

[Mr. Puneet Kishor]

On Nov 2, 2011, at 11:24 AM, Fabian wrote:

> Now if I re-open the database, I can add an additional 10.000 rows very
> fast (<1 sec). But if I reboot the (Windows) PC, and insert an additional
> 10.000 rows, it takes at least 30 secs, which seems very slow, if I can add
> the first 1 million in under 10 seconds.


Others will have better answers, but methinks that when you reboot the 
computer, the operating system's caches are flushed out, which slows the 
operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.) and 
notice if the speed increases again to what you expect.



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

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2

[Yuriy Kaminskiy]

ChingChang Hsiao wrote:
> I can't reply in my system, so I create the problem description again.
> 
> I miss one source code line "char tempString[1024];"in the last email. The
> code dump happened after 4 days' run in a test script not immediately. The
> SQLITE statements seem to be ok. Could be a performance issue?

Core dumps are *never* performance issue.

Indeed, it would be /more efficient/ to prepare statements once (and maybe also
cache prepared statements between function invocations), and just bind different
values in loop, and that's "performance issue", but it is unrelated to 
coredumps.

> ChingChang
> 
> 
> The source code is shown as below,
> 
> 
> char tempString[1024];
> vector dbStatements;
>   dbStatements.push_back( "BEGIN TRANSACTION;" );
>   for ( int x = 0; x < 10; x++ ) {
> sprintf( tempString,
>  "update utilization_table set utilization=%5.2f,sample=%d where 
> slot='0' and device='cavium' and resource='bus' and sample='%d';",
>  ntohd(msg->bus_util[x]),
>  x,
>  x );
> dbStatements.push_back( tempString );
> sprintf( tempString,
>  "update utilization_table set utilization=%5.2f,sample=%d where 
> slot='0' and device='cavium' and resource='icache' and sample='%d';",
>  100.00-ntohd(msg->inst_hit_rate[x]),  // Convert to misses
>  x,
>  x );
> dbStatements.push_back( tempString );
> sprintf( tempString,
>  "update utilization_table set utilization=%5.2f,sample=%d where 
> slot='0' and device='cavium' and resource='dcache' and sample='%d';",

Hmm... One thing to consider: some locales uses different decimal point
separator instead of ".". That may cause problems. E.g.

$ cat >t.c << __EOF__
#include 
#include 
int main() { setlocale(LC_ALL, ""); return printf("%5.2f\n", 123.456) < 0; }
__EOF__
$ gcc t.c && LC_ALL=ru_RU.UTF-8 ./a.out
123,46
   ^ of course, SQL parser won't like this.

But that would likely trigger error *every* time, and not after few hours, so
does not fit your error description.

One more potential problem: NAN and infinity.
printf("%5.2f\n", 1.0/0.0); -> "  inf"
printf("%5.2f\n", 0.0/0.0); -> "  nan"
That would also confuse SQL parser.

If your ntohd function can sometimes return NAN/infinity, that would cause 
problems.

Still, does not fit your error description very well (I'd expect sqlite3_exec to
return error, and don't trigger assertion failure).

Both problem would be avoided by switching to "prepare statement once, then bind
values" pattern.

>  100.00-ntohd(msg->data_hit_rate[x]),  // Convert to misses
>  x,
>  x );
> dbStatements.push_back( tempString );
>   }
>   dbStatements.push_back( "COMMIT;" );
> 
>   // populate the DB
>   vector::iterator dbStatementsIter;
>   SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
>   for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter != 
> dbStatements.end(); dbStatementsIter++ ) {
> oper_db.execw( *(dbStatementsIter) );
>   }
> 
>   dbStatements.clear();
> 
> The core dump is shown as below.
> 
> #0  0x32e94b04 in raise () from /lib/libc.so.6
> #1  0x32e962f4 in abort () from /lib/libc.so.6
> #2  0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
> #3  0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
> #4  0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
> #5  0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so

Note: this is *not* SIGSEGV/SIGBUS, but *assertion failure*; it prints error on
stderr before program termination, it would be useful to look at this message.

And it would be useful to rebuild libsqlite3.mgmt-crd.so with unstripped
debugging symbols.

> #6  0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
> #7  0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
> #8  0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
> #9  0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
> #10 0x10010290 in NpuMessageHandler::processUtilReport (this= out>, msg=,
> nbytes=) at cavium_driver.cpp:1387
> #11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at 
> cavium_driver.cpp:954
> #12 0x328a65b0 in Thread::start_thread () from /ovn/lib/libCommon.mgmt-crd.so
> #13 0x3278b5cc in ?? () from /lib/libpthread.so.0
> #14 0x32f39b88 in clone () from /lib/libc.so.6

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

[sqlite] Slow INDEX

[Fabian]

I asked a similar question before, but received no response, so maybe it's
a very stupid question, but if so, feel free to say so.

I create a database, create one table with a TEXT column, insert 1 million
rows in 10 secs, create an index in 10 secs, VACUUM the database, and close
the database.

Now if I re-open the database, I can add an additional 10.000 rows very
fast (<1 sec). But if I reboot the (Windows) PC, and insert an additional
10.000 rows, it takes at least 30 secs, which seems very slow, if I can add
the first 1 million in under 10 seconds.

It probably has to do with inefficient disk seeks, but even if SQLite has
to read the whole database in memory to do the inserts, 30 secs is still
slow for a 150mb file (the database).

So is there anyone who can explain these slow inserts, or has ideas to
optimize for a scenario like the above? Because currently this renders my
application almost unusable.

The only optimization I can think of would be dropping the index, inserting
the rows, and re-creating the index. But it would require some smart logic
when to do it (because for just 3 inserts it would make things slower
instead of faster), and it feels like more of a work-around than a solution.

Please help me out, thanks in advance!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Black, Michael (IS)]

Then how's about we update the docs?  I can understand the need for consistency 
but never would've guessed that it sped things up so much.  A hint in the docs 
would help.



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 Dan Kennedy [danielk1...@gmail.com]
Sent: Wednesday, November 02, 2011 11:05 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] does sqlite3_reset have a performance impact 
onqueries?

On 11/02/2011 08:13 PM, Black, Michael (IS) wrote:
> Maybe my memory is fading but this is the first time I've heard anybody say 
> the wrapping a BEBIN around a SELECT was needed.  I'd swear it was always 
> said it wasn't ever needed.
>
>
>
>> From the docs
>
> http://www.sqlite.org/lang_transaction.html
>
> basically, any SQL command other than 
> SELECT) will automatically start a 
> transaction if one is not already in effect
>
>
>
> Now you're saying SELECT always starts a transaction?

There are two types of transactions - read-only transactions and
read/write transactions.

A read-only transaction contains only reads. All reads that occur
within a single read transaction see the same snapshot of the
database. Logically, they are useful when you have one or more
SELECT statements to run and you don't want anybody else modifying
the database between them. If you don't wrap a SELECT in a
BEGIN/COMMIT block, then it will open and close its own
read-transaction.

But, read-transactions can also speed things up in SQLite - since
you don't have to lock and unlock the database file (and do some
other stuff too) for every SELECT.

___
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] Informal speaker in UK on SQLite

[Ketan Majmudar]

Hi

I wondered if there were any savvy SQLite users in the UK  that might be 
interested in speaking generally about the topic at the London Titanium Meetup 
http://www.meetup.com/London-Titanium group. Titanium Mobile leverages SQLIte 
for its database APIs and although we can and will be talking about that, I 
felt it might be interesting and useful to hear about SQLite itself and how it 
might help other Titanium Mobile developers.

If any one has any leads  or advice on a good general talk on the subject 
please let me know.

Kind Regards,

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Dan Kennedy]

On 11/02/2011 08:13 PM, Black, Michael (IS) wrote:

Maybe my memory is fading but this is the first time I've heard anybody say the 
wrapping a BEBIN around a SELECT was needed.  I'd swear it was always said it 
wasn't ever needed.




From the docs


http://www.sqlite.org/lang_transaction.html

basically, any SQL command other than 
SELECT) will automatically start a 
transaction if one is not already in effect



Now you're saying SELECT always starts a transaction?


There are two types of transactions - read-only transactions and
read/write transactions.

A read-only transaction contains only reads. All reads that occur
within a single read transaction see the same snapshot of the
database. Logically, they are useful when you have one or more
SELECT statements to run and you don't want anybody else modifying
the database between them. If you don't wrap a SELECT in a
BEGIN/COMMIT block, then it will open and close its own
read-transaction.

But, read-transactions can also speed things up in SQLite - since
you don't have to lock and unlock the database file (and do some
other stuff too) for every SELECT.

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

Re: [sqlite] EXT : core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 3

[Paul Corke]

On 02 November 2011 15:46, Black, Michael (IS) wrote:

> You're corrupting the stack somewhere.

Possibly, or it might be that libsqlite3.mgmt-crd.so just
doesn't have all the necessary symbols.

> You can enable stack checking if you're using gcc.

Assuming the stack trace given was generated from the core
file with gdb then in gdb I'd try

frame 10
print *dbStatementsIter

to see the query to see if that gives any clues.  It might
need "gdb-stl-views" or similar installed to make any sense.

Regards,

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

Re: [sqlite] EXT : core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 3

[Black, Michael (IS)]

You're corrupting the stack somewhere.



You can enable stack checking if you're using gcc.



-fstack-protector

-fstack-protector-all



Might help identify where it's actually happening.




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 ChingChang Hsiao [chingchang.hs...@overturenetworks.com]
Sent: Wednesday, November 02, 2011 10:36 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] core dump happened in sqlite3_step for 30 statements in 
BEGIN TRANSACTION executing every 10 Millisecond Take 3




I can't reply in my system, so I create the problem description again.

It seems it's not the problem of sprintf. If it comes from sprintf array size, 
the core dump will be like this " in __stack_chk_fail () from /lib/libc.so.6".

The code dump happened after 4 days' run in a test script, not immediately. The 
SQLITE statements seem to be ok. Could be a performance issue?

ChingChang


The source code is shown as below,


char tempString[1024];
vector dbStatements;
  dbStatements.push_back( "BEGIN TRANSACTION;" );
  for ( int x = 0; x < 10; x++ ) {
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='bus' and sample='%d';",
 ntohd(msg->bus_util[x]),
 x,
 x );
dbStatements.push_back( tempString );
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='icache' and sample='%d';",
 100.00-ntohd(msg->inst_hit_rate[x]),  // Convert to misses
 x,
 x );
dbStatements.push_back( tempString );
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='dcache' and sample='%d';",
 100.00-ntohd(msg->data_hit_rate[x]),  // Convert to misses
 x,
 x );
dbStatements.push_back( tempString );
  }
  dbStatements.push_back( "COMMIT;" );

  // populate the DB
  vector::iterator dbStatementsIter;
  SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
  for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter != 
dbStatements.end(); dbStatementsIter++ ) {
oper_db.execw( *(dbStatementsIter) );
  }

  dbStatements.clear();

The core dump is shown as below.

#0  0x32e94b04 in raise () from /lib/libc.so.6
#1  0x32e962f4 in abort () from /lib/libc.so.6
#2  0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
#3  0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#4  0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#5  0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#6  0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
#7  0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
#8  0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#9  0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#10 0x10010290 in NpuMessageHandler::processUtilReport (this=, msg=,
nbytes=) at cavium_driver.cpp:1387
#11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at 
cavium_driver.cpp:954
#12 0x328a65b0 in Thread::start_thread () from /ovn/lib/libCommon.mgmt-crd.so
#13 0x3278b5cc in ?? () from /lib/libpthread.so.0
#14 0x32f39b88 in clone () from /lib/libc.so.6


___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 3

[ChingChang Hsiao]

I can't reply in my system, so I create the problem description again.

It seems it's not the problem of sprintf. If it comes from sprintf array size, 
the core dump will be like this " in __stack_chk_fail () from /lib/libc.so.6".

The code dump happened after 4 days' run in a test script, not immediately. The 
SQLITE statements seem to be ok. Could be a performance issue?

ChingChang


The source code is shown as below,


char tempString[1024];
vector dbStatements;
  dbStatements.push_back( "BEGIN TRANSACTION;" );
  for ( int x = 0; x < 10; x++ ) {
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='bus' and sample='%d';",
 ntohd(msg->bus_util[x]),
 x,
 x );
dbStatements.push_back( tempString );
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='icache' and sample='%d';",
 100.00-ntohd(msg->inst_hit_rate[x]),  // Convert to misses
 x,
 x );
dbStatements.push_back( tempString );
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='dcache' and sample='%d';",
 100.00-ntohd(msg->data_hit_rate[x]),  // Convert to misses
 x,
 x );
dbStatements.push_back( tempString );
  }
  dbStatements.push_back( "COMMIT;" );

  // populate the DB
  vector::iterator dbStatementsIter;
  SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
  for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter != 
dbStatements.end(); dbStatementsIter++ ) {
oper_db.execw( *(dbStatementsIter) );
  }

  dbStatements.clear();

The core dump is shown as below.

#0  0x32e94b04 in raise () from /lib/libc.so.6
#1  0x32e962f4 in abort () from /lib/libc.so.6
#2  0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
#3  0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#4  0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#5  0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#6  0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
#7  0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
#8  0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#9  0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#10 0x10010290 in NpuMessageHandler::processUtilReport (this=, msg=,
nbytes=) at cavium_driver.cpp:1387
#11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at 
cavium_driver.cpp:954
#12 0x328a65b0 in Thread::start_thread () from /ovn/lib/libCommon.mgmt-crd.so
#13 0x3278b5cc in ?? () from /lib/libpthread.so.0
#14 0x32f39b88 in clone () from /lib/libc.so.6


___
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] does sqlite3_reset have a performance impact onqueries?

[Yuriy Kaminskiy]

Teg wrote:
> I'd  like  this  clarified  too. I specifically don't use transactions
> when  I'm  selecting.  In  fact, I'll select, then start a transaction
> later  for  inserting the results.  Would I be better off wrapping the
> whole thing in a transaction?

Cannot be sure without looking at your code/database schema/..., but it looks
your usage pattern is wrong - there are race condition this way (that maybe
alleviated by implicit transaction start by [unfinished] SELECT, but anyway it
is fragile and wrong).

> Wednesday, November 2, 2011, 9:13:20 AM, you wrote:
> 
> BMI> Maybe my memory is fading but this is the first time I've heard
> BMI> anybody say the wrapping a BEBIN around a SELECT was needed.  I'd
> BMI> swear it was always said it wasn't ever needed.

BTW, what is "needed"? Nobody *force* you to use BEGIN/COMMIT around SELECT -
transaction will be started (and ended) implicitly; just if you issue multiple
statement, it is more efficient to take lock once, than take and release lock
(and lot more - check database schema, flush cache, etc) for each statement.

And, of course, if you need consistent database state across many sql
statements, you need to wrap all of them in single transaction.

>> >From the docs
> 
> BMI> http://www.sqlite.org/lang_transaction.html
> 
> BMI> basically, any SQL command other than
> BMI> SELECT) will
> BMI> automatically start a transaction if one is not already in effect
> 
> 
> 
> BMI> Now you're saying SELECT always starts a transaction?

PS
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Teg]

I'd  like  this  clarified  too. I specifically don't use transactions
when  I'm  selecting.  In  fact, I'll select, then start a transaction
later  for  inserting the results.  Would I be better off wrapping the
whole thing in a transaction?

Wednesday, November 2, 2011, 9:13:20 AM, you wrote:

BMI> Maybe my memory is fading but this is the first time I've heard
BMI> anybody say the wrapping a BEBIN around a SELECT was needed.  I'd
BMI> swear it was always said it wasn't ever needed.



>>From the docs

BMI> http://www.sqlite.org/lang_transaction.html

BMI> basically, any SQL command other than
BMI> SELECT) will
BMI> automatically start a transaction if one is not already in effect



BMI> Now you're saying SELECT always starts a transaction?



BMI> Michael D. Black

BMI> Senior Scientist

BMI> Advanced Analytics Directorate

BMI> Advanced GEOINT Solutions Operating Unit

BMI> Northrop Grumman Information Systems
BMI> ___
BMI> sqlite-users mailing list
BMI> sqlite-users@sqlite.org
BMI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Yuriy Kaminskiy]

Black, Michael (IS) wrote:
> Maybe my memory is fading but this is the first time I've heard anybody say
> the wrapping a BEBIN around a SELECT was needed.  I'd swear it was always
> said it wasn't ever needed.
> 
> 
> 
> From the docs
> 
> http://www.sqlite.org/lang_transaction.html

> basically, any SQL command other than 
> SELECT) will automatically start a 
> transaction if one is not already in effect

That's, of course, documentation bug. How can you get consistent results from
SELECT without taking (at least) SHARED lock?

Only difference, that UPDATE/INSERT/DELETE needs RESERVED lock (and then
EXCLUSIVE lock to (auto)commit), and SELECT only needs SHARED lock.

> Now you're saying SELECT always starts a transaction?

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Black, Michael (IS)]

Maybe my memory is fading but this is the first time I've heard anybody say the 
wrapping a BEBIN around a SELECT was needed.  I'd swear it was always said it 
wasn't ever needed.



>From the docs

http://www.sqlite.org/lang_transaction.html

basically, any SQL command other than 
SELECT) will automatically start a 
transaction if one is not already in effect



Now you're saying SELECT always starts a transaction?



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Tal Tabakman]

Thanks,
FYI, while it was a bad code styling on my behalf (I just did it for
showing en example) this was not my problem .
creating 2 short strings does not takes 20 seconds :)
also note, that when avoiding sqlite3_reset and creating new sqlite3_stmt
every time (and still creating those redundant string), there is no
performance overhead.

the real problem is the fact that each time, a new transaction was created
(see Richard's reply).
by doing BEGIN and clutter all operations under one transaction, the
program fly,

On Wed, Nov 2, 2011 at 2:11 PM, Stephan Beal  wrote:

> On Wed, Nov 2, 2011 at 12:59 PM, Igor Tandetnik 
> wrote:
>
> > The behavior of this fragment is in fact well defined by the C++
> standard.
> > The temporary is destroyed at the end of the full expression - basically,
> > at the semicolon, after the function call
> >
>
> That was also my understanding until recently when a friend of mine asked
> me to debug some code of his which mysteriously crashed. It was caused by a
> Qt debug wrapper macro which did the equivalent of c_str() on a temporary
> string instance. i unfortunately don't have the docs handy, and to be
> honest i'm not 100% convinced that what i wrote about undefined behaviour
> is true in that case, but i am 100% convinced that i've seen that usage
> cause problems before. Perhaps it was the compiler in question  (one of the
> MSVC variants) which made the behaviour undefined, as opposed to be a
> violation of the standard.
>
> In any case (undefined or not), calling ostringstream::str() twice there is
> unnecessary, and downright inefficient if the user's STL does not use CoW
> (all of them do, AFAIK, but that's an implementation detail clients
> shouldn't count on).
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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] does sqlite3_reset have a performance impact on queries?

[Yuriy Kaminskiy]

Stephan Beal wrote:
> On Tue, Nov 1, 2011 at 11:25 PM, Tal Tabakman wrote:
> 
>> second,needless to say that I want to avoid this since it causes mem
>> leaks.)
>>
> 
> Why would it leak? Are you intentionally NOT calling finalize()?
> 
> 
>>sqlite3_prepare_v2(handle, query.str().c_str(),
>> query.str().length()+1, _entrySelectSnumStmt, 0);
>>
> 
> Technically speaking, the length you are passing there is not correct. The
> +1 accounts for the NUL byte at the end of the string, which prepare() does
> not need to see (that said, it seems harmless enough).

No. Take look at prepare_v2 documentation:

** ^If the nByte argument is less than zero, then zSql is read up to the
** first zero terminator. ^If nByte is non-negative, then it is the maximum
** number of  bytes read from zSql.  ^When nByte is non-negative, the
** zSql string ends at either the first '\000' or '\u' character or
** the nByte-th byte, whichever comes first. If the caller knows
   
** that the supplied string is nul-terminated, then there is a small
** performance advantage to be gained by passing an nByte parameter that
** is equal to the number of bytes in the input string including
   
** the nul-terminator bytes.
   

FWIW, it seems, *only* case when it make sense to bother with nByte argument -
when string is *not* nul-terminated; otherwise, it is more efficient to just
pass -1, no matter if length is known or not.

And std::string::c_str() always nul-terminate string.

> Also, because 'query' is-a ostringstream, you are possibly creating 2 unneeded
> std::string copies here and you are definitely invoking undefined behaviour
> with this part:
> 
>sqlite3_prepare_v2(handle, query.str().c_str(),
> 
> The problem is that query.str() returns a COPY of the string, which you
> call c_str() on to get its bytes, and then the copy is destroyed. It is
> "likely to work" on many platforms but it is technically undefined. To fix
> that:
> 
> std::string const & s( query.str() );
> 
> (note that a (const &) created this way is guaranteed to stay alive until
> the end of the scope)

Just get rid of str().length() and don't bother with this :-)

> Then use s.c_str() and s.size() instead of query.str().xxx().

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Stephan Beal]

On Wed, Nov 2, 2011 at 1:11 PM, Stephan Beal  wrote:

> about undefined behaviour is true in that case, but i am 100% convinced
> that i've seen that usage cause problems before. Perhaps it was the
> compiler in question  (one of the MSVC variants) which made the behaviour
> undefined, as opposed to be a violation of the standard.
>
>
After reading up on this a bit, and finding the old email thread, i'm 99%
certain that the behaviour is (as you say) well-defined vis-a-vis the
standard. The crash my friend was seeing at the time may have been
compiler-specific or a side-effect of the specific implementation of the
debug macro he was using in conjunction with the c_str() return value.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Stephan Beal]

On Wed, Nov 2, 2011 at 12:59 PM, Igor Tandetnik  wrote:

> The behavior of this fragment is in fact well defined by the C++ standard.
> The temporary is destroyed at the end of the full expression - basically,
> at the semicolon, after the function call
>

That was also my understanding until recently when a friend of mine asked
me to debug some code of his which mysteriously crashed. It was caused by a
Qt debug wrapper macro which did the equivalent of c_str() on a temporary
string instance. i unfortunately don't have the docs handy, and to be
honest i'm not 100% convinced that what i wrote about undefined behaviour
is true in that case, but i am 100% convinced that i've seen that usage
cause problems before. Perhaps it was the compiler in question  (one of the
MSVC variants) which made the behaviour undefined, as opposed to be a
violation of the standard.

In any case (undefined or not), calling ostringstream::str() twice there is
unnecessary, and downright inefficient if the user's STL does not use CoW
(all of them do, AFAIK, but that's an implementation detail clients
shouldn't count on).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2

[ChingChang Hsiao]

I can't reply in my system, so I create the problem description again.

I miss one source code line "char tempString[1024];"in the last email.  The 
code dump happened after 4 days' run in a test script not immediately. The 
SQLITE statements seem to be ok. Could be a performance issue?

ChingChang


The source code is shown as below,


char tempString[1024];
vector dbStatements;
  dbStatements.push_back( "BEGIN TRANSACTION;" );
  for ( int x = 0; x < 10; x++ ) {
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='bus' and sample='%d';",
 ntohd(msg->bus_util[x]),
 x,
 x );
dbStatements.push_back( tempString );
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='icache' and sample='%d';",
 100.00-ntohd(msg->inst_hit_rate[x]),  // Convert to misses
 x,
 x );
dbStatements.push_back( tempString );
sprintf( tempString,
 "update utilization_table set utilization=%5.2f,sample=%d where 
slot='0' and device='cavium' and resource='dcache' and sample='%d';",
 100.00-ntohd(msg->data_hit_rate[x]),  // Convert to misses
 x,
 x );
dbStatements.push_back( tempString );
  }
  dbStatements.push_back( "COMMIT;" );

  // populate the DB
  vector::iterator dbStatementsIter;
  SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
  for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter != 
dbStatements.end(); dbStatementsIter++ ) {
oper_db.execw( *(dbStatementsIter) );
  }

  dbStatements.clear();

The core dump is shown as below.

#0  0x32e94b04 in raise () from /lib/libc.so.6
#1  0x32e962f4 in abort () from /lib/libc.so.6
#2  0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
#3  0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#4  0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#5  0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#6  0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
#7  0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
#8  0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#9  0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#10 0x10010290 in NpuMessageHandler::processUtilReport (this=, msg=,
nbytes=) at cavium_driver.cpp:1387
#11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at 
cavium_driver.cpp:954
#12 0x328a65b0 in Thread::start_thread () from /ovn/lib/libCommon.mgmt-crd.so
#13 0x3278b5cc in ?? () from /lib/libpthread.so.0
#14 0x32f39b88 in clone () from /lib/libc.so.6


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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Igor Tandetnik]

Stephan Beal  wrote:
> Also, because
> 'query' is-a ostringstream, you are possibly creating 2 unneeded
> std::string copies here and you are definitely invoking undefined behaviour
> with this part:
> 
>   sqlite3_prepare_v2(handle, query.str().c_str(),
> 
> The problem is that query.str() returns a COPY of the string, which you
> call c_str() on to get its bytes, and then the copy is destroyed.

The behavior of this fragment is in fact well defined by the C++ standard. The 
temporary is destroyed at the end of the full expression - basically, at the 
semicolon, after the function call.
-- 
Igor Tandetnik

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

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond

[Stephan Beal]

On Wed, Nov 2, 2011 at 2:57 AM, Teg  wrote:

> What  happens  if you replace all the sprinfs with some simple inserts
> inserted directly into vector?
>
> dbStatements.push_back("INSERT INTO...");
>


Since you need integers and floats in your strings, you can use
std::ostringstream as a replacement for sprintf():

std::ostringstring q;
q << "SELECT  " << x << " ... ";
 dbStatements.push_back( q.str() );

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] does sqlite3_reset have a performance impact on queries?

[Stephan Beal]

On Tue, Nov 1, 2011 at 11:25 PM, Tal Tabakman wrote:

> second,needless to say that I want to avoid this since it causes mem
> leaks.)
>

Why would it leak? Are you intentionally NOT calling finalize()?


>sqlite3_prepare_v2(handle, query.str().c_str(),
> query.str().length()+1, _entrySelectSnumStmt, 0);
>

Technically speaking, the length you are passing there is not correct. The
+1 accounts for the NUL byte at the end of the string, which prepare() does
not need to see (that said, it seems harmless enough). Also, because
'query' is-a ostringstream, you are possibly creating 2 unneeded
std::string copies here and you are definitely invoking undefined behaviour
with this part:

   sqlite3_prepare_v2(handle, query.str().c_str(),

The problem is that query.str() returns a COPY of the string, which you
call c_str() on to get its bytes, and then the copy is destroyed. It is
"likely to work" on many platforms but it is technically undefined. To fix
that:

std::string const & s( query.str() );

(note that a (const &) created this way is guaranteed to stay alive until
the end of the scope)

Then use s.c_str() and s.size() instead of query.str().xxx().

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users