Re: [sqlite] SQLite performance for 10 Million Records

[Martin Jenkins]

Joe Wilson wrote:

--- Martin Jenkins <[EMAIL PROTECTED]> wrote:
For :memory: databases, long periods 
were observed where the VM size crept up but I/O write bytes did not, 
followed by periods where I/O bytes increased.


If you use "PRAGMA temp_store=MEMORY" with your :memory: database 
you will have no I/O whatsoever:


So it does. It smoothed the VM size counter rate, but didn't affect the 
overall run time.


Martin

[sqlite] sqlite and in memory databases

[james osburn]

i am looking for a embeddable sql database that i can use to manipulate 
memory stored in battery back sram. would sqllite be cable of doing this?


thanks
j osburn

Re: [sqlite] Hardcopy docs?

[Roger Binns]

> Do you know exactly which version of SQLite the book discusses?
>
Version 3.3.x.


The examples all use 3.3.4.  For what it is worth, my review of the
book is at:

 http://www.rogerbinns.com/sqlitereview.html

Roger

Re: [sqlite] Memory vs File: speed improvement factor ?

[Rick Keiner]

It also depends on the synchronous pragma. If you leave synchronous on with
a disk DB, you will see a huge improvement with an "in-memory". With
synchronous off, there is not that big an improvement until you start
inserting large amounts of data. You will then start seeing a slight
improvement. Totally unscientific observation.

Rick Keiner


On 6/26/06, Joe Wilson <[EMAIL PROTECTED]> wrote:


--- "Meier, Roger" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> What factor of speed improvement could I expect with a memory database?
>
> The database is located on compact flash(vfat) and has a size of 1 MB.
>
> Thank you very much
>
> Roger Meier

It depends what you mean by 'memory database' and the hardware you're
using.
Compact flash as read by a USB port is a couple orders of magnitudes
slower than system memory on a PC.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[sqlite] SQLite on Zeta: SQL error: kernel lacks large file support

[Johann 'Myrkraverk' Oskarsson]

Hi,

I've successfully built SQLite 3.3.6 on Zeta 1.2 but it fails with the
following error:

  sqlite> create table files (name text, contents blob);
  SQL error: kernel lacks large file support 

Would anyone here have a clue as to what function might return such an
error?  I'm not well enough versed in posix to know off hand what this
might be.

I'm just hoping I don't have to implement Zeta specific filesystem
fuctions.

For the curious about zeta, check out http://www.zeta-os.com/


Johann

-- 
johann myrkraverk com (you know the drill with the @ and .)
I classify Outlook mail as spam, use something else.

Re: [sqlite] Hardcopy docs?

[Bernie Cosell]

On 26 Jun 2006 at 14:14, [EMAIL PROTECTED] wrote:

> "Bernie Cosell" <[EMAIL PROTECTED]> wrote:
> > I'll confess that I am an old-fashioned "hardcopy" kind of guy... are the 
> > sqlite3 docs available in any sort of reasonably-printable format?
> > 
> 
> http://www.apress.com/book/bookDisplay.html?bID=10130

