[sqlite] SSD with TRIM

[Simon Slavin]

By the way, I have a new MacBook Pro with a Solid State Drive which supports 
TRIM.  Was running some unit tests which include plenty of SQLite operations.  
The apps in question use various recent versions of SQLite and don't use any 
PRAGMAs on any of them.

Fast.  Fasty fast.  Speed is high.  INSERT, UPDATE, and DELETE all 
significantly faster.  SELECT is a bit faster, but there's less difference.

Of course, the question of what fsync() actually does is now even more of a 
mystery.  SSDs do quite complicated things when they make changes.

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

Re: [sqlite] Slow commits

[Normand Mongeau]

Thanks, you've been very helpful. Being a recent lurker here (but a seasoned
developer), let me commend you for your outstanding work and support.

Normand



-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de Richard Hipp
Envoyé : 13 janvier 2012 19:35
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Slow commits

On Fri, Jan 13, 2012 at 6:49 PM, Normand Mongeau
wrote:

>
>
>>>  Begin by doing:
>>
>> PRAGMA synchronous=OFF;
>>
>
> With the above, the total commitTransaction time goes down to 385 
> milliseconds... Impressive.
>
>
The "PRAGMA synchronous=OFF" command turns of syncing of content to the disk
surface.  Normally, SQLite will pause at critical points and wait for
content to actually make it to disk oxide.  This ensures that your
transactions commit, and the database file is undamaged, even if a power
loss occurs in the middle of a write.  But "PRAGMA synchronous=OFF" turns
that mechanism off, so that SQLite just sends a "write()" system call to the
operating system and lets the operating system get the content to the disk
surface at its leisure.  That will work fine, as long as the power never
goes out.  But pull the power plug in the middle of a write, and you might
corrupt your database file.


>
>
>> That will determine if the problem is a slow disk or if we need to 
>> look elsewhere.  I'm not suggesting you deploy with the above setting 
>> -- just use it for debugging.
>>
>> You might also try:
>>
>>PRAGMA synchronous=NORMAL;
>>PRAGMA journal_mode=WAL;
>>
>> And see if you get better performance that way.
>>
>
> with strictly synchronous=NORMAL, time is 63 seconds.  Combined with 
> WAL, time is 2.6 seconds.
>
> Not sure what it means exactly though.
>

The "PRAGMA journal_mode=WAL" uses a newer transaction mechanism that is
faster in many causes (such as yours).  The "PRAGMA synchronous=NORMAL"
means that syncs to disk only occur during a "checkpoint" operation, which
happens on a few commits, but rarely.  That is sufficient to ensure that the
database file is never corrupted by a power loss.  But one or more of the
most recent transactions might get rolled back by a power loss.  In other
words, you lose Durability.  If Durability is important to you (it probably
is not, unless you are a bank) then you can set "PRAGMA synchronous=FULL"
with "PRAGMA journal_mode=WAL" and it will sync after every transaction.
That will reduce performance somewhat.  Usually the reduction isn't
noticeable.  But on your machine..

So what I think this all means is that you ought to be using:

PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;

Actually, you only have to do the journal_mode=WAL once, when you first
create the database file.  But it doesn't hurt to do it every time.  And
doing it every time is a good safety mechanism in case some rogue user slips
in and turns the WAL mode back off without your program noticing.


>
>
>
>>
>>>
>>> On 2012-01-13 15:35, Richard Hipp wrote:
>>>
>>>  On Fri, Jan 13, 2012 at 3:34 PM, Normand 
>>> Mongeau>**wrote:


  On 2012-01-13 15:23, Richard Hipp wrote:
>
>  On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau **
>
>> com>wrote:
>>
>>
>>
>>  not really, no. This is a server that receives files, and the 
>> transaction
>>
>>  below means a file has arrived.
>>>
>>>  Does your server have a really, really slow disk drive?  
>>> Transaction
>>>
>>>  commit
>> normally takes milliseconds.  I'm not sure why you are having 
>> problems.
>>
>> Might another process be soaking up all the disk I/O bandwidth 
>> and making your process have to wait for an available slot?
>>
>>
>>  No, my machine is a normal PC, and I tried on several machines 
>> and
> they
> all react the same way.
>
>  What version of SQLite are you using?  Have you tried running 
> with all
>
 anti-virus software disabled, to see if that makes a difference?




  Normand

>
>
>   Normand
>
>>
>>> On 2012-01-13 15:16, Simon Slavin wrote:
>>>
>>>  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:
>>>
>>>   begin immediate transaction

  insert 1 record in tableA

> insert 1 record in tableB
> insert 1 record in tableC
> commit transaction
>
> Inserting 534 records takes about 75 seconds. Most of the time 
> (about
> 71
> seconds) is spent on the commit transaction instruction.
>
>  Can you put one transaction around the whole lot rather than 
> 178
>
>  separate
 transactions ?

 Simon.
 

Re: [sqlite] Slow commits

[Richard Hipp]

On Fri, Jan 13, 2012 at 6:49 PM, Normand Mongeau wrote:

>
>
>>>  Begin by doing:
>>
>> PRAGMA synchronous=OFF;
>>
>
> With the above, the total commitTransaction time goes down to 385
> milliseconds... Impressive.
>
>
The "PRAGMA synchronous=OFF" command turns of syncing of content to the
disk surface.  Normally, SQLite will pause at critical points and wait for
content to actually make it to disk oxide.  This ensures that your
transactions commit, and the database file is undamaged, even if a power
loss occurs in the middle of a write.  But "PRAGMA synchronous=OFF" turns
that mechanism off, so that SQLite just sends a "write()" system call to
the operating system and lets the operating system get the content to the
disk surface at its leisure.  That will work fine, as long as the power
never goes out.  But pull the power plug in the middle of a write, and you
might corrupt your database file.


>
>
>> That will determine if the problem is a slow disk or if we need to look
>> elsewhere.  I'm not suggesting you deploy with the above setting -- just
>> use it for debugging.
>>
>> You might also try:
>>
>>PRAGMA synchronous=NORMAL;
>>PRAGMA journal_mode=WAL;
>>
>> And see if you get better performance that way.
>>
>
> with strictly synchronous=NORMAL, time is 63 seconds.  Combined with WAL,
> time is 2.6 seconds.
>
> Not sure what it means exactly though.
>

