[sqlite] sqlite3 query really slow with version > 3.7.5

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 and 3.7.6.2 The query difference is huge: Open

Re: [sqlite] DOMAIN new error code

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 16/10/11 17:21, Jean-Christophe Deschamps wrote: > rather than being confronted to the uninformative SQLITE_ERROR "SQL > error or missing database". That is the default text for the error code, but your extension should provide different text.

Re: [sqlite] Sqlite3_step

Hi, I do not have a ORDER BY , only a WHERE clause. So sorting should not be the cause for the overhead. -Sreekumar On Mon, Oct 17, 2011 at 9:08 AM, Simon Slavin wrote: > > On 17 Oct 2011, at 4:22am, Sreekumar TP wrote: > > > In case of a prepared statement that uses

Re: [sqlite] Sqlite3_step

On 17 Oct 2011, at 4:22am, Sreekumar TP wrote: > In case of a prepared statement that uses SELECT > , the first sqlite3_step statement consumes a lot of time (order of > magnitude can be 100 or more) > when compared to the subsequent executions of step used to iterate through > the results. Does

Re: [sqlite] Sqlite3_step

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 16/10/11 20:22, Sreekumar TP wrote: > Does the first execution of step cache the entire result set and tje > subsequent steps get the results from the cache? No, the first execution does the minimum amount of work necessary in order to get you the

[sqlite] Sqlite3_step

Hi, In case of a prepared statement that uses SELECT , the first sqlite3_step statement consumes a lot of time (order of magnitude can be 100 or more) when compared to the subsequent executions of step used to iterate through the results. Does the first execution of step cache the entire result

[sqlite] DOMAIN new error code

I have the need to use some math extension functions. While testing them I found it very difficult to select an informative error code in case of a domain error, like sqrt(-1). Would it be sensible to create a new generic error code in some next release for extension functions to report such

