Re: [sqlite] Data structure

[Lloyd]

Would anybody suggest a good tool for performance measurement (on
Linux) ?

On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote:
> You might discover that you can craft a very effective memory
> resident 
> storage system using a compression system like Huffman Encoding and
> an 
> index method appropriate to the key you are using for retrieval.
> That 
> could work very well in an embedded system, have a small footprint in 
> data and code and be very fast.


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[Lloyd]

Thank you all. I got so many new ideas from your replies. Now I just
have to derive the best solution for me, thanks :)

Lloyd


On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote:
> You might discover that you can craft a very effective memory
> resident 
> storage system using a compression system like Huffman Encoding and
> an 
> index method appropriate to the key you are using for retrieval.
> That 
> could work very well in an embedded system, have a small footprint in 
> data and code and be very fast.


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] nevermind: [sqlite] DBD::SQLite 1.13 and v 3.3.15

[Jim Dodgen]

I figured it out, i needed to pass a parm to Makefile.Pl to force it to 
use the local SQLite source.



Jim Dodgen wrote:

Im having a problem geting the perl DBD working with 3.3.15

I integrated the 3.3.15 source with the perl module and all seemed ok.

all is fine with the command line version (sqlite3) but when I run a perl 
script i get this:


--- cut here ---
[EMAIL PROTECTED] perl]# ./sqlite_version.pl
install_driver(SQLite) failed: Can't 
load '/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-
multi/auto/DBD/SQLite/SQLite.so' for module 
DBD::SQLite: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-
multi/auto/DBD/SQLite/SQLite.so: undefined symbol: sqlite3_version 
at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.

 at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at ./sqlite_version.pl line 8
--- end ---

at first glance it looks as SQLite.so is missing but when I do a ls it is found:

--- cut here ---
[EMAIL PROTECTED] perl]# ls -l /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-
thread-multi/auto/DBD/SQLite/SQLite.so
-r-xr-xr-x 1 root root 83835 Apr 11 
14:11 /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-

multi/auto/DBD/SQLite/SQLite.so
--- end ---

the "undefined symbol: sqlite3_version" has me stumped, any ideas.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[Gerry Snyder]

[EMAIL PROTECTED] wrote:

Here is an excerpt from my requirements doc:


If a transaction is aborted all database state is reset 

("rolled-back") to its value at the time the transaction was opened.
Nested transactions abort or commit relative to their 

parent transaction. In the case of an aborted transaction,
the database state is rolled back to the point where
the transaction was started. (This is true whether
or not the transaction is nested.) In the case of
a commit, the nested transaction’s changes become
part of its parent transaction, as if the nested
transaction boundaries had never been established.

If I had to implement this with the current SQLite,
I would start a new TEMP table at each BEGIN, and
destroy the current TEMP table at a ROLLBACK or
add its contents to its parent table (either
the previous TEMP table or the "real" table at a
COMMIT.

I think that does everything you need, and would be
easy to program.

HTH,

Gerry

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Select columns & performance

[Joe Wilson]

--- Mike Johnston <[EMAIL PROTECTED]> wrote:
> Are there any significant performance benefits by limiting the number of 
> columns in a single
> table select statement?   Does joining (<5 tables) make a significant 
> difference to that answer?

If you need the columns, you don't have much choice but to select them.

Limiting the number of columns in subqueries makes a big difference.
i.e., try to avoid SELECT * in subqueries when you just need a few values.

Do a lot of benchmarking.


   

Get your own web address.  
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Select columns & performance

[Mike Johnston]

Are there any significant performance benefits by limiting the number of 
columns in a single table select statement?   Does joining (<5 tables) make a 
significant difference to that answer?
   
  TIA

   
-
TV dinner still cooling?
Check out "Tonight's Picks" on Yahoo! TV.

Re: [sqlite] Re: SQLite and nested transactions

[Darren Duncan]

At 3:33 PM -0600 4/11/07, Dennis Cote wrote:
You have lost me here. If this transaction is considered successful 
without executing the middle step (which is the same as executing it 
and then undoing that execution) then that step does not belong in 
this transaction.


Instead of

   begin
   step 1
   savepoint after_1
   optional step 2
   if error
   rollback to savepoint after_1
   step 3
   commit

You can do

   begin
   step 1
   step 3
   commit
   begin
   optional step 2
   if error
   rollback
   else
   commit


Your example assumes no coupling between the different steps, which 
is true some times, but in the general case there can be coupling. 
That is, the effect that step 3 actually has can be different 
depending on whether step 2 is rolled back or not, either because 
step 3 is operating on a different database state, or because step 3 
contains conditionals that cause different statements to execute 
depending on database state that could have been changed by step 2. 
So in the general case, step 2 must always be run after step 1 and 
before step 3.


This *is* an example of an application where a nested transaction or 
a savepoint could be useful. However there is a fairly simple 
workaround that gets the same result without a nested transaction.


Instead of:

   begin
   step 1
   begin nested
   temp step 2
   var = query current state
   rollback nested
   step 3 using var
   commit

You could do:

   begin
   step 1
   temp step 2
   var = query current state
   rollback
   begin
   step 1
   step 3 using var
   commit

The cost of this approach is repeating the work done in step 1.


While that workaround may be an acceptable solution for some 
situations, I see that as overly complicated and difficult in the 
general case.


For one thing, it requires the application to keep track of all the 
details of what step 1 was, and step 1 could be arbitrarily complex.


Moreover, step 1 could have been expensive, involving a large amount 
of data which may have been input from somewhere and can't be 
retrieved again nor stored in RAM; the only copy of it is in the 
database.


Or even ignoring the last point there is still the complexity, 
especially if one used bind variables that were since freed up for 
other tasks, since you aren't just keeping a log of SQL strings to 
re-run.


I think that a SQLite pager-based mechanism for tracking child 
transactions is quite a bit less complicated and more reliable than 
using your workaround, since no details have to be remembered but for 
the pages that changed.


Now going off on a tangent ...

To address the oft-raised comment that some people make that any 
proposed additions or changes to SQLite be opposed in general on the 
principal that "it is supposed to be lite", I say this:


It is perfectly in keeping with SQLite's philosophy for us to add 
lots of power to it if the cost of adding that power is low.


Low cost meaning that the codebase doesn't need to increase much if 
any, the resource efficiency of running SQLite isn't impacted much, 
complexity doesn't raise the bug density appreciably, and 
particularly, it isn't more difficult for users to use.


Some features, like proper child transactions as I described, are a 
situation where users gain a lot of power at very little cost. 
Having proper child transactions means it is a lot easier for users 
and developers, particularly SQLite extension or wrapper writers such 
as myself, to add powerful features to SQLite using programs while 
SQLite itself is hardly more complex.  Users are saved a lot of work, 
and SQLite developers gain next to none.


By contrast, say, supporting named users in the database and 
concurrent database writes and stuff like that is indeed way too 
complicated for SQLite to have, and I still support SQLite never 
adding support for it.


So SQLite with child transactions is only trivially less lite than it 
is now, which is still lite.


In fact, I propose moving rollbackable child transaction support to 
the top of the todo list, rather than it being in the middle, given 
that its presence can make a lot of other todo or wishlist items much 
easier to implement, I believe.


And if it will make a difference, I will even make a monetary 
donation (as I can afford to) in order to sponsor its development 
(though I would like to think that the benefits are compelling on 
their own).


-- Darren Duncan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File size issue?

[Joe Wilson]

> What is worse is that VACUUM didn't really help that much. It takes
> forever, and it doesn't really "fix" the fragmentation either.

That used to be the case, but VACUUM is vastly improved in the latest 
version of SQLite.


   

Sucker-punch spam with award-winning protection. 
Try the free Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/features_spam.html

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Failed subquery (possible bug?)

[Dennis Cote]

Steve Krulewitz wrote:

Hey all,

On sqlite 3.3.12, I see the following:

create table test_in (data text, ordinal text);

insert into test_in values ('foo', '0');
insert into test_in values ('bar', '1');
insert into test_in values ('baz', '2');

Running:

select count(1) from test_in where ordinal in (select ordinal from 
test_in);


returns "3" as expected.  However, if I add an order by to the subquery:

select count(1) from test_in where ordinal in (select ordinal from
test_in order by ordinal);

this returns "0".  But note that this works:

select count(1) from test_in where rowid in (select rowid from test_in
order by ordinal);

My actual use case needs the "order by" in the subquery since I also
add a "limit 1" clause.

Is there something I am doing wrong here?



Steve,

I don't think you are doing anything wrong. I think you have found a 
real bug.


You should open a bug report at http://www.sqlite.org/cvstrac/tktnew

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] 3.3.15 test coverage improvements? (.dump again)

[Joe Wilson]

Unfortunately, the sqlite3 commandline shell is not part of the test suite
despite its widespread use as an administrative tool for sqlite databases.

 http://marc.info/?l=sqlite-users=117253099812346=2

But you know this already - you're the same guy as this previous post.  :-)