The "PRAGMA journal_mode=WAL" uses a newer transaction mechanism that is
faster in many causes (such as yours).  The "PRAGMA synchronous=NORMAL"
means that syncs to disk only occur during a "checkpoint" operation, which
happens on a few commits, but rarely.  That is sufficient to ensure that
the database file is never corrupted by a power loss.  But one or more of
the most recent transactions might get rolled back by a power loss.  In
other words, you lose Durability.  If Durability is important to you (it
probably is not, unless you are a bank) then you can set "PRAGMA
synchronous=FULL" with "PRAGMA journal_mode=WAL" and it will sync after
every transaction.  That will reduce performance somewhat.  Usually the
reduction isn't noticeable.  But on your machine..

So what I think this all means is that you ought to be using:

PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;

Actually, you only have to do the journal_mode=WAL once, when you first
create the database file.  But it doesn't hurt to do it every time.  And
doing it every time is a good safety mechanism in case some rogue user
slips in and turns the WAL mode back off without your program noticing.


>
>
>
>>
>>>
>>> On 2012-01-13 15:35, Richard Hipp wrote:
>>>
>>>  On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau>**wrote:


  On 2012-01-13 15:23, Richard Hipp wrote:
>
>  On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau **
>
>> com>wrote:
>>
>>
>>
>>  not really, no. This is a server that receives files, and the
>> transaction
>>
>>  below means a file has arrived.
>>>
>>>  Does your server have a really, really slow disk drive?  Transaction
>>>
>>>  commit
>> normally takes milliseconds.  I'm not sure why you are having
>> problems.
>>
>> Might another process be soaking up all the disk I/O bandwidth and
>> making
>> your process have to wait for an available slot?
>>
>>
>>  No, my machine is a normal PC, and I tried on several machines and
> they
> all react the same way.
>
>  What version of SQLite are you using?  Have you tried running with all
>
 anti-virus software disabled, to see if that makes a difference?




  Normand

>
>
>   Normand
>
>>
>>> On 2012-01-13 15:16, Simon Slavin wrote:
>>>
>>>  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:
>>>
>>>   begin immediate transaction

  insert 1 record in tableA

> insert 1 record in tableB
> insert 1 record in tableC
> commit transaction
>
> Inserting 534 records takes about 75 seconds. Most of the time
> (about
> 71
> seconds) is spent on the commit transaction instruction.
>
>  Can you put one transaction around the whole lot rather than 178
>
>  separate
 transactions ?

 Simon.
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org

 http://sqlite.org:8080/cgi-bin/mailman/listinfo/**
 sqlite-***
 ***users

Re: [sqlite] Slow commits

[Normand Mongeau]

On 2012-01-13 18:10, Roger Binns wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 13/01/12 14:35, Normand Mongeau wrote:

It gets worse. On a clean empty database, the same 534 transactions
take 140 seconds.  That's a not very impressive rate of 3.8 inserts
per second. The FAQ says that SQLite should be able to do a "few dozen
transactions per second". I'd be happy to see that.

What file extension are you using for the database?  There is a long list
of extensions that System Restore monitors, and makes backups of the files
as they change.  This will kill your performance.


I was using .db as an extension, and changed it to something ludicrous, 
but it didn't make a difference.  Good idea though.


Thanks,

Normand




Extension list and terse details are at:

   http://msdn.microsoft.com/en-us/library/Aa378870

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8Quc4ACgkQmOOfHg372QQAowCgi0DlewfcHs6MIPIHSyjHw6mN
nFIAnjJch3erZfRF+I88yA3CzAkCQWVl
=HoSZ
-END PGP SIGNATURE-
___
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 commits

[Normand Mongeau]

On 2012-01-13 17:45, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 5:35 PM, Normand Mongeauwrote:


It gets worse. On a clean empty database, the same 534 transactions take
140 seconds.  That's a not very impressive rate of 3.8 inserts per second.
The FAQ says that SQLite should be able to do a "few dozen transactions per
second". I'd be happy to see that.

I don't have much experience with SQLite, but where does one start when
one wants to troubleshoot such issues?



Begin by doing:

 PRAGMA synchronous=OFF;


With the above, the total commitTransaction time goes down to 385 
milliseconds... Impressive.




That will determine if the problem is a slow disk or if we need to look
elsewhere.  I'm not suggesting you deploy with the above setting -- just
use it for debugging.

You might also try:

PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;

And see if you get better performance that way.


with strictly synchronous=NORMAL, time is 63 seconds.  Combined with 
WAL, time is 2.6 seconds.


Not sure what it means exactly though.







On 2012-01-13 15:35, Richard Hipp wrote:


On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau>wrote:



On 2012-01-13 15:23, Richard Hipp wrote:

  On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau>**wrote:


  not really, no. This is a server that receives files, and the
transaction


below means a file has arrived.

  Does your server have a really, really slow disk drive?  Transaction


commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and
making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they
all react the same way.

  What version of SQLite are you using?  Have you tried running with all

anti-virus software disabled, to see if that makes a difference?




  Normand



   Normand


On 2012-01-13 15:16, Simon Slavin wrote:

  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:


  begin immediate transaction

  insert 1 record in tableA

insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about
71
seconds) is spent on the commit transaction instruction.

  Can you put one transaction around the whole lot rather than 178


separate
transactions ?

Simon.
__**_
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-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 commits

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 13/01/12 14:35, Normand Mongeau wrote:
> 
> It gets worse. On a clean empty database, the same 534 transactions 
> take 140 seconds.  That's a not very impressive rate of 3.8 inserts
> per second. The FAQ says that SQLite should be able to do a "few dozen 
> transactions per second". I'd be happy to see that.

What file extension are you using for the database?  There is a long list
of extensions that System Restore monitors, and makes backups of the files
as they change.  This will kill your performance.

Extension list and terse details are at:

  http://msdn.microsoft.com/en-us/library/Aa378870

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8Quc4ACgkQmOOfHg372QQAowCgi0DlewfcHs6MIPIHSyjHw6mN
nFIAnjJch3erZfRF+I88yA3CzAkCQWVl
=HoSZ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[Richard Hipp]

On Fri, Jan 13, 2012 at 4:49 PM, John Elrick wrote:

>
> 3.6.17
>
> Procedure   % TimeTimeCalls
> _sqlite3_step   58.4%  1.795,052
> _memcpy 22.8%  0.691,342,957
> _memset  7.8%  0.24  465,299
> ...
> _malloc  1.9%  0.06   95,505
>
>
> 3.7.9
>
> Procedure   % TimeTimeCalls
> _malloc 56.9% 44.396,975,335
> _sqlite3_step   30.4% 23.685,052
> _memcpy  4.8%  3.707,710,259
>

