Re: [sqlite] Very slow processing of some SELECT statements

[Nick Gammon]

On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote:

> Is it normal that fromuid of the table exits is STRING ?
> I think it should be TEXT to be surely processed as text and not float

That was an error. However it shouldn't take SQLite 2.5 seconds to handle *any* 
numeric literal. Especially as it was quoted. For example, in C you don't 
expect:

x = "123E45678942";

... to go through any sort of numeric conversion. Now I know this isn't C, but 
the "let's see if we can turn a string into a number, and take two to three 
seconds to do so" is not right, IMHO.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

[Sylvain Pointeau]

Is it normal that fromuid of the table exits is STRING ?
I think it should be TEXT to be surely processed as text and not float
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help please. Create and open encrypted sqlite database in java

[Mahesh Walasang]

Hello blogger,

I want to create encrypted sqlite database in java. I don't know whether 
it is possible or not. There is not much details about this in the net.

I tried

 SQLite.Database db = new SQLite.Database();
try {
db.key("Steer@123");
db.open("db3", 0666);
}
etc .

but its not working. Can anybody give any suggestions ?

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

Re: [sqlite] Very slow processing of some SELECT statements

[Nick Gammon]

On 18/10/2011, at 3:38 PM, Dan Kennedy wrote:

> Now fixed here:
> 
>  http://www.sqlite.org/src/ci/59bb999c8b?sbs=0


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

Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Fabian
> Sent: 17 October 2011 15:34
> >
> No, I only want to have a capped total available.
> 
> If I would go with Simons solution, I have to read the rows for the first
> 100 pages (or whatever the cap is) into a temporary table, just to show
the
> first page. I don't need a cache for all those other pages, so that seems
a lot
> of overhead. I only want to know if there are 100 or less pages (without
> copying data around).
> 
> Maybe COUNT() is also creating a temporary table behind the scenes, then
> the performance of Simons solutions would be comparable with what I have
> now, and I would have the advantage that I can re-use that table to show
> subsequent pages without reading from disk.
> 
> But I always assumed COUNT() was faster than copying between tables,
> maybe I should just benchmark it.

I had the idea that you just retrieved the first 100 records and not the
first 100 pages.
Could the user not just see the first 100 records and perhaps an indicator
if there were more or not. He could then get 100 records at a time browsing
through them or if he so wished get a record count (you would then use the
count function on all records).

If you need to know up front whether there are more than 5000 records or not
I suggest you use the suggestion from Petite Abeille:

select count( * )
from   (
 select 1   or even just "select null" which
will not fetch anything.
 fromtable
 limit 5000
   )

You could do that and also just select e.g. 50 actual rows and display these
to the user.


/Frank


 

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

Re: [sqlite] Very slow processing of some SELECT statements

[Dan Kennedy]

On 10/18/2011 03:17 AM, Nick Gammon wrote:


On 17/10/2011, at 9:55 PM, Dan Kennedy wrote:


Did you download the binary from the website or build it yourself?
If the latter, which compiler are you using? And what level of
optimization is enabled?



I initially observed the problem with version 3.7.7.1, as embedded in my application 
using Visual C++ version 6.0. I have "maximum speed" optimization selected.

When a user reported the problem, and I confirmed it, I downloaded the latest 
command-line utility from the SQLite site (version 3.7.8), as follows:

http://www.sqlite.org/sqlite-shell-win32-x86-3070800.zip

So the test was confirmed using sqlite3.exe, from the SQLite3 site, as 
distributed.


Now fixed here:

  http://www.sqlite.org/src/ci/59bb999c8b?sbs=0

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

Re: [sqlite] Very slow processing of some SELECT statements

[Nick Gammon]

On 17/10/2011, at 9:55 PM, Dan Kennedy wrote:

> Did you download the binary from the website or build it yourself?
> If the latter, which compiler are you using? And what level of
> optimization is enabled?


I initially observed the problem with version 3.7.7.1, as embedded in my 
application using Visual C++ version 6.0. I have "maximum speed" optimization 
selected.