--- Travis Daygale <[EMAIL PROTECTED]> wrote:
> Change log for 3.3.15 says:
> Many improvements to the test suite.  Test coverage now exceeded 98%
> 
> What does this mean?  
> 
> Does it mean that (say) the sqlite3 command line tool (especially the .dump 
> command) is tested
> at each release now?
> 
>   ---
> 
> I'm asking this because previously on this list it was made clear that the 
> command line tool was
> not part of the test suite.  The .dump command in that tool is (very very 
> often!!!) described as
> a good backup.  But it isn't (IMHO).  It's not a tested tool, and indeed, 
> there were 3.x.x
> releases where that tool was not correctly dumping out (eg.) triggers.  
> 
> It just seems that to download sqlite source and know that it contains a 
> reliable backup tool
> with it would add to this first rate open source software, regardless of how 
> simple it might be
> to write our own code in our language of choice to dump databases.
> 
> Either that or consider removing the .dump command?  I say this because I 
> have seen so very much
> documentation that references the .dump feature, and yet that feature lies in 
> code that is
> (was?) tested very differently than the rest of sqlite.That just seems 
> uncomfortable... 
> 
> Backups are so important, etc..
> 
> I hope my point is made.  I'm not complaining here at all.   Trying to be 
> helpful.



   

Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and nested transactions

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:

> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- [EMAIL PROTECTED] wrote:
> > > It may be more difficult to implement this in a backwards-compatible
> > > way such that older versions of SQLite can rollback a journal created
> > > by a newer version if it encounters one.
> > 
> > I wonder if there are many projects that have different versions of 
> > SQLite updating and reading the same database file at the same time.
> > This can't be very common.
> > 
> 
> As we learned from the release of 3.3.0, this is more common
> that you might expect.  There are surprisingly many projects 
> that expect to be able to access SQLite databases from both 
> older and newer versions of the library.

It's not quite the same thing - the 3.x file format change was not 
backwards compatible _at all_ with previous versions of SQLite.
Having a large degree of backwards compatibilty makes all the difference.

Changing the journal file format to accommodate a hypothetical new 
feature would still produce a backwards compatible database _except_ 
in the rare case where a transaction in a new version of SQLite is 
abruptly aborted or if the power fails. If the transactions are finished 
you would still have backwards compatibility with previous versions.

But I still think that simultaneous read/write access to the same database 
file with different version of SQLite is not very common.


   

Don't get soaked.  Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[Dennis Cote]

Ramon Ribó wrote:



 Imagine one application that can import data from a file. You want 
that, in case of computer crash, either all the data of the file is 
imported or none. At the same time, you want the user to manually 
accept or reject every section of the file.


  This example can be modelled in a very natural way with a 
transaction covering the full file import and a nested transaction 
covering every section.


 

Ramon,

I don't see that where nested transactions are needed for this example.

You seem to be suggesting a loop reading each file section and writing 
it into the database in a nested transaction and then rolling back a 
nested transaction if the user says they want to skip that section.


   begin
   for each section in file {
  read section
  begin nested
  insert section
  if promp_user(section) == keep
 commit nested   
  else

 rollback nested
   }
   commit

The same thing can be done far more efficiently by prompting the user 
first and only inserting the sections the user wants to keep.


   begin
   for each section in file {
  read section
  if promp_user(section) == keep
 insert section
   }
   commit

If the program completes all users selected sections are inserted into 
the database atomically. If the program crashes the entire file will be 
deleted when the incomplete transaction is rolled back. Similarly if an 
I/O error occur when reading the file or a disk full condition happens 
when inserting a section, those and any other errors would cause the 
transaction to be rolled back so that none of the file sections are 
inserted. I want to insert all of the user selected sections or none of 
them.


Nested transaction only create more work and make the application more 
complicated.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Failed subquery (possible bug?)

[Steve Krulewitz]

Hey all,

On sqlite 3.3.12, I see the following:

create table test_in (data text, ordinal text);

insert into test_in values ('foo', '0');
insert into test_in values ('bar', '1');
insert into test_in values ('baz', '2');

Running:

select count(1) from test_in where ordinal in (select ordinal from test_in);

returns "3" as expected.  However, if I add an order by to the subquery:

select count(1) from test_in where ordinal in (select ordinal from
test_in order by ordinal);

this returns "0".  But note that this works:

select count(1) from test_in where rowid in (select rowid from test_in
order by ordinal);

My actual use case needs the "order by" in the subquery since I also
add a "limit 1" clause.

Is there something I am doing wrong here?

cheers,
-steve

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] 3.3.15 test coverage improvements? (.dump again)

[Travis Daygale]

Change log for 3.3.15 says:
Many improvements to the test suite.  Test coverage now exceeded 98%

What does this mean?  

Does it mean that (say) the sqlite3 command line tool (especially the .dump 
command) is tested at each release now?

  ---

I'm asking this because previously on this list it was made clear that the 
command line tool was not part of the test suite.  The .dump command in that 
tool is (very very often!!!) described as a good backup.  But it isn't (IMHO).  
It's not a tested tool, and indeed, there were 3.x.x releases where that tool 
was not correctly dumping out (eg.) triggers.  

It just seems that to download sqlite source and know that it contains a 
reliable backup tool with it would add to this first rate open source software, 
regardless of how simple it might be to write our own code in our language of 
choice to dump databases.

Either that or consider removing the .dump command?  I say this because I have 
seen so very much documentation that references the .dump feature, and yet that 
feature lies in code that is (was?) tested very differently than the rest of 
sqlite.That just seems uncomfortable... 

Backups are so important, etc..

I hope my point is made.  I'm not complaining here at all.   Trying to be 
helpful.

Thanks much in advance,

Trever

   
-
Sucker-punch spam with award-winning protection.
 Try the free Yahoo! Mail Beta.

Re: [sqlite] Implementing type find on a large result set

[Steve Krulewitz]

Thanks, Samuel, I forgot that trick :)

cheers,
-steve

On 4/6/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:


Instead of using LIKE use '<' to get a count of records before the one your
targeting.  Something like this would work (names are from my schema):

SELECT PermissionRef
FROM LU_Permissions
LIMIT 10
OFFSET (
SELECT COUNT(*)
FROM LU_Permissions
WHERE PermissionRef < 'Sc');

HTH,

Sam



---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Performance of Joins

[Yves Goergen]

On 11.04.2007 21:38 CE(S)T, Igor Tandetnik wrote:
> No. The other table affects the result of the query, so the join still 
> has to be performed.

I see. I haven't thought of that effect. Thank you for the reply.

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[rhurst2]

I forgot to mention that the stream data contains a BEGIN TRANSACTION and END 
TRANSACTION marker.
Ray

 [EMAIL PROTECTED] wrote: 
> In my case, I am a slave device that must accept a stream of commands from an 
> external device. I'm not sure if I can make intelligent decisions about 
> choosing what I commit to the database.
> Ray
> 
>  Darren Duncan <[EMAIL PROTECTED]> wrote: 
> > At 12:49 PM -0600 4/11/07, Dennis Cote wrote:
> > >[EMAIL PROTECTED] wrote:
> > >>It appears that my requirements are to be able to do the following:
> > >>
> > >>BEGIN parent;
> > >>insert into t values ('a');
> > >>BEGIN child;
> > >>insert into t values ('b');
> > >>insert into t values ('c');
> > >>ROLLBACK child;  // child aborts
> > >>insert into t values ('d');
> > >>COMMIT parent;
> > >>
> > >>As a result of this sequence, the table should have two new rows 
> > >>with values 'a' and 'd', but not 'b' and 'c'.
> > >
> > >Can you explain why your application is rolling back the child transaction?
> > >
> > >If the above is really how your application works (and I don't think 
> > >it is), then the exact same result can always be achieved with the 
> > >simpler sequence:
> > >
> > >BEGIN;
> > >insert into t values ('a');
> > >insert into t values ('d');
> > >COMMIT;
> > >
> > >You don't need to bother inserting b and c if you are going to undo 
> > >those insertions with a static rollback.
> > 
> > While it is true in some cases that an application can be written to 
> > know in advance whether certain SQL statements need to be run or not, 
> > there are other cases where it can only easily know after having 
> > tried it.
> > 
> > One type of situation that stands out the most to me is if you have 
> > state constraints defined (in some way) on the database for which it 
> > is only really feasible to calculate the constraint definition after 
> > DML has occurred, because you want SQLite to do the calculation 
> > itself on the post-DML-state and it is technically simpler that way; 
> > if the constraint fails, we would want to be able to just rollback 
> > the DML that caused the state to break, but not the other valid stuff 
> > before that, since we could have more stuff after this attempt that 
> > needs to be atomic with stuff before the attempt.
> > 
> > Well, the key thing in that example, and many situations, is that the 
> > child transaction is doing something that we may or may not want to 
> > rollback and we won't know until after it is tried.
> > 
> > This said, I can also see situations where there is an unconditional 
> > child rollback, and that is where we want SQLite itself to perform a 
> > querying or calculating task using a temporarily changed database 
> > state as input.  We want the result of the query, but not the changed 
> > state that went into its input.  But there is other changed state 
> > before that which does need to go into the input, and it needs to 
> > persist, and be atomic with other state changes done after the 
> > calculation.
> > 
> > Now, you could say make the application do that work, but the fact is 
> > that certain kinds of data processing are more efficiently done by a 
> > DBMS itself.
> > 
> > All this said, I look forward to rhurst's specific problem being 
> > explained, so it is easier for us to see whether child transaction 
> > support would help *that* problem.
> > 
> > -- Darren Duncan
> > 
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> > 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[Dennis Cote]