Very curious

SQLite has lots of interfaces that can be used to determine performance and
status information.  Some example code from the command-line shell that
accesses this status information is here:
http://www.sqlite.org/src/artifact/aa4183d4a5243d?ln=990-1076

I would very much like to see some of these same stats from your
application.  Do you think you could add some code like that found in the
shell and give us some more insight into what is going on?


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

Re: [sqlite] Slow commits

[Simon Slavin]

On 13 Jan 2012, at 10:35pm, Normand Mongeau wrote:

> It gets worse. On a clean empty database, the same 534 transactions take 140 
> seconds.  That's a not very impressive rate of 3.8 inserts per second. The 
> FAQ says that SQLite should be able to do a "few dozen transactions per 
> second". I'd be happy to see that.

The figures you are getting are so slow they're a definite sign something is 
wrong.

> I don't have much experience with SQLite, but where does one start when one 
> wants to troubleshoot such issues?

Create a new user account on that computer.  Reboot.  Log into it.  Try running 
the same application doing the same thing.

But I think you may have already done a better test: run it on a different 
computer which has no anti-virus package running.  Presumably your computer 
isn't malware-ridden, and is performing reasonably quickly when running other 
applications, for instance, that it can start up Word in less than 30 seconds.

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

Re: [sqlite] Slow commits

[Richard Hipp]

On Fri, Jan 13, 2012 at 5:35 PM, Normand Mongeau wrote:

>
> It gets worse. On a clean empty database, the same 534 transactions take
> 140 seconds.  That's a not very impressive rate of 3.8 inserts per second.
> The FAQ says that SQLite should be able to do a "few dozen transactions per
> second". I'd be happy to see that.
>
> I don't have much experience with SQLite, but where does one start when
> one wants to troubleshoot such issues?
>
>
Begin by doing:

PRAGMA synchronous=OFF;

That will determine if the problem is a slow disk or if we need to look
elsewhere.  I'm not suggesting you deploy with the above setting -- just
use it for debugging.

You might also try:

   PRAGMA synchronous=NORMAL;
   PRAGMA journal_mode=WAL;

And see if you get better performance that way.


>
>
>
> On 2012-01-13 15:35, Richard Hipp wrote:
>
>> On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau> com >wrote:
>>
>>
>>> On 2012-01-13 15:23, Richard Hipp wrote:
>>>
>>>  On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau>**wrote:


  not really, no. This is a server that receives files, and the
 transaction

> below means a file has arrived.
>
>  Does your server have a really, really slow disk drive?  Transaction
>
 commit
 normally takes milliseconds.  I'm not sure why you are having problems.

 Might another process be soaking up all the disk I/O bandwidth and
 making
 your process have to wait for an available slot?


>>> No, my machine is a normal PC, and I tried on several machines and they
>>> all react the same way.
>>>
>>>  What version of SQLite are you using?  Have you tried running with all
>> anti-virus software disabled, to see if that makes a difference?
>>
>>
>>
>>
>>  Normand
>>>
>>>
>>>
>>>   Normand

>
>
> On 2012-01-13 15:16, Simon Slavin wrote:
>
>  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:
>
>>  begin immediate transaction
>>
>>  insert 1 record in tableA
>>> insert 1 record in tableB
>>> insert 1 record in tableC
>>> commit transaction
>>>
>>> Inserting 534 records takes about 75 seconds. Most of the time (about
>>> 71
>>> seconds) is spent on the commit transaction instruction.
>>>
>>>  Can you put one transaction around the whole lot rather than 178
>>>
>> separate
>> transactions ?
>>
>> Simon.
>> __**_
>> 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] Slow commits

[Normand Mongeau]

It gets worse. On a clean empty database, the same 534 transactions take 
140 seconds.  That's a not very impressive rate of 3.8 inserts per 
second. The FAQ says that SQLite should be able to do a "few dozen 
transactions per second". I'd be happy to see that.


I don't have much experience with SQLite, but where does one start when 
one wants to troubleshoot such issues?




On 2012-01-13 15:35, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeauwrote:



On 2012-01-13 15:23, Richard Hipp wrote:


On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau>wrote:

  not really, no. This is a server that receives files, and the transaction

below means a file has arrived.

  Does your server have a really, really slow disk drive?  Transaction

commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they
all react the same way.


What version of SQLite are you using?  Have you tried running with all
anti-virus software disabled, to see if that makes a difference?





Normand




  Normand



On 2012-01-13 15:16, Simon Slavin wrote:

  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

  begin immediate transaction


insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about
71
seconds) is spent on the commit transaction instruction.

  Can you put one transaction around the whole lot rather than 178

separate
transactions ?

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

Re: [sqlite] Some pieces of the puzzle are coming together

[Simon Slavin]

On 13 Jan 2012, at 9:49pm, John Elrick wrote:

> 3.6.17,
> _sqlite3_exec calls _malloc 1,101 times
> _sqlite3_step calls _malloc 1,812 times
> 
> 3.7.9
> _sqlite3_exec calls _malloc 65,227 times
> _sqlite3_step calls _malloc 47,109 times
> 
> At this point I've exhausted my avenues of research.  Does anyone have any
> further ideas as to what may be going on which could cause this increase in
> memory management activity?  Or any suggestions of where to look next?

Can you patch the call, or do something else, in order to find out the total 
amount of memory which _malloc is being called to allocate ?  In other words, 
if you have 60 times the number of calls, is it allocating 60 times the amount 
of memory, or asking for smaller pages so it has to ask for more of them, or 
are the extra calls to malloc failing ?

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

Re: [sqlite] Some pieces of the puzzle are coming together

[Pavel Ivanov]

> At this point I've exhausted my avenues of research.  Does anyone have any
> further ideas as to what may be going on which could cause this increase in
> memory management activity?  Or any suggestions of where to look next?

IIRC, SQLite 3.7.9 changed its PCACHE interface and thus page cache
implementation. It introduced one level of indirection into each page
in the cache. But or course I don't think it should result in such
huge amount of additional mallocs.
Also as you see effects only inside Delphi and not in command line
utility it suggests me that Delphi installed its own page cache. And
as it uses old (deprecated in 3.7.9) interface it may have some bad
interaction with new SQLite.

And just a thought: number of calls to memcpy with 3.7.9 is larger
than with 3.6.17 roughly on the same amount as number of mallocs.
Which suggests that all new calls are actually calls to realloc() (is
number of calls to free() also bigger on 6M?).


