[sqlite] Questions on views

[Dennis Volodomanov]

Hello all,
 
Is it quicker (slower or the same) to execute a query on a view or to
execute the original query from which this view was created?
 
I'm basically looking for the best (fastest) way to execute thousands of
queries to check whether they return any results or not. At the moment
I'm doing a COUNT, but I'm hoping to find an easier way than to execute
count thousands of times...
 
Thank you in advance,
 
   Dennis

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

Re: [sqlite] Index creation on huge table will never finish.

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > 
> > As for the stats from sqlite3_analyzer, they seem to be in the right 
> > ballpark.
> > But I'm not sure its heuristic accounts for rows that are significantly 
> > larger
> > than the page size, though. In such cases I am seeing higher than expected 
> > fragmentation after a VACUUM. This is just a guess, of course.
> 
> I'm not sure sqlite3_analyzer does any of the fragmentation
> measurement right.  For that matter, how do you measure
> fragmentation with a number?  (Suggestions are welcomed.)
> 
> I may yet just yank that whole fragmentation measurement
> idea.

With non-volatile RAM drives getting larger and cheaper by the day, 
you may not need to worry about fragmentation, fsync and disk-seek time 
in a year or two.



 

Expecting? Get great news right away with email Auto-Check. 
Try the Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html 

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

Re: [sqlite] Index creation on huge table will never finish.

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> 
> As for the stats from sqlite3_analyzer, they seem to be in the right ballpark.
> But I'm not sure its heuristic accounts for rows that are significantly larger
> than the page size, though. In such cases I am seeing higher than expected 
> fragmentation after a VACUUM. This is just a guess, of course.
> 

I'm not sure sqlite3_analyzer does any of the fragmentation
measurement right.  For that matter, how do you measure
fragmentation with a number?  (Suggestions are welcomed.)

I may yet just yank that whole fragmentation measurement
idea.

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


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

Re: [sqlite] Index creation on huge table will never finish.

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > 
> > See also: Changes to support fragmentation analysis in sqlite3_analyzer.
> > http://www.sqlite.org/cvstrac/chngview?cn=3634
> > 
> 
> I'm not real sure those patches are working right.
> I need to revisit that whole fragmentation analysis
> thing before the next release.  Somebody please
> remind me

What do you suspect is not working right?

I've run the new CVS VACUUM on large databases without any apparent ill effect
and it's noticably faster than the previous VACUUM implementation.

As for the stats from sqlite3_analyzer, they seem to be in the right ballpark.
But I'm not sure its heuristic accounts for rows that are significantly larger
than the page size, though. In such cases I am seeing higher than expected 
fragmentation after a VACUUM. This is just a guess, of course.


 

Expecting? Get great news right away with email Auto-Check. 
Try the Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html 

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

Re: [sqlite] Index creation on huge table will never finish.

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> 
> See also: Changes to support fragmentation analysis in sqlite3_analyzer.
> http://www.sqlite.org/cvstrac/chngview?cn=3634
> 

I'm not real sure those patches are working right.
I need to revisit that whole fragmentation analysis
thing before the next release.  Somebody please
remind me
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Index creation

[Joe Wilson]

--- turnleftjohn <[EMAIL PROTECTED]> wrote:
> the production system, and after 4 hours no index.  I can't detect any
> activity at all. The journal file and the .db file just sit at the same size
> for 4 hours.  Why is this failing?  It seems like it is just sitting there
> doing nothing.  When I created the test index, I noticed the journal file
> changing and the .db file changing during the 2.5 hours to create.  On the
> production .db file, nothing is happening.  I have all associated processes
> killed that ineract with the db file, so I know it is not locked.

If all else fails...

Attach strace to the seemingly idle process to see if it is making any 
system calls:

 strace -p pid

You might also use http://sourceforge.net/projects/lsstack/ to get a live
stack trace of the Linux process. (Or use gdb to attach to the live process).



 

Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

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

Re: [sqlite] Index creation

[Joe Wilson]

--- turnleftjohn <[EMAIL PROTECTED]> wrote:
> improved dramatically. So I attempted the creation of the index off hours on
> the production system, and after 4 hours no index.  I can't detect any
> activity at all. The journal file and the .db file just sit at the same size
> for 4 hours.  Why is this failing?  It seems like it is just sitting there
> doing nothing.  When I created the test index, I noticed the journal file
> changing and the .db file changing during the 2.5 hours to create.  On the
> production .db file, nothing is happening.  I have all associated processes
> killed that ineract with the db file, so I know it is not locked.

Run lsof on the production database file just in case there is some contention. 
(You never know).

I assume that the copied "test" database was indexed immediately after its
creation. If this was the case then the entire file may have been in the OS
cache resulting in very quick indexing. Try running "wc prod.db" or 
"cat prod.db >/dev/null" and then creating the indexes on prod.db to see 
what happens.


 

8:00? 8:25? 8:40? Find a flick in no time 
with the Yahoo! Search movie showtime shortcut.
http://tools.search.yahoo.com/shortcuts/#news

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

Re: [sqlite] data type problem

[qinligeng]

Thanks for you all.
I don't know if [char](32) is valid standard SQL or not. 
But the MS use this type in SQLServer2000(and above?).
The SQL statements that I used to create tables in SQLite3 was auto generated 
by my SQLServer2000 EnterpriseManager.
And SQLite3 did not report any error about this. It likes a trap for me.
What ever, I have reported this as a bug in www.sqlite.org, and, I will also 
choose my columnnames carefully the next time :)
- Original Message - 
From: "Kees Nuyt" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 23, 2007 4:55 AM
Subject: Re: [sqlite] data type problem


> On Tue, 20 Mar 2007 13:24:17 +0800, you wrote:
> 
>>if you create a table use following statement (script generated from MS SQL 
>>Server 2000)
>>CREATE TABLE [XTollData] (
>> [DutyID] [char] (32) NOT NULL ,
>> [CarNumber] [char] (10) NULL 
>>);
>>
>>SQLite3_Column_decltype will treat DutyID as data type 'char' but not 
>>'char(32)'
> 
> I don't think [char] (32) in
> [DutyID] [char] (32) NOT NULL ,
> is valid SQL. Keywords can be quoted with "" or [] to be able to
> use them as identifiers (i.e. objectnames), but IMHO keywords
> that have to stay keywords shouldn't be quoted at all.
> 
> If you choose your columnnames carefully (not contained in the
> collection of reserved words), the [] could easily be filtered
> out with sed or awk.
> -- 
>  (  Kees Nuyt
>  )
> c[_]
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
>

[sqlite] Index creation

[turnleftjohn]

I am new to sqlite.  I have done some reading up.  I have a table that I am
having difficulty creating an index on.  The table holds ~180 million rows,
simple four column, integer schema.  It is taking up about 1.5 gigs of
space.  I inherited this application and it is slowing down.  The table has
a primary key using the three columns, but delete and inserts are slow. I
can't afford to lock up the production table to create the index so I am
running some off line tests.  I have a copy of the database, and I was able
to create an index on each column in about 2.5 hours.  Inserts and deleted
improved dramatically. So I attempted the creation of the index off hours on
the production system, and after 4 hours no index.  I can't detect any
activity at all. The journal file and the .db file just sit at the same size
for 4 hours.  Why is this failing?  It seems like it is just sitting there
doing nothing.  When I created the test index, I noticed the journal file
changing and the .db file changing during the 2.5 hours to create.  On the
production .db file, nothing is happening.  I have all associated processes
killed that ineract with the db file, so I know it is not locked.
-- 
View this message in context: 
http://www.nabble.com/Index-creation-tf3451503.html#a9627719
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Index creation on huge table will never finish.