[EMAIL PROTECTED] wrote:

I'm not sure if I can make intelligent decisions about choosing what I commit 
to the database.

  

Things don't look too bright for you or your users then. ;-)

I couldn't resist. :-)

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File size issue?

[Kastuar, Abhitesh]

> Are you using indices? Do you have an integer primary key
> on the table, but the data is not inserted in key order?
> Do you delete and insert records a lot?

Yes, we do have indices on a combination of fields in the table and we
do not guarantee insert of the data in key order. However, we do not do
(much) deletes. And, as you point out, I have seen severe database
fragmentation.

What is worse is that VACUUM didn't really help that much. It takes
forever, and it doesn't really "fix" the fragmentation either.

As suggested by another reply, I have been focusing my time on analyzing
the right combination of page size and cache size. 


-Abhitesh.

-Original Message-
From: Michael Scharf [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 11, 2007 1:23 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] File size issue?

Hi,

 > I am running into some issues that seem related to the current
database
 > file size.


I think it has to do with the file system cache: if you
database is small, the entire database is held in your
systems file cache. Once the database exceeds a certain
size, real disk operations have to be done to access the
data.

SQLite is not very good in managing huge amounts of data
in the database. When you are in the "slow mode", check
your CPU usage and disk usage. I'd bet that CPU is very
low and the disk is seeking a lot

Regular VACUUM might help. Because it puts the data into
a good order on disk.

Are you using indicees? Do you have an integer primary key
on the table, but the data is not inserted in key order?
Do you delete and insert records a lot?
Those operations can lead to heavy fragmentation of your
database.

The calculation is simple: suppose you have disk with
10ms average seek time and 30Mb/s read speed and
a database of 4Gb. It takes about 130 sec to read
the entire database. In the same time you can
do 13000 seeks (in the worst case of fragmentation).

Unfortunately the SQLite tables are not optimized
for disk access. Records are accessed in key
order which can be much more expensive than to
access the table or index in disk-order.

So, VACUUM brings  the key order close to the
disk order and therefore your database is much
faster. However, if you wait to long to do the
vacuum (and your database is extremely fragmented)
vacuum might "take forever", because every record
access involves a seek operation. I'm not sure what
happens to huge indicees after VACUUM. Maybe they
are fragmented again. But I don't know.

That's at least my theory after experimenting with SQLite
quite a bit. I haven't tested 3.3.15 which seems to
have some performance improvements...


Michael

-- 
http://MichaelScharf.blogspot.com/



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




E-mail confidentiality.

This e-mail contains confidential and / or privileged information belonging to 
Spirent Communications plc, its affiliates and / or subsidiaries. If you are 
not the intended recipient, you are hereby notified that any disclosure, 
copying, distribution and / or the taking of any action based upon reliance on 
the contents of this transmission is strictly forbidden. If you have received 
this message in error please notify the sender by return e-mail and delete it 
from your system. If you require assistance, please contact our IT department 
at [EMAIL PROTECTED]

Spirent Communications plc,
Spirent House, Crawley Business Quarter, Fleming Way, Crawley, West Sussex, 
RH10 9QL, United Kingdom.
Tel No. +44 (0) 1293 767676
Fax No. +44 (0) 1293 767677

Registered in England Number 470893
Registered at Spirent House, Crawley Business Quarter, Fleming Way, Crawley, 
West Sussex, RH10 9QL, United Kingdom 

Or if within the US,

Spirent Communications,
26750 Agoura Road, Calabasas, CA, 91302, USA.
Tel No. 1-818-676- 2300 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[Dennis Cote]

Darren Duncan wrote:


While it is true in some cases that an application can be written to 
know in advance whether certain SQL statements need to be run or not, 
there are other cases where it can only easily know after having tried 
it.


One type of situation that stands out the most to me is if you have 
state constraints defined (in some way) on the database for which it 
is only really feasible to calculate the constraint definition after 
DML has occurred, because you want SQLite to do the calculation itself 
on the post-DML-state and it is technically simpler that way; if the 
constraint fails, we would want to be able to just rollback the DML 
that caused the state to break, but not the other valid stuff before 
that, since we could have more stuff after this attempt that needs to 
be atomic with stuff before the attempt.


Well, the key thing in that example, and many situations, is that the 
child transaction is doing something that we may or may not want to 
rollback and we won't know until after it is tried.



Darren,

You have lost me here. If this transaction is considered successful 
without executing the middle step (which is the same as executing it and 
then undoing that execution) then that step does not belong in this 
transaction.


Instead of

   begin
   step 1
   savepoint after_1
   optional step 2
   if error
   rollback to savepoint after_1
   step 3
   commit

You can do

   begin
   step 1
   step 3
   commit
   begin
   optional step 2
   if error
   rollback
   else
   commit


This said, I can also see situations where there is an unconditional 
child rollback, and that is where we want SQLite itself to perform a 
querying or calculating task using a temporarily changed database 
state as input.  We want the result of the query, but not the changed 
state that went into its input.  But there is other changed state 
before that which does need to go into the input, and it needs to 
persist, and be atomic with other state changes done after the 
calculation.


Now, you could say make the application do that work, but the fact is 
that certain kinds of data processing are more efficiently done by a 
DBMS itself.
This *is* an example of an application where a nested transaction or a 
savepoint could be useful. However there is a fairly simple workaround 
that gets the same result without a nested transaction.


Instead of:

   begin
   step 1
   savepoint after_1
   temp step 2
   var = query current state
   rollback to savepoint after_1
   step 3 using var
   commit

or

   begin
   step 1
   begin nested
   temp step 2
   var = query current state
   rollback nested
   step 3 using var
   commit

You could do:

   begin
   step 1
   temp step 2
   var = query current state
   rollback
   begin
   step 1
   step 3 using var
   commit

And in a multiple access system you could use a variation using chained 
transactions to ensure that no other process changes the database state 
between the two transactions.


   begin
   step 1
   temp step 2
   var = query current state
   rollback and chain
   step 1
   step 3 using var
   commit

The cost of this approach is repeating the work done in step 1.

Another approach that could be used in some, but definitely not all, 
cases is to use additional SQL statements to undo or invert the effects 
of step 2.


   begin
   step 1
   temp step 2
   var = query current state
   inverse step 2
   step 3 using var
   commit



All this said, I look forward to rhurst's specific problem being 
explained, so it is easier for us to see whether child transaction 
support would help *that* problem.




Likewise.

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[rhurst2]

In my case, I am a slave device that must accept a stream of commands from an 
external device. I'm not sure if I can make intelligent decisions about 
choosing what I commit to the database.
Ray

 Darren Duncan <[EMAIL PROTECTED]> wrote: 
> At 12:49 PM -0600 4/11/07, Dennis Cote wrote:
> >[EMAIL PROTECTED] wrote:
> >>It appears that my requirements are to be able to do the following:
> >>
> >>BEGIN parent;
> >>insert into t values ('a');
> >>BEGIN child;
> >>insert into t values ('b');
> >>insert into t values ('c');
> >>ROLLBACK child;  // child aborts
> >>insert into t values ('d');
> >>COMMIT parent;
> >>
> >>As a result of this sequence, the table should have two new rows 
> >>with values 'a' and 'd', but not 'b' and 'c'.
> >
> >Can you explain why your application is rolling back the child transaction?
> >
> >If the above is really how your application works (and I don't think 
> >it is), then the exact same result can always be achieved with the 
> >simpler sequence:
> >
> >BEGIN;
> >insert into t values ('a');
> >insert into t values ('d');
> >COMMIT;
> >
> >You don't need to bother inserting b and c if you are going to undo 
> >those insertions with a static rollback.
> 
> While it is true in some cases that an application can be written to 
> know in advance whether certain SQL statements need to be run or not, 
> there are other cases where it can only easily know after having 
> tried it.
> 
> One type of situation that stands out the most to me is if you have 
> state constraints defined (in some way) on the database for which it 
> is only really feasible to calculate the constraint definition after 
> DML has occurred, because you want SQLite to do the calculation 
> itself on the post-DML-state and it is technically simpler that way; 
> if the constraint fails, we would want to be able to just rollback 
> the DML that caused the state to break, but not the other valid stuff 
> before that, since we could have more stuff after this attempt that 
> needs to be atomic with stuff before the attempt.
> 
> Well, the key thing in that example, and many situations, is that the 
> child transaction is doing something that we may or may not want to 
> rollback and we won't know until after it is tried.
> 
> This said, I can also see situations where there is an unconditional 
> child rollback, and that is where we want SQLite itself to perform a 
> querying or calculating task using a temporarily changed database 
> state as input.  We want the result of the query, but not the changed 
> state that went into its input.  But there is other changed state 
> before that which does need to go into the input, and it needs to 
> persist, and be atomic with other state changes done after the 
> calculation.
> 
> Now, you could say make the application do that work, but the fact is 
> that certain kinds of data processing are more efficiently done by a 
> DBMS itself.
> 
> All this said, I look forward to rhurst's specific problem being 
> explained, so it is easier for us to see whether child transaction 
> support would help *that* problem.
> 
> -- Darren Duncan
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[Darren Duncan]