When a user reported the problem, and I confirmed it, I downloaded the latest 
command-line utility from the SQLite site (version 3.7.8), as follows:

http://www.sqlite.org/sqlite-shell-win32-x86-3070800.zip

So the test was confirmed using sqlite3.exe, from the SQLite3 site, as 
distributed.
 
- Nick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Kit]

2011/10/16 Petite Abeille :
> On Oct 16, 2011, at 10:39 PM, Kit wrote:
>>> select count(*) from (select 1 from table limit 5000)
>> SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);
>
> you realize that count( * )  has a very specific meaning, right?
> "The count(*) function (with no arguments) returns the total number of rows 
> in the group."
> http://www.sqlite.org/lang_aggfunc.html
> If this is what you mean, then stick to it :)

I originally thought that the symbol "*" means "all columns". I tried
to "EXPLAIN", now I see it differently.
Thank you.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "is not null" and index

[Black, Michael (IS)]

OK...here's a new thumb...data is now randomly distributed at 50%.   I'm 
running on Windows XP with version 3.7.5
And the between logic works about 36% faster.  Is your "rule of thumb" based on 
any benchmark or just a guess?

#include 
#include 
int main()
{
  int i;
  char sql[4096];
  printf("CREATE TABLE x ('col1','col2','col3');\n");
  printf("BEGIN;");
  for(i=0;i<100;++i) {
double r=rand()/(double)RAND_MAX;
if (r < .5) {
  sprintf(sql,"INSERT INTO x values(null,'col2_%d','col3_%d');",i,i,i);
}
else {
  sprintf(sql,"INSERT INTO x values('col_%d','col2_%d','col3_%d');",i,i,i);
}
printf("%s\n",sql);
  }
  printf("COMMIT;");
  printf("CREATE INDEX col1index on x('col1');\n");
}

D:\SQLite\index1>sqlite3 test.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read data.sql
sqlite> .timer on
sqlite> select count(*) from x where col1 between (select min(col1) from x) and 
(select max(col1) from x);
500080
CPU Time: user 0.218750 sys 0.046875
sqlite> select count(*) from x where col1 is not null;
500080
CPU Time: user 0.343750 sys 0.062500
sqlite> select count(*) from x where col1 is null;
499920
CPU Time: user 0.109375 sys 0.015625

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, October 17, 2011 10:06 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] "is not null" and index


On 10/17/2011 9:30 AM, Black, Michael (IS) wrote:
> According to this benchmark the break-even point is at 40% nulls.  I asssume 
> you have a different test?

I did mention "rule of thumb". Specific cases may vary. I must admit I'm
too lazy to build tests for someone else's problem.

The fact that all NULL values are clustered together in rows with
sequential rowids might have skewed the results in your test. Better
locality of reference, fewer pages to read from disk, improved cache
utilization.
--
Igor Tandetnik

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

Re: [sqlite] DOMAIN new error code

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/10/11 02:00, Jean-Christophe Deschamps wrote:
> That's an half-backed solution.  I know of no application which test
> error codes AND THEN test error message _content_ before selecting a
> way to deal with the error.

The application won't but the user the message is displayed to will then
diagnose based on the message and the SQL used.

> The current situation is way too vague and leaves you dry about the
> cause.

A solution is to have an error flag and diagnostics you store somewhere
(best per thread) and set when the error occurs in your code.  In your
wrapper around sqlite3_step you check on its return if the error flag is
set, and if so issue an error and diagnostics in whatever way makes sense.

Consequently you will get rich error information that makes best sense for
your code and will have a considerably easier time diagnosing it.

> I can think of no good reason why it shouldn't make it in the core
> code:

It will end up just as overloaded as SQLITE_ERROR is.

> it costs nothing,

It would have to have added to all the relevant test cases and
documentation.  Every developer using SQLite will have to consider if a
particular situation would result in SQLITE_ERROR being returned or
SQLITE_DOMAIN.  And once this is added every developer till SQLite 4 comes
out would have to go through that cognitive process.  Plus existing error
handling code would need to be looked at and updated in existing code bases.

> it doesn't eat any resource,