Hmmm... Since I don't really need 450+ pages of info, but just something 
akin to a crib sheet of the SQL that sqlite supports (which I'd guess 
would end up at about 20 pages if it weren't so difficultly-embedded in 
html files), the option of a fifty dollar book isn't really very useful 
to me. [BTW: no knock on Mike or on the worth of the book.  But: I'm not 
a beginner [either with Perl, SQL or SQLite] nor do I care hardly at all 
about SQLite's internals... I just need to know the SQL SQLite 
implements, and so virtually all of Mike's book is really not going to be 
of any interest or use to me... I'm just the wrong audience for it].

I guess I'll work harder at getting something readable out of the 
'lang.html' pages...  It looks like that if I cobble up a little script 
to take the pages linked out of lang.html and just delete the  and 
 blocks at the start of each page it'll get me just the reference 
info, and after that a pass through HTMLDOC and I suspect I'll have 
something more in line of what I was hoping for...  I'll report back on 
how it goes in case anyone cares...   tnx!

  /Bernie\

-- 
Bernie Cosell Fantasy Farm Fibers
mailto:[EMAIL PROTECTED] Pearisburg, VA
-->  Too many people, too few sheep  <--   

Re: [sqlite] Memory vs File: speed improvement factor ?

[Joe Wilson]

--- "Meier, Roger" <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> What factor of speed improvement could I expect with a memory database?
> 
> The database is located on compact flash(vfat) and has a size of 1 MB.
> 
> Thank you very much
> 
> Roger Meier

It depends what you mean by 'memory database' and the hardware you're using.
Compact flash as read by a USB port is a couple orders of magnitudes 
slower than system memory on a PC.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite performance for 10 Million Records

[Joe Wilson]

> I think some other factor is at play here.
> SQLite 2.x's memory databases are still twice as fast at batch inserts 
> than either 3.x's disk-based databases or 2.x's disk-based databases
> when the DB size is less than physical machine memory.

I did some experimentation with an SQLite 2.8.17 :memory: database 
versus a 3.3.6 :memory: database (with PRAGMA temp_store=memory in
both cases) and found something worth noting - SQLite 2.8.17 uses over 
four times as much in-process RAM to perform a batch insert of one 
million rows as compared to 3.3.6:

  version  in-process RAM
  ---  --
  2.8.17 268M
  3.3.6   64M

So although 3.3.6 takes almost twice the time to populate a
memory DB with a single transaction, it uses less than a quarter 
of the RAM that 2.8.17 required for the same operation. 

It appears to be a classic memory-for-speed tradeoff. 

I was not able to perform a single batch insert of 4 million
rows into a 2.8.17 :memory: database on my machine due to lack
of RAM.  No such problem with 3.3.6.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite performance for 10 Million Records

[Joe Wilson]

--- Martin Jenkins <[EMAIL PROTECTED]> wrote:
> For :memory: databases, long periods 
> were observed where the VM size crept up but I/O write bytes did not, 
> followed by periods where I/O bytes increased.

If you use "PRAGMA temp_store=MEMORY" with your :memory: database 
you will have no I/O whatsoever:


PRAGMA temp_store=MEMORY;
begin;
select datetime("now");
create table t(id INTEGER, t TEXT);
insert into t values (1,'Hello');
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
end;
select datetime("now");


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite performance for 10 Million Records

[Manzoor Ilahi Tamimy]

Hi,

I am really really thankful to all the members of this group. The Discussion 
here was really very helpful for me and also for the others. 

I was not as much experienced as the other members who took part in this 
dicussion, but i worked hard ad spent a lot of time to find out why i am 
getting ":memory:" mode slower or sometime no change in results or sometime 
only little faster may be one second at most. 

Now I think I am in the position to just leave thinking about to use SQLite 
in memory mode. 

I did some tests in the past and also showed results here to you all. in 
which, memory  mode was slower than disk mode. 

now again i did some tests 
1  Disk mode
2  Memory mode
3  ATTACH DB

these tests were as I mentioned earlier, tested at 
Pentium(R) D CPU 3.00GHz
2.00 GB of RAM 
WINXP Professional.

For All these tests I was Using SQLite 3.3.5, but now when I saw that in 
version 3.3.6 there is some improvement in :memory: then I did some tests 
again, but the results are not satisfactory.

As 'Dennis Cote', 'Joe Wilson', 'Martin' mentioned that "there is another 
factor at work here", I wish to participate in this discussion but I can't 
because of my little knowledge about the SQLite working.

But I wish, and I hope to find out the way , where we can see a clear 
difference between DISK and :memory: mode.

Dear Dennis Cote, 

As u suggested me to change my design, so Now I changed it. and its much 
better now.

Again really thanful to to all the members.

but if you all think that still there is some way then I will be happy to 
know about. 

Regards

TAMIMY. 

Re: [sqlite] SQLite Path Problem On Fedora Core 5

[Robert L Cochran]

Robert L Cochran wrote:


How do I fix my sqlite version mess so that I can have multiple
installed versions: the one used by yum (and possibly other modules) 
and

the latest and greatest release, which I want to link into PHP for my
own purposes. (Yes I compile PHP on my own.)
  


With an up to date yum install, FC5 default SQlite is 3.3.3
You can clean up thethings in the following way.

Instaed of using /usr/local for prefix when configuring a sqlite build
you could pass:
./configure --prefix=/usr/local/sqlite-3.3.6 (for 3.3.6 version)
This will install each of your multiple sqlite versions (except de
default used by yum) in its own envronment.

To build an application with a particular sqlite build, you'll have to
add, ie for PHP 5.1.4:

--with-sqlite=/usr/local/sqlite-3.3.6
--with-pdo-sqlite=/usr/local/sqlite-3.3.6

As PHP configuration system uses the --rpath which tells the binaries
the full path of used libraries, this will work.
For applications where you're not sure --rpath is used, you can add it
to configure options.

I have one more question about this. If I specify --with-tcl in this 
configuration, won't the tcl installer script wipe out the Fedora Core 
installed system TCL configuration (for sqlite 3.3.3) and replace it 
with my customized configuration (for 3.3.6?) Wouldn't this be an 
unintended (and perhaps unwanted) effect of compiling with TCL support? 
To preserve the current, system-installed TCL support for sqlite 3.3.3 
I'm passing --disable-tcl to configure.


Bob Cochran

Re: [sqlite] SQLite Path Problem On Fedora Core 5

[Robert L Cochran]

DJ Anubis wrote:


Robert L Cochran a écrit :
 


However, on Fedora Core 5, my path is set so that objects on
/usr/local/bin are found before those on /usr/bin. I'm not sure how this
is happening; perhaps /etc/profile? The result seems to be that even if
sqlite 3.3.3 was installed by yum, executing /usr/bin/sqlite3 will yeild
a command line stating its version is 3.2.7  which is one of the
versions I believe I installed to /usr/local myself.
   


Yes FC5 uses the following default path:
/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
which means your binaries in /usr/local/bin will be accessed before
those in /usr/bin.

 


How do I fix my sqlite version mess so that I can have multiple
installed versions: the one used by yum (and possibly other modules) and
the latest and greatest release, which I want to link into PHP for my
own purposes. (Yes I compile PHP on my own.)
   


With an up to date yum install, FC5 default SQlite is 3.3.3
You can clean up thethings in the following way.

Instaed of using /usr/local for prefix when configuring a sqlite build
you could pass:
./configure --prefix=/usr/local/sqlite-3.3.6 (for 3.3.6 version)
This will install each of your multiple sqlite versions (except de
default used by yum) in its own envronment.

To build an application with a particular sqlite build, you'll have to
add, ie for PHP 5.1.4:

--with-sqlite=/usr/local/sqlite-3.3.6
--with-pdo-sqlite=/usr/local/sqlite-3.3.6

As PHP configuration system uses the --rpath which tells the binaries
the full path of used libraries, this will work.
For applications where you're not sure --rpath is used, you can add it
to configure options.




 


Thank you very much for your help!

Bob

Re: [sqlite] v3.2.1 and current differences!

[Peter Bierman]

At 4:01 PM -0400 6/26/06, [EMAIL PROTECTED] wrote:

Peter Bierman <[EMAIL PROTECTED]> wrote:

 At 2:20 PM -0400 6/26/06, [EMAIL PROTECTED] wrote:
 >SQLite 3.3.0 can read and write all prior versions of SQLite
 >databases.  But SQLite 3.2.8 cannot read or write a database
 >created by SQLite 3.3.0, unless you use
 >
 >   PRAGMA legacy_file_format=TRUE;
 >
 >prior to creating the database, or unless you compile 3.3.0
 >with -DSQLITE_DEFAULT_FILE_FORMAT=1.

 Does this forward-incompatibility include the PRAGMA user_version?

 Ie., Can 3.2.8 read at least the user_version from a 3.3.0 file, even
 if it can't read anything else?


No.


Oy. Is there any way for a 3.2.x or earlier library to detect this situation?
(Maybe no way via the sqlite APIs, but probably a file format version 
at a known offset?)




Let me reemphasize that the new file format has caused so much
grief that I will likely revert to the older format with 3.4.0.
That is to say, databases created by 3.4.0 will be readable by
3.2.8.  3.4.0 will be able to read and write both the old and the
new formats, of course.  And you will still be able to use the new
format using a pragma or a compile-time option.  It just won't be
the default any more.

I have learned my lesson.  Do not enhance the file format without
a very good reason.  Saving one byte of space when storing booleans
is not a sufficiently good reason...



:-)

