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

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

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);

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

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

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

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

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

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

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

Re: [sqlite] Limit COUNT

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(*)

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

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];

Re: [sqlite] DOMAIN new error code

-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

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

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

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) {

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

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

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

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,

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),

[sqlite] solw down in sqlite 3.7.7.8

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

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

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

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

Re: [sqlite] opinion on possible bad effects from detaching database 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

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

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

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

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

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

Re: [sqlite] Sqlite3_step

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

Re: [sqlite] Sqlite3_step

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

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

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.

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

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

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

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

Re: [sqlite] DOMAIN new error code

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 >

[sqlite] Very slow processing of some SELECT statements

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 (

Re: [sqlite] Limit COUNT

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

[sqlite] "is not null" and index

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.

Re: [sqlite] DOMAIN new error code

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

Re: [sqlite] Limit COUNT

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

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

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

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

On 10/17/2011 11:05 AM, Owen Kaluza wrote: Hi, After upgrading my OS I noticed a huge delay loading my application, I narrowed it down to my system libsqlite3 being upgraded from 3.7.4 to 3.7.7 I did some quick testing with different versions and it seems the change comes about between 3.7.5