It makes switch statements larger, and requires

> it should need no change to the test harness,

Of course it does.  At the very least it would need to be ensured that the
error code passes through correctly, that default error text sticks should
the developer issuing the message not set one etc.

> it covers something that isn't covered yet and can only make life a bit
> less difficult at times.

It would make *your* life a little less difficult in the short term :-)
It incurs a cost on everyone else for no real benefit.

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

iEYEARECAAYFAk6cUPkACgkQmOOfHg372QRLKQCfbhMT8J4ATKI7L/3udgrn1qu3
XU8AnRI5sHjdKy1VQTd+ETBjWes9QXBl
=9Wtn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "is not null" and index

[Igor Tandetnik]

On 10/17/2011 9:30 AM, Black, Michael (IS) wrote:

According to this benchmark the break-even point is at 40% nulls.  I asssume 
you have a different test?


I did mention "rule of thumb". Specific cases may vary. I must admit I'm 
too lazy to build tests for someone else's problem.


The fact that all NULL values are clustered together in rows with 
sequential rowids might have skewed the results in your test. Better 
locality of reference, fewer pages to read from disk, improved cache 
utilization.

--
Igor Tandetnik

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

Re: [sqlite] "is not null" and index

[Black, Michael (IS)]

According to this benchmark the break-even point is at 40% nulls.  I asssume 
you have a different test?

#include 

int main()
{
  int i;
  char sql[4096];
  printf("CREATE TABLE x ('col1','col2','col3');\n");
  printf("BEGIN;");
  for(i=0;i<100;++i) {
if (i < 10) {
  sprintf(sql,"INSERT INTO x values(null,'col2_%d','col3_%d');",i,i,i);
}
else {
  sprintf(sql,"INSERT INTO x values('col_%d','col2_%d','col3_%d');",i,i,i);
}
printf("%s\n",sql);
  }
  printf("COMMIT;");
  printf("CREATE INDEX col1index on x('col1');\n");
}



sqlite> select count(*) from x where col1 between (select min(col1) from x) and 
(select max(col1) from x);
90
CPU Time: user 0.453125 sys 0.015625
sqlite> select count(*) from x where col1 is not null;
90
CPU Time: user 0.281250 sys 0.125000
sqlite> update x set col1=null where rowid < 20;
CPU Time: user 1.906250 sys 0.234375
sqlite> select count(*) from x where col1 between (select min(col1) from x) and 
(select max(col1) from x);
81
CPU Time: user 0.390625 sys 0.031250
sqlite> update x set col1=null where rowid >= 20 and rowid < 30;
CPU Time: user 0.953125 sys 0.125000
sqlite> select count(*) from x where col1 between (select min(col1) from x) and 
(select max(col1) from x);
71
CPU Time: user 0.359375 sys 0.015625
sqlite> select count(*) from x where col1 is not null;
71
CPU Time: user 0.296875 sys 0.078125
sqlite> update x set col1=null where rowid >= 30 and rowid < 40;
CPU Time: user 0.890625 sys 0.156250
sqlite> select count(*) from x where col1 is not null;
61
CPU Time: user 0.281250 sys 0.109375
sqlite> select count(*) from x where col1 between (select min(col1) from x) and 
(select max(col1) from x);
61
CPU Time: user 0.281250 sys 0.031250
sqlite> update x set col1=null where rowid >= 40 and rowid < 50;
CPU Time: user 0.921875 sys 0.171875
sqlite> select count(*) from x where col1 between (select min(col1) from x) and 
(select max(col1) from x);
51
CPU Time: user 0.25 sys 0.015625
sqlite> select count(*) from x where col1 is not null;
51
CPU Time: user 0.312500 sys 0.062500



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, October 17, 2011 8:14 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] "is not null" and index

Jean-Christophe Deschamps  wrote:
>>> How can indexes be used with "not null" queries?
>>
>> They cannot.
>
> If one sees NOT NULL as the complement of NULL, i.e. values in the
> range {min_value, max_value} (min and max depending on the column
> expected content and type), then couldn't an index help?
> select * from table where col between min_value and max_value