Pavel


On Fri, Jan 13, 2012 at 4:49 PM, John Elrick  wrote:
> Richard and all,
>
> On January 6, I wrote a posting (Problems encountered while upgrading
> Sqlite from 3.6.7 to 3.7.9) concerning a slow down noticed in upgrading our
> Delphi application from using 3.6.17 to 3.7.9.  Richard asked for some more
> specific information including a replicatable case, which has proven
> problematic.  Here are the results of my week long investigation.
>
> I created a logging system which took a specific set of data and converted
> all of the automatically run queries to an SQL script which I could use in
> a test application.  When testing this particular script using a test
> program which uses our Delphi wrappers the following times are observed:
>
> Test Application Run Batch Script
>
> 3.6.17: 14 seconds
> 3.7.9: 10 seconds
>
> This clearly demonstrates that the newer version of Sqlite is, all things
> being equal, superior in performance to the older.  However, tests inside
> our Delphi application demonstrate that reaching the exact same point of
> the database result in the following times:
>
> Live Application
>
> 3.6.17: 16 seconds
> 3.7.9: 58 seconds
>
> Extensive profiling of the application finally turned up an unusual and
> inexplicable difference between the console application and our regular
> application.  I am hoping someone on this group may have some ideas.
>
> I created a Pascal unit whose sole purpose was to delegate to our C
> standard library unit.  By having delegates in place it becomes easy to
> profile the application.  This unit is used only by the Sqlite libraries.
>
> When I execute the application with the same data as above, the following
> are the top consumers of time:
>
> 3.6.17
>
> Procedure               % Time    Time        Calls
> _sqlite3_step           58.4%      1.79        5,052
> _memcpy                 22.8%      0.69    1,342,957
> _memset                  7.8%      0.24      465,299
> ...
> _malloc                  1.9%      0.06       95,505
>
>
> 3.7.9
>
> Procedure               % Time    Time        Calls
> _malloc                 56.9%     44.39    6,975,335
> _sqlite3_step           30.4%     23.68        5,052
> _memcpy                  4.8%      3.70    7,710,259
>
>
>
> So, obviously the problem is that _malloc is being called a much larger
> number of times by 3.7.9 in this particular circumstance -- roughly 70
> times as often.  _memcpy demonstrates roughly 6 times as many calls   I ran
> a test with a much smaller dataset tracking every call to malloc back to
> the main source line.  The results were as follows:
>
> 3.6.17,
> _sqlite3_exec calls _malloc 1,101 times
> _sqlite3_step calls _malloc 1,812 times
>
> 3.7.9
>  _sqlite3_exec calls _malloc 65,227 times
> _sqlite3_step calls _malloc 47,109 times
>
> At this point I've exhausted my avenues of research.  Does anyone have any
> further ideas as to what may be going on which could cause this increase in
> memory management activity?  Or any suggestions of where to look next?
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
> ___
> 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] Some pieces of the puzzle are coming together

[Max Vlasov]

John,
I read your previous post and unfortunately, your conversation with Richard
didn't reveal much details. I worked with scenario similar tou yours
(Delphi + statically linked sqlite) for several years and during this
period an advanced admin/console form was developed that allows dynamical
loading of different versions of sqlite, reporting vfs bandwidth, times and
even the number of memory requests. So consider sharing more information
about your db and queries here or contact me directly if you wish, I hope I
can help at least a little.

As a quick guess I remember that fts was one of the places that allocated
heavily with some queries, but I can't recall right now.

Max

On Sat, Jan 14, 2012 at 1:49 AM, John Elrick wrote:

> Richard and all,
>
> On January 6, I wrote a posting (Problems encountered while upgrading
> Sqlite from 3.6.7 to 3.7.9) concerning a slow down noticed in upgrading our
> Delphi application from using 3.6.17 to 3.7.9.  Richard asked for some more
> specific information including a replicatable case, which has proven
> problematic.  Here are the results of my week long investigation.
>
> I created a logging system which took a specific set of data and converted
> all of the automatically run queries to an SQL script which I could use in
> a test application.  When testing this particular script using a test
> program which uses our Delphi wrappers the following times are observed:
>
> Test Application Run Batch Script
>
> 3.6.17: 14 seconds
> 3.7.9: 10 seconds
>
> This clearly demonstrates that the newer version of Sqlite is, all things
> being equal, superior in performance to the older.  However, tests inside
> our Delphi application demonstrate that reaching the exact same point of
> the database result in the following times:
>
> Live Application
>
> 3.6.17: 16 seconds
> 3.7.9: 58 seconds
>
> Extensive profiling of the application finally turned up an unusual and
> inexplicable difference between the console application and our regular
> application.  I am hoping someone on this group may have some ideas.
>
> I created a Pascal unit whose sole purpose was to delegate to our C
> standard library unit.  By having delegates in place it becomes easy to
> profile the application.  This unit is used only by the Sqlite libraries.
>
> When I execute the application with the same data as above, the following
> are the top consumers of time:
>
> 3.6.17
>
> Procedure   % TimeTimeCalls
> _sqlite3_step   58.4%  1.795,052
> _memcpy 22.8%  0.691,342,957
> _memset  7.8%  0.24  465,299
> ...
> _malloc  1.9%  0.06   95,505
>
>
> 3.7.9
>
> Procedure   % TimeTimeCalls
> _malloc 56.9% 44.396,975,335
> _sqlite3_step   30.4% 23.685,052
> _memcpy  4.8%  3.707,710,259
>
>
>
> So, obviously the problem is that _malloc is being called a much larger
> number of times by 3.7.9 in this particular circumstance -- roughly 70
> times as often.  _memcpy demonstrates roughly 6 times as many calls   I ran
> a test with a much smaller dataset tracking every call to malloc back to
> the main source line.  The results were as follows:
>
> 3.6.17,
> _sqlite3_exec calls _malloc 1,101 times
> _sqlite3_step calls _malloc 1,812 times
>
> 3.7.9
>  _sqlite3_exec calls _malloc 65,227 times
> _sqlite3_step calls _malloc 47,109 times
>
> At this point I've exhausted my avenues of research.  Does anyone have any
> further ideas as to what may be going on which could cause this increase in
> memory management activity?  Or any suggestions of where to look next?
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
> ___
> 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] Some pieces of the puzzle are coming together

[John Elrick]

Richard and all,