Re: [sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

On Sun, Oct 16, 2011 at 3:28 PM, Yuriy Kaminskiy wrote: > Thanks for testing. > Have no idea what can trigger sigsegv here. > Patch applied with any rejects/offsets/fuzziness? Error on my end. I had to hand apply the patch and I did it wrong, sorry. Everything looks good now

Re: [sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

Jeremy Evans wrote: > On Sat, Oct 15, 2011 at 11:46 PM, Yuriy Kaminskiy wrote: > 7> Whoops, patch eaten by hungry ewoks. Hopefully, inlining will work better: >> Subject: fix false "ambiguous column" detection in multiple JOIN USING >> >> Instead of skipping only *next* table, we

Re: [sqlite] Limit COUNT

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

Re: [sqlite] Limit COUNT

> select count(*) from (select 1 from table limit 5000) SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Column headers include subselect alias if column is from a subselect

I'm not sure if this is expected/desired behavior, but I think it is incorrect: $ sqlite3 SQLite version 3.7.8 2011-09-19 14:49:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .headers ON sqlite> CREATE TABLE b (a integer); sqlite> INSERT INTO b VALUES (1);

Re: [sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

On Sat, Oct 15, 2011 at 11:46 PM, Yuriy Kaminskiy wrote: 7> Whoops, patch eaten by hungry ewoks. Hopefully, inlining will work better: > > Subject: fix false "ambiguous column" detection in multiple JOIN USING > > Instead of skipping only *next* table, we ignore matches when we

[sqlite] sqlite_stat3

Hi, just noticed the timeline comments concerning sqlite_stat3. Will the new code also regard the INTEGER PRIMARY KEY "index" distribution? regards gerd ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Limit COUNT

On Oct 16, 2011, at 1:09 PM, Fabian wrote: > How can you limit a count-query? I tried: > > SELECT COUNT(*) FROM table LIMIT 5000 > > But it ignores the LIMIT clause. No it doesn't, it works as advertised. You are falling into the same trap as you did just a couple of threads ago. You need

Re: [sqlite] Limit COUNT

> boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: 16 October 2011 21:53 > > Perhaps he could read the rows LIMIT 100 into a buffer (after all, he's going > to need them eventually for when he displays them), then count how many > rows he got. Yeah, I would go that way also. But it sounds

Re: [sqlite] Limit COUNT

On 10/16/11 14:21, Fabian wrote: 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

[sqlite] IT Test message - please ignore and remove

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

Re: [sqlite] Limit COUNT

On 16 Oct 2011, at 2:50pm, Bart Smissaert wrote: > He is trying to make it more efficient, so stop counting if count > X. > So setting the count after having counted the whole lot won't help. Then he can't use count() because SQLite's implementation of it is not efficient for that. Perhaps he

Re: [sqlite] Limit COUNT

He is trying to make it more efficient, so stop counting if count > X. So setting the count after having counted the whole lot won't help. RBS On Sun, Oct 16, 2011 at 2:46 PM, Simon Slavin wrote: > > On 16 Oct 2011, at 1:21pm, Fabian wrote: > >> 2011/10/16 Frank Missel

Re: [sqlite] Limit COUNT

On 16 Oct 2011, at 1:21pm, Fabian wrote: > 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

Re: [sqlite] Limit COUNT

On 10/16/11 14:21, Fabian wrote: 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

Re: [sqlite] Limit COUNT

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)

Re: [sqlite] Limit COUNT

SELECT COUNT(*) FROM table will always return exactly 1 record (with the count). LIMIT 5000 limits the number of records returned to 5000. As 1 record < 5000 records the 5000 is effecively ignored -- Jos -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Limit COUNT

Hi Fabian, The problem is that the limit apparently is applied on the result set, and in this case the result set is only one row which is less than the 5000. The culprit is the count(*) which basically says that to get the first row in the result set all rows from the table has to be processed.

[sqlite] Limit COUNT

How can you limit a count-query? I tried: SELECT COUNT(*) FROM table LIMIT 5000 But it ignores the LIMIT clause. I think the workaround would be counting the results of a sub-query, but I'm trying to understand whats wrong with the syntax above. The goal is to make the engine stop iterating

Re: [sqlite] Changing the data type of an existing table

Hi Dan, > The two values in table "t" are stored in integer form. Were you to magically > change the database schema without rebuilding the underlying > b-trees: > >CREATE TABLE t(a TEXT); > > and then execute the same SELECT, it would not work. SQLite would search > the index for text

Re: [sqlite] Changing the data type of an existing table

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Frank Missel > Sent: 16 October 2011 15:37 > > With this open philosophy of the SQLite database I thought that perhaps > there really isn't any compelling reason to

Re: [sqlite] Changing the data type of an existing table

On 10/16/2011 02:37 PM, Frank Missel wrote: When I first learned about SQLite I never like the relaxed handling of data where column data types from the schema are not enforced strictly on the actual data in the tables. This felt instinctively wrong as it is very contrary to other databases and

[sqlite] Changing the data type of an existing table

Unfortunately, a need has arisen to change the data type of columns in existing tables in some quite large databases. This is due to the fact that Excel does not take kindly to data received from the SQLite ODBC driver unless they are of a certain data type (the declared name), i.e. the data are

[sqlite] Time and date functions

Thank you all. The solution of Jean-Christophe is perfect and thanks to Igor and Michael I begin to understand the logic of the processing of dates in SQLite, which is much more powerful than it had expected at first. sqlite> select * from fechas where fecha between date('2011-12-18','-6

[sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

Yuriy Kaminskiy wrote: > Yuriy Kaminskiy wrote: >> Jeremy Evans wrote: >>> After being open for more than 2 years, this ticket >>> (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1) >>> was closed by Dr. Hipp with the comment: >>> >>> "The column name is ambiguous. Does

[sqlite] [patch] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

Yuriy Kaminskiy wrote: > Jeremy Evans wrote: >> After being open for more than 2 years, this ticket >> (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1) >> was closed by Dr. Hipp with the comment: >> >> "The column name is ambiguous. Does it mean a.a or b.a? The result