If you have an extrinsic knowledge of the domain of the column values, then 
yes, you can reformulate the query as

where col1 between minValue and maxValue

I don't think SQLite would be able to rewrite the query this way automatically.

> Of course if there are only few NULLs then a table scan will probably
> be faster.

In fact, unless some 90% of the rows contain NULLs, a table scan will probably 
be faster. Using an index trades O(N) performance for O(M log N), where M is 
the number of rows actually satisfying the condition. This is clearly an 
improvement when M is much smaller than N, and a pessimization when M is close 
to N. A rule of thumb is that the break-even point is somewhere around M = 0.1*N

And if 90% of the rows do contain NULL in this column, I'd consider splitting 
the data into two tables - one with three columns (containing all non-NULL rows 
from the original table) and the other with two columns (containing the 
remaining rows).
--
Igor Tandetnik

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

Re: [sqlite] "is not null" and index

[Richard Hipp]

On Mon, Oct 17, 2011 at 9:14 AM, Igor Tandetnik  wrote:

> Jean-Christophe Deschamps  wrote:
> >>> How can indexes be used with "not null" queries?
> >>
> >> They cannot.
> >
> > If one sees NOT NULL as the complement of NULL, i.e. values in the
> > range {min_value, max_value} (min and max depending on the column
> > expected content and type), then couldn't an index help?
> > select * from table where col between min_value and max_value
>
> If you have an extrinsic knowledge of the domain of the column values, then
> yes, you can reformulate the query as
>
> where col1 between minValue and maxValue
>
> I don't think SQLite would be able to rewrite the query this way
> automatically.
>

It will if you use the latest SQLite from trunk, compiled using
-DSQLITE_ENABLE_STAT3 and if you run ANALYZE and if enough entries in the
table are NULL to justify using an index to find the ones that are NOT
NULL.  As you point out below, "enough" usually means about 90%.


>
> > Of course if there are only few NULLs then a table scan will probably
> > be faster.
>
> In fact, unless some 90% of the rows contain NULLs, a table scan will
> probably be faster. Using an index trades O(N) performance for O(M log N),
> where M is the number of rows actually satisfying the condition. This is
> clearly an improvement when M is much smaller than N, and a pessimization
> when M is close to N. A rule of thumb is that the break-even point is
> somewhere around M = 0.1*N
>
> And if 90% of the rows do contain NULL in this column, I'd consider
> splitting the data into two tables - one with three columns (containing all
> non-NULL rows from the original table) and the other with two columns
> (containing the remaining rows).
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] opinion on possible bad effects from detachingdatabase whilst statements prepared on it.