On January 6, I wrote a posting (Problems encountered while upgrading
Sqlite from 3.6.7 to 3.7.9) concerning a slow down noticed in upgrading our
Delphi application from using 3.6.17 to 3.7.9.  Richard asked for some more
specific information including a replicatable case, which has proven
problematic.  Here are the results of my week long investigation.

I created a logging system which took a specific set of data and converted
all of the automatically run queries to an SQL script which I could use in
a test application.  When testing this particular script using a test
program which uses our Delphi wrappers the following times are observed:

Test Application Run Batch Script

3.6.17: 14 seconds
3.7.9: 10 seconds

This clearly demonstrates that the newer version of Sqlite is, all things
being equal, superior in performance to the older.  However, tests inside
our Delphi application demonstrate that reaching the exact same point of
the database result in the following times:

Live Application

3.6.17: 16 seconds
3.7.9: 58 seconds

Extensive profiling of the application finally turned up an unusual and
inexplicable difference between the console application and our regular
application.  I am hoping someone on this group may have some ideas.

I created a Pascal unit whose sole purpose was to delegate to our C
standard library unit.  By having delegates in place it becomes easy to
profile the application.  This unit is used only by the Sqlite libraries.

When I execute the application with the same data as above, the following
are the top consumers of time:

3.6.17

Procedure   % TimeTimeCalls
_sqlite3_step   58.4%  1.795,052
_memcpy 22.8%  0.691,342,957
_memset  7.8%  0.24  465,299
...
_malloc  1.9%  0.06   95,505


3.7.9

Procedure   % TimeTimeCalls
_malloc 56.9% 44.396,975,335
_sqlite3_step   30.4% 23.685,052
_memcpy  4.8%  3.707,710,259



So, obviously the problem is that _malloc is being called a much larger
number of times by 3.7.9 in this particular circumstance -- roughly 70
times as often.  _memcpy demonstrates roughly 6 times as many calls   I ran
a test with a much smaller dataset tracking every call to malloc back to
the main source line.  The results were as follows:

3.6.17,
_sqlite3_exec calls _malloc 1,101 times
_sqlite3_step calls _malloc 1,812 times

3.7.9
 _sqlite3_exec calls _malloc 65,227 times
_sqlite3_step calls _malloc 47,109 times

At this point I've exhausted my avenues of research.  Does anyone have any
further ideas as to what may be going on which could cause this increase in
memory management activity?  Or any suggestions of where to look next?


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow commits

[Normand Mongeau]

On 2012-01-13 15:35, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeauwrote:



On 2012-01-13 15:23, Richard Hipp wrote:


On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau>wrote:

  not really, no. This is a server that receives files, and the transaction

below means a file has arrived.

  Does your server have a really, really slow disk drive?  Transaction

commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they
all react the same way.


What version of SQLite are you using?  Have you tried running with all
anti-virus software disabled, to see if that makes a difference?



3.7.9 is the version I'm using (statically linked BTW). Running on 
another machine with no anti-virus gives me similar times.






Normand




  Normand



On 2012-01-13 15:16, Simon Slavin wrote:

  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

  begin immediate transaction


insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about
71
seconds) is spent on the commit transaction instruction.

  Can you put one transaction around the whole lot rather than 178

separate
transactions ?

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

Re: [sqlite] Slow commits

[Richard Hipp]

On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau wrote:

>
>
> On 2012-01-13 15:23, Richard Hipp wrote:
>
>> On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau> com >wrote:
>>
>>  not really, no. This is a server that receives files, and the transaction
>>> below means a file has arrived.
>>>
>>>  Does your server have a really, really slow disk drive?  Transaction
>> commit
>> normally takes milliseconds.  I'm not sure why you are having problems.
>>
>> Might another process be soaking up all the disk I/O bandwidth and making
>> your process have to wait for an available slot?
>>
>
>
> No, my machine is a normal PC, and I tried on several machines and they
> all react the same way.
>

What version of SQLite are you using?  Have you tried running with all
anti-virus software disabled, to see if that makes a difference?




>
> Normand
>
>
>
>>  Normand
>>>
>>>
>>>
>>> On 2012-01-13 15:16, Simon Slavin wrote:
>>>
>>>  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

  begin immediate transaction

> insert 1 record in tableA
> insert 1 record in tableB
> insert 1 record in tableC
> commit transaction
>
> Inserting 534 records takes about 75 seconds. Most of the time (about
> 71
> seconds) is spent on the commit transaction instruction.
>
>  Can you put one transaction around the whole lot rather than 178
 separate
 transactions ?

 Simon.
 ___
 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
>



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

Re: [sqlite] Slow commits

[Normand Mongeau]

On 2012-01-13 15:23, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeauwrote:


not really, no. This is a server that receives files, and the transaction
below means a file has arrived.


Does your server have a really, really slow disk drive?  Transaction commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they 
all react the same way.


Normand





Normand



On 2012-01-13 15:16, Simon Slavin wrote:


On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

  begin immediate transaction

insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about 71
seconds) is spent on the commit transaction instruction.


Can you put one transaction around the whole lot rather than 178 separate
transactions ?

Simon.
__**_
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

Re: [sqlite] Slow commits

[Normand Mongeau]

Record sizes are approx 480 bytes for tableA, 380 bytes for tableB and 
800 bytes for tableC.


Storage is my hard drive, which is a normal SATA disk.


On 2012-01-13 15:23, Stephan Beal wrote:

On Fri, Jan 13, 2012 at 9:19 PM, Normand Mongeauwrote:


not really, no. This is a server that receives files, and the transaction
below means a file has arrived.



You haven't told us how big the records are. If you are storing, e.g., 2GB
file uploads in each transaction then of course it will be slow. You also
haven't told us what type of storage you're using. Someone posted recently
about a server process which writes to an SD card (which is bound to be
somewhat slow).



--
*Normand Mongeau*
ORS (Object Research Systems Inc.)
760 St-Paul W, #101
Montreal, QC
Canada H3C 1M4
Web: www.theobjects.com 
Tel: +1.514.843.3861 #204
Fax: +1.514.543.5475
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow commits

[Richard Hipp]

On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau wrote:

> not really, no. This is a server that receives files, and the transaction
> below means a file has arrived.
>

Does your server have a really, really slow disk drive?  Transaction commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and making
your process have to wait for an available slot?