Progress is good, I just need a way to distinguish progress from 
errors. I thought the user_version PRAGMA would be the one safe value 
that any version could read from any other version.


What's the rough time line for 3.4.0?  (Or even a 3.3.x that reverts 
the file format.) It would be unfortunate if the next release of Mac 
OS X shipped with this issue.


-pmb

Re: [sqlite] Database locked. Any idea ?

[Mario . Hebert]

Just wanted to give an update in case someone follows this thread in the 
message archive later on. 

I found the problem, it was the silicon that was not doing certain memory 
accesses properly. As they say, always blame the hardware.

Thanks,
Mario Hebert





[EMAIL PROTECTED] 
06/20/2006 03:07 PM
Please respond to
sqlite-users@sqlite.org


To
sqlite-users@sqlite.org
cc

Subject
Re: [sqlite] Database locked. Any idea ?






[EMAIL PROTECTED] wrote:
> 
> Anyone has an idea of what may be wrong ? Oh yeah, I am running of my 
own 
> port using uCos and a memory database. 
> 

You say you are using a ":memory:" database and this
is happening?

Can you provide a test program running under Linux?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

Thanks, but do not base your decision on my.
I'm using sqlite to little to complain, i simply was not aware of the 
change.


I write software to make use of sqlite for other people (a designer).
I only need to mention this issue.



- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Monday, June 26, 2006 8:20 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


"Edwin Knoppert" <[EMAIL PROTECTED]> wrote:

Just wanted to warn you i can not read a newly created table created with
the current release and opening it in v3.2.1 (afaik)

Sorry, i removed the older dll, i overwrote it with the latest and read 
the

table instantly.
Before i had 0 tables shown.

A simple query was used:

CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )

I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.

I may assume only a major version will have a different format?



SQLite 3.3.0 can read and write all prior versions of SQLite
databases.  But SQLite 3.2.8 cannot read or write a database
created by SQLite 3.3.0, unless you use

 PRAGMA legacy_file_format=TRUE;

prior to creating the database, or unless you compile 3.3.0
with -DSQLITE_DEFAULT_FILE_FORMAT=1.

The file format enhancement in version 3.3.0 has caused an
inordinate amount of grief for the benefit it provides.  I
deeply regret making it the default.  I might yet, in a future
release, make the old file format the default.  The in a couple
of years time, once all the legacy versions of SQLite that
do not understand it have faded from existance, I can make
the enhanced file format the default again.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] v3.2.1 and current differences!

[drh]

Peter Bierman <[EMAIL PROTECTED]> wrote:
> At 2:20 PM -0400 6/26/06, [EMAIL PROTECTED] wrote:
> >SQLite 3.3.0 can read and write all prior versions of SQLite
> >databases.  But SQLite 3.2.8 cannot read or write a database
> >created by SQLite 3.3.0, unless you use
> >
> >   PRAGMA legacy_file_format=TRUE;
> >
> >prior to creating the database, or unless you compile 3.3.0
> >with -DSQLITE_DEFAULT_FILE_FORMAT=1.
> 
> Does this forward-incompatibility include the PRAGMA user_version?
> 
> Ie., Can 3.2.8 read at least the user_version from a 3.3.0 file, even 
> if it can't read anything else?

No.

> 
> Are there web pages that describe the pros and cons of the new file format?
> 

The new file format stores boolean values (the integers 0 and 1)
more efficiently - requiring only 1 bytes of disk space instead of
2.  There are no other changes.


Let me reemphasize that the new file format has caused so much
grief that I will likely revert to the older format with 3.4.0.
That is to say, databases created by 3.4.0 will be readable by
3.2.8.  3.4.0 will be able to read and write both the old and the
new formats, of course.  And you will still be able to use the new
format using a pragma or a compile-time option.  It just won't be
the default any more.

I have learned my lesson.  Do not enhance the file format without
a very good reason.  Saving one byte of space when storing booleans
is not a sufficiently good reason...

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] v3.2.1 and current differences!

[drh]

"C.Peachment" <[EMAIL PROTECTED]> wrote:
> You can create the database with the older Sqlite version, but any use
> with the new version must be preceded by the pragma, otherwise the
> new version changes the data format, making it unreadable by the
> old version thereafter.
> 

The file format is only updated if you VACUUM.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] v3.2.1 and current differences!

[Peter Bierman]

At 2:20 PM -0400 6/26/06, [EMAIL PROTECTED] wrote:

SQLite 3.3.0 can read and write all prior versions of SQLite
databases.  But SQLite 3.2.8 cannot read or write a database
created by SQLite 3.3.0, unless you use

  PRAGMA legacy_file_format=TRUE;

prior to creating the database, or unless you compile 3.3.0
with -DSQLITE_DEFAULT_FILE_FORMAT=1.


Does this forward-incompatibility include the PRAGMA user_version?

Ie., Can 3.2.8 read at least the user_version from a 3.3.0 file, even 
if it can't read anything else?


Are there web pages that describe the pros and cons of the new file format?

-pmb

Re: [sqlite] v3.2.1 and current differences!

[drh]

"Edwin Knoppert" <[EMAIL PROTECTED]> wrote:
> A bit odd the system does not handle backwards compatibility in minor 
> releases.
> 

It *does* handle backwards compatibility.  It is forwards
compatibility that causes problems.

Newer versions of SQLite can read databases generated
by all older versions.  Older versions of SQLite cannot
necessarily read databases that are generated by newer
versions of SQLite.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] v3.2.1 and current differences!

[C.Peachment]