[Martin Jenkins]

P Kishor wrote:
Mac/Unix person meself, but the Windows XP sort is pretty darn good as 
well.


I'll take a look. Last time I used it it was useless. Win9x days? these 
days (especially for a one off) I'd probably go straight to doing it in 
Python to avoid x-platform syntax issues.


Martin

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

Re: [sqlite] Index creation on huge table will never finish.

[Chris Jones]

Ah yes, I should read more carefully :)  

Thanks, right, I was actually guaranteeing uniqueness originally by just
fetching and then inserting only if there wasn't a match (I needed a rowid
if the row existed anyway).  Now I'm guaranteeing uniqueness by letting sort
do the work for me, but similarly to my last response, it probably makes
sense for me to add it to my schema as a sanity check if nothing else.

Thanks,
Chris



Derrell.Lipman wrote:
> 
> Chris Jones <[EMAIL PROTECTED]> writes:
> 
>> Derrell.Lipman wrote:
> 
> 
> So to guarantee that the *strings* are unique, you need a UNIQUE index on
> the
> string field.  The ROWID is the INTEGER PRIMARY KEY whether you specify a
> different name for it or not, but that will not guarantee that each of
> your
> *strings* is unique.  Only a UNIQUE index on the string field will do
> that.
> 
> As long as you understand this, no need to reply.
> 
> Cheers,
> 
> Derrell
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Index-creation-on-huge-table-will-never-finish.-tf3444218.html#a9626741
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Index creation on huge table will never finish.

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Gerry Snyder <[EMAIL PROTECTED]> wrote:
> > Chris Jones wrote:
> > > Hi all,
> > > 
> > > I have a very simple schema.  I need to assign a unique identifier to a
> > > large collection of strings, each at most 80-bytes, although typically
> > > shorter.
> > > 
> > > The problem is I have 112 million of them.
> > 
> > Maybe you could start by breaking the data into 8 equal groups and make 
> > a table of each group. Then merge the original groups pairwise, then 
> > merge those 4 groups, and finally the 2 semifinal groups (kinda like 
> > March Madness, come to think of it). Since each merging will be of 
> > already sorted/indexed data, it might save a lot of time.
> > 
> 
> This is the right idea.
> 
> The problem is that your working set is bigger than your cache
> which is causing thrashing.  I suggest a solution like this:
> 
> Add entries to table ONE until the table and its unique index get
> so big that they no longer fit in cache all at once.  Then
> transfer ONE into TWO like this:
> 
>INSERT INTO two SELECT * FROM one ORDER BY unique_column;
> 
> The ORDER BY is important here.
> 
> Do the above a time or two until TWO is signficantly larger than ONE.
> Then do the same into TWO_B.  Later combine TWO and TWO_B into THREE:
> 
>INSERT INTO three SELECT * FROM two ORDER BY unique_column;
>INSERT INTO three SELECT * FROM two_b ORDER BY unique_column;
> 
> Repeat as necessary for FOUR, FIVE, SIX and so forth.

I've tried something like the algorithm you've proposed and it's much
slower than pre-sorting all the data prior to bulk insert.
It may have something to do with the cost of multiple repeated inserts 
for each original row.

Here's a different attempt at speeding up bulk insert following your
suggestion from Ticket 2075:

 http://www.mail-archive.com/sqlite-users%40sqlite.org/msg22143.html

Any suggestions to speed it up are welcome.
Sample insert speed test included.


 

Don't get soaked.  Take a quick peek 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] Index creation on huge table will never finish.

[Gerry Snyder]

Chris Jones wrote:






I probably should have made this more explicit, but in sqlite, every row has
a unique identifier named rowid, which exists even if it isn't explicity
declared in the schema, and I was depending on that.   If you declare a
PRIMARY KEY, then this replaces rowid.


A tiny correction: a column will not replace rowid unless it is
INTEGER PRIMARY KEY.

From the web page:

Specifying a PRIMARY KEY normally just creates a UNIQUE index on the 
corresponding columns. However, if primary key is on a single column 
that has datatype INTEGER, then that column is used internally as the 
actual key of the B-Tree for the table.


Gerry

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

Re: [sqlite] Index creation on huge table will never finish.

[Derrell . Lipman]

Chris Jones <[EMAIL PROTECTED]> writes:

> Derrell.Lipman wrote:
>> 
>> Chris Jones <[EMAIL PROTECTED]> writes:
>> 
>> I don't think that your original solution solves that problem either.  You
>> first posted this schema:
>> 
>>> My schema looks as follows:
>>> 
>>> CREATE TABLE rawfen ( fen VARCHAR(80) );
>>> CREATE INDEX rawfen_idx_fen ON rawfen(fen);
>> 
>> but you didn't indicate that rawfen_idx_fen is a UNIQUE INDEX so it won't
>> complain if there are duplicate strings.  To accomplish this (but not
>> solving
>> your timing issue), you need this:
>> 
>>   CREATE TABLE rawfen ( fen VARCHAR(80) );
>>   CREATE UNIQUE INDEX rawfen_idx_fen ON rawfen(fen);
>> 
>
> I probably should have made this more explicit, but in sqlite, every row has
> a unique identifier named rowid, which exists even if it isn't explicity
> declared in the schema, and I was depending on that.   If you declare a
> PRIMARY KEY, then this replaces rowid.
>
> Of course, it's probably better practice to explicitly declare the primary
> key, but anyway, that's where I was going with it.

Those are two separate issues.  Your declared problem was:

> I don't think that solves my problem.  Sure, it guarantees that the IDs are
> unique, but not the strings.  

So to guarantee that the *strings* are unique, you need a UNIQUE index on the
string field.  The ROWID is the INTEGER PRIMARY KEY whether you specify a
different name for it or not, but that will not guarantee that each of your
*strings* is unique.  Only a UNIQUE index on the string field will do that.

As long as you understand this, no need to reply.

Cheers,

Derrell

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

[sqlite] FW: invoking sqlite3 command as a superuser

[Rafi Cohen]