At 12:49 PM -0600 4/11/07, Dennis Cote wrote:

[EMAIL PROTECTED] wrote:

It appears that my requirements are to be able to do the following:

BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child;  // child aborts
insert into t values ('d');
COMMIT parent;

As a result of this sequence, the table should have two new rows 
with values 'a' and 'd', but not 'b' and 'c'.


Can you explain why your application is rolling back the child transaction?

If the above is really how your application works (and I don't think 
it is), then the exact same result can always be achieved with the 
simpler sequence:


BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;

You don't need to bother inserting b and c if you are going to undo 
those insertions with a static rollback.


While it is true in some cases that an application can be written to 
know in advance whether certain SQL statements need to be run or not, 
there are other cases where it can only easily know after having 
tried it.


One type of situation that stands out the most to me is if you have 
state constraints defined (in some way) on the database for which it 
is only really feasible to calculate the constraint definition after 
DML has occurred, because you want SQLite to do the calculation 
itself on the post-DML-state and it is technically simpler that way; 
if the constraint fails, we would want to be able to just rollback 
the DML that caused the state to break, but not the other valid stuff 
before that, since we could have more stuff after this attempt that 
needs to be atomic with stuff before the attempt.


Well, the key thing in that example, and many situations, is that the 
child transaction is doing something that we may or may not want to 
rollback and we won't know until after it is tried.


This said, I can also see situations where there is an unconditional 
child rollback, and that is where we want SQLite itself to perform a 
querying or calculating task using a temporarily changed database 
state as input.  We want the result of the query, but not the changed 
state that went into its input.  But there is other changed state 
before that which does need to go into the input, and it needs to 
persist, and be atomic with other state changes done after the 
calculation.


Now, you could say make the application do that work, but the fact is 
that certain kinds of data processing are more efficiently done by a 
DBMS itself.


All this said, I look forward to rhurst's specific problem being 
explained, so it is easier for us to see whether child transaction 
support would help *that* problem.


-- Darren Duncan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] File size issue?

[Michael Scharf]

Hi,

> I am running into some issues that seem related to the current database
> file size.


I think it has to do with the file system cache: if you
database is small, the entire database is held in your
systems file cache. Once the database exceeds a certain
size, real disk operations have to be done to access the
data.

SQLite is not very good in managing huge amounts of data
in the database. When you are in the "slow mode", check
your CPU usage and disk usage. I'd bet that CPU is very
low and the disk is seeking a lot

Regular VACUUM might help. Because it puts the data into
a good order on disk.

Are you using indicees? Do you have an integer primary key
on the table, but the data is not inserted in key order?
Do you delete and insert records a lot?
Those operations can lead to heavy fragmentation of your
database.

The calculation is simple: suppose you have disk with
10ms average seek time and 30Mb/s read speed and
a database of 4Gb. It takes about 130 sec to read
the entire database. In the same time you can
do 13000 seeks (in the worst case of fragmentation).

Unfortunately the SQLite tables are not optimized
for disk access. Records are accessed in key
order which can be much more expensive than to
access the table or index in disk-order.

So, VACUUM brings  the key order close to the
disk order and therefore your database is much
faster. However, if you wait to long to do the
vacuum (and your database is extremely fragmented)
vacuum might "take forever", because every record
access involves a seek operation. I'm not sure what
happens to huge indicees after VACUUM. Maybe they
are fragmented again. But I don't know.

That's at least my theory after experimenting with SQLite
quite a bit. I haven't tested 3.3.15 which seems to
have some performance improvements...


Michael

--
http://MichaelScharf.blogspot.com/


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] DBD::SQLite 1.13 and v 3.3.15

[Jim Dodgen]

Im having a problem geting the perl DBD working with 3.3.15

I integrated the 3.3.15 source with the perl module and all seemed ok.

all is fine with the command line version (sqlite3) but when I run a perl 
script i get this:

--- cut here ---
[EMAIL PROTECTED] perl]# ./sqlite_version.pl
install_driver(SQLite) failed: Can't 
load '/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-
multi/auto/DBD/SQLite/SQLite.so' for module 
DBD::SQLite: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-
multi/auto/DBD/SQLite/SQLite.so: undefined symbol: sqlite3_version 
at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.
 at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at ./sqlite_version.pl line 8
--- end ---

at first glance it looks as SQLite.so is missing but when I do a ls it is found:

--- cut here ---
[EMAIL PROTECTED] perl]# ls -l /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-
thread-multi/auto/DBD/SQLite/SQLite.so
-r-xr-xr-x 1 root root 83835 Apr 11 
14:11 /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-
multi/auto/DBD/SQLite/SQLite.so
--- end ---

the "undefined symbol: sqlite3_version" has me stumped, any ideas.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Performance of Joins

[Igor Tandetnik]

Yves Goergen <[EMAIL PROTECTED]>
wrote:

I was thinking about what happens when I do an SQL query that names
tables and joins between them that aren't used at all. For example
this:

SELECT m.Sender, m.Subject FROM Message m, MessageTag mt;


Full cartesian product, aka cross-product. For every row in m and every 
row in mt you would have one row in the resultset, for a total of M*N 
rows. Since you are not outputting any fields from mt, you would just 
have each (Sender, Subject) pair duplicated N times.



Does it open and read the table MessageTag at all?


Of course. It has to know at least how many times to duplicate each 
(Sender, Subject) pair.



Also, what happens
in a more complex example where a more explicit join is done but
never used:

SELECT m.Sender, m.Subject FROM Message m NATURAL JOIN MessageTag mt;


Some (Sender, Subject) pairs may be repeated (if the corresponding row 
in m has more than one matching rows in mt), some would appear only 
once, some wouldn't appear at all.



Or:

SELECT m.Sender, m.Subject FROM Message m LEFT JOIN MessageTag mt
USING (MessageId);


Some (Sender, Subject) pairs may be repeated (if the corresponding row 
in m has more than one matching rows in mt), some would appear only 
once. Every pair would appear at least once, even if there's no matching 
row in mt.



Does it impact performance when the join appears in the query or will
the optimiser remove it?


No. The other table affects the result of the query, so the join still 
has to be performed.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] storing the tables in separate files

[Samuel R. Neff]

Not that I would suggest it, but you could create a separate database for
each table and then attach the databases to a single connection to join data
together.  The indexes will need to be in the same database as the table
they index.

But why do you want each table in a different file?

Sam

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Guilherme C. Hazan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 11, 2007 3:10 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] storing the tables in separate files

 Hi,
 
 From what i seen, sqlite stores all tables in a single file. Is there an
 easy way to change this behaviour and store the tables and indexes in
 separate files?
 
 thanks
 
 guich


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] storing the tables in separate files

[Gauthier, Dave]

If you want to store tables in different files, you may want to consider
a heavier relational db , like Postgres, MySQL, Oracle, SQLServer... 




-Original Message-
From: Guilherme C. Hazan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 11, 2007 3:10 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] storing the tables in separate files

 Hi,
 
 From what i seen, sqlite stores all tables in a single file. Is there
an
 easy way to change this behaviour and store the tables and indexes in
 separate files?
 
 thanks
 
 guich
 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] storing the tables in separate files

[Guilherme C. Hazan]

 Hi,
 
 From what i seen, sqlite stores all tables in a single file. Is there an
 easy way to change this behaviour and store the tables and indexes in
 separate files?
 
 thanks
 
 guich
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Performance of Joins

[Yves Goergen]

Hello,

I was thinking about what happens when I do an SQL query that names
tables and joins between them that aren't used at all. For example this:

SELECT m.Sender, m.Subject FROM Message m, MessageTag mt;

Does it open and read the table MessageTag at all? Also, what happens in
a more complex example where a more explicit join is done but never used:

SELECT m.Sender, m.Subject FROM Message m NATURAL JOIN MessageTag mt;

Or:

SELECT m.Sender, m.Subject FROM Message m LEFT JOIN MessageTag mt USING
(MessageId);

Does it impact performance when the join appears in the query or will
the optimiser remove it? I haven't seen anything about this in the
"query optimiser overview" documentation.

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: SQLite and nested transactions

[Dennis Cote]

[EMAIL PROTECTED] wrote:

It appears that my requirements are to be able to do the following:

BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child;  // child aborts
insert into t values ('d');
COMMIT parent;

As a result of this sequence, the table should have two new rows with values 
'a' and ‘d', but not 'b' and ‘c’.

I expanded Igor's example because a single transaction failure performs a 
rollback. ( Think that is true).

Anyway, the above result is what is required and I cannot do this with the all 
or none concept.
  

Ray,

Can you explain why your application is rolling back the child transaction?

If the above is really how your application works (and I don't think it 
is), then the exact same result can always be achieved with the simpler 
sequence:


BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;

You don't need to bother inserting b and c if you are going to undo 
those insertions with a static rollback.



I see that other databases have the concept of SavePoint.
I believe this will work for me but I am not sure.


BEGIN
insert into t values  ('a');
savepoint = SetSavePoint();
insert into t values ('b');
insert into t values ('c');
RestoreSavePoint(savepoint);
insert into t values ('d');
Commit

As a result of this sequence, the table should have two new rows with values 
'a' and ‘d', but not 'b' and ‘c’.