You can create the database with the older Sqlite version, but any use
with the new version must be preceded by the pragma, otherwise the
new version changes the data format, making it unreadable by the
old version thereafter.

It got me confused when I started too.


On Mon, 26 Jun 2006 21:28:59 +0200, Edwin Knoppert wrote:

>>After opening the database file with a later library version and before 
>>doing anything else, issue the command:
>Sorry, maybe i misunderstood.
>The db is created with the latest release, v3.2.1 did not manage to read it.
>Maybe the pragma helps anyway..


>- Original Message - 
>From: "C.Peachment" <[EMAIL PROTECTED]>
>To: 
>Sent: Monday, June 26, 2006 8:16 PM
>Subject: Re: [sqlite] v3.2.1 and current differences!


>> On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:
>>
>>>Just wanted to warn you i can not read a newly created table created with
>>>the current release and opening it in v3.2.1 (afaik)
>>
>>>Sorry, i removed the older dll, i overwrote it with the latest and read 
>>>the
>>>table instantly.
>>>Before i had 0 tables shown.
>>
>>>A simple query was used:
>>
>>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )
>>
>>>I believe i also tried first:
>>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
>>>Sorry, i forgot.
>>
>>>I may assume only a major version will have a different format?
>>
>>
>> After opening the database file with a later library version and
>> before doing anything else, issue the command:
>>
>> PRAGMA legacy_file_format=ON;
>>
>>
>>
>> 

Re: [sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

After opening the database file with a later library version and before 
doing anything else, issue the command:

Sorry, maybe i misunderstood.
The db is created with the latest release, v3.2.1 did not manage to read it.
Maybe the pragma helps anyway..


- Original Message - 
From: "C.Peachment" <[EMAIL PROTECTED]>

To: 
Sent: Monday, June 26, 2006 8:16 PM
Subject: Re: [sqlite] v3.2.1 and current differences!



On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:


Just wanted to warn you i can not read a newly created table created with
the current release and opening it in v3.2.1 (afaik)


Sorry, i removed the older dll, i overwrote it with the latest and read 
the

table instantly.
Before i had 0 tables shown.



A simple query was used:



CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )



I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.



I may assume only a major version will have a different format?



After opening the database file with a later library version and
before doing anything else, issue the command:

PRAGMA legacy_file_format=ON;

Re: [sqlite] Hardcopy docs?

[Alex Roston]

Excellent. I'll start saving my pennies!!

Alex

[EMAIL PROTECTED] wrote:


Alex Roston <[EMAIL PROTECTED]> wrote:
 


Do you know exactly which version of SQLite the book discusses?

   



Version 3.3.x.
--
D. Richard Hipp   <[EMAIL PROTECTED]>


 

Re: [sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

OK, thanks!
Must been a change in the fileformat then..
A bit odd the system does not handle backwards compatibility in minor 
releases.


:)

PS, still can't find my own post, while a reply is given.. odd



- Original Message - 
From: "C.Peachment" <[EMAIL PROTECTED]>

To: 
Sent: Monday, June 26, 2006 8:16 PM
Subject: Re: [sqlite] v3.2.1 and current differences!



On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:


Just wanted to warn you i can not read a newly created table created with
the current release and opening it in v3.2.1 (afaik)


Sorry, i removed the older dll, i overwrote it with the latest and read 
the

table instantly.
Before i had 0 tables shown.



A simple query was used:



CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )



I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.



I may assume only a major version will have a different format?



After opening the database file with a later library version and
before doing anything else, issue the command:

PRAGMA legacy_file_format=ON;

Re: [sqlite] Hardcopy docs?

[drh]

Alex Roston <[EMAIL PROTECTED]> wrote:
> Do you know exactly which version of SQLite the book discusses?
> 

Version 3.3.x.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Hardcopy docs?

[Alex Roston]

Do you know exactly which version of SQLite the book discusses?

Thanks

Alex

[EMAIL PROTECTED] wrote:


"Bernie Cosell" <[EMAIL PROTECTED]> wrote:
 

I'll confess that I am an old-fashioned "hardcopy" kind of guy... are the 
sqlite3 docs available in any sort of reasonably-printable format?


   



http://www.apress.com/book/bookDisplay.html?bID=10130

--
D. Richard Hipp   <[EMAIL PROTECTED]>


 

Re: [sqlite] v3.2.1 and current differences!

[drh]

"Edwin Knoppert" <[EMAIL PROTECTED]> wrote:
> Just wanted to warn you i can not read a newly created table created with 
> the current release and opening it in v3.2.1 (afaik)
> 
> Sorry, i removed the older dll, i overwrote it with the latest and read the 
> table instantly.
> Before i had 0 tables shown.
> 
> A simple query was used:
> 
> CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )
> 
> I believe i also tried first:
> CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
> Sorry, i forgot.
> 
> I may assume only a major version will have a different format?
> 