Hi, 2 days passed since I've sent this message to the list, but I
received no replies so far.
As I thought that may be not everybody received it, I resend it one more
time.
I also feel that my message might be unclear, so I also juggest to try
it and give "steps to reproduce" below.
Before this, I would like to emphasize that I'm using linux susse 9.3
here and I did not try this on any other system, but I hope some of you
can shed some light on this problem and -- even better -- offer some
posible solution.
Steps to reproduce:
1. login as a regular user with your own username.
2. create a small file (let's call it import.txt) with some rows similar
to the construct of a table you'll create in the next step.
3. type: sqlite3 test (test being the database).
4. in sqlite3, enter: create table tbl(with appropriate column list);
5. Then enter: .import import.txt tbl in order to insert the rows into
tbl that you wrote in step 2.
6. Enter: select * from tbl; you'll probably see the rows you just
inserted through the file, as expected.
7. enter: delete from tbl; in order to reinsert those lines again next
time.
8. exit sqlite3 (ctrl-d).
9. Now, at the shell command line enter the command to be a super user:
su, ENTER, password (if any, probably there is. (I have this on this
system, since I'm working remotely on a company's computer from home and
it is, of course, multiuser system).
10. Now, invoke again sqlite3 test.
11. enter: .import import.txt tbl
12. Then enter: select * from tbl; this time, at least in my case, I get
nothing, as like the table is empty, although .import did not output any
error.
This is, from my point of view, a very strange and anoying behavior.
Now, if you read on, you'll understand why I need this way of usage.
Yes, I may solve this avoiding use of .import, but this way seems neat
and quicker.
I hope somebody will lead me towards a solution, or at least explain
this strange behavior.
Sorry for the lengthy message, thanks, Rafi.
-Original Message-
From: Rafi Cohen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 20, 2007 8:06 PM
To: 'sqlite-users@sqlite.org'
Subject: inv ok i ng sqlite3 command as a superuser


Hi, for various reasons, I preffer to invoke my application as the
superuser.
This, in turn, invokes sqlite3 command to apply a .import command from
file to table.
Although .import does not return any error, the table remains empty.
So, I manually entered to sqlite3 and applied the .import command. Most
surprisingly, as a superuser it does just nothing, as ignored.
But when I do just the same as a regular user, it works with no problem.
So, first, can anybody confirm this or reffer me to other reasons for
this? And second, does anybody have any solution for this? As I said, I
have to run my application as a superuser as it has to open and
communicate with serial ports.
Thanks, Rafi.

Re: [sqlite] Index creation on huge table will never finish.

[Dennis Cote]

Chris Jones wrote:

So, I did a "sort -u -S 1800M fenout.txt > fenoutsort.txt"

The sort took about 45 minutes, which is acceptable for me (it was much
longer without the -S option to tell it to make use of more memory), and
then loading the table was very efficient.  Inserting all the rows into my
table in sorted order took only 18 minutes.  



  
The sort command is more sophisticated than its usage info would lead 
one (at least me) to believe.


After I saw your post I checked the sort source and found that it does 
all the splitting, sorting, and merging that I had suggested internally 
when given a file bigger than its allowed buffer space. No need for any 
scripts at all.


I'm glad you found a suitable solution.

Dennis Cote



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

Re: [sqlite] Index creation on huge table will never finish.

[Joe Wilson]

--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> You could also improve the locality in the database file further by 
> running a vacuum command after it has been created. this will move the 
> pages around so that the page of the table are contiguous and so are the 
> pages of the index, rather than having them interspersed with each other.

In SQLite 3.3.13 and earlier, VACUUM does indeed intermix the table pages 
with the index pages leading to fragmentation. It's also quite slow on large 
tables with multiple indexes unless you use a large cache.

This patch in CVS address these problems:
http://www.sqlite.org/cvstrac/chngview?cn=3643

See also: Changes to support fragmentation analysis in sqlite3_analyzer.
http://www.sqlite.org/cvstrac/chngview?cn=3634



 

Need Mail bonding?
Go to the Yahoo! Mail Q for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list=396546091

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

Re: [sqlite] beginner's questions: atexit problem

[Joe Wilson]

I haven't heard of Dev-Cpp.
If you use MinGW gcc from http://mingw.org/ and MSYS, you shouldn't have any 
problems.

--- timm2 <[EMAIL PROTECTED]> wrote:
> I use MMinGW as it was installed by Dev-Cpp, I think, thera are no Cygwin 
> files.
> Tim
> 
> > Are you mixing Cygwin and MinGW libraries and/or header files?
> > 
> > --- timm2 <[EMAIL PROTECTED]> wrote:
> > > Using dev-c++ 4.9.9.2-wx-beta-6.8 with mingw 3.4.2 I have got the error:
> > > 
> > > sqlite3.A(.text+0x44):fake: multiple definition of `atexit'
> > > C:/../lib/gcc/mingw32/3.4.2/../../../crt2.o(.text+0x260):crt1.c: first 
> > > defined here



 

Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

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

Re: [sqlite] INSERT or REPLACE

[P Kishor]

Oracle has MERGE. Search for UPSERT and SQL Server.

On 3/22/07, Doug <[EMAIL PROTECTED]> wrote:

The conflict algorithms in SQLite are brilliant.  It's exactly what I've
needed, and after working with SQLite for so long, I had forgotten that it
wasn't part of the SQL standard (as far as I know--but it should be!!!)

For those of you using MS SQL or Oracle, is there any way to get the same
result without first selecting and then doing an insert/update depending on
what I find?  (ie I want to put a value into a table.  If it's there fine,
and if not, just add it).  I know I should ask this in a MS SQL forum, but I
figure you folks are quite familiar with the end goal (which is to make MS
SQL behave like SQLite).

Thanks for any ideas.

Doug


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





--
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] Re: Missing tables in sqlite_master

[Christian Mattar]

Hi Igor,

Igor Tandetnik wrote:

http://www.phpbuilder.com/manual/en/function.sqlite-fetch-array.php

"Fetches the next row from the given result handle. If there are no more 
rows, returns FALSE, otherwise returns an associative array representing 
the row data."


You need to call it repeatedly to retrieve all the rows.


ok, now I feel stupid :-). Thank you very much for your kind help.

-Christian

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

[sqlite] INSERT or REPLACE

[Doug]

The conflict algorithms in SQLite are brilliant.  It's exactly what I've
needed, and after working with SQLite for so long, I had forgotten that it
wasn't part of the SQL standard (as far as I know--but it should be!!!)

For those of you using MS SQL or Oracle, is there any way to get the same
result without first selecting and then doing an insert/update depending on
what I find?  (ie I want to put a value into a table.  If it's there fine,
and if not, just add it).  I know I should ask this in a MS SQL forum, but I
figure you folks are quite familiar with the end goal (which is to make MS
SQL behave like SQLite).

Thanks for any ideas.

Doug


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

[sqlite] Re: Missing tables in sqlite_master

[Igor Tandetnik]

Christian Mattar <[EMAIL PROTECTED]> wrote:

I've been having trouble with SQLite in PHP. Basically I want to
iterate over all table of a database. I use the following query:

$handle = sqlite_open("db.sqlite");
$result = sqlite_query($handle, "SELECT name FROM sqlite_master WHERE
type='table' ORDER BY name");
$tables = sqlite_fetch_array($result);
var_dump($tables);

Unfortunately, it only returns the first table name in the result.


http://www.phpbuilder.com/manual/en/function.sqlite-fetch-array.php

"Fetches the next row from the given result handle. If there are no more 
rows, returns FALSE, otherwise returns an associative array representing 
the row data."


You need to call it repeatedly to retrieve all the rows.

Igor Tandetnik 



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

[sqlite] Missing tables in sqlite_master

[Christian Mattar]

Hi everyone!

I've been having trouble with SQLite in PHP. Basically I want to iterate
over all table of a database. I use the following query:

$handle = sqlite_open("db.sqlite");
$result = sqlite_query($handle, "SELECT name FROM sqlite_master WHERE
type='table' ORDER BY name");
$tables = sqlite_fetch_array($result);
var_dump($tables);

Unfortunately, it only returns the first table name in the result. If I
change the ORDER BY clause, the first table name of the according new
order gets returned.

If I run the same query in SQLiteBrowser, it returns all table names.
I do nothing else with the database in the PHP script. I've tried
exporting the database into SQL Commands and reimporting it, without any
change in behaviour.
Maybe someone has an idea about what I'm doing wrong?

-Christian


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

Re: [sqlite] data type problem

[Kees Nuyt]

On Tue, 20 Mar 2007 13:24:17 +0800, you wrote:

>if you create a table use following statement (script generated from MS SQL 
>Server 2000)
>CREATE TABLE [XTollData] (
> [DutyID] [char] (32) NOT NULL ,
> [CarNumber] [char] (10) NULL 
>);
>
>SQLite3_Column_decltype will treat DutyID as data type 'char' but not 
>'char(32)'

I don't think [char] (32) in
[DutyID] [char] (32) NOT NULL ,
is valid SQL. Keywords can be quoted with "" or [] to be able to
use them as identifiers (i.e. objectnames), but IMHO keywords
that have to stay keywords shouldn't be quoted at all.

If you choose your columnnames carefully (not contained in the
collection of reserved words), the [] could easily be filtered
out with sed or awk.
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Index creation on huge table will never finish.

[John Stanton]

A fast technique to achieve your objective is to perform what I believe 
is called a "monkey puzzle" sort.  The data is not moved, instead an 
array of descriptors to each element is sorted.  The output is realized 
by scanning the list of descriptors and picking up the associated record 
from the input list.


Using a modest machine your application should run in less than ten 
minutes using that method.  One way we use it is as a first stage in 
building a B-Tree index rapidly.


Chris Jones wrote:

Thanks everyone for your feedback.

I ended up doing a presort on the data, and then adding the data in order.  
At first I was a little concerned about how I was going to implement an

external sort on a data set that huge, and realized that the unix "sort"
command can handle large files, and in fact does it pretty efficiently.

So, I did a "sort -u -S 1800M fenout.txt > fenoutsort.txt"

The sort took about 45 minutes, which is acceptable for me (it was much
longer without the -S option to tell it to make use of more memory), and
then loading the table was very efficient.  Inserting all the rows into my
table in sorted order took only 18 minutes.  


So, all in all, I can now load the table in just about an hour, which is
great news for me.

Thanks!
Chris




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

Re: [sqlite] Index creation on huge table will never finish.

[John Stanton]

An issue with cache is cache shadowing, the churning as data is copied 
from one cache to another to another.


An example is the speed-up achieved on network accesses by using 
sendfile or TransmitFile and bypassing up to four levels of buffering 
for a message being despatched to a network interface using send or similar.


Another is opening a file using open and writing to it with write and 
reading with read when you are transferring buffers full of data to or 
from a file.  You avoid the extra level of buffer copying inherent in 
fopen.  If you dispense with the reads and writes and access the virtual 
memory directly you get a further win.


A modern OS uses main memory as a file system cache by virtue of its 
virtual memory capability.  If you take advantage of that your 
applications will run faster.


P Kishor wrote:

On 3/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"P Kishor" <[EMAIL PROTECTED]> wrote:
> Richard,
>
> On 3/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> ...
> > The problem is that your working set is bigger than your cache
> > which is causing thrashing.  I suggest a solution like this:
> >
> > Add entries to table ONE until the table and its unique index get
> > so big that they no longer fit in cache all at once.
>
> What is this "cache" you talk about? Is this the hard disk cache, or
> some kind of OS-level cache, or a SQLite level cache?

All of the above.

> How can I find
> the size of this, and is this cache user-adjustable? If yes, how?
>

On Linux, the "top" command will show you how much OS disk
cache you are currently using.  The hard disk cache is usually
small enough that it can be neglected.  The OS cache is also
usually much larger than SQLite's own internal cache.

You might run an experiment where you start inserting and
timing each insert, then switch to a different table when
the insert speed drops below a threshold.




ok. I was hoping for something magical like 'showme_cache_size'. This
sounds more voodoo-ish, but do-able. In any case, it doesn't seem that
this OS cache is user-adjustable, at least not without pipe wrenches
and WD-40.

On a note more related to the OP, even if one could adjust the cache
to the working set (and that would be a moving target -- either make
the cache gigantic, or keep on querying the relative sizes of the two
and somehow keep on adjusting the cache, the time taken to determine
uniqueness in a bigger working set will keep on increasing as the
working set itself keeps on increasing... the curve won't be flat.




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

Re: [sqlite] Index creation on huge table will never finish.

[P Kishor]

On 3/22/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:

Chris Jones wrote:
> realized that the unix "sort"

If I'd known you were on 'nix I'd have suggested using 'sort' and/or
'md5sum' about 12 hours ago. ;)




Mac/Unix person meself, but the Windows XP sort is pretty darn good as well.


--
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] Index creation on huge table will never finish.

[Martin Jenkins]

Chris Jones wrote:

realized that the unix "sort"


If I'd known you were on 'nix I'd have suggested using 'sort' and/or 
'md5sum' about 12 hours ago. ;)


Martin

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

Re: [sqlite] Index creation on huge table will never finish.

[John Stanton]

You could sort the table then perform a merge which removes duplicates.

Chris Jones wrote:

I don't think that solves my problem.  Sure, it guarantees that the IDs are
unique, but not the strings.  


My whole goal is to be able to create a unique identifier for each string,
in such a way that I dont have the same string listed twice, with different
identifiers.

In your solution, there is no way to lookup a string to see if it already
exists, since there is no index on the string.

Thanks,
Chris



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

Re: [sqlite] Re: Re: Re: Question about multithreading

[Rich Rattanni]

On 3/22/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

Voxen <[EMAIL PROTECTED]> wrote:
>> There's no "like" - you do use connection pointer directly from
>> thread B. The fact that the piece of code thread B currently
>> executes is a method of an object that happened to be created by
>> thread A is immaterial.
>
> That clears things and it shows me I need to open/close the database
> locally when a method is called by several threads.

Well, you can have each thread open a connection, then pass it along as
a parameter to whatever function the thread needs to call. This way, the
method would always operate on a connection associated with whatever
thread were calling it, and you won't have to keep opening and closing
connections all the time.

Igor Tandetnik


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




Just be careful with this, because if you have multiple threads
requesting this shared connection you will have to use some kind of
mutual exclusion technique to allow only one thread to access the
connection at a time.

--
Rich

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

Re: [sqlite] beginner's questions: atexit pr oblem

[timm2]

I use MMinGW as it was installed by Dev-Cpp, I think, thera are no Cygwin files.
Tim

> Are you mixing Cygwin and MinGW libraries and/or header files?
> 
> --- timm2 <[EMAIL PROTECTED]> wrote:
> > Using dev-c++ 4.9.9.2-wx-beta-6.8 with mingw 3.4.2 I have got the error:
> > 
> > sqlite3.A(.text+0x44):fake: multiple definition of `atexit'
> > C:/../lib/gcc/mingw32/3.4.2/../../../crt2.o(.text+0x260):crt1.c: first 
> > defined here
> > 
> > I did not find helpfull hint in archive of conference. 
> > Could you help me?
> >  Thanks for any hint,
> > Tim
> 
> 
> 
> 
>  
> 
> Need Mail bonding?
> Go to the Yahoo! Mail Q for great tips from Yahoo! Answers users.
> http://answers.yahoo.com/dir/?link=list=396546091
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Index creation on huge table will never finish.

[Chris Jones]

Thanks everyone for your feedback.

I ended up doing a presort on the data, and then adding the data in order.  
At first I was a little concerned about how I was going to implement an
external sort on a data set that huge, and realized that the unix "sort"
command can handle large files, and in fact does it pretty efficiently.

So, I did a "sort -u -S 1800M fenout.txt > fenoutsort.txt"

The sort took about 45 minutes, which is acceptable for me (it was much
longer without the -S option to tell it to make use of more memory), and
then loading the table was very efficient.  Inserting all the rows into my
table in sorted order took only 18 minutes.  

So, all in all, I can now load the table in just about an hour, which is
great news for me.

Thanks!
Chris

-- 
View this message in context: 
http://www.nabble.com/Index-creation-on-huge-table-will-never-finish.-tf3444218.html#a9618709
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] how it works

[Jakub Ladman]

Dne čtvrtek 22 březen 2007 13:16 [EMAIL PROTECTED] napsal(a):
> Jakub Ladman <[EMAIL PROTECTED]> wrote:
> > Hi
> >
> > I need to know how this works in detail:
> > I have sqlite3 database file stored on SD/MMC FLASH card and i would to
> > insert rows to some tables often. There will be triggers to clean old and
> > obsolete data.
> > How often it will write to database file? After every INSERT command, or
> > it goes to some buffer in ram and file is updated as late as multiple
> > inserts are done?
>
> The database file is written when you COMMIT (either implicitly or
> explicitly) or when the internal cache overflows.
>
> The rollback journal file on the same flash card is written
> continuously as you make changes.

Thank you for answer.
Is there a possibility to store journal at some other place instead of the 
directory where main file stays?
Flash memory is not so good for "random access", because its limited number of 
writing cycles.
I think ramdisk is not a best place to store journal :-), but i have also 8KB 
of  non-volatile ferroelectric ram accessible through i2c bus (character 
device).
 But 8KB is not too much. May it be enough in some specific configuration, or 