The difference here is that there is only one Begin and Commit.
There can be any number of savepoints. This seems to perform the same function.

  

The standard SQL syntax for this sequence would be:

BEGIN;
insert into t values  ('a');
SAVEPOINT after_a;
insert into t values ('b');
insert into t values ('c');
ROLLBACK TO SAVEPOINT after_a;
insert into t values ('d');
COMMIT;

This still begs the question "why are you rolling back to the savepoint"?

These samples are very much out of context in that they don't show how 
or why the application is issuing this sequence of SQL statements. 
Without that context information its nearly impossible decide if it must 
be done this way or not.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: SQLite and nested transactions

[rhurst2]

It appears that my requirements are to be able to do the following:

BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child;  // child aborts
insert into t values ('d');
COMMIT parent;

As a result of this sequence, the table should have two new rows with values 
'a' and ‘d', but not 'b' and ‘c’.

I expanded Igor's example because a single transaction failure performs a 
rollback. ( Think that is true).

Anyway, the above result is what is required and I cannot do this with the all 
or none concept.

I see that other databases have the concept of SavePoint.
I believe this will work for me but I am not sure.


BEGIN
insert into t values  ('a');
savepoint = SetSavePoint();
insert into t values ('b');
insert into t values ('c');
RestoreSavePoint(savepoint);
insert into t values ('d');
Commit

As a result of this sequence, the table should have two new rows with values 
'a' and ‘d', but not 'b' and ‘c’.

The difference here is that there is only one Begin and Commit.
There can be any number of savepoints. This seems to perform the same function.

I have used SQLite and like the ease of use.
Ray


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and nested transactions

[Joel Lucsy]

It seems to me that the biggest worry about nested transactions is backwards
compatibility. So, first, let me reiterate some points:

1). Actual changes to the database occur thru only one user at a time and
modify the database directly.
2). The journal is created by this one user and is a record of how to put
modified database back to its original state.
3). If the process/computer dies, upon reading the database, if a journal is
found, the database is restored.

So, now, it seems to me that to handle nested transactions while maintaining
backward compatibility you'd:

1). Leave the name of the current journal alone, it will represent the
lowest or first transaction.
2). Upon first read of the database, if this journal exists, do what it
currently does, restore the database using only this journal. This maintains
backwards compatibility.
3). Upon start of a new transaction, create a new journal, perhaps journal1
then journal2 and so on. It will record changes to the database just like
the current journal does.
4). If this transaction commits, nuke the journal, its no longer needed.
5). If the transaction is aborted, apply it just like applying the journal
of the base transaction.

Internally, you'd need to keep track of the transactions in a list/stack
with their associated journal name.
Now, with all that said, I can't say how much work it would take to apply
this kind of change, but conceptually it doesn't seem all that hard. Any
takers want to debate this?

--
Joel Lucsy
"The dinosaurs became extinct because they didn't have a space program." --
Larry Niven

Re: [sqlite] Data structure

[John Stanton]

Lloyd wrote:

On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote:


I think, looking from Lloyd's email address, (s)he might be limited to
what CDAC, Trivandrum might be providing its users.

Lloyd, you already know what size your data sets are. Esp. if it
doesn't change, putting the entire dataset in RAM is the best option.
If you don't need SQL capabilities, you probably can just use
something like BerkeleyDB or DBD::Deep (if using Perl), and that will
be plenty fast. Of course, if it can't be done then it can't be done,
and you will have to recommend more RAM for the machines (the CPU
seems fast enough, just the memory may be a bottleneck).



Sorry, I am not talking about the limitations of the system in our side,
but end user who uses our software. I want the tool to be run at its
best on a low end machine also. 


I don't want the capabilities of a data base here. Just want to store
data, search for presence, remove it when there is no more use of it.

Surely I will check out BerkeleyDB. The data set must be in ram, because
the total size of it is very small. (Few maga bytes) I just want to
spped up the search, which is done millions of times.

Thanks,

 LLoyd

You might discover that you can craft a very effective memory resident 
storage system using a compression system like Huffman Encoding and an 
index method appropriate to the key you are using for retrieval.  That 
could work very well in an embedded system, have a small footprint in 
data and code and be very fast.


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and nested transactions

[Dennis Cote]

Darren Duncan wrote:


I wasn't speaking in absolutes there, only in the context that if 
child transactions were implemented, and there were 2 ways to do it, 
then which way might have the least impact would be the way that most 
closely resembles SQLite's current behaviour.


Yes, I know. I re-read my last post this morning and it sounded a bit 
harsh, which is not what I intended. I'm sorry if I offended.


A transaction is just another name for a unit of work that is atomic, 
and each individual SQL statement *is* a transaction.


Therefore, SQLite right now already supports nested transactions to a 
limited degree, either 1 level (a SQL statement by itself) or 2 levels 
(a SQL statement within an explicit larger transaction).  I propose it 
extend this to N levels.


I understand, but this extension is a; non-standard, and b; unnecessary. 
Additional levels can only include additional actions. If those actions 
must be completed atomically, then a single transaction is sufficient. 
If not, then they don't need to be in the same transaction.
If you have 2 layers of explicit transactions, and the child 
transaction rolls back, and the parent one retains its changes prior 
to when the child started, then this behaviour is identical to a 
partially completed SQL statement rolling back on failure to the 
beginning of that statement, and the results of prior executed 
statements remaining.


If you have executed a series of SQL statements without a transaction 
active, the changes due to each statement are committed immediately when 
it ends. If you need the series to be atomic, you enclose them in a 
transaction. If you haven't enclosed them in a transaction, then you are 
implicitly saying you don't require the last one to complete 
successfully for the first ones to be considered successful.


Proper child transaction support, where rollback of a child does not 
rollback the parent, is needed to deal with the reality that SQL 
statements are recursive, and any given SQL statement is itself 
defined in terms of other SQL statements, to arbitrary levels, and a 
SQL statement needs to be atomic no matter what level of abstraction 
you are at.


This simply isn't true. SQL statements are not recursive, and SQL 
statements are not defined in terms of other SQL statements.
This is best illustrated with a stored procedure.  A stored procedure 
is a routine that contains one or more SQL statements.  Moreover, the 
invocation of said stored procedure is also a SQL statement.


Yes it is, and if the invocation is not included in a larger 
transaction, the invocation statement will succeed or fail depending 
upon the result of the procedure. If the procedure completes 
successfully, the invocation was successful, and the invocation 
statement's  implicit transaction is committed. 

However, if the invocation statement is one part of a larger 
transaction, then the changes are not committed until that larger 
transaction completes successfully. If the stored procedure fails, then 
the invocation statement will return an error. If the invocation 
statement fails, then the larger transaction can not be completed 
successfully, and so it should be rolled back.


If you think the prior statements in the larger transaction should be 
committed even if the stored procedure invocation  statement fails, then 
you are really saying that the invocation statement should not be part 
of the transaction.


Therefore, both the individual SQL statements in the procedure plus 
the entire invocation of the stored procedure each need to be atomic, 
and hence a transaction.


If only the SQL statements inside the procedure were atomic, and the 
call to the procedure wasn't, then we can end up with a situation 
where, if the procedure fails and aborts part way through completion, 
then from the point of view of the users, the single SQL statement 
that they wrote (which invoked the procedure) did not complete, but 
did not leave the database untouched either, and hence left an 
inconsistent state.  This violates ACID.


No, you are missing the point of a transaction. It's *all-or-nothing*. 
The individual statements in the stored procedure are not transactions. 
They are statements executed as part of the transaction that  is opened 
implicitly by the invocation statement, or as parts of a previously 
opened transaction. They themselves will never open a transaction 
implicitly because one will always be open when they execute, either one 
open by the invocation statement, or one that was already open when the 
invocation statement is executed.


Or another example, say you have a trigger (an implicitly invoked 
procedure) defined to happen when a table is mutated 
(eg,ins/upd/del).  From a user's point of view, everything that 
trigger does is part of the SQL statement that they invoked to mutate 
the table, and if the whole trigger isn't made atomic, then the user's 
ordinary SQL statement was not atomic from their POV.


The same 

Re: [sqlite] Data structure

[Mohd Radzi Ibrahim]

Hi,

If you are using C++, then try hash_map. I've used this on strings with more 
that 50,000 records - in memory. Very fast. Much easier to program than 
BerkeleyDB.



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

To: 
Sent: Wednesday, April 11, 2007 11:20 PM
Subject: Re: [sqlite] Data structure



On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote:

I think, looking from Lloyd's email address, (s)he might be limited to
what CDAC, Trivandrum might be providing its users.

Lloyd, you already know what size your data sets are. Esp. if it
doesn't change, putting the entire dataset in RAM is the best option.
If you don't need SQL capabilities, you probably can just use
something like BerkeleyDB or DBD::Deep (if using Perl), and that will
be plenty fast. Of course, if it can't be done then it can't be done,
and you will have to recommend more RAM for the machines (the CPU
seems fast enough, just the memory may be a bottleneck).


Sorry, I am not talking about the limitations of the system in our side,
but end user who uses our software. I want the tool to be run at its
best on a low end machine also.

I don't want the capabilities of a data base here. Just want to store
data, search for presence, remove it when there is no more use of it.