[O'Neill, Owen]

Unfortunately part of the process does require us to run queries joining
the server and local data :-(  We make life complicated for ourselves !


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, October 17, 2011 1:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] opinion on possible bad effects from
detachingdatabase whilst statements prepared on it.


On 17 Oct 2011, at 1:22pm, O'Neill, Owen wrote:

> the application is distributed, so while it is running it
> re-syncronises it's state with the server by recieving a copy of the
> server's database. At this point it detaches it's current mounted
> database and mounts the new file. It has a local database with local
> settings which is the applications 'main' database connection with the
> copy of the server's database mounted from that via the attach
command.

Unless you actually have relations between tables in the two separate
databases, I think I'd recommend that you open the two databases on
separate connections rather than attach one to the other.

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

Re: [sqlite] "is not null" and index

[Igor Tandetnik]

Jean-Christophe Deschamps  wrote:
>>> How can indexes be used with "not null" queries?
>> 
>> They cannot.
> 
> If one sees NOT NULL as the complement of NULL, i.e. values in the
> range {min_value, max_value} (min and max depending on the column
> expected content and type), then couldn't an index help?
> select * from table where col between min_value and max_value

If you have an extrinsic knowledge of the domain of the column values, then 
yes, you can reformulate the query as

where col1 between minValue and maxValue

I don't think SQLite would be able to rewrite the query this way automatically.

> Of course if there are only few NULLs then a table scan will probably
> be faster.

In fact, unless some 90% of the rows contain NULLs, a table scan will probably 
be faster. Using an index trades O(N) performance for O(M log N), where M is 
the number of rows actually satisfying the condition. This is clearly an 
improvement when M is much smaller than N, and a pessimization when M is close 
to N. A rule of thumb is that the break-even point is somewhere around M = 0.1*N

And if 90% of the rows do contain NULL in this column, I'd consider splitting 
the data into two tables - one with three columns (containing all non-NULL rows 
from the original table) and the other with two columns (containing the 
remaining rows).
-- 
Igor Tandetnik

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

[sqlite] solw down in sqlite 3.7.7.8

[Azat Manukyan]

at firs please forgive me for my English.
about an hour ago i upgraded my sqlite from 3.5.2 to 3.7.8
and performance of tool winch use sqlite is downgraded about 20 times, i
tried to turn of auto index , and many other ways winch i found in internet
forums and in this mail list, but seems everything is useless can you help
someway please.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "is not null" and index

[Jean-Christophe Deschamps]

Yoav Apter  wrote:
> I have the following table:
>
> CREATE TABLE x ('col1', 'col2', 'col3')
> Create col1index on x ('col1')
>
> When I run this query: "select * from x where col1 is null" I see 
the index on x is used.
> When I run this query: "select * from x where col1 is NOT null" I 
see the index on x is not used.


Why is that suprising? Imagine you are given a book with an index at 
the end, and are asked to enumerate all pages where a particular term 
does *not* appear. Would an index be helpful in this task?


> How can indexes be used with "not null" queries?

They cannot.


If one sees NOT NULL as the complement of NULL, i.e. values in the 
range {min_value, max_value} (min and max depending on the column 
expected content and type), then couldn't an index help?

select * from table where col between min_value and max_value

Of course if there are only few NULLs then a table scan will probably 
be faster.



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

Re: [sqlite] "is not null" and index

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> What does distinct do?
> sqlite> explain query plan select distinct (col1) from x where col1 is not 
> null;
> sele  order  from  deta
>   -    
> 0 0  0 SCAN TABLE x USING COVERING INDEX col1index 
> (~50 rows)
> OK...we're still using an index here...

In a sense, yes - but note that you have SCAN TABLE USING INDEX; compare and 
contrast with SEARCH TABLE USING INDEX.

DISTINCT effectively implies ORDER BY. The way DISTINCT is implemeted, SQLite 
enumerates the rows in order (and the index is helpful here) and discards any 
where the value is the same as in the previous row. You've just replaced a full 
table scan with a full index scan (and changed the meaning of the query in the 
process).

> so using this subselect we do this:
> sqlite> explain query plan select * from x where col1 in (select distinct 
> (col1) from x where col1 is not null);
> sele  order  from  deta
>   -    
> 0 0  0 SEARCH TABLE x USING INDEX col1index (col1=?) 
> (~250 rows)
> 0 0  0 EXECUTE LIST SUBQUERY 1
> 1 0  0 SCAN TABLE x USING COVERING INDEX col1index 
> (~50 rows)

So now you are scanning the table, and also searching on top of that. How is 
this an improvement?

> Performance probably depends on how many "not null" things there are...if not 
> many of them this may not be any faster.

In fact, this will always be strictly slower than a straightforward table scan.

> A count() could be a lot faster though I'd think.

How so? I'm not even sure how you would use count() here, let alone use it in a 
way that leads to performance gains.
-- 
Igor Tandetnik

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

Re: [sqlite] opinion on possible bad effects from detaching database whilst statements prepared on it.

[Simon Slavin]

On 17 Oct 2011, at 1:22pm, O'Neill, Owen wrote:

> the application is distributed, so while it is running it
> re-syncronises it's state with the server by recieving a copy of the
> server's database. At this point it detaches it's current mounted
> database and mounts the new file. It has a local database with local
> settings which is the applications 'main' database connection with the
> copy of the server's database mounted from that via the attach command.

Unless you actually have relations between tables in the two separate 
databases, I think I'd recommend that you open the two databases on separate 
connections rather than attach one to the other.

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

[sqlite] opinion on possible bad effects from detaching database whilst statements prepared on it.

[O'Neill, Owen]

Hi Everyone,
I'm trying to hunt down an awkard bug in a multi-threaded application so
I was interested in people's opinions on whether the following is a
likely problem scenario.

-   the application is distributed, so while it is running it
re-syncronises it's state with the server by recieving a copy of the
server's database. At this point it detaches it's current mounted
database and mounts the new file. It has a local database with local
settings which is the applications 'main' database connection with the
copy of the server's database mounted from that via the attach command.

What I think could be happening is this

1) Thread A prepares a bit of update SQL on the mounted database.  (it
is using the execute API call but this just does a prepare and an
execute)

2) Thread B detaches the mounted database