is it completely wrong idea?

Thank you

Jakub

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

Re: [sqlite] slow "group by" in query

[Dennis Cote]

Jonas Henriksen wrote:


I need to return the last data for each data_logger. In the testdata
there is only 1 distinct data_logger_id

executing:

select max(date_time) as date_time, data_logger_id
from data_values
where date_time>datetime('now','-2 hours')
group by data_logger_id

...takes ca 1,5 second, while

select *
from data_values
where date_time>datetime('now','-2 hours')

takes only 0 ms and returns  a ca 70 rows.

I also tried to use the second query as a subquery instead of the
whole table, but with the same result:
select max(date_time) as date_time, data_logger_id
from (
select *
from data_values
where date_time>datetime('now','-2 hours')
)
group by data_logger_id

1.5 seconds

Can anyone help me speed up the first query? (I have added queries to
generate random data at the bottom of this mail)




Jonas,

I think you have an indexing problem. You currently have two indexes on 
your table, but SQLite will only use one index per table for a given 
query. I used the "explain query plan" command to see which indexes are 
being used by your two queries.


SQLite version 3.3.13
Enter ".help" for instructions
sqlite> create table data_values(
  ...> data_value_id integer not null primary key autoincrement,
  ...> data_type_id integer not null references 
data_types(data_type_id) ON


  ...> UPDATE CASCADE ON DELETE RESTRICT,
  ...> data_collection_id integer not null references
  ...> data_collections(data_collection_id) ON UPDATE CASCADE ON DELETE
  ...> RESTRICT,
  ...> data_logger_id integer references 
data_loggers(data_logger_id) ON

  ...> UPDATE CASCADE ON DELETE RESTRICT,
  ...> date_time timestamp ,
  ...> lat_wgs84 double precision,
  ...> lon_wgs84 double precision,
  ...> height integer,
  ...> parallell integer default 0 not null,
  ...> data_value double precision not null
  ...> );
