[sqlite] Separate INTEGER and REAL affinity ?

[Zibetti Paolo]

I read in the "changes" page of the SQLite site that version 3.3.x of SQLite
features "Separate INTEGER and REAL affinity".

What does this exactly mean ?
How is SQLite 3.3.x different from 2.8.x with respect to column affinity ?

Thank you, bye

Re: [sqlite] Versioning in SQL database?

[Randall]

Hi,
I acnnot get "IF EXISTS" to work for "DROP TABLE IF EXISTS tablename";
ver 3.2.8;
I see "IF NOT EXISTS" started in ver 3.3.0, but "IF EXISTS" has been in the 
syntax page for at least months; am I doing something wrong?
Randall 

Re: [sqlite] Versioning in SQL database?

[Randall]

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

To: 
Sent: Wednesday, February 08, 2006 8:58 AM
Subject: Re: [sqlite] Versioning in SQL database?



On Tue, Feb 07, 2006 at 10:55:27AM -0500, Paul Tomblin wrote:

I am putting together something that will act like a Wiki for structured
data (in this case, airport and navigation aid data like id, location,
runways, etc).  I currently store the data in an SQL databasee, but only
the "current" version.  I want to allow people to edit that data, but 
that

means being able to compare versions, roll back erroneous edits, get what
the database looked like before a particular editor came along, etc.  Is
there anything written on this topic?  Has anybody ever tried it before?


I would add a version column to the appropriate tables and create views
that pull the most recent version of everything. Or if performance
becomes an issue, keep a table with only the current versions, and a
seperate table with either all older versions or just all versions. An
added benefit of a version field is it's a trivial way to detect
potential conflicts; if you have the edit code pass back the version it
was editing, you can verify that that version is what's still in the
database.
--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461 

Re: [sqlite] Versioning in SQL database?

[Arjen Markus]

Paul Tomblin wrote:
> 
> I am putting together something that will act like a Wiki for structured
> data (in this case, airport and navigation aid data like id, location,
> runways, etc).  I currently store the data in an SQL databasee, but only
> the "current" version.  I want to allow people to edit that data, but that
> means being able to compare versions, roll back erroneous edits, get what
> the database looked like before a particular editor came along, etc.  Is
> there anything written on this topic?  Has anybody ever tried it before?
> 
>

I recently saw something similar to what you describe (well, the airport
and navigation stuff) - http://www.flightaware.com - but maybe that is
just a similar area of application.

Regards,

Arjen

Re: [sqlite] Fedora Core 4 RPM Version of SQLite And A Sad Tale of PHP Configure Error In v3.3.3

[Robert L Cochran]

Dan Kennedy wrote:


configure:79872: checking for sqlite_open in -lsqlite
   



It might be looking for sqlite version 2, not 3. Grep the
configure script for the string "sqlite3_open", and then
"sqlite_open". If you find the latter and not the former,
it's version 2 you need to install.



 



You were exactly right. I compiled 2.8.17 and that let PHP 6 configure, 
make, and make install.


However I think I blew away Fedora's yum (yellowdog updater) program 
when I compiled and installed 3.3.3...or maybe 2.8.17 did it...yum seems 
to have gone haywire. But there was also a raft of Fedora updates so 
I'll not point fingers at SQLite. I was just recalling that yum uses 
SQLite now and my own misconfiguration could have caused a problem.


Bob Cochran



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



 

Re: [sqlite] aynchronous loading

[Eric Bohlman]

chetana bhargav wrote:

Does sqlite provides asynchronous loading of data. Basically if I
have something around 3000 records and want to do some query, instead
of returning the result in one single query is it possible for me to
relinquish the control to other apps so that I wont get a time out
error. and get the data in sets.


Certainly.  Just use the prepare/bind/step interface rather than the 
(legacy) sqlite3_exec() interface.  Prepare your query, bind any 
parameters to it, and then retrieve as many rows at a time as you want 
using sqlite3_step().

Re: [sqlite] Versioning in SQL database?

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 10:55:27AM -0500, Paul Tomblin wrote:
> I am putting together something that will act like a Wiki for structured
> data (in this case, airport and navigation aid data like id, location,
> runways, etc).  I currently store the data in an SQL databasee, but only
> the "current" version.  I want to allow people to edit that data, but that
> means being able to compare versions, roll back erroneous edits, get what
> the database looked like before a particular editor came along, etc.  Is
> there anything written on this topic?  Has anybody ever tried it before?

I would add a version column to the appropriate tables and create views
that pull the most recent version of everything. Or if performance
becomes an issue, keep a table with only the current versions, and a
seperate table with either all older versions or just all versions. An
added benefit of a version field is it's a trivial way to detect
potential conflicts; if you have the edit code pass back the version it
was editing, you can verify that that version is what's still in the
database.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

RE: [sqlite] How to create a queue using Sqlite

[Lodewijk Duymaer van Twist]

Thanks this helps a lot! I'll get on it right away. 

Kind regards,

Lodewijk