3) Thread B atttaches a new mounted database

4) Thread A executes the previously prepared SQL. Seg fault. Oops

steps 3 and 4 maybe the other way round.

So what is the general opinion on this ? (apart from the obvious of it
not being a bright idea trying to detached and re-attach databases in a
multi-threaded environment !!)
-   would using the online backup / restore API be better worse or
indifferent ?

many thanks 
Owen





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

Re: [sqlite] "is not null" and index

[Black, Michael (IS)]

Does this make sense to try?

First, duplicate the lack of index
sqlite> explain query plan select * from x where col1 is null;
sele  order  from  deta
  -    
0 0  0 SEARCH TABLE x USING INDEX col1index (col1=?) (~10 
rows)
sqlite> explain query plan select * from x where col1 is not null;
sele  order  from  deta
  -    
0 0  0 SCAN TABLE x (~50 rows)

What does distinct do?
sqlite> explain query plan select distinct (col1) from x where col1 is not null;
sele  order  from  deta
  -    
0 0  0 SCAN TABLE x USING COVERING INDEX col1index (~50 
rows)
OK...we're still using an index here...so using this subselect we do this:
sqlite> explain query plan select * from x where col1 in (select distinct 
(col1) from x where col1 is not null);
sele  order  from  deta
  -    
0 0  0 SEARCH TABLE x USING INDEX col1index (col1=?) (~250 
rows)
0 0  0 EXECUTE LIST SUBQUERY 1
1 0  0 SCAN TABLE x USING COVERING INDEX col1index (~50 
rows)

Performance probably depends on how many "not null" things there are...if not 
many of them this may not be any faster.
A count() could be a lot faster though I'd think.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, October 17, 2011 6:56 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] "is not null" and index


Yoav Apter  wrote:
> I have the following table:
>
> CREATE TABLE x ('col1', 'col2', 'col3')
> Create col1index on x ('col1')
>
> When I run this query: "select * from x where col1 is null" I see the index 
> on x is used.
> When I run this query: "select * from x where col1 is NOT null" I see the 
> index on x is not used.

Why is that suprising? Imagine you are given a book with an index at the end, 
and are asked to enumerate all pages where a particular term does *not* appear. 
Would an index be helpful in this task?

> How can indexes be used with "not null" queries?

They cannot.
--
Igor Tandetnik

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

Re: [sqlite] "is not null" and index

[Igor Tandetnik]

Yoav Apter  wrote:
> I have the following table:
> 
> CREATE TABLE x ('col1', 'col2', 'col3')
> Create col1index on x ('col1')
> 
> When I run this query: "select * from x where col1 is null" I see the index 
> on x is used.
> When I run this query: "select * from x where col1 is NOT null" I see the 
> index on x is not used.

Why is that suprising? Imagine you are given a book with an index at the end, 
and are asked to enumerate all pages where a particular term does *not* appear. 
Would an index be helpful in this task?

> How can indexes be used with "not null" queries?

They cannot.
-- 
Igor Tandetnik

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

Re: [sqlite] Sqlite3_step

[Igor Tandetnik]

Sreekumar TP  wrote:
> I do not have a ORDER BY , only  a WHERE  clause. So sorting should not be
> the cause for the overhead.

Show your query, and the output of EXPLAIN QUERY PLAN on it.
-- 
Igor Tandetnik

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