sqlite> CREATE INDEX data_values_data_logger_id_index
  ...>   ON data_values (data_logger_id);
sqlite> CREATE INDEX data_values_data_date_time_index
  ...>  ON data_values  (date_time);
sqlite>
sqlite>
sqlite> explain query plan
  ...> select max(date_time) as date_time, data_logger_id
  ...> from data_values
  ...> where date_time>datetime('now','-2 hours')
  ...> group by data_logger_id;
0|0|TABLE data_values WITH INDEX data_values_data_logger_id_index ORDER BY
sqlite>
sqlite> explain query plan
  ...> select *
  ...> from data_values
  ...> where date_time>datetime('now','-2 hours');
0|0|TABLE data_values WITH INDEX data_values_data_date_time_index


I think you need to create a compound index on data_logger_id (for the 
group by  operation) and date_time (for the time comparison).



sqlite> create index compound on data_values(data_logger_id, date_time);
sqlite> explain query plan
  ...> select max(date_time) as date_time, data_logger_id
  ...> from data_values
  ...> where date_time>datetime('now','-2 hours')
  ...> group by data_logger_id;
0|0|TABLE data_values WITH INDEX compound ORDER BY

When I run this query with you test data it returns instantly.

HTH
Dennis Cote

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

Re: [sqlite] Index creation on huge table will never finish.

[Dennis Cote]

Chris Jones wrote:

I've read elsewhere that this is a data locality issue, which certainly
makes sense.

And in those threads, a suggestion has been made to insert in sorted order. 
But it's unclear to me exactly what the sorting function would need to be -

it's likely my sorting function (say strcmp()) wouldn't match what sqlite
expects.  It's also a little unclear if I can even sort this many keys
externally with any ease.  After all, that's why I'm trying to use a
database.

  

Chris,

You are correct that this is a locality of reference issue. Using a 
presorted list speeds insertions because all the index insertions to be 
made at end of the index. To make this run faster you need prepare a 
presorted list. The question is how best to do that with a large file.


You didn't say what OS you are running, but these days most basic unix 
commands are available on Windows as well. I ams assuming you have your 
list of 112M lines in a text file. You can prepare a large sorted file 
pretty efficiently using a merge sort, since it reads the input files in 
one pass. The trick is the input files must be presorted. So you need to 
split your original data file into many smaller files, sort each in 
memory (where locality isn't a problem as long as the data fits in real 
memory), and then merge the individual sorted files. This is best done 
is a script.


You can use the split command to split you text file into many separate 
files. If you split your file into chunks of 1M lines you will have 112 
files to sort and merge. Each file will be less than 80 MB, so it should 
be in memory sortable.


   split -l 100 infile part

This will generate 112 files names partaa, partab, partac ...

Now you need to sort each of these files. Note the sort command can also 
eliminate any duplicate lines in your files (the -u for unique option).


   parts=`ls part*`
   for f in $parts
   do
   sort -o $f -u $f
   done

Now you have 112 sorted files that need to be merged.

   mv partaa sorted
   parts=`ls part*`
   for f in $parts
   do
   sort -m -o sorted sorted $f
   done   

You now have a single large sorted file that contains unique strings. 
You should be able to insert the strings from this file into your sqlite 
database much faster than you could before.


If you want you can get fancier with the script and merge pairs of 
sorted files into increasingly larger files until you have a single 
file. I'll leave that as an exercise for the reader.


You could also improve the locality in the database file further by 
running a vacuum command after it has been created. this will move the 
pages around so that the page of the table are contiguous and so are the 
pages of the index, rather than having them interspersed with each other.


HTH
Dennis Cote



A shell script that takes your raw text file as an argument.

#!/bin/sh
split -l 100 $1 part

parts=`ls part*`
for f in $parts
do
   sort -o $f -u $f
done

mv partaa sorted
parts=`ls part*`
for f in $parts
do
   sort -m -o sorted sorted $f
done  





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

[sqlite] Re: Re: Re: Question about multithreading

[Igor Tandetnik]

Voxen <[EMAIL PROTECTED]> wrote:

There's no "like" - you do use connection pointer directly from
thread B. The fact that the piece of code thread B currently
executes is a method of an object that happened to be created by
thread A is immaterial.


That clears things and it shows me I need to open/close the database
locally when a method is called by several threads.


Well, you can have each thread open a connection, then pass it along as 
a parameter to whatever function the thread needs to call. This way, the 
method would always operate on a connection associated with whatever 
thread were calling it, and you won't have to keep opening and closing 
connections all the time.


Igor Tandetnik 



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

Re: [sqlite] Index creation on huge table will never finish.

[drh]

"P Kishor" <[EMAIL PROTECTED]> wrote:
> Richard,
> 
> On 3/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> ...
> > The problem is that your working set is bigger than your cache
> > which is causing thrashing.  I suggest a solution like this:
> >
> > Add entries to table ONE until the table and its unique index get
> > so big that they no longer fit in cache all at once.
> 
> What is this "cache" you talk about? Is this the hard disk cache, or
> some kind of OS-level cache, or a SQLite level cache? 