-Original Message-
From: Christian Smith [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 06, 2006 9:34 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to create a queue using Sqlite

On Thu, 2 Feb 2006, Lodewijk Duymaer van Twist wrote:

>Hi,
>
>
>
>I would like to create some sort of queue using sqlite. I'm thinking about
>transmitting commands to hardware devices/managers using a queue table.
>
>Is it possible to trigger a function created with sqlite3_create_function
in
>one application by another application. Kind of like a plug-in application.


No. The function is associated with the database connection, and is
invoked by the sqlite VM of the compiled statement.


>
>
>
>Is this possible without polling the content of this queue table.


Question is, what's specifically wrong with polling?

Use an auto-increment integer primary key, then the queueing application
queues jobs by inserting them into the table (using NULL for the primary
key.)

The second application sits in a loop, reading the entries as they are
created using the primary auto-increment key to keep track of what has
already been processed. Pseudo code:

App 1:
void submit_job( args )
{
exec("insert into spool values ();");
}

App 2:
boolean process_jobs()
{
static int last_job = 0;
int prev_last_job = last_job;

// Get jobs to be processed into separate table
exec("begin");
exec("insert into temp_spool select * from spool where
id>$last_job");
exec("delete from spool where id>$last_job");
exec("commit;");

// Process jobs from separate table, then delete.
exec("begin");
foreach("select * from temp_spool;") {
last_job = id;
process job;
}
exec("delete from temp_spool;");
exec("commit;");

return ( last_job != prev_last_job );
}


In App2, process_jobs will return true if any jobs were processed, and
false otherwise. You'd test this return value and re-call process_jobs if
true as more jobs may have come in as you processed the last jobs:
App2:
while(1) {
if (!process_jobs) {
sleep(1);
}
}

This ensures you don't poll excessively when there ae no jobs coming, and
don't sleep unnecassarily when jobs are coming in steadily. You'd want the
temp_spool table in a seperate database, so as not to tie up the main
database while processing jobs.

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 07:54:19PM +0100, Nemanja Corlija wrote:
> On 2/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> > You might want to put a legend on your results tables so it's clear
> > what the numbers represent. I assume these are times but I didn't see
> > anything so far that said.
> Its time in seconds.
> "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of
> MySQL it signifies usage of InnoDB engine.
> "nosync" is the opposite, of course synchronous=OFF; and MyISAM engine.

BTW, if you want to test both sync and async options you can turn fsync
off in PostgreSQL by setting fsync to false. If you do that I'd also
turn full_page_writes off as well, since there's no point in writing the
extra info.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 08:07:03PM +0100, Nemanja Corlija wrote:
> On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> > AFAIK MySQL ships with a few different config files, so presumably
> > choosing the appropriate one would be equivalent to what I provided for
> > PostgreSQL.
> Yes, and I installed it as a developer machine. That should be the
> least performant one of the 3 available.
 
Which is probably targeted at a machine similar to yours.

> > BTW, has anyone looked at adding SQLite support to any of the DBT
> > benchmarks? http://sourceforge.net/projects/osdldbt
> I just glanced over it, but I think they concentrate on heavy load
> testing while my focus is on single user environment for now.

Well, they're replacements for the various TPC benchmarks. dbt3 is
equivalent to TPC:H, which is a warehousing environment.

The advantage to these tests is that they're built on real-world
applications, while much of this benchmark is stuff you'd never want to
see in a real application, at least not if you cared about performance.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 11:51:22AM -0500, [EMAIL PROTECTED] wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> > On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote:
> > > Hrm, that's rather odd. What does top show when it's running through
> > > psql? Are the test scripts available for download? I'll try this on my
> > > machine as well...
> > 
> > I see theh tcl now... is TCL piping into psql, or are there a set of raw
> > files you could post or send me? If you're piping from TCL, I'd be
> > curious to see what the difference is if you run this manually. For
> > these large data sets I also think it's not very reflective of the
> > database to send the result set all the way back through the client,
> > since that's not very representative of the real world. In the case of
> > PostgreSQL, a good alternative would be
> > 
> > SELECT count(*) FROM (
> > SELECT t1.a  FROM ...
> > ) a
> > ;
> > 
> > But I'm not sure if all the other databases support that.
> 
> SQLite supports the syntax above, FWIW.
> 
> Your theory is that SQLite does well because it doesn't need to
> send data back and forth between the client and server?  You're
> probably right.  On the other hand, what good is the data if
> the client never sees it?

Well, my point was that the test in question is probably generating
close to 100k rows if not more. Trying to pull that much data from the
database at once is either poor design (something I've seen far too
often) or a pretty unusual set of requirements. In any case, it
certainly wouldn't surprise me if psql gets in the way here.

> You'll notice that SQLite seems to do particularly well on the
> tests that involve a lot of SQL.  For example, test 2 with
> 25000 separate INSERT statements.  SQLite ran in 0.7 seconds
> versus 16.5 seconds for PostgreSQL.  Probably a big fraction of
> the 16.5 seconds PostgreSQL used were in transmitting all of
> that SQL over a socket to the server.  I'm wondering if the
> use of prepared statements might reduce the performance gap
> somewhat?  Notice that when doing an equally large insert in
> Test 12, but an insert that involves much less SQL and parsing,
> that PostgreSQL is actually a little faster than SQLite.
> 
> Any volunteers to run the experiment?  Jim?

The original poster is sending me the generated files. I'll run an
experiment with prepared statements and see what that gains us. But yes,
trying to prepare that many statements over and over is a sure-fire way
to slow things down.

> Another explanation for the poor performance by PostgreSQL in
> test 2 might be the PostgreSQL parser is less efficient.  Or
> perhaps the PostgreSQL spends a lot more time trying to
> optimize - which can pay off on a big query but is a drag for
> lots of silly little inserts.  A test using prepared statements
> would help clearify the issue.

Off the top of my head, in the INNER JOIN case I believe there's about 8
different ways to execute that query, and PostgreSQL will consider all
of them for every statement. So it certainly wouldn't surprise me if
that was a major issue.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

RE: [sqlite] disk locality (and delta storage)

[DeMarco, Paul]

Trac (http://projects.edgewall.com/trac) is a VCS (Subversion) + Wiki + 
Ticketing... And is built on top of a sqlite db.  It has a fair amount of 
installation pre-requisites but is a very clean interface and trys to adhere to 
KISS for their feature set.

Its of course not usable if your still debating your VCS, Trac forces you into 
SVN (mostly).  There is currently work to support varying VCS backends, 
monotone is not on the list of supported systems yet... But there is a ticket 
to have it added.

http://projects.edgewall.com/trac/ticket/1492

--paul


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 07, 2006 7:18 AM
To: sqlite-users@sqlite.org
Cc: monotone-devel@nongnu.org
Subject: Re: [sqlite] disk locality (and delta storage)

Nathaniel Smith <[EMAIL PROTECTED]> wrote:
> 
> So and rows are basically written to the file in the same order that 
> the INSERT statements are executed?

Right.  If there are no free pages in the database file (which is the usual 
case for Monotone, I expect) then new pages are allocated from the end of the 
file.  If the INSERT is small and will fit on an existing page, then no new 
page allocations are required and the data gets inserted in exactly the right 
spot.
But when inserting large blobs, as monotone does, you typically will require a 
least one new page and that page will come at the end.

> 
> Oh, and should I assume that individual row cells are kept together on 
> disk, even if they are (much) larger than a db block?  I assume so, 
> but just want to make sure...

If a table row is too big to fit on a page, then the excess spills onto a 
linked list of overflow pages.  SQLite tries to allocate the base page and the 
overflow pages near each other and in order.

> 
> > After you VACUUM, everything will be on disk in row order.  If
> 
> I assume this means "sorted by primary key"?  (And with tables in some 
> random order relative to each other, but I don't think I care about 
> that at all.)

Tables are always sorted by rowid - which is the same as the INTEGER PRIMARY 
KEY if you have one.

The "true" primary key for every SQLite table is the rowid.  If you specify a 
primary key that is not of type INTEGER, then what SQLite does really is create 
a UNIQUE index on that field.  There is still a rowid which is the "true" 
primary key in the sense that the table is stored in rowid order.

> 
> > you see a big performance improvement after VACUUMing, then the disk 
> > layout is perhaps an optimization worth looking into.  If however 
> > (as I suspect) your performance is similar after vacuuming, then 
> > changing the way information is added to the disk probably will not 
> > help, since after a VACUUM the information is pretty much optimally 
> > ordered for minimum seeking.
> 
> I think you left out the end of the sentence, "...assuming an in-order 
> access pattern".

You have 64 bits of rowid space.  You could assign rowids to deltas in clumps.  
Whenever you encounter a new file, assign it a block of (say) a billion rowids. 
 Each delta to that file goes into successive rowids.  Since the table is 
stored in rowid order, all delta for a particular file are therefore close to 
each other in the table.  This does not guarantee that the btree will be laid 
out on disk in order - it probably will not be unless you run a VACUUM - but it 
will help.  And I suspect it will help a lot.


> 
> Unless you just mean, during the btree traversals involved in each key 
> lookup?  Man, there's a whole 'nother part I don't know much about 
> :-).  I guess walking the btrees can obviously be another source of 
> disk latency; I'm not sure whether I should worry about this or not.

The fanout on tables is typically large - about 50 to 75.  Even more if you 
select a larger page size.  Fanout on indices is much smaller, 10 or 20, 
because index keys are typically larger than the integer rowid keys of tables.  
So to reduce your disk latency, you want to try to always search by rowid.

Something you should experiment with, by the way, is increasing the page size 
so that more records fit on one page and you get larger fanout.  Do you get 
better performance if you rebuild your database with say a 16K or 32K page size 
instead of the default 1K?

> If I do an INSERT of a row that has some indexes on it, where do those 
> index entries get written?  Next to the actual row data, at the end of 
> the file?  (Assuming there are no free blocks earlier in the file.) 
> And then at VACUUM time each index gets groups into one spot on disk?

Indices are stored in completely separate btrees from the tables.  An index has 
key only, and the key is the fields being indexed followed by a the rowid.  So 
to lookup a record by index, you first do a search of the index btree to find 
the entry with the matching fields.
Then you pull the rowid off of the end of the index entry and use that rowid to 
do a separate search in the table btree 

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 08:11:02PM +0100, Nemanja Corlija wrote:
> On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> > I see theh tcl now... is TCL piping into psql, or are there a set of raw
> > files you could post or send me?
> TCL generates files and then I'm piping those files to all databases
> except Firebird, which doesn't accept commands from stdin so I'm
> pointing isql to read that same file from disk.
> I'll email you row files if you want. Its less 750KB bziped.

Please do.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] Prepared statements

[Marian Olteanu]

On Tue, 7 Feb 2006, [EMAIL PROTECTED] wrote:
Thank you for your answer!
Thanks the rest of you that gave me an answer to my problem!


Marian Olteanu <[EMAIL PROTECTED]> wrote:

Is there a way in SQLite to use real prepared statements? Statements with
variables, that you fill after you compile the query and reuse then reuse?

I imagine something like:
prepared_statement ps = db.prepare( "select * from tbl where c = %q' );

for( int i = 0 ; i < 100 ; i++ )
{
ps.setValue(0,i);
ps.execute(callback_handler);
}



http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob


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

Re: [sqlite] SQLite to MySQL

[John Stanton]

Jim C. Nasby wrote:

On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:


Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)



FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to
MySQL, so it might me easier to migrate that direction...


If you are migrating to an enterprise level DBMS, PostgreSQL is a better 
choice than Mysql.  It is fully featured with all the qualities of DB2 
and Oracle but without the expense.


Note that you will need considerably more machine resources to run a 
"heavier" DBMS than Sqlite.  For example on our 166MHz, 256MB RS/6000 
PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series it 
is lightning fast.  Sqlite runs fast on the 166MHz machine.

Re: [sqlite] More benchmarks

[Nemanja Corlija]

On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> I see theh tcl now... is TCL piping into psql, or are there a set of raw
> files you could post or send me?
TCL generates files and then I'm piping those files to all databases
except Firebird, which doesn't accept commands from stdin so I'm
pointing isql to read that same file from disk.
I'll email you row files if you want. Its less 750KB bziped.

--
Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] More benchmarks

[Nemanja Corlija]

On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> AFAIK MySQL ships with a few different config files, so presumably
> choosing the appropriate one would be equivalent to what I provided for
> PostgreSQL.
Yes, and I installed it as a developer machine. That should be the
least performant one of the 3 available.

> BTW, has anyone looked at adding SQLite support to any of the DBT
> benchmarks? http://sourceforge.net/projects/osdldbt
I just glanced over it, but I think they concentrate on heavy load
testing while my focus is on single user environment for now.

--
Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] More benchmarks

[Jay Sprenkle]

> > You might want to put a legend on your results tables so it's clear
> > what the numbers represent. I assume these are times but I didn't see
> > anything so far that said.
> Its time in seconds.
> "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of
> MySQL it signifies usage of InnoDB engine.
> "nosync" is the opposite, of course synchronous=OFF; and MyISAM engine.

Thanks, :)

 I thought it was important to update the page so visitors stumbling
upon it were clear what it said.

Re: [sqlite] More benchmarks

[Clay Dowling]

Jim C. Nasby said:

> Finally, and luckily this applies to SQLite as well so this doesn't get
> too off topic :), PLEASE seek help/advice BEFORE spending a bunch of
> money on a big server! All too often I see people who spend a load of $$
> on equipment they didn't need or won't be able to utilize because they
> didn't do enough research before hand. Granted, I'm biased since I make
> money on consulting, but the amount of money I've seen people spend on
> needless hardware would often buy a pretty good chunk of my time.