SQLite 3.3.0 can read and write all prior versions of SQLite
databases.  But SQLite 3.2.8 cannot read or write a database
created by SQLite 3.3.0, unless you use

  PRAGMA legacy_file_format=TRUE;

prior to creating the database, or unless you compile 3.3.0
with -DSQLITE_DEFAULT_FILE_FORMAT=1.

The file format enhancement in version 3.3.0 has caused an
inordinate amount of grief for the benefit it provides.  I
deeply regret making it the default.  I might yet, in a future
release, make the old file format the default.  The in a couple
of years time, once all the legacy versions of SQLite that
do not understand it have faded from existance, I can make 
the enhanced file format the default again.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] v3.2.1 and current differences!

[C.Peachment]

On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:

>Just wanted to warn you i can not read a newly created table created with 
>the current release and opening it in v3.2.1 (afaik)

>Sorry, i removed the older dll, i overwrote it with the latest and read the 
>table instantly.
>Before i had 0 tables shown.

>A simple query was used:

>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )

>I believe i also tried first:
>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
>Sorry, i forgot.

>I may assume only a major version will have a different format?


After opening the database file with a later library version and
before doing anything else, issue the command:

PRAGMA legacy_file_format=ON;

Re: [sqlite] Hardcopy docs?

[drh]

"Bernie Cosell" <[EMAIL PROTECTED]> wrote:
> I'll confess that I am an old-fashioned "hardcopy" kind of guy... are the 
> sqlite3 docs available in any sort of reasonably-printable format?
> 

http://www.apress.com/book/bookDisplay.html?bID=10130

--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

Just wanted to warn you i can not read a newly created table created with 
the current release and opening it in v3.2.1 (afaik)


Sorry, i removed the older dll, i overwrote it with the latest and read the 
table instantly.

Before i had 0 tables shown.

A simple query was used:

CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )

I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.

I may assume only a major version will have a different format?

[sqlite] Hardcopy docs?

[Bernie Cosell]

I'll confess that I am an old-fashioned "hardcopy" kind of guy... are the 
sqlite3 docs available in any sort of reasonably-printable format?

  /Bernie\

-- 
Bernie Cosell Fantasy Farm Fibers
mailto:[EMAIL PROTECTED] Pearisburg, VA
-->  Too many people, too few sheep  <--   

Re: [sqlite] how make a case insensitive index

[Wilfried Mestdagh]

Thank you for the advice and the url :)
--
View this message in context: 
http://www.nabble.com/how-make-a-case-insensitive-index-t1849023.html#a5049996
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Re: problem with creating a table

[Dennis Cote]

Fred Williams wrote:


If brackets are a visual distraction, more common " Quote Marks may be
used as well.


  
Not only are double quote characters more common, they are the standard 
method for quoting names in SQL. The square brackets are a non-standard 
extension introduced by Microsoft in Access.  You should stick to the 
standard double quote for better compatibility with other databases engines.


create table t (id integer primary key, "order" integer, "a long name" 
text);


select "a long name", "order" from t;

HTH
Dennis Cote

Re: [sqlite] SQLite performance for 10 Million Records

[Martin Jenkins]

Dennis Cote wrote:

Joe Wilson wrote:


I think some other factor is at play here.


Yes there is another factor at work here. [...] I suspect there are 
optimizations that could be made to the memory I/O routines to speed 
them up, they should at least be able to run slightly faster than file 
based I/O.


Dennis Cote


I still find it rather hard to accept that disk databases are as fast or 
faster than memory databases, so I ran a *simple* test on WinXP and they 
are. It's so counterintuitive. :(


So, I used the Task Manager to watch the sqlite shell run a simple bit 
of table stuffing SQL, shown below. For :memory: databases, long periods 
were observed where the VM size crept up but I/O write bytes did not, 
followed by periods where I/O bytes increased. For disk I/O VM size 
stayed constant at about 4MB and I/O bytes increased steadily 
throughout. The tests took about 3m5s and 3m47s, so the disk based 
database was slower in this case, but not by much.


On the face of it, it seems that :memory: databases cause sqlite to 
spend a lot of time mallocing large amounts of memory by requesting a 
large number of small chunks, but I wouldn't have expected this to be as 
slow as disk I/O.