Surely I will check out BerkeleyDB. The data set must be in ram, because
the total size of it is very small. (Few maga bytes) I just want to
spped up the search, which is done millions of times.

Thanks,

LLoyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[John Stanton]

I used an approach similar to the Bloom Filter for data retrieval.  It 
could be very fast at retrieving substrings from large data sets but was 
fairly complex to implement.


I would not go with that approach unless you had some very broad 
retrieval requirements and a very large data set.


Lloyd wrote:
I was just wondering what the odds were of doing a better job than the 
filing system pros, how much time/code that would take on your part and 
how much that time would cost versus speccing a bigger/faster machine.


Martin



I am not fully clear. I just want my program to run at most efficient
and fast even on a low profile end user system. So I am in search for
the best data structure. 


I have heard about bloom filter, but in my case it is not applicable.
The final point is, I am just searching for a data structure which is
the result of lateral thinking by experts ;) (like bloom filter)

Thanks,
  Lloyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Data structure

[Gauthier, Dave]

There are many important variables to consider.  What kind of data?  How
many tables (if a relational DB approach even makes sense)? How many
records in each table?  How will this data be queried?  Many different,
often unperdicatable ways? Or just a couple static ways?  

There's nothing faster than having the data in RAM (if you can fit all
your data in what's available that is).  A fast approach might be to
write a C program that stores the data in a static array of a data
structure, sort that array according to what you expect to be querying
on, then use a b-tree search algorithm to query.  I've done this myself
in the past.  It's probably what the relational DB engines do "under the
hood".  

SQLite might be a good solution because it's in-memory, easy to use and,
if constructed right, runs fast.  It can also deal with unpredictable
queries from users.

So, you have to be a lot more specific.

-dave

-Original Message-
From: Lloyd [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 11, 2007 11:12 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Data structure


> 
> I was just wondering what the odds were of doing a better job than the

> filing system pros, how much time/code that would take on your part
and 
> how much that time would cost versus speccing a bigger/faster machine.
> 
> Martin

I am not fully clear. I just want my program to run at most efficient
and fast even on a low profile end user system. So I am in search for
the best data structure. 

I have heard about bloom filter, but in my case it is not applicable.
The final point is, I am just searching for a data structure which is
the result of lateral thinking by experts ;) (like bloom filter)

Thanks,
  Lloyd


__
Scanned and protected by Email scanner


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[John Stanton]

If it is just a read-only access to data then storing the data im memory 
with an index which can be either a hashing method or a binary tree 
would be the fastest.  An easy to handle method is to store the data and 
index in a flat file and load it into memory.  Loading it in virtual 
memory gives you more flexibility and gets more performance from your 
available memory because when memory is constrained only your most 
accessed data stays in memory rather than your application failing.


P Kishor wrote:

On 4/11/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:


Lloyd wrote:
> hi Puneet and Martin,
> On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote:
>> File system cache and plenty of RAM?
>>
>
> It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If 
you

> mean Swap space as file system cache, it is also limited, may be 2GB.

I was just wondering what the odds were of doing a better job than the
filing system pros, how much time/code that would take on your part and
how much that time would cost versus speccing a bigger/faster machine.




I think, looking from Lloyd's email address, (s)he might be limited to
what CDAC, Trivandrum might be providing its users.

Lloyd, you already know what size your data sets are. Esp. if it
doesn't change, putting the entire dataset in RAM is the best option.
If you don't need SQL capabilities, you probably can just use
something like BerkeleyDB or DBD::Deep (if using Perl), and that will
be plenty fast. Of course, if it can't be done then it can't be done,
and you will have to recommend more RAM for the machines (the CPU
seems fast enough, just the memory may be a bottleneck).





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[Lloyd]

On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote:
> I think, looking from Lloyd's email address, (s)he might be limited to
> what CDAC, Trivandrum might be providing its users.
> 
> Lloyd, you already know what size your data sets are. Esp. if it
> doesn't change, putting the entire dataset in RAM is the best option.
> If you don't need SQL capabilities, you probably can just use
> something like BerkeleyDB or DBD::Deep (if using Perl), and that will
> be plenty fast. Of course, if it can't be done then it can't be done,
> and you will have to recommend more RAM for the machines (the CPU
> seems fast enough, just the memory may be a bottleneck).

Sorry, I am not talking about the limitations of the system in our side,
but end user who uses our software. I want the tool to be run at its
best on a low end machine also. 

I don't want the capabilities of a data base here. Just want to store
data, search for presence, remove it when there is no more use of it.

Surely I will check out BerkeleyDB. The data set must be in ram, because
the total size of it is very small. (Few maga bytes) I just want to
spped up the search, which is done millions of times.

Thanks,

 LLoyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[Lloyd]

> 
> I was just wondering what the odds were of doing a better job than the 
> filing system pros, how much time/code that would take on your part and 
> how much that time would cost versus speccing a bigger/faster machine.
> 
> Martin

I am not fully clear. I just want my program to run at most efficient
and fast even on a low profile end user system. So I am in search for
the best data structure. 

I have heard about bloom filter, but in my case it is not applicable.
The final point is, I am just searching for a data structure which is
the result of lateral thinking by experts ;) (like bloom filter)

Thanks,
  Lloyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[P Kishor]

On 4/11/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:

Lloyd wrote:
> hi Puneet and Martin,
> On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote:
>> File system cache and plenty of RAM?
>>
>
> It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If you
> mean Swap space as file system cache, it is also limited, may be 2GB.

I was just wondering what the odds were of doing a better job than the
filing system pros, how much time/code that would take on your part and
how much that time would cost versus speccing a bigger/faster machine.




I think, looking from Lloyd's email address, (s)he might be limited to
what CDAC, Trivandrum might be providing its users.

Lloyd, you already know what size your data sets are. Esp. if it
doesn't change, putting the entire dataset in RAM is the best option.
If you don't need SQL capabilities, you probably can just use
something like BerkeleyDB or DBD::Deep (if using Perl), and that will
be plenty fast. Of course, if it can't be done then it can't be done,
and you will have to recommend more RAM for the machines (the CPU
seems fast enough, just the memory may be a bottleneck).


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[Martin Jenkins]

Lloyd wrote:

hi Puneet and Martin,
On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote:

File system cache and plenty of RAM?



It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If you
mean Swap space as file system cache, it is also limited, may be 2GB.


I was just wondering what the odds were of doing a better job than the 
filing system pros, how much time/code that would take on your part and 
how much that time would cost versus speccing a bigger/faster machine.


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Some questions on hierarchical data (nested set model)

[Jef Driesen]

Jef Driesen wrote:
I want to store a tree in an sqlite database. My first choice was the 
adjacency list model:


CREATE TABLE tree (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
parent_id INTEGER
);

But this method requires multiple queries to display the entire tree (or 
a subtree) in my GUI (a gtk+ treeview). Because childs can only be added 
to the treeview if all its parents are already added.


But then I found some resources on the nested set model [1,2]:

CREATE TABLE tree (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
lft INTEGER,
rgt INTEGER
);

Retrieving a (sub)tree can be done with only one sql query, at the 
expense of more complex queries to add or remove rows. Because all lft 
and rgt values to the right of the node have to be modified.