Fear not, I'm pretty feircely conservative (i.e. cheap) when it comes to
acquiring hardware and software.  This comes of having a small budget.

Clay
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] More benchmarks

[Nemanja Corlija]

On 2/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> You might want to put a legend on your results tables so it's clear
> what the numbers represent. I assume these are times but I didn't see
> anything so far that said.
Its time in seconds.
"sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of
MySQL it signifies usage of InnoDB engine.
"nosync" is the opposite, of course synchronous=OFF; and MyISAM engine.

--
Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] More benchmarks

[Nemanja Corlija]

On 2/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Looks like I need to work on Test 6 some, huh?  Your suggestion
> that the servers are creating a temporary index to do the join
> was my first throught too.  I wonder if I should look into teaching
> that trick to SQLite.  Do you think you might add another test
> (6b?) that repeated the same join after indexing one of the join
> columns?  You do this at Test 13, but at that point the tables contain
> different data, I think.
I guess I'll just copy test 13 to where test 8 is right now. Though
those test numbers will likely create a lot of confusion that way.

> Other people have posted that the PostgreSQL tests are meaningless
> because the database is not tuned.  I am someone sympathetic to
> their complaints.  If you have time, I think it would be useful
> to show both a tuned and and untuned version for PostgreSQL.  It
> is also useful to know that PostgreSQL needs tuning in order to
> run well.
At first, I wanted to get by cheaply by not tuning anything. But yeah,
tuning each database would be a sensible thing to do after all.

> It is also interesting to note that PostgreSQL get significantly
> slower in Test 13 (join with an index) versus Test 6 (the same
> join without an index).  What is that about?  Firebird shows the
> same effect, but less dramatically.  Could it be a difference in
> the data that the tables hold at that point.  Test 6B proposed
> above really would be instructive here, I think.
I suspect that postgres and firebird just lost track of what's in the
database at that point and they could really use some ANALYZE at that
point. Just an assumption though.

> I also wonder if MySQL and Firebird would benefit from tuning.
> The MySQL people are rather laid back and probably will say
> something like "whatever" if asked.  The Firebird crowd, on the
> other hand, tend to be edgy and I suspect we will be hearing
> some pointed commentary from them in the near future.
I'd like to gather some input on this and then rerun test after that.
So if you have some tips for optimizing any database involved, please
speak up.

> Is there any chance of seeing additional information such as
> the amount of disk space used by the various databases or the
> amount of RAM consumed?  These values would be more difficult
> to arrive at, but will be helpful to many people, I think, if
> available.
I don't have much of those information ATM, but I will tell you that
SQLite3 used 3MB, while SQLite2 used 3.8MB for the duration of test 6
and they both used up all CPU.
Will try to gather information about database sizes next time. But I
don't know how can I reliably measure memory usage on windows.

--
Nemanja Corlija <[EMAIL PROTECTED]>

[sqlite] New User With Basic Install/Configure Questions

[Farrah, Yusuf P.]

 

I am a new user with some basic very questions.  My specific
SQLite questions are in regard to the installation/building of SQLite using
configure, release 3.3.2, the “Optional
Features” cited when “./configure –-help” is
invoke from the command line shell.  I have attached a file with the exact
feature syntax associated with the following questions. Does the “--enable-tempstore” feature put the
entire database file into RAM or only temporary tables?  Does the feature “—-enable-static” and the
feature “--enable-shared” if
both are enabled counteract each other and the last one invoked actually
implemented? To use these features normally the [ARG]=yes, what is [PKG] set
equal to?

 

Thanks,

 

 

__

Yusuf P. Farrah

SAIC

Tel: 732-933-3603

Email: [EMAIL PROTECTED]


 






`configure' configures this package to adapt to many kinds of systems.

Usage: ./configure [OPTION]... [VAR=VALUE]...

To assign environment variables (e.g., CC, CFLAGS...), specify them as
VAR=VALUE.  See below for descriptions of some of the useful variables.

Defaults for the options are specified in brackets.

Configuration:
  -h, --help  display this help and exit
  --help=shortdisplay options specific to this package
  --help=recursivedisplay the short help of all the included packages
  -V, --version   display version information and exit
  -q, --quiet, --silent   do not print `checking...' messages
  --cache-file=FILE   cache test results in FILE [disabled]
  -C, --config-cache  alias for `--cache-file=config.cache'
  -n, --no-create do not create output files
  --srcdir=DIRfind the sources in DIR [configure dir or `..']

Installation directories:
  --prefix=PREFIX install architecture-independent files in PREFIX
  [/usr/local]
  --exec-prefix=EPREFIX   install architecture-dependent files in EPREFIX
  [PREFIX]