Re: [sqlite] Sqlite3_step

[Igor Tandetnik]

Simon Slavin  wrote:
> The first step has to make a lot of decisions about what plan to follow in 
> retrieving the rows: Which index is the best to use ? 
> Is it going to be necessary to sort the rows even after that index ?  These 
> things do take some extra time. 

These decisions are made by sqlite3_prepare, before the first sqlite3_step.
-- 
Igor Tandetnik

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

Re: [sqlite] Very slow processing of some SELECT statements

[Dan Kennedy]

On 10/17/2011 04:33 PM, Nick Gammon wrote:

Hello,

Running under Windows XP, using sqlite3.exe version:

3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177
...
Trying under Mac OS/X (Lion) does not appear to exhibit this
problem.


No problem with that version on Linux either.

Did you download the binary from the website or build it yourself?
If the latter, which compiler are you using? And what level of
optimization is enabled?





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

Re: [sqlite] Very slow processing of some SELECT statements

[Simon Slavin]

On 17 Oct 2011, at 10:33am, Nick Gammon wrote:

> Running under Windows XP, using sqlite3.exe version:
> 
> 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177 [snip]
> 
> Trying under Mac OS/X (Lion) does not appear to exhibit this problem.

Just a note that the version of the sqlite3 binary included with Lion is 
currently

SQLite version 3.7.5

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

Re: [sqlite] Very slow processing of some SELECT statements

[Nick Gammon]

On 17/10/2011, at 8:33 PM, Nick Gammon wrote:

> ...

> The following SQL:
> 
> ...

>  SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC';
> 
> 
> Takes over 2 seconds to execute (in particular, the SELECT statement).
> 

Further to the above, changing the column type from STRING to TEXT also fixes 
it.

However isn't over 2 seconds a bit much for evaluating a number, regardless of 
the column type?

- Nick

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

Re: [sqlite] DOMAIN new error code

[Tim Streater]

On 17 Oct 2011 at 10:00, Jean-Christophe Deschamps  wrote: 

> At least it would gives a fairly good hint as to what to look for and
> where to look.  You know that some extension function was passed an
> out-of-range argument during the course of the last operation.  From
> there, tracking down the culprit is much easier.
>
> Anyway the issue to solve is not "which library issued it" but "what
> extension function in the few last statements could have got invalid
> argument[s]".  The current situation is way too vague and leaves you
> dry about the cause.

With 350 calls in my code to query and exec, this is why I have a wrapper 
around them to include a location code, so that error logging can tell me 
straight away which call had the error and what was the SQL statement it was 
trying.

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

[sqlite] Very slow processing of some SELECT statements

[Nick Gammon]

Hello,

Running under Windows XP, using sqlite3.exe version:

3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177

As follows:

sqlite3 foo.db


The following SQL:
 

  DROP TABLE IF EXISTS rooms;
  DROP TABLE IF EXISTS exits;
  
  CREATE TABLE IF NOT EXISTS rooms (
  roomidINTEGER PRIMARY KEY AUTOINCREMENT,
  uid   TEXT NOT NULL  -- unique room ID
);

  CREATE TABLE IF NOT EXISTS exits (
  exitid  INTEGER PRIMARY KEY AUTOINCREMENT,
  fromuid STRING  NOT NULL -- exit from which room (in rooms table)
);

  CREATE INDEX IF NOT EXISTS fromuid_index ON exits (fromuid);
  
  SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC';


Takes over 2 seconds to execute (in particular, the SELECT statement).

Two changes make it much faster. Either:

* Delete the CREATE INDEX line

or

* Change the select statement to:

  SELECT * FROM exits WHERE fromuid = 'x2E515665758C87202B281C7FC';


I'm not sure what is going on, but it appears that somewhere internally SQLite3 
is trying to calculate the very large number 2e515665758 (2 times 10 to the 
power 515665758). And somehow the index is influencing this behaviour.

Trying under Mac OS/X (Lion) does not appear to exhibit this problem.

The string I am searching for is a hex hash string, generated by hashing 
various other things (not shown here). Occasionally it would appear, the hash 
"looks like" a decimal number with an exponent.