All of the above.

> How can I find
> the size of this, and is this cache user-adjustable? If yes, how?
> 

On Linux, the "top" command will show you how much OS disk
cache you are currently using.  The hard disk cache is usually
small enough that it can be neglected.  The OS cache is also
usually much larger than SQLite's own internal cache.

You might run an experiment where you start inserting and
timing each insert, then switch to a different table when
the insert speed drops below a threshold.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Index creation on huge table will never finish.

[Eduardo Morras]

At 04:47 22/03/2007, you wrote:


I don't think that solves my problem.  Sure, it guarantees that the IDs are
unique, but not the strings.

My whole goal is to be able to create a unique identifier for each string,
in such a way that I dont have the same string listed twice, with different
identifiers.

In your solution, there is no way to lookup a string to see if it already
exists, since there is no index on the string.

Thanks,
Chris


So you have a file with data, a large collection of strings 112 
millions, each at most 80-bytes, although typically

shorter.

How do you manage repeated data? Replace? First In? Modify string to be unique?

You want put them in a sqlite3 database, but each string must be only 
once. The problem i see here is if you have a data file with repeated 
strings or not. I think that a grep or a perl script can help you a 
lot cleaning your data first. Then import to database will be fast.


HTH




--
"Hemos encontrado al enemigo y somos nosotros"



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

Re: [sqlite] Index creation on huge table will never finish.

[P Kishor]

Richard,

On 3/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
..

The problem is that your working set is bigger than your cache
which is causing thrashing.  I suggest a solution like this:

Add entries to table ONE until the table and its unique index get
so big that they no longer fit in cache all at once.


What is this "cache" you talk about? Is this the hard disk cache, or
some kind of OS-level cache, or a SQLite level cache? How can I find
the size of this, and is this cache user-adjustable? If yes, how?

--
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] Index creation on huge table will never finish.

[Brad Stiles]

Gerry Snyder <[EMAIL PROTECTED]> wrote:

> Chris Jones wrote:
> Hi all,

> I have a very simple schema.  I need to assign a unique identifier
> to a large collection of strings, each at most 80-bytes, although
> typically shorter.

Would it help to hash the strings, then save them in the DB, checking the hash 
instead of the string for duplication?  You might still get duplicates, 
depending on what hashing algorithm you use, but those should be relatively 
easy to find afterwards.  Hashing could take a while, but that should be a 
linear time operation all by itself.



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

[sqlite] ANN: SQLite Maestro 7.3 released

[SQL Maestro Group]

Hi All,

SQL Maestro Group is happy to announce the release of SQLite Maestro 7.3, a 
powerful Windows GUI tool for SQLite databases administration and 
development. The new version is immediately available at:

http://www.sqlmaestro.com/products/sqlite/maestro/

The new version is mostly a maintenance release meant to add some interface 
features, increase stability and fix several non-critical bugs. Details are 
available at:

http://www.sqlmaestro.com/news/company/3640/

Sincerely yours,
SQL Maestro Group
http://www.sqlmaestro.com 



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

Re: [sqlite] Re: Re: Question about multithreading

[Voxen]

There's no "like" - you do use connection pointer directly from thread  
B. The fact that the piece of code thread B currently executes is a  
method of an object that happened to be created by thread A is  
immaterial.


Thanks Igor.
That clears things and it shows me I need to open/close the database  
locally when a method is called by several threads.
This is why I asked about using local connections instead of global ones  
on my other message "Holding sqlite connection".


Regards
Gil


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

Re: [sqlite] Re: Question about multithreading

[Voxen]

Gil:
   In you threads, dont declare sqlite3 *db private or public, instead
make it a local variable in each method.  Then if one class calls a
method from another, a seperate database pointer will exist (on each
threads local stack) and you will have no problems.  Let me know how
it works.

--
Rich


Rich,

So you mean I should open/close a sqlite database locally on methods that  
can be called by several threads, instead of using a global pointer?


That would makes sense to me.

Thanks!


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

RE: [sqlite] Holding sqlite connection

[Samuel R. Neff]

If the database schema is not small then use opening a connection does make
a difference.  In initial testing we found opening a connection with 100
simple test tables take 3 ms.  Later testing with our actual schema which
has fewer tables but is more complex takes 17ms (~65 tables with indexes and
foreign key triggers).

Also that's just opening time.  The cache is stored as part of the
connection so you'll see additional benefits through use with a single
connection as opposed to multiple connections.  

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]
 