By default, `make install' will install all the files in
`/usr/local/bin', `/usr/local/lib' etc.  You can specify
an installation prefix other than `/usr/local' using `--prefix',
for instance `--prefix=$HOME'.

For better control, use the options below.

Fine tuning of the installation directories:
  --bindir=DIR   user executables [EPREFIX/bin]
  --sbindir=DIR  system admin executables [EPREFIX/sbin]
  --libexecdir=DIR   program executables [EPREFIX/libexec]
  --datadir=DIR  read-only architecture-independent data [PREFIX/share]
  --sysconfdir=DIR   read-only single-machine data [PREFIX/etc]
  --sharedstatedir=DIR   modifiable architecture-independent data [PREFIX/com]
  --localstatedir=DIRmodifiable single-machine data [PREFIX/var]
  --libdir=DIR   object code libraries [EPREFIX/lib]
  --includedir=DIR   C header files [PREFIX/include]
  --oldincludedir=DIRC header files for non-gcc [/usr/include]
  --infodir=DIR  info documentation [PREFIX/info]
  --mandir=DIR   man documentation [PREFIX/man]

System types:
  --build=BUILD configure for building on BUILD [guessed]
  --host=HOST   cross-compile to build programs to run on HOST [BUILD]

Optional Features:
  --disable-FEATURE   do not include FEATURE (same as --enable-FEATURE=no)
  --enable-FEATURE[=ARG]  include FEATURE [ARG=yes]
  --enable-shared[=PKGS]
  build shared libraries [default=yes]
  --enable-static[=PKGS]
  build static libraries [default=yes]
  --enable-fast-install[=PKGS]
  optimize for fast installation [default=yes]
  --disable-libtool-lock  avoid locking (might break parallel builds)
  --enable-threadsafe   Support threadsafe operation
  --enable-cross-thread-connections   Allow connection sharing across threads
  --enable-releasemode   Support libtool link to release mode
  --enable-tempstore Use an in-ram database for temporary tables 
(never,no,yes,always)
  --disable-tcl   do not build TCL extension
  --enable-debug enable debugging & verbose explain

Optional Packages:
  --with-PACKAGE[=ARG]use PACKAGE [ARG=yes]
  --without-PACKAGE   do not use PACKAGE (same as --with-PACKAGE=no)
  --with-gnu-ld   assume the C compiler uses GNU ld [default=no]
  --with-pic  try to use only PIC/non-PIC objects [default=use
  both]
  --with-tags[=TAGS]
  include additional configurations [automatic]
  --with-hints=FILE   Read configuration options from FILE
  --with-tcl=DIR  directory containing tcl configuration (tclConfig.sh)

Some influential environment variables:
  CC  C compiler command
  CFLAGS  C compiler flags
  LDFLAGS linker flags, e.g. -L if you have libraries in a
  nonstandard directory 
  CPPFLAGSC/C++ preprocessor flags, e.g. -I if you have
  headers in a nonstandard 

Re: [sqlite] Prepared statements

[Dennis Cote]

Marian Olteanu wrote:

Is there a way in SQLite to use real prepared statements? Statements 
with variables, that you fill after you compile the query and reuse 
then reuse?


I imagine something like:
prepared_statement ps = db.prepare( "select * from tbl where c = %q' );

for( int i = 0 ; i < 100 ; i++ )
{
ps.setValue(0,i);
ps.execute(callback_handler);
}


Marian,

see http://www.sqlite.org/capi3.html in particular section 2.2, 
Executing SQL statements.


HTH
Dennis Cote

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 10:04:43AM -0500, Clay Dowling wrote:
> 
> Jim C. Nasby said:
> 
> > Well, that certainly won't help things... at a minimum, on your machine,
> > you should change the following:
> > shared_buffers=1
> > effective_cache_size=10
> >
> > The following should also help:
> > work_mem=1
> > vacuum_cost_delay=50
> > autovacuum=on
> > autovacuum_vacuum_scale_factor=0.2
> 
> Jim,
> 
> I just want to say thanks for providing these tuning parameters.  I not
> currently using your product, but I hope to in the near future for a
> larger scale version of my own product.  Performance tuning is something
> of an arcane art from my perspective, so getting any help on it is highly
> desirable.

You're welcome. Just to clarify, PostgreSQL isn't really a Pervasive
product; we just have a bundled installer and offer support and
services, but it's all the same as the community code. When it comes to
tuning, http://www.powerpostgresql.com/PerfList and
http://www.revsys.com/writings/postgresql-performance.html are a couple
places to start looking.

Finally, and luckily this applies to SQLite as well so this doesn't get
too off topic :), PLEASE seek help/advice BEFORE spending a bunch of
money on a big server! All too often I see people who spend a load of $$
on equipment they didn't need or won't be able to utilize because they
didn't do enough research before hand. Granted, I'm biased since I make
money on consulting, but the amount of money I've seen people spend on
needless hardware would often buy a pretty good chunk of my time.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

[sqlite] Re: Prepared statements

[Igor Tandetnik]

Marian Olteanu wrote:

Is there a way in SQLite to use real prepared statements? Statements
with variables, that you fill after you compile the query and reuse
then reuse? 


See sqlite3_prepare, sqlite3_bind*, sqlite3_step, sqlite3_reset

Igor Tandetnik

Re: [sqlite] Prepared statements

[Gerhard Häring]

Marian Olteanu wrote:
Is there a way in SQLite to use real prepared statements? Statements 
with variables, that you fill after you compile the query and reuse then 
reuse? [...]


SQLite 3 introduced prepared statements.

Read http://www.sqlite.org/capi3.html for enlightenment.

-- Gerhard

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote:
> Hrm, that's rather odd. What does top show when it's running through
> psql? Are the test scripts available for download? I'll try this on my
> machine as well...

I see theh tcl now... is TCL piping into psql, or are there a set of raw
files you could post or send me? If you're piping from TCL, I'd be
curious to see what the difference is if you run this manually. For
these large data sets I also think it's not very reflective of the
database to send the result set all the way back through the client,
since that's not very representative of the real world. In the case of
PostgreSQL, a good alternative would be

SELECT count(*) FROM (
SELECT t1.a  FROM ...
) a
;

But I'm not sure if all the other databases support that.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 08:07:53AM -0500, [EMAIL PROTECTED] wrote:
> It is also interesting to note that PostgreSQL get significantly
> slower in Test 13 (join with an index) versus Test 6 (the same
> join without an index).  What is that about?  Firebird shows the
> same effect, but less dramatically.  Could it be a difference in
> the data that the tables hold at that point.  Test 6B proposed
> above really would be instructive here, I think.

Well, I'm a bit skeptical as to the usefulness of that test, since if
I'm reading things correctly it's essentially a cartesian product. In
any case, it's very likely that the lack of analysis and default
parameters resulted in a bad query plan. The output of EXPLAIN ANALYZE
would be most instructive.

> I also wonder if MySQL and Firebird would benefit from tuning.
> The MySQL people are rather laid back and probably will say
> something like "whatever" if asked.  The Firebird crowd, on the
> other hand, tend to be edgy and I suspect we will be hearing 
> some pointed commentary from them in the near future.

FWIW, I wouldn't really consider the changes I suggested 'tuning', as 
they're rather off-the-cuff based strictly on my experience and limited
knowledge as to the workload. Personally, I'd prefer if PostgreSQL would
at least provide multiple sample configs, but c'est la vie.

AFAIK MySQL ships with a few different config files, so presumably 
choosing the appropriate one would be equivalent to what I provided for
PostgreSQL.

BTW, has anyone looked at adding SQLite support to any of the DBT
benchmarks? http://sourceforge.net/projects/osdldbt
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

[sqlite] Prepared statements

[Marian Olteanu]

Is there a way in SQLite to use real prepared statements? Statements with 
variables, that you fill after you compile the query and reuse then reuse?


I imagine something like:
prepared_statement ps = db.prepare( "select * from tbl where c = %q' );

for( int i = 0 ; i < 100 ; i++ )
{
ps.setValue(0,i);
ps.execute(callback_handler);
}

[sqlite] RE: [RBL] Re[2]: [sqlite] R: [sqlite] Snapshot database creation performance

[Steve O'Hara]

Hi Teg,

Presumably you have a transaction in place around the whole of your
inserts and that you have the PRAGMA synchronous = OFF; set.

Have you looked at perhaps not creating the database on the server, but
merely creating the INSERT statements in one big file that you compress
and send down to the client, who then decompresses and runs the inserts?
You could even abbreviate the insert statements but I've always found
(possibly because the indices don't compress well) that compressing the
source of a database gets you a much smaller payload than compressing
the finished database.

Steve


-Original Message-
From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
org] On Behalf Of Teg
Sent: 07 February 2006 15:40
To: Andrew Piskorski
Subject: [RBL] Re[2]: [sqlite] R: [sqlite] Snapshot database creation
performance

Hello Andrew,

My purpose is primarily disk storage savings, the data's mainly text
so it's highly compressible. 500K on disk chunks of data decompress
out to about 8 megabytes of text. What compression scheme do they use?
I might consider trading some disk space for faster
compression/decompression.

C

Tuesday, February 7, 2006, 10:26:02 AM, you wrote:

AP> On Tue, Feb 07, 2006 at 08:51:43AM -0500, Teg wrote:

>> My application uses compressed data (gzip) but, the tradeoff to small
>> data files is exceptionally heavy CPU usage when the data is
>> decompressed/compressed.

AP> Incidentally, the MonetDB folks have done research on that sort of
AP> thing.  In their most recent project, "X100", they keep the data
AP> compressed both on disk AND in main memory, and decompress it only
in
AP> the CPU cache when actually manipulating values.

AP> They do that not primarily to save disk space, but to reduce the
AP> amount of memory bandwith needed.  Apparently in some cases it's a
big
AP> speed-up, and shifts the query from being memory I/O bound to CPU
AP> bound.  Of course, in order for that to work they have to use very
AP> lightweight compression/decompression algorithms.  Gzip gives much
AP> better compression, but in comparison it's extremely slow.

AP> Probably not immediately useful, but it seems like interesting
stuff:

AP>   http://monetdb.cwi.nl/
AP>   http://homepages.cwi.nl/~mk/MonetDB/
AP>   http://sourceforge.net/projects/monetdb/
AP>   http://homepages.cwi.nl/~boncz/

AP>   "MonetDB/X100 - A DBMS In The CPU Cache"
AP>   by Marcin Zukowski, Peter Boncz, Niels Nes, Sandor Himan
AP>   ftp://ftp.research.microsoft.com/pub/debull/A05june/issue1.htm

AP> Btw, apparently the current stable version of MonetDB is open source
AP> but they haven't decided whether the X100 work will be or not.

AP> Googling just now, there seems to have been a fair amount of
research
AP> and commercialization of this sort of stuff lately, e.g.:

AP>   http://db.csail.mit.edu/projects/cstore/




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

[sqlite] Versioning in SQL database?

[Paul Tomblin]

I am putting together something that will act like a Wiki for structured
data (in this case, airport and navigation aid data like id, location,
runways, etc).  I currently store the data in an SQL databasee, but only
the "current" version.  I want to allow people to edit that data, but that
means being able to compare versions, roll back erroneous edits, get what
the database looked like before a particular editor came along, etc.  Is
there anything written on this topic?  Has anybody ever tried it before?

-- 
Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/
Ahhh, the permie offer. The "Please sign up with us clueless fsckwits
so you can spend all your time digging us out at a pittance" offer.
  -- Dan Holdsworth

Re: [sqlite] More benchmarks

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 10:08:23AM +0100, Nemanja Corlija wrote:
> On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> > On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote:
> > > > Well, that certainly won't help things... at a minimum, on your machine,
> > > > you should change the following:
> > > > shared_buffers=1
> > > > effective_cache_size=10
> > > >
> > > > The following should also help:
> > > > work_mem=1
> > > > vacuum_cost_delay=50
> > > > autovacuum=on
> > > > autovacuum_vacuum_scale_factor=0.2
> > > Sure, I could do that. But then I'd also need to tune all other
> > > databases to make things fair and that's not really what I intended to
> > > do here. I want to keep things as "out of the box" as possible.
> >
> > Then you should just drop PostgreSQL from the tests, because they're not
> > doing anyone any good. It's pretty well known that the default
> > postgresql.conf is meant to allow for bringing the database up on a
> > machine with very minimal hardware. It's the equivalent to using MySQL's
> > minimum configuration file.
> 
> OK, I've changed above settings but now I get even worse performance.
> 265.223 seconds.
> File I've edited is C:\Program Files\PostgreSQL\8.1\data\postgresql.conf
> AFAICT that's the one. Then I've restarted postgres. I guess that
> should load new settings?
> Then I ran VACUUM ANALYZE t2;
> 
> Hmmm, now I ran that same script from pgAdmin and it completed in 5 seconds.
> I guess its reasonable to assume that psql is actually the bottleneck
> here. I tried redirecting to file but that was a minute ago and it's
> still running. Any ideas?

Hrm, that's rather odd. What does top show when it's running through
psql? Are the test scripts available for download? I'll try this on my
machine as well...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re[2]: [sqlite] R: [sqlite] Snapshot database creation performance

[Teg]

Hello Andrew,

My purpose is primarily disk storage savings, the data's mainly text
so it's highly compressible. 500K on disk chunks of data decompress
out to about 8 megabytes of text. What compression scheme do they use?
I might consider trading some disk space for faster
compression/decompression.

C

Tuesday, February 7, 2006, 10:26:02 AM, you wrote:

AP> On Tue, Feb 07, 2006 at 08:51:43AM -0500, Teg wrote:

>> My application uses compressed data (gzip) but, the tradeoff to small
>> data files is exceptionally heavy CPU usage when the data is
>> decompressed/compressed.

AP> Incidentally, the MonetDB folks have done research on that sort of
AP> thing.  In their most recent project, "X100", they keep the data
AP> compressed both on disk AND in main memory, and decompress it only in
AP> the CPU cache when actually manipulating values.

AP> They do that not primarily to save disk space, but to reduce the
AP> amount of memory bandwith needed.  Apparently in some cases it's a big
AP> speed-up, and shifts the query from being memory I/O bound to CPU
AP> bound.  Of course, in order for that to work they have to use very
AP> lightweight compression/decompression algorithms.  Gzip gives much
AP> better compression, but in comparison it's extremely slow.

AP> Probably not immediately useful, but it seems like interesting stuff:

AP>   http://monetdb.cwi.nl/
AP>   http://homepages.cwi.nl/~mk/MonetDB/
AP>   http://sourceforge.net/projects/monetdb/
AP>   http://homepages.cwi.nl/~boncz/

AP>   "MonetDB/X100 - A DBMS In The CPU Cache"
AP>   by Marcin Zukowski, Peter Boncz, Niels Nes, Sandor Himan
AP>   ftp://ftp.research.microsoft.com/pub/debull/A05june/issue1.htm

AP> Btw, apparently the current stable version of MonetDB is open source
AP> but they haven't decided whether the X100 work will be or not.

AP> Googling just now, there seems to have been a fair amount of research
AP> and commercialization of this sort of stuff lately, e.g.:

AP>   http://db.csail.mit.edu/projects/cstore/




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

Re: [sqlite] More benchmarks

[Jay Sprenkle]

On 2/6/06, Nemanja Corlija <[EMAIL PROTECTED]> wrote:
> I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL.
>
> Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison

You might want to put a legend on your results tables so it's clear
what the numbers represent. I assume these are times but I didn't see
anything so far that said.

Re: [sqlite] R: [sqlite] Snapshot database creation performance

[Andrew Piskorski]

On Tue, Feb 07, 2006 at 08:51:43AM -0500, Teg wrote:

> My application uses compressed data (gzip) but, the tradeoff to small
> data files is exceptionally heavy CPU usage when the data is
> decompressed/compressed.

Incidentally, the MonetDB folks have done research on that sort of
thing.  In their most recent project, "X100", they keep the data
compressed both on disk AND in main memory, and decompress it only in
the CPU cache when actually manipulating values.

They do that not primarily to save disk space, but to reduce the
amount of memory bandwith needed.  Apparently in some cases it's a big
speed-up, and shifts the query from being memory I/O bound to CPU
bound.  Of course, in order for that to work they have to use very
lightweight compression/decompression algorithms.  Gzip gives much
better compression, but in comparison it's extremely slow.

Probably not immediately useful, but it seems like interesting stuff:

  http://monetdb.cwi.nl/
  http://homepages.cwi.nl/~mk/MonetDB/
  http://sourceforge.net/projects/monetdb/
  http://homepages.cwi.nl/~boncz/

  "MonetDB/X100 - A DBMS In The CPU Cache"
  by Marcin Zukowski, Peter Boncz, Niels Nes, Sandor Himan
  ftp://ftp.research.microsoft.com/pub/debull/A05june/issue1.htm

Btw, apparently the current stable version of MonetDB is open source
but they haven't decided whether the X100 work will be or not.

Googling just now, there seems to have been a fair amount of research
and commercialization of this sort of stuff lately, e.g.:

  http://db.csail.mit.edu/projects/cstore/

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] disk locality (and delta storage)

[drh]

Nathaniel Smith <[EMAIL PROTECTED]> wrote:
> 
> So and rows are basically written to the file in the same order
> that the INSERT statements are executed?

Right.  If there are no free pages in the database file (which
is the usual case for Monotone, I expect) then new pages are
allocated from the end of the file.  If the INSERT is small and
will fit on an existing page, then no new page allocations are
required and the data gets inserted in exactly the right spot.
But when inserting large blobs, as monotone does, you typically
will require a least one new page and that page will come at the
end.

> 
> Oh, and should I assume that individual row cells are kept together on
> disk, even if they are (much) larger than a db block?  I assume so,
> but just want to make sure...

If a table row is too big to fit on a page, then the excess spills
onto a linked list of overflow pages.  SQLite tries to allocate
the base page and the overflow pages near each other and in order.

> 
> > After you VACUUM, everything will be on disk in row order.  If
> 
> I assume this means "sorted by primary key"?  (And with tables in some
> random order relative to each other, but I don't think I care about
> that at all.)

Tables are always sorted by rowid - which is the same as the INTEGER
PRIMARY KEY if you have one.

The "true" primary key for every SQLite table is the rowid.  If you
specify a primary key that is not of type INTEGER, then what SQLite
does really is create a UNIQUE index on that field.  There is still
a rowid which is the "true" primary key in the sense that the table
is stored in rowid order.

> 
> > you see a big performance improvement after VACUUMing, then the
> > disk layout is perhaps an optimization worth looking into.  If
> > however (as I suspect) your performance is similar after vacuuming,
> > then changing the way information is added to the disk probably
> > will not help, since after a VACUUM the information is pretty much
> > optimally ordered for minimum seeking.
> 
> I think you left out the end of the sentence, "...assuming an in-order
> access pattern".

You have 64 bits of rowid space.  You could assign rowids to deltas
in clumps.  Whenever you encounter a new file, assign it a block
of (say) a billion rowids.  Each delta to that file goes into
successive rowids.  Since the table is stored in rowid order, all
delta for a particular file are therefore close to each other in
the table.  This does not guarantee that the btree will be laid out
on disk in order - it probably will not be unless you run a VACUUM -
but it will help.  And I suspect it will help a lot.


> 
> Unless you just mean, during the btree traversals involved in each key
> lookup?  Man, there's a whole 'nother part I don't know much about
> :-).  I guess walking the btrees can obviously be another source of
> disk latency; I'm not sure whether I should worry about this or not.

The fanout on tables is typically large - about 50 to 75.  Even more
if you select a larger page size.  Fanout on indices is much smaller,
10 or 20, because index keys are typically larger than the integer
rowid keys of tables.  So to reduce your disk latency, you want to
try to always search by rowid.

Something you should experiment with, by the way, is increasing
the page size so that more records fit on one page and you get
larger fanout.  Do you get better performance if you rebuild 
your database with say a 16K or 32K page size instead of the
default 1K?

> If I do an INSERT of a row that has some indexes on it, where do those
> index entries get written?  Next to the actual row data, at the end of
> the file?  (Assuming there are no free blocks earlier in the file.)
> And then at VACUUM time each index gets groups into one spot on disk?

Indices are stored in completely separate btrees from the tables.  An
index has key only, and the key is the fields being indexed followed
by a the rowid.  So to lookup a record by index, you first do a
search of the index btree to find the entry with the matching fields.
Then you pull the rowid off of the end of the index entry and use
that rowid to do a separate search in the table btree to get your
actual data.  So an index search actually does two binary lookups - 
one on the index and another on the table.

> 
> I was actually thinking more about the cost of looking up many items
> from a table.  Here, unfortunately, our current access pattern is
> quite pessimal.  The current schema is:
> 
> CREATE TABLE files (id primary key, data not null); 
> 
> 'id' is the SHA1 hash of the file; 'data' is a compressed raw file.
> 
> CREATE TABLE file_deltas
>   (id not null, base not null, delta not null,
>unique(id, base)
>   );
> 
> 'id' is the SHA1 of the file this delta lets us construct, 'base' is
> the SHA1 of the file that the delta is against, and 'delta' is the
> compressed xdelta.
> 
> So, when we traverse delta chains, we go wandering all over this table
> indexing by the SHA1 of intermediate versions.  Our 

Re: [sqlite] New benchmark comparisons

[Robert Simpson]

- Original Message - 
From: "Brandon, Nicholas (UK)" <[EMAIL PROTECTED]>



Just had a quick look and noticed a peculiarity. SQLite seems to do
pretty well across the board except in the graph for "INSERT 1 Row
Test (implicit transaction)". It seemly does well except when using
the SD card. Then I noticed your labelling is not consistent
across all the pictures. Is that a mistake or is it what you had
expected?

For me it would be easier for my brain to compare if you had them in
the same label order >across the pictures (i.e. SQLite (M), SQLMobile
(M), SQLite (C) ...)


I had originally arranged them by storage medium best to worst, but I agree 
it was somewhat confusing to have the order changed midway through, so I 
changed it back.


The one thing that really impressed me was Join Test #2, which joined 3 
tables and returned a sum on a column in the 3rd table.  The *only* database 
that was able to even get near SQLite was MS Sql Server, which actually beat 
SQLite soundly -- returning a full 500ms sooner than SQLite did.  All the 
other database engines I tested completely tanked that test.


Robert

Re: [sqlite] New benchmark comparisons

[Robert Simpson]

- Original Message - 
From: "Denis Sbragion" <[EMAIL PROTECTED]>




Hello Robert,

At 06.36 06/02/2006, you wrote:
...

Also coming up next week are comparisons vs. Sql Server Mobile, which is
where SQLite is really racking up some wins recently.


interesting but, be careful! Last time I checked benchmarking was 
explicitely forbidden by the SQL Server end user license. Probably the 
same apply to SQL Server Mobile.
BTW we're using sqlite under Windows CE since at least 3 years, 
and we have no intention at all to get back from there. I've yet to find a 
real situation where sqlite is the bottleneck. This is expecially true for 
our single user/single thread/no locking typical application. I will never 
thank enough DRH and the other sqlite developers and contributors for 
their invaluable work.


I had originally blanked out the Sql Mobile scores, but then I found other 
folks on the net posting benchmarks and even found an MS employee's blog 
referencing some scores someone posted (and even posting a link).  After 
that I decided what the hell.


Robert

[sqlite] Snapshot database creation performance

[Clinco, Michele]

Hallo!

I'm not using compressed data while SQLite is creating the database.
I create it in the standard way, and AFTER it is created and closed I compress 
the just generated file and I transfer it.
The compression is used only for performance reasons during the file transfer.

I cannot use a memory database because it is lost when it is closed.

Your suggestion to rewrite the file access functions is surely nice, but a bit 
too complex.

bye
-Messaggio originale-
Da: Teg [mailto:[EMAIL PROTECTED] 
Inviato: martedì, 7. febbraio 2006 14:52
A: Clinco, Michele
Oggetto: Re: [sqlite] R: [sqlite] Snapshot database creation performance

Hello Michele,

Perhaps replacing the "os_win.c" (or whatever your OS) with functions
the simulate disk IO through your compressed stream functions. The
biggest problem I see is random access. Typically there is no quick
seeking within compressed data, you just have to "Read" to the point
you want and then do this repeatedly every time you seek. Why can't
you use a plain "memory" database?

My application uses compressed data (gzip) but, the tradeoff to small
data files is exceptionally heavy CPU usage when the data is
decompressed/compressed. The datafiles are sized so, seeking isn't too
painful.

C

Tuesday, February 7, 2006, 8:09:47 AM, you wrote:

CM> You understood perfectly.
CM> The ramdisk is not the goal, but just a mean to obtain the
CM> 'best performance' possible goal.

CM> I don't need the ramdisk at all, just a 'all in memory processing'.
CM> The solution to this problem should help many users because I
CM> think we are many that use a SQLite database to store data in
CM> offline mode.

CM> Bye, Michele
CM> -Messaggio originale-
CM> Da: Andrew Piskorski [mailto:[EMAIL PROTECTED] 
CM> Inviato: martedì, 7. febbraio 2006 13:54
CM> A: sqlite-users@sqlite.org
CM> Oggetto: Re: [sqlite] Snapshot database creation performance

CM> On Tue, Feb 07, 2006 at 07:43:44AM -0500, Andrew Piskorski wrote:
>> On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote:

>> > My program is written in .Net and the compression routines I'm using are
>> > stream based, so I need to create a memory stream from the internal
>> > buffers that can be used by the compression routine...
>> 
>> Do you mean you wish to directly access SQLite's in-memory data
>> structures, rather than using a SQL query to get the data?  Why?

CM> Oh, never mind, I wrote too soon.  I realize now that you want an
CM> in-memory representation of a SQLite database file, which you than
CM> gzip or whatever, all still in memory, and then transfer over the
CM> network to whomever wants that file.  And you want to know if there's
CM> some way to do that which offers more programatic control than the
CM> ramdisk filesystem you're using now.

CM> Essentially, you want a way to dynamically control the size of the RAM
CM> disk.  But it sounds like you don't necessarily need ALL the
CM> facilities of a normal file-system, so you're wondering if perhaps you
CM> could implement something more purpose-specific yourself rather than
CM> using that ramdisk driver.

CM> Interesting, but I don't know the answer.




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

[sqlite] Snapshot database creation performance

[Clinco, Michele]

I already tried. I could see no particular effect or, at least, not so dramatic.

-Messaggio originale-
Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Inviato: martedì, 7. febbraio 2006 14:22
A: sqlite-users@sqlite.org
Oggetto: Re: [sqlite] Snapshot database creation performance

"Clinco, Michele" <[EMAIL PROTECTED]> wrote:
> 
> This operation is quite heavy and takes several seconds (20 sec on my
> laptop, generating a 1700k data file)
> 
> Since the file is absolutely temporary, I installed a ramdisk driver and
> I tried to create the file in this driver instead that in the hard disk.
> 
> The difference is really astonishing: 0.9 seconds instead of >20. This
> means that I don' have to worry about performance any more.
> 

Maybe if you set "PRAGMA synchronous=OFF" the performance would
be nearly as good when going to disk as it is when using a RAM disk.

With "PRAGMA synchronous=OFF", your database can become corrupt
if you take an OS crash or power failure in the middle of an update,
but I gather you really don't care about that.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] R: [sqlite] Snapshot database creation performance

[Teg]

Hello Michele,

Perhaps replacing the "os_win.c" (or whatever your OS) with functions
the simulate disk IO through your compressed stream functions. The
biggest problem I see is random access. Typically there is no quick
seeking within compressed data, you just have to "Read" to the point
you want and then do this repeatedly every time you seek. Why can't
you use a plain "memory" database?

My application uses compressed data (gzip) but, the tradeoff to small
data files is exceptionally heavy CPU usage when the data is
decompressed/compressed. The datafiles are sized so, seeking isn't too
painful.

C

Tuesday, February 7, 2006, 8:09:47 AM, you wrote:

CM> You understood perfectly.
CM> The ramdisk is not the goal, but just a mean to obtain the
CM> 'best performance' possible goal.

CM> I don't need the ramdisk at all, just a 'all in memory processing'.
CM> The solution to this problem should help many users because I
CM> think we are many that use a SQLite database to store data in
CM> offline mode.

CM> Bye, Michele
CM> -Messaggio originale-
CM> Da: Andrew Piskorski [mailto:[EMAIL PROTECTED] 
CM> Inviato: martedì, 7. febbraio 2006 13:54
CM> A: sqlite-users@sqlite.org
CM> Oggetto: Re: [sqlite] Snapshot database creation performance

CM> On Tue, Feb 07, 2006 at 07:43:44AM -0500, Andrew Piskorski wrote:
>> On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote:

>> > My program is written in .Net and the compression routines I'm using are
>> > stream based, so I need to create a memory stream from the internal
>> > buffers that can be used by the compression routine...
>> 
>> Do you mean you wish to directly access SQLite's in-memory data
>> structures, rather than using a SQL query to get the data?  Why?

CM> Oh, never mind, I wrote too soon.  I realize now that you want an
CM> in-memory representation of a SQLite database file, which you than
CM> gzip or whatever, all still in memory, and then transfer over the
CM> network to whomever wants that file.  And you want to know if there's
CM> some way to do that which offers more programatic control than the
CM> ramdisk filesystem you're using now.

CM> Essentially, you want a way to dynamically control the size of the RAM
CM> disk.  But it sounds like you don't necessarily need ALL the
CM> facilities of a normal file-system, so you're wondering if perhaps you
CM> could implement something more purpose-specific yourself rather than
CM> using that ramdisk driver.

CM> Interesting, but I don't know the answer.




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

Re: [sqlite] More benchmarks

[Clay Dowling]

Jim C. Nasby said:

> Well, that certainly won't help things... at a minimum, on your machine,
> you should change the following:
> shared_buffers=1
> effective_cache_size=10
>
> The following should also help:
> work_mem=1
> vacuum_cost_delay=50
> autovacuum=on
> autovacuum_vacuum_scale_factor=0.2

Jim,

I just want to say thanks for providing these tuning parameters.  I not
currently using your product, but I hope to in the near future for a
larger scale version of my own product.  Performance tuning is something
of an arcane art from my perspective, so getting any help on it is highly
desirable.

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] disk locality

[Nathaniel Smith]

On Wed, Feb 01, 2006 at 08:56:37PM -0800, Joe Wilson wrote:
> Another question... Does Monotone still Base64 encode all its data before 
> putting it into blobs?
> If so, using raw binary SQLite blobs would likely give Monotone a 33% speedup 
> and smaller
> database.

It does, actually, but that's going away as soon as we get around to
finishing reviewing/merging the relevant branch...

-- Nathaniel

-- 
- Don't let your informants burn anything.
- Don't grow old.
- Be good grad students.
  -- advice of Murray B. Emeneau on the occasion of his 100th birthday

Re: [sqlite] disk locality (and delta storage)

[Nathaniel Smith]

Thanks for the helpful reply.  Sorry I've taken so long to get back to
this; I've had some hardware trouble and am only catching up on email
now...

On Wed, Feb 01, 2006 at 07:27:06AM -0500, [EMAIL PROTECTED] wrote:
> Nathaniel Smith <[EMAIL PROTECTED]> wrote:
> > I was wondering if there were any docs or explanations available on
> > how SQLite decides to lay out data on disk.
> 
> Free pages in the middle of the file are filled first.  Some effort
> is made to uses pages that are close together for related information.
> In mototone, where you seldom if ever delete anything, you probably
> never have any free pages, so new information is always added to the
> end of the file.

I'm going to ask a bunch of finicky boring questions to make sure I'm
understanding :-).

So and rows are basically written to the file in the same order
that the INSERT statements are executed?

Oh, and should I assume that individual row cells are kept together on
disk, even if they are (much) larger than a db block?  I assume so,
but just want to make sure...

> After you VACUUM, everything will be on disk in row order.  If

I assume this means "sorted by primary key"?  (And with tables in some
random order relative to each other, but I don't think I care about
that at all.)

> you see a big performance improvement after VACUUMing, then the
> disk layout is perhaps an optimization worth looking into.  If
> however (as I suspect) your performance is similar after vacuuming,
> then changing the way information is added to the disk probably
> will not help, since after a VACUUM the information is pretty much
> optimally ordered for minimum seeking.

I think you left out the end of the sentence, "...assuming an in-order
access pattern".

Unless you just mean, during the btree traversals involved in each key
lookup?  Man, there's a whole 'nother part I don't know much about
:-).  I guess walking the btrees can obviously be another source of
disk latency; I'm not sure whether I should worry about this or not.
If I do an INSERT of a row that has some indexes on it, where do those
index entries get written?  Next to the actual row data, at the end of
the file?  (Assuming there are no free blocks earlier in the file.)
And then at VACUUM time each index gets groups into one spot on disk?

I was actually thinking more about the cost of looking up many items
from a table.  Here, unfortunately, our current access pattern is
quite pessimal.  The current schema is:

CREATE TABLE files (id primary key, data not null); 

'id' is the SHA1 hash of the file; 'data' is a compressed raw file.

CREATE TABLE file_deltas
  (id not null, base not null, delta not null,
   unique(id, base)
  );

'id' is the SHA1 of the file this delta lets us construct, 'base' is
the SHA1 of the file that the delta is against, and 'delta' is the
compressed xdelta.

So, when we traverse delta chains, we go wandering all over this table
indexing by the SHA1 of intermediate versions.  Our access isn't just
random, it's _cryptographically strongly_ random! :-)

So, we've been throwing around ways to overhaul this stuff.  Obviously
sqlite is not going to be able to improve on the current situation
without some help from us.

> Let me propose a radical solution:  I've been experimenting with adding
> a VCS component to CVSTrac (http://www.cvstrac.org/) to replace CVS and
> thus provide a complete project management system in a standalone CGI
> program.  My latest thinking on this (backed up by experiment) is to

Entering the VCS game?  Good luck!  It's an interesting (and
surprisingly deep) field.

(Total tangent: I basically know how to make monotone work over a CGI
transport; it's some work, but doable, just no-one's picked it up yet.
It might be worth considering such a solution before trying to build a
new system from scratch.  The basic trade-off would be a CGI script
plus a statically linked binary instead of just a CGI script, but on
the other hand, building Yet Another VCS from scratch is a significant
undertaking.  The detailed trade-off would of course be more
complicated :-).  Something to throw out there in case it leads to
discussion...)

> avoid storing long series of xdeltas.  Each file version is instead stored
> as a baseline and a single xdelta.  The manifest stores two UUIDs instead
> of one.  That way, you can always load a particular file version with
> at most two lookups.  As a file evolves, the baseline version stays the
> same and the xdelta changes from one version to the next.  When the size
> of the xdelta reachs some fraction of the baseline file size, create a
> new baseline.  Experimentally, I have found it works well to create a
> new baseline when the xdelta becomes 15% of the size of the baseline.

Ah, indeed, I'd forgotten about this technique.  Thanks for bringing
it up!  It inspired me to go and sketch out some notes on different
options:
  http://venge.net/monotone/wiki/DeltaStorageStrategies

There are a few different things we're thinking 

Re: [sqlite] Snapshot database creation performance

[drh]

"Clinco, Michele" <[EMAIL PROTECTED]> wrote:
> 
> This operation is quite heavy and takes several seconds (20 sec on my
> laptop, generating a 1700k data file)
> 
> Since the file is absolutely temporary, I installed a ramdisk driver and
> I tried to create the file in this driver instead that in the hard disk.
> 
> The difference is really astonishing: 0.9 seconds instead of >20. This
> means that I don' have to worry about performance any more.
> 

Maybe if you set "PRAGMA synchronous=OFF" the performance would
be nearly as good when going to disk as it is when using a RAM disk.

With "PRAGMA synchronous=OFF", your database can become corrupt
if you take an OS crash or power failure in the middle of an update,
but I gather you really don't care about that.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] aynchronous loading

[chetana bhargav]

Hi,
   
  Does sqlite provides asynchronous loading of data. Basically if I have 
something around 3000 records and want to do some query, instead of returning 
the result in one single query is it possible for me to relinquish the control 
to other apps so that I wont get a time out error. and get the data in sets.
   
  Or any other possible way to speed up the result.
   
   
  -Chetan.


-
Relax. Yahoo! Mail virus scanning helps detect nasty viruses!

[sqlite] R: [sqlite] Snapshot database creation performance

[Clinco, Michele]

You understood perfectly.
The ramdisk is not the goal, but just a mean to obtain the 'best performance' 
possible goal.

I don't need the ramdisk at all, just a 'all in memory processing'.
The solution to this problem should help many users because I think we are many 
that use a SQLite database to store data in offline mode.

Bye, Michele
-Messaggio originale-
Da: Andrew Piskorski [mailto:[EMAIL PROTECTED] 
Inviato: martedì, 7. febbraio 2006 13:54
A: sqlite-users@sqlite.org
Oggetto: Re: [sqlite] Snapshot database creation performance

On Tue, Feb 07, 2006 at 07:43:44AM -0500, Andrew Piskorski wrote:
> On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote:

> > My program is written in .Net and the compression routines I'm using are
> > stream based, so I need to create a memory stream from the internal
> > buffers that can be used by the compression routine...
> 
> Do you mean you wish to directly access SQLite's in-memory data
> structures, rather than using a SQL query to get the data?  Why?

Oh, never mind, I wrote too soon.  I realize now that you want an
in-memory representation of a SQLite database file, which you than
gzip or whatever, all still in memory, and then transfer over the
network to whomever wants that file.  And you want to know if there's
some way to do that which offers more programatic control than the
ramdisk filesystem you're using now.

Essentially, you want a way to dynamically control the size of the RAM
disk.  But it sounds like you don't necessarily need ALL the
facilities of a normal file-system, so you're wondering if perhaps you
could implement something more purpose-specific yourself rather than
using that ramdisk driver.

Interesting, but I don't know the answer.

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] More benchmarks

[drh]

Nemanja Corlija <[EMAIL PROTECTED]> wrote:
> I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdS=
> QL.
> 
> Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison
> 

Thanks for your hard work, Nemanja!  This is useful information.

Looks like I need to work on Test 6 some, huh?  Your suggestion
that the servers are creating a temporary index to do the join
was my first throught too.  I wonder if I should look into teaching
that trick to SQLite.  Do you think you might add another test
(6b?) that repeated the same join after indexing one of the join
columns?  You do this at Test 13, but at that point the tables contain
different data, I think.

Other people have posted that the PostgreSQL tests are meaningless
because the database is not tuned.  I am someone sympathetic to
their complaints.  If you have time, I think it would be useful
to show both a tuned and and untuned version for PostgreSQL.  It
is also useful to know that PostgreSQL needs tuning in order to
run well.

It is also interesting to note that PostgreSQL get significantly
slower in Test 13 (join with an index) versus Test 6 (the same
join without an index).  What is that about?  Firebird shows the
same effect, but less dramatically.  Could it be a difference in
the data that the tables hold at that point.  Test 6B proposed
above really would be instructive here, I think.

I also wonder if MySQL and Firebird would benefit from tuning.
The MySQL people are rather laid back and probably will say
something like "whatever" if asked.  The Firebird crowd, on the
other hand, tend to be edgy and I suspect we will be hearing 
some pointed commentary from them in the near future.

Is there any chance of seeing additional information such as
the amount of disk space used by the various databases or the
amount of RAM consumed?  These values would be more difficult
to arrive at, but will be helpful to many people, I think, if
available.

Thanks again for your hard work in preparing these benchmarks!
Even if you do nothing else with them, your work so far has been
a big help.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Snapshot database creation performance

[Andrew Piskorski]

On Tue, Feb 07, 2006 at 07:43:44AM -0500, Andrew Piskorski wrote:
> On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote:

> > My program is written in .Net and the compression routines I'm using are
> > stream based, so I need to create a memory stream from the internal
> > buffers that can be used by the compression routine...
> 
> Do you mean you wish to directly access SQLite's in-memory data
> structures, rather than using a SQL query to get the data?  Why?

Oh, never mind, I wrote too soon.  I realize now that you want an
in-memory representation of a SQLite database file, which you than
gzip or whatever, all still in memory, and then transfer over the
network to whomever wants that file.  And you want to know if there's
some way to do that which offers more programatic control than the
ramdisk filesystem you're using now.

Essentially, you want a way to dynamically control the size of the RAM
disk.  But it sounds like you don't necessarily need ALL the
facilities of a normal file-system, so you're wondering if perhaps you
could implement something more purpose-specific yourself rather than
using that ramdisk driver.

Interesting, but I don't know the answer.

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] Snapshot database creation performance

[Andrew Piskorski]

On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote:

> The question is:
> Is there a way that allows to create the database in memory (this is
> present: use :memory: as file name) and to use the allocated memory
> before it is deallocated when database is closed?

I'm confused, why do you want to do that?  Use the memory how?

If you need a lot of memory for other purposes after you're done with
the SQLite database, what's wrong with just letting SQLite free the
memory, and then malloc'ing as much memory as you want?

> My program is written in .Net and the compression routines I'm using are
> stream based, so I need to create a memory stream from the internal
> buffers that can be used by the compression routine...

Do you mean you wish to directly access SQLite's in-memory data
structures, rather than using a SQL query to get the data?  Why?

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

RE: [sqlite] Problem with complex query

[Bielik, Robert]

Scrap that... I think I found the problem and its nowhere near SQLite...

> -Original Message-
> From: Bielik, Robert 
> Sent: Tuesday, February 07, 2006 1:22 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Problem with complex query
> 
> 
> Well, it isn't that complex really. I have a database 
> structure with T_ITEM possibly having T_ITEM children 
> (connected by T_MAP (childid, parentid)). Also connected to a 
> T_ITEM (possibly) is a T_DATA (id integer, size integer, data blob).
> 
> I wan't to retrieve all T_ITEM children of a T_ITEM also 
> returning the size of data in a (possibly) connected T_DATA. So...:
> 
> SELECT I.,D.SIZE FROM T_ITEM I LEFT JOIN T_DATA D ON 
> I.DATAID=D.ID INNER JOIN T_MAP M ON I.ID=M.CHILDID AND 
> M.PARENTID=;
> 
> I have the exact same structure in Oracle AND in SQLite (I 
> use SQLite for archival purposes). Oracle retrieves the 
> designated rowset, whereas SQLite returns zero rows. I've 
> used the latest SQLite sources 3.3.3 for the test.
> 
> Any ideas on either a query that works, or anything else?
> 
> TIA
> /Rob
> 

RE: [sqlite] New benchmark comparisons

[Brandon, Nicholas (UK)]

>It did pretty well against Sql Server Mobile as well.  I just posted those
>benchmarks here:
>http://sqlite.phxsoftware.com/forums/623/ShowPost.aspx

Just had a quick look and noticed a peculiarity. SQLite seems to do pretty well 
across the board except in the graph for "INSERT 1 Row Test (implicit 
transaction)". It seemly does well except when using the SD card. Then I 
noticed your labelling is not consistent across all the pictures. Is that a 
mistake or is it what you had expected?

For me it would be easier for my brain to compare if you had them in the same 
label order across the pictures (i.e. SQLite (M), SQLMobile (M), SQLite (C) ...)

Regards
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.

[sqlite] Problem with complex query

[Bielik, Robert]

Well, it isn't that complex really. I have a database structure with T_ITEM 
possibly having T_ITEM children (connected by T_MAP (childid, parentid)). Also 
connected to a T_ITEM (possibly) is a T_DATA (id integer, size integer, data 
blob).

I wan't to retrieve all T_ITEM children of a T_ITEM also returning the size of 
data in a (possibly) connected T_DATA. So...:

SELECT I.,D.SIZE FROM T_ITEM I LEFT JOIN T_DATA D ON I.DATAID=D.ID 
INNER JOIN T_MAP M ON I.ID=M.CHILDID AND M.PARENTID=;

I have the exact same structure in Oracle AND in SQLite (I use SQLite for 
archival purposes). Oracle retrieves the designated rowset, whereas SQLite 
returns zero rows. I've used the latest SQLite sources 3.3.3 for the test.

Any ideas on either a query that works, or anything else?

TIA
/Rob

[sqlite] Snapshot database creation performance

[Clinco, Michele]

Hallo.

I have an application that uses a central database (Oracle or SQL
server) and creates a copy of the data on an SQLite database file.

This file contains a read only copy of more or less all the central
database tables and contains about 50 tables and may contain up to 100k
records spread between the different tables.

When application starts, it makes a request to a service that runs where
the central database is that creates the database file, creates the
tables, fills the tables with data and creates the necessary indexes.
The file is then compressed, sent to the client and deleted because it
is not used any more.

This operation is quite heavy and takes several seconds (20 sec on my
laptop, generating a 1700k data file)

Since the file is absolutely temporary, I installed a ramdisk driver and
I tried to create the file in this driver instead that in the hard disk.

The difference is really astonishing: 0.9 seconds instead of >20. This
means that I don' have to worry about performance any more.

There is only a problem: ramdisk memory sizing. The server may receive
multiple concurrent requests, so the ramdisk must be dimensioned
accordingly, wasting memory that is normally not used... 
This is simple, but causes administration problems and error checking
routines that I would like to avoid.

The question is:
Is there a way that allows to create the database in memory (this is
present: use :memory: as file name) and to use the allocated memory
before it is deallocated when database is closed?

I had a look at the sources but I did not understand how memory
allocation takes place. I imagine that the library is not using a
contiguous block of memory but a sort of list

My program is written in .Net and the compression routines I'm using are
stream based, so I need to create a memory stream from the internal
buffers that can be used by the compression routine...

Bye, Michele

Re: [sqlite] More benchmarks

[Nemanja Corlija]

On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote:
> > > Well, that certainly won't help things... at a minimum, on your machine,
> > > you should change the following:
> > > shared_buffers=1
> > > effective_cache_size=10
> > >
> > > The following should also help:
> > > work_mem=1
> > > vacuum_cost_delay=50
> > > autovacuum=on
> > > autovacuum_vacuum_scale_factor=0.2
> > Sure, I could do that. But then I'd also need to tune all other
> > databases to make things fair and that's not really what I intended to
> > do here. I want to keep things as "out of the box" as possible.
>
> Then you should just drop PostgreSQL from the tests, because they're not
> doing anyone any good. It's pretty well known that the default
> postgresql.conf is meant to allow for bringing the database up on a
> machine with very minimal hardware. It's the equivalent to using MySQL's
> minimum configuration file.

OK, I've changed above settings but now I get even worse performance.
265.223 seconds.
File I've edited is C:\Program Files\PostgreSQL\8.1\data\postgresql.conf
AFAICT that's the one. Then I've restarted postgres. I guess that
should load new settings?
Then I ran VACUUM ANALYZE t2;

Hmmm, now I ran that same script from pgAdmin and it completed in 5 seconds.
I guess its reasonable to assume that psql is actually the bottleneck
here. I tried redirecting to file but that was a minute ago and it's
still running. Any ideas?
--
Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] More benchmarks

[Andrew Piskorski]

On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote:
> On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:

> > Did you happen to do an analyze?
> Nope. All databases are run as default as possible. And, they all get
> same scripts to execute.

Then your results for PostgreSQL are utterly meaningless.  (And in
this case, the poor performance reflects poorly on you, the DBA, not
on PostgreSQL.)

> > What changes have you made to the default postgresql.conf?
> None.

Then your test results are bogus.  Last I heard, the default value in
postgresql.conf were intended to simply work AT ALL on the widest
possible range of hardware, operating systems, etc., and are NOT
recommended values for any actual production use.

Yes, I that sounds very foolish of the PostgreSQL folks to me too, but
there you have it.  Using PostgreSQL properly REQUIRES that you modify
those settings.

> Sure, I could do that. But then I'd also need to tune all other
> databases to make things fair and that's not really what I intended to
> do here. I want to keep things as "out of the box" as possible.

The above is not exactly "tuning", it is basic "Running PostgreSQL
101" type stuff.

Look at it this way: Different databases have different installation
requirements.  Editing postgresql.conf and collecting statistics with
vacuum analyze are simply part of the required install procedure for
PostgreSQL.  If you don't do the basic stuff like that, your database
is simply misconfigured, and any performance results you generate are
worthless - because in the real world, NO ONE with any clue at all
would ever run their database that way.

Minimally, you need to install and configure each of the databases
you're benchmarking in the manner expected of a competent but
non-expert user of that tool.  Naturally this various for different
databases.

If you find the process of properly installing and configuring the
database software overly complicated or poorly documented, then that's
a perfectly legitimate complaint, but it has nothing to do with
performance benchmarking.

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] Indexing

[Nemanja Corlija]

On 2/7/06, chetana bhargav <[EMAIL PROTECTED]> wrote:
>   I am sure that this question would have been asked many times earlier also. 
> I am new to
> this list, can any one point me about some info on indexing in SQLite. The 
> time efficiency
> and space it requires. Some Do's and Dont's about indexing.
Probably best place to start is http://www.sqlite.org/php2004/page-001.html

I can't speak about space, but as far as time goes, take a look at
http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison
As you can see there index creation is relatively fast. Sometimes it
even makes sense to create index, execute query and then drop the
index. Though this won't save you any space since index will still be
created on disk, if I'm not mistaken.

--
Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] New benchmark comparisons

[Denis Sbragion]

Hello Robert,

At 06.36 06/02/2006, you wrote:
...

Also coming up next week are comparisons vs. Sql Server Mobile, which is
where SQLite is really racking up some wins recently.


interesting but, be careful! Last time I checked benchmarking was 
explicitely forbidden by the SQL Server end user license. Probably the same 
apply to SQL Server Mobile.
BTW we're using sqlite under Windows CE since at least 3 years, 
and we have no intention at all to get back from there. I've yet to find a 
real situation where sqlite is the bottleneck. This is expecially true for 
our single user/single thread/no locking typical application. I will never 
thank enough DRH and the other sqlite developers and contributors for their 
invaluable work.


Bye,
--
Denis Sbragion
InfoTecna
Tel: +39 0362 805396, Fax: +39 0362 805404
URL: http://www.infotecna.it

[sqlite] Indexing

[chetana bhargav]

Hi,
   
  I am sure that this question would have been asked many times earlier also. I 
am new to this list, can any one point me about some info on indexing in 
SQLite. The time efficiency and space it requires. Some Do's and Dont's about 
indexing.
   
  Thanks for the help in advance.
   
   
  Cheers,
  Chetan.


-
 Yahoo! Mail - Helps protect you from nasty viruses.