Has anyone done a proper profiling analysis? I don't really buy the disk 
caching suggestion because if we have a large transaction, then surely 
we still have to wait an appreciable time after the commit for the data 
to be sync'd to the disk. If we extrapolate through extremes, are we 
saying that disk caching makes really slow hard disks and floppies as 
fast as memory I/O. Seems very odd. Where does the sync time go?


Martin

Noddy SQL follows:


begin;
select datetime("now");
create table t(id INTEGER, t TEXT);
insert into t values (1,'Hello');
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
end;
select datetime("now");

Re: [sqlite] how make a case insensitive index

[A. Pagaltzis]

* Wilfried Mestdagh <[EMAIL PROTECTED]> [2006-06-26 15:45]:
> How to make a case insensitieve index ?

Add `COLLATE NOCASE` to the column definition.

See http://www.sqlite.org/datatype3.html

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] how make a case insensitive index

[Wilfried Mestdagh]

Hi,

Made an extra field with a lowercase portion of the field I wants to sort
on. I think it is the most fast. Of course when someone can give better
advice it is very welcome :)

rgds, Wilfried
--
View this message in context: 
http://www.nabble.com/how-make-a-case-insensitive-index-t1849023.html#a5048042
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] SQLite performance for 10 Million Records

[Dennis Cote]

Joe Wilson wrote:


I think some other factor is at play here.
SQLite 2.x's memory databases are still twice as fast at batch inserts 
than either 3.x's disk-based databases or 2.x's disk-based databases

when the DB size is less than physical machine memory.

  

Joe,

Yes there is another factor at work here. In version 3.x SQLite uses the 
same pager for both memory and file based databases. The file based 
pager calls to the OS for I/O to the disk file, but SQLite implements 
its own I/O (really read/write) routines to get and save pages in a 
memory based database. In version 2.x the memory based databases used a 
completely different pager than the file based databases. This change 
was made to simplify the code and helps to ensure all features work the 
same regardless of where the database pages are stored, but it has 
definitely slowed down the memory based databases. Memory based database 
used to be twice as fast as file based databases, but they are now 
slightly slower than file based databases. I suspect there are 
optimizations that could be made to the memory I/O routines to speed 
them up, they should at least be able to run slightly faster than file 
based I/O.


Dennis Cote

[sqlite] how make a case insensitive index

[Wilfried Mestdagh]

Hi,

How to make a case insensitieve index ?
I tryed with [lower(fieldname)] and other combinations but this give me a
syntax error.

The select will work with 'order by lower(fieldname)' but it should use the
right index.
--
View this message in context: 
http://www.nabble.com/how-make-a-case-insensitive-index-t1849023.html#a5047266
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Sqlite crashes when i imort huge list

[Jay Sprenkle]

On 6/26/06, C.Peachment <[EMAIL PROTECTED]> wrote:


>>The use of sequential numbers as the trailing part of the 'word'
>>results in a continual rebalancing of the b-tree with each insertion.

>Is that right considering it looks like you have not created an index on
>the word column before inserting the data?


Oops, you got that right :-)

I was doing a lazy Sunday morning quick and dirty implementation
and forgot to define the index. If you assume there had been
a PRIMARY KEY phrase after the field definition then I think
my comment would be valid.


I wrote a command line importer. You're welcome to try it if your
problem wasn't already solved. It is much slower to insert into an indexed
table than to insert all the records then create the index. It doesn't use
time rebalancing the trees when it's done as a batch at the end.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] Memory vs File: speed improvement factor ?

[Unit 5]

--- John Stanton <[EMAIL PROTECTED]> wrote:
> Meier, Roger wrote:
> > Hi,
> > 
> > What factor of speed improvement could I expect
> with a memory database?
> > 

> Little to none
> 

This is my experience as well, under a variety of
conditions that I have tested.  It is interesting; at
times, it may in fact be slightly slower.



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Where is the sqlite3.h file?

[Roberto]

The .h file is in the preprocessed source. The def file lists the
exports from the dll, which is useful when some new experimental
functions get defined in the .h file, but are not yet exported form
the Dll.

On 25/06/06, onemind <[EMAIL PROTECTED]> wrote:


Hi,

I am trying to use the .dll with c but it doesn't come with the eader file.
Also, what is the.def file that comes with it?

Re: [sqlite] Problem with lempar.c revision 1.17

[Garrett Rooney]