[1] http://www.sitepoint.com/article/hierarchical-data-database
[2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I start to understand this model, but I still have some questions:

Q3. How do I move a node (or subtree)?

In the adjacency list model, this is extremely easy by pointing the 
parent_id to another node. But I don't know how to do that in the nested 
set model.


I managed to solve this problem now. I had to write my solution (see
below) in pseudo sql code (e.g some extra non-sql code was required)
because sqlite does not support stored procedures. It think it is doable
to incorporate the IF/THEN/ELSE inside the sql query, but I didn't try
to do that.

a = @source_lft;
b = @source_rgt;
IF @source_lft < @target_lft THEN
 c = @b + 1;
 d = @target_rgt - 1;
 v =  (@d - @c + 1) = @target_rgt - (@b + 1);
 w = -(@b - @a + 1);
 e = @a;
 f = @d;
ELSE
 c = @target_rgt;
 d = @a - 1;
 v = -(@d - @c + 1) = @target_rgt - @a
 w =  (@b - @a + 1);
 e = @c;
 f = @b;
END IF;

UPDATE tree SET lft = lft + CASE
 WHEN lft BETWEEN @a AND @b THEN
@v /* Move the subtree up/down */
 ELSE
@w /* Make room for the subtree under the new parent */
 END
 WHERE lft BETWEEN @e AND @f;
UPDATE tree SET rgt = rgt + CASE
 WHEN rgt BETWEEN @a AND @b THEN
@v /* Move the subtree up/down */
 ELSE
@w /* Make room for the subtree under the new parent */
 END
 WHERE rgt BETWEEN @e AND @f;




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[Lloyd]

hi Puneet and Martin,
On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote:
> File system cache and plenty of RAM?
> 

It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If you
mean Swap space as file system cache, it is also limited, may be 2GB.


Puneet Kishor
> you haven't provided any further details about the data itself so I
> would venture to say -- get a computer with gobs of RAM (the new Mac
> Pro 8-cores can accomodate 16 GB), and use a memory based hash. Query
> away.


Each node of the data structure will contain only very little data (A
structure sizing 70 bytes and the key field would be of size maximum of
20 bytes). (I will see, what is memory based hash)

Thanks and Regards,
  Lloyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Data structure

[Gauthier, Dave]

I'm not sure I understand the question, but I'll take a stab at it
anyway.

If the data is to be loaded by and queried from the same program
execution, you may wnat to consider using a temporary table as opposed
to a regular (permanent) one that will go to disk.  The time you might
save has to do with SQLite not bothering to work with (slow) disk.  But
beware of taking this approach if you expect to exceed the amount of
memory available to you.

You can optimize query performance by creating indexes on the field(s)
you will be querying on.  For example, if the table contains employee
data, and one of the columns in that table is the employee's name, and
you expect to be retrieving data based on the employee's name, create an
index on the name column.  

Here's a pointer to some good info on performance tuning. It has helped
me.

http://katastrophos.net/andre/blog/2007/01/04/sqlite-performance-tuning-
and-optimization-on-embedded-systems/

Good Luck !

-Original Message-
From: Lloyd [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 11, 2007 9:12 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Data structure

Hi,
I don't know whether this is an irrelevant question in SQLite list, but
I don't see a better place to ask.

Which data structure is best to store and retrieve data very fastly?
There is a 95% chance that the searched data to be present in the data
structure. There will be 1000s of nodes in the data structure and it
will be searched millions of times. The data is not in any ordered form.

Thanks for your information,

Lloyd


__
Scanned and protected by Email scanner


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3BtreeMoveto broke between 3.3.12->3.3.14/15

[pompomJuice]

Indeed the query in the new code is prepared once and then reset and rebound
between queries, but maybe I must go double check if that was the case with
the code that performed poorly. I remember asking the developer if he did it
like that so I presumed it was done correctly when the answer was yes. 

Regardless I am glad that SQL is back on the menu as it makes configuring
non trivial lookups, well, trivial.

Your affinity suggestion is an interesting one and I will look into that
more carefully. With the new over designed code the affinities are kept the
same where possible but the previous concept code might have missed such
technicalities. I do believe though that the affinities are mostly sorted
out when the statement is prepared so from the lookup point of view it might
not have made a difference at all. I might be wrong on this. I do however
extract all data with sqlite3_column_text even though some are INTEGERS, but
I think somewhere it is mentioned that sqlite uses a simple snprintf to
accomplish this; something that I would have had to do anywais.

p


Andrew Finkenstadt wrote:
> 
> I'm speaking purely from an intellectual knowledge of "reading the docs"
> (and having been an Oracle database developer since 1990), but I would do
> two things in your coding of the SQL query:
> 
> 1. use a bind variable instead of inlining the SQL if you aren't already,
> so
> that you can prepare the statement once, and then bind & execute for each
> different sought key value.  I realize that you were giving examples of
> queries rather than actual code fragments, so this may be a moot point.
> 
> 2.  double-check the affinity of the column for text versus numeric
> values,
> and bind the corresponding type.  If the column has text affinity, then my
> understanding is that if the bound parameter will be converted to text
> prior
> to execution beginning, a perhaps unnecessary overhead.
> 
> --a
> 
> 
> 
> On 4/11/07, pompomJuice <[EMAIL PROTECTED]> wrote:
>>
>>
>> Done coding it back to using SQL to do the lookups and there seems to be
>> no
>> performance issues whatsoever O_o. Although I did not actually write the
>> previous implementation using SQL  to do the lookups  I remember going
>> over
>> the code and all seemed fine. No idea what went wrong there but I am
>> happy
>> I
>> dont have to mess around with the btree anymore, its a bit complicated.
>>
>> Thanks for the tip.
>>
>>
>> drh wrote:
>> >
>> > pompomJuice <[EMAIL PROTECTED]> wrote:
>> >> I could get a maximum of 300-400 lookups per second using
>> >> a conventional "select * from table where column = key" type query.
>> >
>> > I would guess, then, that either (1) table.column is not indexed
>> > or else (2) you are running on very, very slow hardware (a 386?),
>> > or (3) you are doing something terribly wrong in the query.  I
>> > just ran a quick test and I'm getting around 25000 queries/second
>> > using the Tcl interface (i.e. using a scripting language rather
>> > than calling the APIs directly) using a query of the form:
>> >
>> >SELECT * FROM table WHERE rowid=?
>> >
>> >>
>> >> Is there some other aspect such as for example "incKey" that causes
>> btree
>> >> lookups to function differently? I desperately need help here.
>> >>
>> >
>> > There were several subtle but important differences in the Btree
>> > layer interface going from 3.3.13->3.3.15.  But you need to understand
>> > that the Btree is a non-exported internal-use-only interface.  It is
>> > unsupported and is likely to change in strange and incomprehensible
>> > ways from one point release to the next, as indeed it has in each
>> > of the previous two releases.  I strongly discourage you from
>> > pursuing this path.
>> >
>> > --
>> > D. Richard Hipp  <[EMAIL PROTECTED]>
>> >
>> >
>> >
>> -
>> > To unsubscribe, send email to [EMAIL PROTECTED]
>> >
>> -
>> >
>> >
>> >
>>
>> --
>> View this message in context:
>> http://www.nabble.com/sqlite3BtreeMoveto-broke-between-3.3.12-%3E3.3.14-15-tf3547873.html#a9937387
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
>> -
>>
>>
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite3BtreeMoveto-broke-between-3.3.12-%3E3.3.14-15-tf3547873.html#a9939877
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: FTS does not support REPLACE

[Scott Hess]

On 4/11/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:

On Tue, 2007-04-10 at 09:26 -0700, Scott Hess wrote:
> On 4/10/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:
> > I checked the code and conflict handling mechanisms (OR ERROR,
> > OR ABORT, OR REPLACE) do not apply to virtual tables.
> 
> > Something to think about anyhow... Do we want conflict handling
> > for FTS (and other virtual modules)?
>
> I think OR REPLACE would be really unfortunate to lose, it's a pretty
> common idiom (especially using REPLACE in place of INSERT).
>
> Do we really need to have this expressed in the API?  If the virtual
> table returned SQLITE_CONSTRAINT from xUpdate(), and guaranteed that
> the table was unmodified, then the core should be able to implement
> all of the variants.  I can see how expressing it in the API might
> allow certain implementations to be more efficient.

SQLite could take care of it if the only constraint that can fail is
a duplicate rowid. Just by saying:

if( xUpdate(INSERT)==SQLITE_CONSTRAINT ){
  xUpdate(UPDATE);
}

and other variants for UPDATE etc.

But that would leave no way for the virtual table to implement
constraints related to other columns of the virtual table. At
least, that's my reason for thinking it would be better done as
a flag passed to the implementation of xUpdate().


That makes sense to me.

On the flip side, it seems to me like there are fewer cases to handle
for xUpdate().  The OR REPLACE case might effectively delete multiple
rows in the table, which couldn't be done unless the indices were
somehow exposed, or the conflict info was passed in.  Virtual tables
could conceivably implement a concept of OR REPLACE which simply
wouldn't make sense to SQL, so the latter is probably bother easier
and more flexible.  But wouldn't the other cases all be the same, with
xUpdate() returning SQLITE_CONSTRAINT and the core handling things
from there?

My assumption, here, is that sqlite has already factored things down
to the row level.  So, for an UPDATE OR FAIL who's WHERE clauses
touches 100 rows, but where the 51st update wants to fail with
SQLITE_CONSTRAINT, you do want the first 50 rows to be updated but not
the last 50.  But, if the virtual table does multiple operations to
implement a single-row update, you probably _don't_ want only half
those operations to persist (even if that doesn't corrupt the table's
backing).  Instead, you probably want xUpdate() to appear entirely
atomic.

Basically, the conflict handling on lang_conflict.html seems to me to
be implicitly operating at the statement level, with rows considered
atomic.

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[Martin Jenkins]

Lloyd wrote:

Hi,
I don't know whether this is an irrelevant question in SQLite list, but
I don't see a better place to ask.

Which data structure is best to store and retrieve data very fastly?
There is a 95% chance that the searched data to be present in the data
structure. There will be 1000s of nodes in the data structure and it
will be searched millions of times. The data is not in any ordered form.


File system cache and plenty of RAM?

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[P Kishor]

On 4/11/07, Lloyd <[EMAIL PROTECTED]> wrote:

Hi,
I don't know whether this is an irrelevant question in SQLite list, but
I don't see a better place to ask.

Which data structure is best to store and retrieve data very fastly?
There is a 95% chance that the searched data to be present in the data
structure. There will be 1000s of nodes in the data structure and it
will be searched millions of times. The data is not in any ordered form.




you haven't provided any further details about the data itself so I
would venture to say -- get a computer with gobs of RAM (the new Mac
Pro 8-cores can accomodate 16 GB), and use a memory based hash. Query
away.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3BtreeMoveto broke between 3.3.12->3.3.14/15

[Andrew Finkenstadt]

I'm speaking purely from an intellectual knowledge of "reading the docs"
(and having been an Oracle database developer since 1990), but I would do
two things in your coding of the SQL query:

1. use a bind variable instead of inlining the SQL if you aren't already, so
that you can prepare the statement once, and then bind & execute for each
different sought key value.  I realize that you were giving examples of
queries rather than actual code fragments, so this may be a moot point.

2.  double-check the affinity of the column for text versus numeric values,
and bind the corresponding type.  If the column has text affinity, then my
understanding is that if the bound parameter will be converted to text prior
to execution beginning, a perhaps unnecessary overhead.

--a



On 4/11/07, pompomJuice <[EMAIL PROTECTED]> wrote:



Done coding it back to using SQL to do the lookups and there seems to be
no
performance issues whatsoever O_o. Although I did not actually write the
previous implementation using SQL  to do the lookups  I remember going
over
the code and all seemed fine. No idea what went wrong there but I am happy
I
dont have to mess around with the btree anymore, its a bit complicated.

Thanks for the tip.


drh wrote:
>
> pompomJuice <[EMAIL PROTECTED]> wrote:
>> I could get a maximum of 300-400 lookups per second using
>> a conventional "select * from table where column = key" type query.
>
> I would guess, then, that either (1) table.column is not indexed
> or else (2) you are running on very, very slow hardware (a 386?),
> or (3) you are doing something terribly wrong in the query.  I
> just ran a quick test and I'm getting around 25000 queries/second
> using the Tcl interface (i.e. using a scripting language rather
> than calling the APIs directly) using a query of the form:
>
>SELECT * FROM table WHERE rowid=?
>
>>
>> Is there some other aspect such as for example "incKey" that causes
btree
>> lookups to function differently? I desperately need help here.
>>
>
> There were several subtle but important differences in the Btree
> layer interface going from 3.3.13->3.3.15.  But you need to understand
> that the Btree is a non-exported internal-use-only interface.  It is
> unsupported and is likely to change in strange and incomprehensible
> ways from one point release to the next, as indeed it has in each
> of the previous two releases.  I strongly discourage you from
> pursuing this path.
>
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
>
>

--
View this message in context:
http://www.nabble.com/sqlite3BtreeMoveto-broke-between-3.3.12-%3E3.3.14-15-tf3547873.html#a9937387
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] Data structure

[Lloyd]

Hi,
I don't know whether this is an irrelevant question in SQLite list, but
I don't see a better place to ask.

Which data structure is best to store and retrieve data very fastly?
There is a 95% chance that the searched data to be present in the data
structure. There will be 1000s of nodes in the data structure and it
will be searched millions of times. The data is not in any ordered form.

Thanks for your information,

Lloyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] File size issue?