-Original Message-
From: Gil Delavous [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 22, 2007 7:59 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Holding sqlite connection

Hi,

I was wondering if its better to open a sqlite database connection when my  
application launches, use it all along the process life, and closing it  
when it exits... or if its better to open/close the database connection  
each time a method has to query/store data, thus leaving the database not  
open all the time.

As my application is multithreaded (each thread open its own, global  
connection too) I'm not sure if holding connections is the best way to  
avoid conflicts (I sometimes have SQLITE_MISUSE errors).

I originally didn't want to open the connection each time I need to query  
data (to optimize performances) but does it really make a difference?

Regards,
Gil Delavous


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

Re: [sqlite] Re: Question about multithreading

[Rich Rattanni]

On 3/22/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

Gil Delavous <[EMAIL PROTECTED]>
wrote:
> However, what happens when a thread calls a method from the main
> thread, this one using its own sqlite connection?

The question doesn't make any sense to me, sorry. Methods don't belong
to threads. Any function in a program can, in principle, be executed by
any thread at any time, including by multiple threads simultaneously.

> For example:
>
> Main thread:
> void main::query_something() {
> // query something using main thread's sqlite connection
> }
>
> Print thread:
> void print::print_result() {
> int value = main->query_something();
> }

What makes you think these classes are somehow affine to a particular
thread? They are not. When you call query_something from print_result,
the same thread that executed print_result now executes query_something,
whether it's a "main" thread (whatever that means) or otherwise.

If it's your intention that all methods from class main be called on one
thread, and all methods of print be called on another, it's up to your
program to ensure that. You need some kind of inter-thread communication
mechanism, e.g. a producer/consumer queue.

> As my main thread has tons of utility methods called from other
> threads

You seem to say "thread" when you mean "class", and this lies at the
heart of your confusion. Realize that the two are entirely different,
largely unrelated concepts.

Igor Tandetnik


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



Gil:
  In you threads, dont declare sqlite3 *db private or public, instead
make it a local variable in each method.  Then if one class calls a
method from another, a seperate database pointer will exist (on each
threads local stack) and you will have no problems.  Let me know how
it works.

--
Rich

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

Re: [sqlite] Index creation on huge table will never finish.

[drh]

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> > drh wrote:
> >   INSERT INTO two SELECT * FROM one ORDER BY unique_column;
> 
> >The ORDER BY is important here.
> 
> This is an excerpt from SQLite documentation:
> 
> The second form of the INSERT statement takes it data from a SELECT statement.
> The number of columns in the result of the SELECT must exactly match the 
> number
> of columns in the table if no column list is specified,
> or it must match the number of columns name in the column list.
> A new entry is made in the table for every row of the SELECT result.
> The SELECT may be simple or compound.
> If the SELECT statement has an ORDER BY clause, the ORDER BY is ignored.
>  ^
> 
> Question: ORDER BY is ignored or not ?
> 

You are looking at obsolete documentation.  See

  http://www.sqlite.org/cvstrac/tktview?tn=1923
  http://www.sqlite.org/cvstrac/chngview?cn=3356

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


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

[sqlite] Re: Clarification of bound parameter usage

[Igor Tandetnik]

Ian Frosst <[EMAIL PROTECTED]> wrote:

The problem here though, is that I'm doing the prepare/step/finalize
each time I want to execute the query, even though the only thing
that is going to change are the values.  Can I write a loop which
prepares the SQL, using named parameters, then in a loop just call
sqlite3_bind_*/step/reset, finalizing after all of my calls have been
done? 


Of course. That's precisely what parameters are for.

Igor Tandetnik

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

Re: [sqlite] Index creation on huge table will never finish.

[Derrell . Lipman]

Chris Jones <[EMAIL PROTECTED]> writes:

> I don't think that solves my problem.  Sure, it guarantees that the IDs are
> unique, but not the strings.  
>
> My whole goal is to be able to create a unique identifier for each string,
> in such a way that I dont have the same string listed twice, with different
> identifiers.

I don't think that your original solution solves that problem either.  You
first posted this schema:

> My schema looks as follows:
> 
> CREATE TABLE rawfen ( fen VARCHAR(80) );
> CREATE INDEX rawfen_idx_fen ON rawfen(fen);

but you didn't indicate that rawfen_idx_fen is a UNIQUE INDEX so it won't
complain if there are duplicate strings.  To accomplish this (but not solving
your timing issue), you need this:

  CREATE TABLE rawfen ( fen VARCHAR(80) );
  CREATE UNIQUE INDEX rawfen_idx_fen ON rawfen(fen);

or, more concisely,

  CREATE TABLE rawfen ( fen VARCHAR(80) UNIQUE);

As previously stated, you can guarantee a unique id for each string with

  CREATE TABLE rawfen (id INTEGER PRIMARY KEY, fen VARCHAR(80) UNIQUE);

Cheers,

Derrell

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

Re[2]: [sqlite] Index creation on huge table will never finish.

[Ion Silvestru]

> drh wrote:
>   INSERT INTO two SELECT * FROM one ORDER BY unique_column;

>The ORDER BY is important here.

This is an excerpt from SQLite documentation:

The second form of the INSERT statement takes it data from a SELECT statement.
The number of columns in the result of the SELECT must exactly match the number
of columns in the table if no column list is specified,
or it must match the number of columns name in the column list.
A new entry is made in the table for every row of the SELECT result.
The SELECT may be simple or compound.
If the SELECT statement has an ORDER BY clause, the ORDER BY is ignored.
 ^

Question: ORDER BY is ignored or not ?



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

[sqlite] Clarification of bound parameter usage

[Ian Frosst]

Hi all, I'm looking for some clarification on the usage of bound parameters
in queries.  I have one SQL statement that is going to be executed numerous
times (thousands of times, in a fairly tight loop.)  Right now I am using a
string, "INSERT INTO tableX (col1, col2) VALUES ('%s', '%s')". and calling
printf to substitute in the parameters (I know, it's seceptible to
injection, and doesn't escape, but that's not of concern to me right now.)

The problem here though, is that I'm doing the prepare/step/finalize each
time I want to execute the query, even though the only thing that is going
to change are the values.  Can I write a loop which prepares the SQL, using
named parameters, then in a loop just call sqlite3_bind_*/step/reset,
finalizing after all of my calls have been done?

Thanks,
Ian

[sqlite] Re: SPAM: Re: Question about multithreading

[Igor Tandetnik]

Voxen <[EMAIL PROTECTED]> wrote:

So, from my example, let say thread A created the object "main", and
thread B created the object "print".

When thread B calls the method from object "main" (as shown by the
example), my question was to know if the sqlite connection opened by
object "main" can be considered as shared with thread B


Of course.


much like if
I used this connection pointer directly from thread B?


There's no "like" - you do use connection pointer directly from thread 
B. The fact that the piece of code thread B currently executes is a 
method of an object that happened to be created by thread A is 
immaterial.


Igor Tandetnik 



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

[sqlite] slow "group by" in query

[Jonas Henriksen]

Hi,

I'm new to sqlite but have experience from other systems. I have a
question regarding speed of a query when using "group by":
table:
create table data_values(
data_value_id integer not null primary key autoincrement,
data_type_id integer not null references data_types(data_type_id) ON
UPDATE CASCADE ON DELETE RESTRICT,
data_collection_id integer not null references
data_collections(data_collection_id) ON UPDATE CASCADE ON DELETE
RESTRICT,
data_logger_id integer references data_loggers(data_logger_id) ON
UPDATE CASCADE ON DELETE RESTRICT,
date_time timestamp ,
lat_wgs84 double precision,
lon_wgs84 double precision,
height integer,
parallell integer default 0 not null,
data_value double precision not null
);
CREATE INDEX data_values_data_logger_id_index
  ON data_values (data_logger_id);
CREATE INDEX data_values_data_date_time_index
 ON data_values  (date_time);


It currently has 294912 rows.

I need to return the last data for each data_logger. In the testdata
there is only 1 distinct data_logger_id

executing:

select max(date_time) as date_time, data_logger_id
from data_values
where date_time>datetime('now','-2 hours')
group by data_logger_id

...takes ca 1,5 second, while

select *
from data_values
where date_time>datetime('now','-2 hours')

takes only 0 ms and returns  a ca 70 rows.

I also tried to use the second query as a subquery instead of the
whole table, but with the same result:
select max(date_time) as date_time, data_logger_id
from (
select *
from data_values
where date_time>datetime('now','-2 hours')
)
group by data_logger_id

1.5 seconds

Can anyone help me speed up the first query? (I have added queries to
generate random data at the bottom of this mail)

regards Jonas:))



Generate random data:

insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 100, 0,
random()/9223372036854775808*5);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 300, 0,
random()/9223372036854775808*5);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 500, 0,
random()/9223372036854775808*5);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 1000, 0,
random()/9223372036854775808*3);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 1500, 0,
random()/9223372036854775808*3);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 2000, 0,
random()/9223372036854775808*3);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 3000, 0,
random()/9223372036854775808*2);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 4000, 0,
random()/9223372036854775808*2);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 5000, 0,
random()/9223372036854775808*2);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 6000, 0,
random()/9223372036854775808*2);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 7000, 0,
random()/9223372036854775808*2);
insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1, 1, 1, '2007-03-05 09:12', 52.20044, 33.232323, 8000, 0,
random()/9223372036854775808*2);