>
> Normand
>
>
>
> On 2012-01-13 15:16, Simon Slavin wrote:
>
>> On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:
>>
>>  begin immediate transaction
>>> insert 1 record in tableA
>>> insert 1 record in tableB
>>> insert 1 record in tableC
>>> commit transaction
>>>
>>> Inserting 534 records takes about 75 seconds. Most of the time (about 71
>>> seconds) is spent on the commit transaction instruction.
>>>
>> Can you put one transaction around the whole lot rather than 178 separate
>> transactions ?
>>
>> Simon.
>> __**_
>> 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
>



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

Re: [sqlite] Slow commits

[Stephan Beal]

On Fri, Jan 13, 2012 at 9:19 PM, Normand Mongeau wrote:

> not really, no. This is a server that receives files, and the transaction
> below means a file has arrived.
>
>
You haven't told us how big the records are. If you are storing, e.g., 2GB
file uploads in each transaction then of course it will be slow. You also
haven't told us what type of storage you're using. Someone posted recently
about a server process which writes to an SD card (which is bound to be
somewhat slow).

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

Re: [sqlite] Slow commits

[Normand Mongeau]

not really, no. This is a server that receives files, and the 
transaction below means a file has arrived.


Normand


On 2012-01-13 15:16, Simon Slavin wrote:

On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:


begin immediate transaction
insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about 71 
seconds) is spent on the commit transaction instruction.

Can you put one transaction around the whole lot rather than 178 separate 
transactions ?

Simon.
___
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] Using non-aggregate columns in group by (with anadditional issue)

[Dilip Ranganathan]

On Fri, Jan 13, 2012 at 9:10 AM, Igor Tandetnik  wrote:

> Dilip Ranganathan  wrote:
> > Suppose among emp1 and emp2, I only want to see the entry with the latest
> > timestamp.
>
> select timestamp, value, person from mytable t1
> where rowid = (
>select rowid from mytable t2 where t1.person=t2.person
>order by value desc, timestamp desc limit 1);
>
>
Igor
Thank you very very much. That worked like a charm.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow commits

[Simon Slavin]

On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

> begin immediate transaction
> insert 1 record in tableA
> insert 1 record in tableB
> insert 1 record in tableC
> commit transaction
> 
> Inserting 534 records takes about 75 seconds. Most of the time (about 71 
> seconds) is spent on the commit transaction instruction.

Can you put one transaction around the whole lot rather than 178 separate 
transactions ?

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

[sqlite] Slow commits

[Normand Mongeau]

Hi,

I have an app using sqlite, and the main insertion point is very slow on 
commitTransaction.


This is what I do:

begin immediate transaction
insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about 71 
seconds) is spent on the commit transaction instruction.


If it's any important, this is all using the C++ API, with prepared 
statements.


Table A has 14 columns, with 2 indexes (not unique) and 1 primary key
Table B has 9 columns, with 2 non-unique indexes and 1 primary key
Table C has 13 columns, 1 non-unique index and 1 primary key

Current record count is 12, 10 and 627. Reason for few records in tableA 
and tableB is that I insert duplicate primary keys and just ignore 
SQLITE_CONSTRAINT errors.


Any ideas on what could be causing this?

Thanks,

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

Re: [sqlite] Solaris Sparc, SIGBUS core dump

[Alejandro Martínez]

Patch worked like a charm!

I'm sorry i couldn't find the patch myself, seems i didnt hit the
right words with google.

Thanks a lot, you saved my life :P.

On Fri, Jan 13, 2012 at 12:23 PM, Richard Hipp  wrote:
> 2012/1/13 Alejandro Martínez 
>
>> Hello,
>>
>> I'm running this piece of software on 2 machines that are "equal".
>>
>> SunOS brsassmm090 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire-V445
>>
>> In on of them, everything runs ok, but in the other (production :S) i
>> get a core dump:
>>
>
> http://www.sqlite.org/src/info/54cc119811
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Solaris Sparc, SIGBUS core dump

[Richard Hipp]

2012/1/13 Alejandro Martínez 

> Hello,
>
> I'm running this piece of software on 2 machines that are "equal".
>
> SunOS brsassmm090 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire-V445
>
> In on of them, everything runs ok, but in the other (production :S) i
> get a core dump:
>

http://www.sqlite.org/src/info/54cc119811

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

[sqlite] Solaris Sparc, SIGBUS core dump

[Alejandro Martínez]

Hello,

I'm running this piece of software on 2 machines that are "equal".

SunOS brsassmm090 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire-V445

In on of them, everything runs ok, but in the other (production :S) i
get a core dump:

In mdb:

> ::status
debugging core file of ftma_pss_cache (64-bit) from brsassmm091
file: /appl/CMS/ftmadmin/CMS/FTM_ROOT_BIN/FTM/FTMa/bin/ftma_pss_cache
initial argv: ftma_pss_cache
threading model: multi-threaded
status: process terminated by SIGBUS (Bus Error)
> ::stack
sqlite3CreateIndex+0xc18(1003b29c8, 1003b3d60, 1003b3d80, 1003b0d68,
1002bd3f8, 2)
yy_reduce+0x26ec(1003b3ce0, 1003b3e20, 17, 7fffb820, 1c00, ff)
sqlite3Parser+0x12c(1003b3c88, 1, 100291083, 1, 1003b29c8, 1003b2c71)
sqlite3RunParser+0x28c(1003b29c8, 100291047, 7fffba48,
1002bd1bc, 7b700200, 6)
sqlite3Prepare+0x2d4(10039fd38, 100291047, , 0, 0,
7fffbd50)
sqlite3LockAndPrepare+0xb0(10039fd38, 100291047, , 0,
0, 7fffbd50)
sqlite3_prepare+0x44(10039fd38, 100291047, ,
7fffbd50, 7fffbd58, 0)
sqlite3_exec+0x124(10039fd38, 100290f69, 0, 0, 7fffefc8,
7fffef4c)
main+0x448(1, 7538, 7548, 7db4bfc0,
7b900100, 7b700200)
_start+0x17c(0, 0, 0, 0, 0, 0)


Sigbus would make me think that it could be a bug in sqlite3 related
to memory alignment, but if so, why would it behave differently in two
computers that are "equal" as far as i can tell?

If it were a bug, anyone has any ideas on configuration changes,
compilation parameters or other things i could try to make it work?
I'm almost freaking out :P

Thanks a lot in advance.

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

Re: [sqlite] Using non-aggregate columns in group by (with anadditional issue)

[Igor Tandetnik]

Dilip Ranganathan  wrote:
> Suppose among emp1 and emp2, I only want to see the entry with the latest
> timestamp.