[pompomJuice]

Hi,

I am no expert but try to increase your btree page size to the default page
size of your storage. I think sqlite defaults to a 1K page size but im sure
you can bump it up to 4K and see if that helps. I work with rather large
databases ( 5-8Gb ) and although increasing my page size from 1K to 8K (
unix server with 8k page size ), it made no difference but surely it has
to?.

Messing with the default_cache_size does make a massive difference,
especially on systems with lots of applications hogging the IO. I also
started by just increasing it to an arb number such as 2, but try to
work out how much of the system's memory you are willing to allocate to
SQLite and set it accordingly. For my 4-5Gb database I set my cache size so
that SQLite can use up to about 1Gb of memory. Easy to work out just take
the amount you want to allocate, say 500Mb and divide by the btree page size
and thats the number you set default_cache_size to.

Also, if you can somehow guarentee that the system wont crash ( which you
cant ) or if a database crash makes no difference and you can just rebuild
the DB ( or have a backup ) then do a "PRAGMA synchronous = OFF" or a safer
"PRAGMA synchronous = NORMAL". This makes a massive difference but at the
cost of losing your database if something goes wrong.

I hope it helps.


Kastuar, Abhitesh wrote:
> 
> Hi,
> 
> I am running into some issues that seem related to the current database
> file size.
> 
>  
> 
> Our application is periodically saving about 150MB of data to the
> database. Starting around the 30th interval or so, the time to insert
> the data grows significantly - it initially goes up by 3-4x but then
> each subsequent save cycle continues to grow eventually leading to
> performance that is 20x slower than it is at the beginning.
> 
>  
> 
> Some of the changes I have tried: 
> 
> -  larger transactions i.e. more inserts coalesced into one
> "giant" transaction
> 
> -  changing the default_cache_size to 2 
> 
>  
> 
> My next thought was to try "VACUUM"ing the database after each save
> cycle (cause I am seeing extensive disk fragmentation). However, I am
> not sure if that will eliminate the drastic performance degradation.
> 
>  
> 
> Our application is running on Windows XP (Core 2 Duo with 2G of RAM and
> 200GB hard drive)
> 
>  
> 
> Would appreciate any pointers
> 
>  
> 
> Thanks.
> 
> -Abhitesh.
> 
>  
> 
> 
> 
> 
> E-mail confidentiality.
> 
> This e-mail contains confidential and / or privileged information
> belonging to Spirent Communications plc, its affiliates and / or
> subsidiaries. If you are not the intended recipient, you are hereby
> notified that any disclosure, copying, distribution and / or the taking of
> any action based upon reliance on the contents of this transmission is
> strictly forbidden. If you have received this message in error please
> notify the sender by return e-mail and delete it from your system. If you
> require assistance, please contact our IT department at
> [EMAIL PROTECTED]
> 
> Spirent Communications plc,
> Spirent House, Crawley Business Quarter, Fleming Way, Crawley, West
> Sussex, RH10 9QL, United Kingdom.
> Tel No. +44 (0) 1293 767676
> Fax No. +44 (0) 1293 767677
> 
> Registered in England Number 470893
> Registered at Spirent House, Crawley Business Quarter, Fleming Way,
> Crawley, West Sussex, RH10 9QL, United Kingdom 
> 
> Or if within the US,
> 
> Spirent Communications,
> 26750 Agoura Road, Calabasas, CA, 91302, USA.
> Tel No. 1-818-676- 2300 
> 
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/File-size-issue--tf3555473.html#a9937397
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3BtreeMoveto broke between 3.3.12->3.3.14/15

[pompomJuice]

Done coding it back to using SQL to do the lookups and there seems to be no
performance issues whatsoever O_o. Although I did not actually write the
previous implementation using SQL  to do the lookups  I remember going over
the code and all seemed fine. No idea what went wrong there but I am happy I
dont have to mess around with the btree anymore, its a bit complicated.

Thanks for the tip.


drh wrote:
> 
> pompomJuice <[EMAIL PROTECTED]> wrote:
>> I could get a maximum of 300-400 lookups per second using
>> a conventional "select * from table where column = key" type query.
> 
> I would guess, then, that either (1) table.column is not indexed
> or else (2) you are running on very, very slow hardware (a 386?),
> or (3) you are doing something terribly wrong in the query.  I
> just ran a quick test and I'm getting around 25000 queries/second
> using the Tcl interface (i.e. using a scripting language rather
> than calling the APIs directly) using a query of the form:
> 
>SELECT * FROM table WHERE rowid=?
> 
>> 
>> Is there some other aspect such as for example "incKey" that causes btree
>> lookups to function differently? I desperately need help here.
>> 
> 
> There were several subtle but important differences in the Btree
> layer interface going from 3.3.13->3.3.15.  But you need to understand
> that the Btree is a non-exported internal-use-only interface.  It is
> unsupported and is likely to change in strange and incomprehensible
> ways from one point release to the next, as indeed it has in each
> of the previous two releases.  I strongly discourage you from 
> pursuing this path.
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite3BtreeMoveto-broke-between-3.3.12-%3E3.3.14-15-tf3547873.html#a9937387
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and nested transactions

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > It may be more difficult to implement this in a backwards-compatible
> > way such that older versions of SQLite can rollback a journal created
> > by a newer version if it encounters one.
> 
> I wonder if there are many projects that have different versions of 
> SQLite updating and reading the same database file at the same time.
> This can't be very common.
> 

As we learned from the release of 3.3.0, this is more common
that you might expect.  There are surprisingly many projects 
that expect to be able to access SQLite databases from both 
older and newer versions of the library.

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: FTS does not support REPLACE

[Dan Kennedy]

On Tue, 2007-04-10 at 09:26 -0700, Scott Hess wrote:
> On 4/10/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:
> > I checked the code and conflict handling mechanisms (OR ERROR,
> > OR ABORT, OR REPLACE) do not apply to virtual tables.
> 
> > Something to think about anyhow... Do we want conflict handling
> > for FTS (and other virtual modules)?
> 
> I think OR REPLACE would be really unfortunate to lose, it's a pretty
> common idiom (especially using REPLACE in place of INSERT).
> 
> Do we really need to have this expressed in the API?  If the virtual
> table returned SQLITE_CONSTRAINT from xUpdate(), and guaranteed that
> the table was unmodified, then the core should be able to implement
> all of the variants.  I can see how expressing it in the API might
> allow certain implementations to be more efficient.

SQLite could take care of it if the only constraint that can fail is
a duplicate rowid. Just by saying:

if( xUpdate(INSERT)==SQLITE_CONSTRAINT ){
  xUpdate(UPDATE);
}

and other variants for UPDATE etc.

But that would leave no way for the virtual table to implement 
constraints related to other columns of the virtual table. At
least, that's my reason for thinking it would be better done as
a flag passed to the implementation of xUpdate().

Dan.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-