I draw your attention to the fact that the string being searched for is quoted, 
and that it is declared as a text field in the database.

Any suggestions welcomed.

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

Re: [sqlite] Limit COUNT

[reseok]

What about this:


SELECT
 CASE count(*) WHEN 5000 THEN 'More than 5000' ELSE 'Less than 5000' END
FROM (SELECT ID FROM table ORDER BY whatever LIMIT 5000 OFFSET 25000)



Fabian schrieb:
> 2011/10/16 Frank Missel 
> 
>> What do you want to attain with the count?
>>
>>
> I want to allow users to paginate through a result set. The pages are
> retreived through LIMIT/OFFSET, but to calculate the total number of pages,
> I have execute a separate COUNT() query (without LIMIT) once.
> 
> Because I'm basicly executing the same query twice just to get a total
> count, I'm trying to optimize this. Restricting the maximum number of pages
> to 10 should improve performance, if there was some way to put make COUNT()
> respect the LIMIT specified.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

[sqlite] "is not null" and index

[Yoav Apter]

Hi

I have the following table:

CREATE TABLE x ('col1', 'col2', 'col3')
Create col1index on x ('col1')

When I run this query: "select * from x where col1 is null" I see the index on 
x is used.
When I run this query: "select * from x where col1 is NOT null" I see the index 
on x is not used.

How can indexes be used with "not null" queries?


Thanks


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

Re: [sqlite] DOMAIN new error code

[Jean-Christophe Deschamps]

That is the default text for the error code, but your extension should
provide different text.


That's an half-backed solution.  I know of no application which test 
error codes AND THEN test error message _content_ before selecting a 
way to deal with the error.  Most of the times, the application (or 
third-party manager) will get the error code and report the standard 
message from SQLite, sometimes translated into local language via a 
fixed string table (not everyone on Earth reads English).



Adding a new error code won't really help that much.  For example what
happens if someone combines your math library with my Python extension as
you wouldn't be able to tell which is responsible for a SQLITE_DOMAIN.


At least it would gives a fairly good hint as to what to look for and 
where to look.  You know that some extension function was passed an 
out-of-range argument during the course of the last operation.  From 
there, tracking down the culprit is much easier.


Anyway the issue to solve is not "which library issued it" but "what 
extension function in the few last statements could have got invalid 
argument[s]".  The current situation is way too vague and leaves you 
dry about the cause.


I can think of no good reason why it shouldn't make it in the core 
code: it costs nothing, it doesn't eat any resource, it should need no 
change to the test harness, it covers something that isn't covered yet 
and can only make life a bit less difficult at times. 


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

Re: [sqlite] Limit COUNT

[Fabian]

2011/10/16 Frank Missel 

>
> But it sounds a bit like Fabian both wants to have the total number of
> records available and at the same time limit the count.
>
>
No, I only want to have a capped total available.

If I would go with Simons solution, I have to read the rows for the first
100 pages (or whatever the cap is) into a temporary table, just to show the
first page. I don't need a cache for all those other pages, so that seems a
lot of overhead. I only want to know if there are 100 or less pages (without
copying data around).

Maybe COUNT() is also creating a temporary table behind the scenes, then the
performance of Simons solutions would be comparable with what I have now,
and I would have the advantage that I can re-use that table to show
subsequent pages without reading from disk.

But I always assumed COUNT() was faster than copying between tables, maybe I
should just benchmark it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 query really slow with version > 3.7.5

[Owen Kaluza]

On 17 October 2011 17:51, Dan Kennedy  wrote:

>
> Likely you are hitting a problem causing SQLite to create an automatic
> index for this type of query. Fixed here:
>
>  
> http://www.sqlite.org/src/ci/**27c65d4d9c?sbs=0
>
> Updating to 3.7.8 should fix it.
>
>
That's it!, I've set
PRAGMA automatic_index = false;
and it's back to normal speed.

Seems a very long hold up just for creating an index though - It's a very
small table really, < 100 rows.

Anyway happy enough with a workaround for now, appreciate your help.

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