insert into data_values( data_logger_id, data_type_id,
data_collection_id, date_time, lat_wgs84, lon_wgs84, height,
parallell, data_value)
values ( 1,2, 1, '2007-03-05 09:12', 52.20044, 

Re: [sqlite] how it works

[drh]

Jakub Ladman <[EMAIL PROTECTED]> wrote:
> Hi
> 
> I need to know how this works in detail:
> I have sqlite3 database file stored on SD/MMC FLASH card and i would to 
> insert 
> rows to some tables often. There will be triggers to clean old and obsolete 
> data.
> How often it will write to database file? After every INSERT command, or it 
> goes to some buffer in ram and file is updated as late as multiple inserts 
> are done?

The database file is written when you COMMIT (either implicitly or
explicitly) or when the internal cache overflows.

The rollback journal file on the same flash card is written
continuously as you make changes.

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


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

Re: [sqlite] Index creation on huge table will never finish.

[drh]

Gerry Snyder <[EMAIL PROTECTED]> wrote:
> Chris Jones wrote:
> > Hi all,
> > 
> > I have a very simple schema.  I need to assign a unique identifier to a
> > large collection of strings, each at most 80-bytes, although typically
> > shorter.
> > 
> > The problem is I have 112 million of them.
> 
> Maybe you could start by breaking the data into 8 equal groups and make 
> a table of each group. Then merge the original groups pairwise, then 
> merge those 4 groups, and finally the 2 semifinal groups (kinda like 
> March Madness, come to think of it). Since each merging will be of 
> already sorted/indexed data, it might save a lot of time.
> 

This is the right idea.

The problem is that your working set is bigger than your cache
which is causing thrashing.  I suggest a solution like this:

Add entries to table ONE until the table and its unique index get
so big that they no longer fit in cache all at once.  Then
transfer ONE into TWO like this:

   INSERT INTO two SELECT * FROM one ORDER BY unique_column;

The ORDER BY is important here.

Do the above a time or two until TWO is signficantly larger than ONE.
Then do the same into TWO_B.  Later combine TWO and TWO_B into THREE:

   INSERT INTO three SELECT * FROM two ORDER BY unique_column;
   INSERT INTO three SELECT * FROM two_b ORDER BY unique_column;

Repeat as necessary for FOUR, FIVE, SIX and so forth.

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


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

[sqlite] Re: Holding sqlite connection

[Igor Tandetnik]

Gil Delavous <[EMAIL PROTECTED]>
wrote:

I was wondering if its better to open a sqlite database connection
when my application launches, use it all along the process life, and 
closing

it when it exits... or if its better to open/close the database
connection each time a method has to query/store data, thus leaving 
the database

not open all the time.


There is no downside to keeping the database open. On the other hand, 
when SQLite opens the database it reads and parses its schema, which 
takes some (short) time, so you usually don't want to do it too often.



As my application is multithreaded (each thread open its own, global
connection too) I'm not sure if holding connections is the best way to
avoid conflicts (I sometimes have SQLITE_MISUSE errors).


Just holding a connection open doesn't cause conflicts by itself - you 
need to actually execute some statement.


SQLITE_MISUSE error doesn't indicate a conflict (SQLITE_BUSY does) - it 
means you are doing something wrong, like trying to execute a statement 
that was already finalized. In other words, SQLITE_MISUSE signals a bug 
in your program. Find it and fix it.


Igor Tandetnik 



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

Re: SPAM: [sqlite] Re: Question about multithreading

[Voxen]

Hi Igor,

Thanks for you reply. I might have confused things, sorry about that.

So, from my example, let say thread A created the object "main", and  
thread B created the object "print".


When thread B calls the method from object "main" (as shown by the  
example), my question was to know if the sqlite connection opened by  
object "main" can be considered as shared with thread B, much like if I  
used this connection pointer directly from thread B?


Gil


Le Thu, 22 Mar 2007 12:53:57 +0100, Igor Tandetnik <[EMAIL PROTECTED]> a  
écrit:



Gil Delavous <[EMAIL PROTECTED]>
wrote:

However, what happens when a thread calls a method from the main
thread, this one using its own sqlite connection?


The question doesn't make any sense to me, sorry. Methods don't belong  
to threads. Any function in a program can, in principle, be executed by  
any thread at any time, including by multiple threads simultaneously.



For example:

Main thread:
void main::query_something() {
// query something using main thread's sqlite connection
}

Print thread:
void print::print_result() {
int value = main->query_something();
}


What makes you think these classes are somehow affine to a particular  
thread? They are not. When you call query_something from print_result,  
the same thread that executed print_result now executes query_something,  
whether it's a "main" thread (whatever that means) or otherwise.


If it's your intention that all methods from class main be called on one  
thread, and all methods of print be called on another, it's up to your  
program to ensure that. You need some kind of inter-thread communication  
mechanism, e.g. a producer/consumer queue.



As my main thread has tons of utility methods called from other
threads


You seem to say "thread" when you mean "class", and this lies at the  
heart of your confusion. Realize that the two are entirely different,  
largely unrelated concepts.


Igor Tandetnik  
-

To unsubscribe, send email to [EMAIL PROTECTED]
-







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

[sqlite] Holding sqlite connection

[Gil Delavous]

Hi,

I was wondering if its better to open a sqlite database connection when my  
application launches, use it all along the process life, and closing it  
when it exits... or if its better to open/close the database connection  
each time a method has to query/store data, thus leaving the database not  
open all the time.


As my application is multithreaded (each thread open its own, global  
connection too) I'm not sure if holding connections is the best way to  
avoid conflicts (I sometimes have SQLITE_MISUSE errors).


I originally didn't want to open the connection each time I need to query  
data (to optimize performances) but does it really make a difference?


Regards,
Gil Delavous


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

[sqlite] Re: Question about multithreading

[Igor Tandetnik]

Gil Delavous <[EMAIL PROTECTED]>
wrote:

However, what happens when a thread calls a method from the main
thread, this one using its own sqlite connection?


The question doesn't make any sense to me, sorry. Methods don't belong 
to threads. Any function in a program can, in principle, be executed by 
any thread at any time, including by multiple threads simultaneously.



For example:

Main thread:
void main::query_something() {
// query something using main thread's sqlite connection
}

Print thread:
void print::print_result() {
int value = main->query_something();
}


What makes you think these classes are somehow affine to a particular 
thread? They are not. When you call query_something from print_result, 
the same thread that executed print_result now executes query_something, 
whether it's a "main" thread (whatever that means) or otherwise.


If it's your intention that all methods from class main be called on one 
thread, and all methods of print be called on another, it's up to your 
program to ensure that. You need some kind of inter-thread communication 
mechanism, e.g. a producer/consumer queue.



As my main thread has tons of utility methods called from other
threads


You seem to say "thread" when you mean "class", and this lies at the 
heart of your confusion. Realize that the two are entirely different, 
largely unrelated concepts.


Igor Tandetnik 



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

[sqlite] Question about multithreading

[Gil Delavous]

Hi,

I've read that it is better that each thread creates its own sqlite  
connection, and that's what I'm doing.


However, what happens when a thread calls a method from the main thread,  
this one using its own sqlite connection?


For example:

Main thread:
void main::query_something() {
// query something using main thread's sqlite connection
}

Print thread:
void print::print_result() {
int value = main->query_something();
}

In that situation, is calling a method from another thread can be  
considered as sharing a same sqlite connection between threads?
As my main thread has tons of utility methods called from other threads,  
how should I deal with this?


Thanks and regards,
Gil Delavous


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

Re: [sqlite] how it works

[A.J.Millan]

> Hi
>
> I need to know how this works in detail:
> I have sqlite3 database file stored on SD/MMC FLASH card and i would to
insert
> rows to some tables often. There will be triggers to clean old and
obsolete
> data.
> How often it will write to database file? After every INSERT command, or
it
> goes to some buffer in ram and file is updated as late as multiple inserts
> are done?
> Do you understand me?
> I do not know how to conceive this question in English exactly.
>
> Best regards
>
> Jakub Ladman
>

Besides what can say on the matter the forum's gurus, included the own
author Mr. Hipp, it is a matter of fact that the process to write in
external devices is regularly monitorized by the OS. Sometimes the compiler
uses its own buffer system that run over the Systems buffers. Although there
are some resources to force a real write in some circumstances, with some
languages and some compilers.  flush, commit, etc. (_commit in MS VC++).

A.J. Millan


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

[sqlite] how it works

[Jakub Ladman]

Hi

I need to know how this works in detail:
I have sqlite3 database file stored on SD/MMC FLASH card and i would to insert 
rows to some tables often. There will be triggers to clean old and obsolete 
data.
How often it will write to database file? After every INSERT command, or it 
goes to some buffer in ram and file is updated as late as multiple inserts 
are done?
Do you understand me?
I do not know how to conceive this question in English exactly.

Best regards

Jakub Ladman

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