select timestamp, value, person from mytable t1
where rowid = (
select rowid from mytable t2 where t1.person=t2.person
order by value desc, timestamp desc limit 1);

-- 
Igor Tandetnik

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

[sqlite] Possible enhancement request.

[Mike King]

Hi All,

This is my first post here so please be gentle!

I'd like to make a suggestion for a future enhancement.I'm using the latest
System.Data.Sqlite and C#, I've got a small database with a freetext (FTS3)
table. When I query this table I use the Offsets function to get the
position of the matches in the record. The problem is that the offsets
returned by Offsets are byte offsets, in a future release would it be
possible to have a new function or something similar which returns
character offsets instead? (naturally depending on whether the database is
using UTF8 or 16).

I'm using the information returned by Offsets to highlight areas in a
string. If the string contains multi-byte Unicode characters then the byte
offsets do not match the position in the string. To get round this I
convert the string into a UTF8 byte array and then I can find the correct
byte positions easily and then the selected areas back to a string.

Cheers,

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

Re: [sqlite] Need a sqlite c api that wrires data into a table.

[Steve and Amy]

If I understand your question, the answer is NO.  There is NO function 
like sqlite3_insert_data_into_table(TableName, Data, FieldName).  The 
SQL engine responsible for reading and writing data from and to tables 
only responds to SQL queries passed to it via functions like sqlite3_exec().


For multiple INSERTions you can prepare a parameterized SQL query using 
sqlite3_prepare().  The query would look something like "INSERT INTO 
my_table (FirstField, SecondField) VALUES (:FirstField, :SecondField)".  
After preparing the query, the parameters (FirstField, SecondField) can 
be accessed individually via a simple function:  sqlite3_bind_...()


Steve.

On 1/13/2012 1:29 AM, bhaskarReddy wrote:

Hi friends,


   I check all the C APIs which are provided by sqlite.  I can able
to write data to a table, using sqlite3_exec() function.

But i need a C API that will write data into table, i.e., sqlite3_exec().

Ex: if i give "database name", table name and values to that
function, the particular function will enter the record of values into that
table.

Is there any C API like that in SQlite.


Regards,
Bhaskar


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

Re: [sqlite] Resetting virtual table

[Max Vlasov]

On Fri, Jan 13, 2012 at 3:02 PM, Dan Kennedy  wrote:

> On 01/13/2012 03:31 PM, Max Vlasov wrote:
>
>> As I see sqlite can accept the schema of a virtual table
>> (Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if
>> I
>> want to refresh not only data, but schema also, is it possible?
>>
>
> No way to do that. Can you Drop and then recreate the table?
>

Probably this way and reopening the db are the only options in this case.

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

Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)

[Dilip Ranganathan]

On Fri, Jan 13, 2012 at 6:22 AM, Simon Slavin  wrote:

>
> On 13 Jan 2012, at 11:07am, Dilip Ranganathan wrote:
>
> > I have a table that looks like something like this:
> >
> >timestampvalue   person
> >===
> >2010-01-12 00:00:00   33  emp1
> >2010-01-12 11:00:00   22  emp1
> >2010-01-12 09:00:00   16  emp2
> >2010-01-12 08:00:00   16  emp2
> >2010-01-12 12:12:00   45  emp3
> >2010-01-12 13:44:00   64  emp4
> >2010-01-12 06:00:00   33  emp1
> >2010-01-12 15:00:00   12  emp5
> >
> > I wanted to find the maximum value associated with each person.
>
> Do you have another table with your persons in it ?  In other words, can
> you do
>
>
Unfortunately No. I am stuck with that one table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)

[Luuk]

On 13-01-2012 12:07, Dilip Ranganathan wrote:
> I have a table that looks like something like this:
> 
> timestampvalue   person
> ===
> 2010-01-12 00:00:00   33  emp1
> 2010-01-12 11:00:00   22  emp1
> 2010-01-12 09:00:00   16  emp2
> 2010-01-12 08:00:00   16  emp2
> 2010-01-12 12:12:00   45  emp3
> 2010-01-12 13:44:00   64  emp4
> 2010-01-12 06:00:00   33  emp1
> 2010-01-12 15:00:00   12  emp5
> 
> I wanted to find the maximum value associated with each person. The obvious
> query was:
> 
> select person,max(value) from table group by person
> 
> Now I wanted to include the timestamp associated with each max(value). I
> could not use timestamp column in the above query because as everyone
> knows, it won't appear in the group by clause. So I wrote this instead:
> 
> select x.timestamp,x.value,x.person from table as x,
> (select person,max(value) as maxvalue from table group by person order by
> maxvalue desc) as y
> where x.person = y.person
> and x.value = y.maxvalue
> 
> This works -- to an extent. I now see:

Because it only works 'to an extent', try:

select t1.person, max(t1.value), t2.timestamp
from test t1
left join test t2 on t1.person=t2.person and t.1value=t2.value
group by t1.person;

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

Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)

[Simon Slavin]

On 13 Jan 2012, at 11:07am, Dilip Ranganathan wrote:

> I have a table that looks like something like this:
> 
>timestampvalue   person
>===
>2010-01-12 00:00:00   33  emp1
>2010-01-12 11:00:00   22  emp1
>2010-01-12 09:00:00   16  emp2
>2010-01-12 08:00:00   16  emp2
>2010-01-12 12:12:00   45  emp3
>2010-01-12 13:44:00   64  emp4
>2010-01-12 06:00:00   33  emp1
>2010-01-12 15:00:00   12  emp5
> 
> I wanted to find the maximum value associated with each person. 

Do you have another table with your persons in it ?  In other words, can you do

SELECT code FROM people ORDER BY code

?  If so, that gives you a list of people to start from.  Then you can do 
something like

SELECT people.code, max(scores.score)
FROM people
JOIN scores on scores.person = person.code
ORDER BY people.code

Note: the above is off the top of my head and untested.

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

[sqlite] Using non-aggregate columns in group by (with an additional issue)

[Dilip Ranganathan]

I have a table that looks like something like this:

timestampvalue   person
===
2010-01-12 00:00:00   33  emp1
2010-01-12 11:00:00   22  emp1
2010-01-12 09:00:00   16  emp2
2010-01-12 08:00:00   16  emp2
2010-01-12 12:12:00   45  emp3
2010-01-12 13:44:00   64  emp4
2010-01-12 06:00:00   33  emp1
2010-01-12 15:00:00   12  emp5

I wanted to find the maximum value associated with each person. The obvious
query was:

select person,max(value) from table group by person

Now I wanted to include the timestamp associated with each max(value). I
could not use timestamp column in the above query because as everyone
knows, it won't appear in the group by clause. So I wrote this instead:

select x.timestamp,x.value,x.person from table as x,
(select person,max(value) as maxvalue from table group by person order by
maxvalue desc) as y
where x.person = y.person
and x.value = y.maxvalue

This works -- to an extent. I now see:

timestampvalue   person
===
2010-01-12 13:44:00   64  emp4
2010-01-12 12:12:00   45  emp3
2010-01-12 06:00:00   33  emp1
2010-01-12 00:00:00   33  emp1
2010-01-12 08:00:00   16  emp2
2010-01-12 09:00:00   16  emp2
2010-01-12 15:00:00   12  emp5

The problem is now I get all the entries for emp1 and emp2 that ends up
with the same max(value).

Suppose among emp1 and emp2, I only want to see the entry with the latest
timestamp. IOW, I want this:

timestampvalue   person
===
2010-01-12 13:44:00   64  emp4
2010-01-12 12:12:00   45  emp3
2010-01-12 06:00:00   33  emp1
2010-01-12 09:00:00   16  emp2
2010-01-12 15:00:00   12  emp5

What kind of query would I have to write? Is it possible to extend the
nested query I wrote to achieve what I want or does one have to rewrite
everything from the scratch?

If its important, timestamps are actually stored as julian days. I use the
datetime() function to convert them back to a string representation in
every query.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Resetting virtual table

[Dan Kennedy]

On 01/13/2012 03:31 PM, Max Vlasov wrote:

Hi,

As I see sqlite can accept the schema of a virtual table
(Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if I
want to refresh not only data, but schema also, is it possible? For
example, I already did a query for my virtual table based on clipboard
contents, the clipboard has changed and I want the new select to contain
not only new rows, but also new columns (if this new contents is different
to the previous one).


No way to do that. Can you Drop and then recreate the table?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suppress "database is locked"?

[Hajo Locke]

Hello,
What did you want that command to actually do ?  Are you searching for 
short strings which occur in long strings ?  Do you perhaps mean something 
like


SELECT stringfield FROM mydb WHERE '%'||stringfield||'%' LIKE 
'abcabcabcabc'


basically yes. i did not kew the '%'||xxx||'%' notation, i just tried 
similar commands

But unfortunately it is not working. no error, but no dataset was found.

ahh, it works in reverse order:
SELECT stringfield FROM mydb WHERE 'abcabcabcabc' LIKE 
'%'||stringfield||'%';


may be a speacial restriction of like-operator to have wildcardparts only on 
right hand...


from performances point of view it should be ok to load pcre extension at 
scriptstart and do the regexp. in all cases the strings are not very long.

doing dbi instead of exec will be the most cost saving factor.

Thank you,
Hajo

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

Re: [sqlite] Performance gain from ATTACHing small writeable DB to large readonly?

[Simon Slavin]

On 13 Jan 2012, at 8:45am, Ulf BJORKENGREN wrote:

> I have a handful of quite populated tables that are only read, and two tables 
> with small number of rows that are written into. Right now it is all opened 
> as one readwriteable DB.
> If I make the large tables into one readonly DB, and the other two into one 
> readwriteable DB, and the use ATTACH to add them together, would I then gain 
> performance due to less overhead in terms of journalling files?
> I.e. will there not be any journaling of the readonly DB?
> Would it make any difference in a WAL mode?

I don't think so.

A SQLite database file is split into pages of a fixed length.  Each page can 
have information about only one table in it.  In other words, if you had a 
hundred tables, even if each table stored only a tiny bit of information, the 
database file would still need to use a hundred pages to store all that data.

The journaling mechanism reflects only pages which have changed.  So if you 
have tables in a database which don't change, they never use up any journal 
space.

However, there are other reasons to use ATTACH.  For instance, for security 
reasons you might want to keep the data which never changes in a file the user 
doesn't have privileges to change.

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

Re: [sqlite] suppress "database is locked"?

[Simon Slavin]

On 13 Jan 2012, at 8:06am, Hajo Locke wrote:

> in my db-field there are always just short strings like:
> 
> stringfield
> -
> abc
> acd
> bac
> bca
> 
> 
> the string which i get from script is in most cases much longer.
> so i was not successful when selecting this way:
> select stringfield from mydb where stringfield like '%abcabcabcabc%;

What did you want that command to actually do ?  Are you searching for short 
strings which occur in long strings ?  Do you perhaps mean something like

SELECT stringfield FROM mydb WHERE '%'||stringfield||'%' LIKE 'abcabcabcabc'

 ?

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

[sqlite] Performance gain from ATTACHing small writeable DB to large readonly?

[Ulf BJORKENGREN]

I have a handful of quite populated tables that are only read, and two tables 
with small number of rows that are written into. Right now it is all opened as 
one readwriteable DB.
If I make the large tables into one readonly DB, and the other two into one 
readwriteable DB, and the use ATTACH to add them together, would I then gain 
performance due to less overhead in terms of journalling files?
I.e. will there not be any journaling of the readonly DB?
Would it make any difference in a WAL mode?
/Ulf

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

[sqlite] Resetting virtual table

[Max Vlasov]

Hi,

As I see sqlite can accept the schema of a virtual table
(Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if I
want to refresh not only data, but schema also, is it possible? For
example, I already did a query for my virtual table based on clipboard
contents, the clipboard has changed and I want the new select to contain
not only new rows, but also new columns (if this new contents is different
to the previous one).

Thanks,

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

Re: [sqlite] suppress "database is locked"?

[Hajo Locke]

Hello,

On the other hand, it looks like all that extension does is implement some 
regex stuff, and you might be able to do what you want to do with SQLite's 
"LIKE" operator.


hmm, like operator would be the 1st choice, but this is not working in my 
case.

in my db-field there are always just short strings like:

stringfield
-
abc
acd
bac
bca


the string which i get from script is in most cases much longer.
so i was not successful when selecting this way:
select stringfield from mydb where stringfield like '%abcabcabcabc%;

it just works after using regexp this way:

select stringfield from mydb where 'abcabcabcabc' regexp stringfield;

i did not found a solution using like-operator, so i did it with regexp.

like Stephan Beal suggested the >>select 
load_extension("/usr/lib/sqlite3/pcre.so");<< is working. with perl-dbi.

so i think i will go this way.

Thanks,
Hajo


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