On 6/26/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Garrett Rooney" <[EMAIL PROTECTED]> wrote:
> >
> > Your grammer does not generate an empty string.  That means
> > if you call the parser with an EOF token first, without any
> > prior tokens, you are going to get a syntax error.  The
> > "yymajor==0" test was removed in order to get this to
> > work correctly.  Geert Janssen at IBM found this bug back
> > in November - his grammar does not generate an empty string
> > but lemon was not giving him a syntax error like it should
> > if he passed in an empty string.  If I add the yymajor==0
> > test back, it will reintroduce the bug.
>
> Hmm.  I don't believe that's happening, but I'll look into adding
> something to deal with that case.
>
> > Perhaps you are calling Parse() with EOF twice in a row
> > when you should only be calling it once?  That would trigger
> > the syntax error.  Or perhaps you should adjust your grammar
> > to accept an empty string?
>
> Nope, I only send EOF in one location, the lexer never produces it,
> and I only pass it in after I've run out of tokens from the lexer.
>
> > The %extra_argument that gets passed to the %syntax_error procedure
> > should be the %extra_argument that got passed into with the
> > call to Parse() that contained the EOF token.  If that %extra_argument
> > was NULL, then the %syntax_error procedure will get a NULL.
> > Perhaps you can either pass in a non-null parameter to Parse
> > or check for NULL in %syntax_error and branch accordingly.
>
> The extra argument passed in to the parse function is a pointer to
> memory on the stack.  It's just not possible for it to be null, unless
> something is messing with that value before it gets into the
> %syntax_error block...
>

I don't have any more ideas about what might be going wrong.
Can you send me a reproducible test case that I use to track
down the problem?


I've been a bit busy lately, so I haven't been able to look at this.
For now, if you want to download ETL from Subversion and build it with
that version of lempar.c just running "make check" should give you a
number of failures.  If you'd like me to package up a tarball you can
try out I can do that, but it'll probably take me a few days to get
around to it.

Thanks,

-garrett

Re: [sqlite] Problem with lempar.c revision 1.17

[drh]

"Garrett Rooney" <[EMAIL PROTECTED]> wrote:
> >
> > Your grammer does not generate an empty string.  That means
> > if you call the parser with an EOF token first, without any
> > prior tokens, you are going to get a syntax error.  The
> > "yymajor==0" test was removed in order to get this to
> > work correctly.  Geert Janssen at IBM found this bug back
> > in November - his grammar does not generate an empty string
> > but lemon was not giving him a syntax error like it should
> > if he passed in an empty string.  If I add the yymajor==0
> > test back, it will reintroduce the bug.
> 
> Hmm.  I don't believe that's happening, but I'll look into adding
> something to deal with that case.
> 
> > Perhaps you are calling Parse() with EOF twice in a row
> > when you should only be calling it once?  That would trigger
> > the syntax error.  Or perhaps you should adjust your grammar
> > to accept an empty string?
> 
> Nope, I only send EOF in one location, the lexer never produces it,
> and I only pass it in after I've run out of tokens from the lexer.
> 
> > The %extra_argument that gets passed to the %syntax_error procedure
> > should be the %extra_argument that got passed into with the
> > call to Parse() that contained the EOF token.  If that %extra_argument
> > was NULL, then the %syntax_error procedure will get a NULL.
> > Perhaps you can either pass in a non-null parameter to Parse
> > or check for NULL in %syntax_error and branch accordingly.
> 
> The extra argument passed in to the parse function is a pointer to
> memory on the stack.  It's just not possible for it to be null, unless
> something is messing with that value before it gets into the
> %syntax_error block...
> 

I don't have any more ideas about what might be going wrong.
Can you send me a reproducible test case that I use to track
down the problem?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Memory vs File: speed improvement factor ?

[John Stanton]

Meier, Roger wrote:

Hi,

What factor of speed improvement could I expect with a memory database?

The database is located on compact flash(vfat) and has a size of 1 MB.

Thank you very much

Roger Meier

Little to none

RE: [sqlite] Sqlite crashes when i imort huge list

[C.Peachment]

On Mon, 26 Jun 2006 11:20:59 +0100, Brandon, Nicholas (UK) wrote:



>>The use of sequential numbers as the trailing part of the 'word'
>>results in a continual rebalancing of the b-tree with each insertion.

>Is that right considering it looks like you have not created an index on
>the word column before inserting the data?


Oops, you got that right :-)

I was doing a lazy Sunday morning quick and dirty implementation
and forgot to define the index. If you assume there had been
a PRIMARY KEY phrase after the field definition then I think
my comment would be valid.

RE: [sqlite] Sqlite crashes when i imort huge list

[Brandon, Nicholas (UK)]

>The use of sequential numbers as the trailing part of the 'word'
>results in a continual rebalancing of the b-tree with each insertion.

Is that right considering it looks like you have not created an index on
the word column before inserting the data?

Nick



This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.