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 and 3.7.6.2 The query difference is huge: Open database successful, SQLite version 3.7.5 0.2800 seconds to load 16 geometry records from database Open database successful, SQLite version 3.7.6.2 30.8400 seconds to load 16 geometry records from database As you can see from the last row the same delay occurs even when no records are found! Queries to other tables in the db cause no problem, it's only the table that holds the majority of the data (in blob fields), example query: SELECT timestep,rank,idx,type,data_type,size,count,width,minimum,maximum,dim_factor,units,labels,data FROM geometry WHERE object_id=1 AND timestep=0 ORDER BY idx,rank; Table schema: CREATE TABLE geometry (id INTEGER PRIMARY KEY ASC, object_id INTEGER, timestep INTEGER, rank INTEGER, idx INTEGER, type INTEGER, data_type INTEGER, size INTEGER, count INTEGER, width INTEGER, minimum REAL, maximum REAL, dim_factor REAL, units VARCHAR(32), labels VARCHAR(2048), properties VARCHAR(2048), data BLOB, FOREIGN KEY (object_id) REFERENCES object (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (timestep) REFERENCES timestep (id) ON DELETE CASCADE ON UPDATE CASCADE); 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 database successful, SQLite version 3.7.5 ... loaded 8 rows, 11300352 bytes, 0.2000 seconds ... loaded 2 rows, 16 bytes, 0.0100 seconds ... loaded 2 rows, 3390352 bytes, 0.0500 seconds ... loaded 2 rows, 1131968 bytes, 0. seconds ... loaded 2 rows, 1126736 bytes, 0.0100 seconds ... loaded 0 rows, 0 bytes, 0. seconds 0.2800 seconds to load 16 geometry records from database Open database successful, SQLite version 3.7.6.2 ... loaded 8 rows, 11300352 bytes, 5.4300 seconds ... loaded 2 rows, 16 bytes, 5.2200 seconds ... loaded 2 rows, 3390352 bytes, 5.2100 seconds ... loaded 2 rows, 1131968 bytes, 5. seconds ... loaded 2 rows, 1126736 bytes, 4.9900 seconds ... loaded 0 rows, 0 bytes, 4.9800 seconds 30.8400 seconds to load 16 geometry records from database As you can see from the last row the same delay occurs even when no records are found! Queries to other tables in the db cause no problem, it's only the table that holds the majority of the data (in blob fields), example query: SELECT timestep,rank,idx,type,data_type,size,count,width,minimum,maximum,dim_factor,units,labels,data FROM geometry WHERE object_id=1 AND timestep=0 ORDER BY idx,rank; The delay occurs in the read loop on: sqlite3_step(statement); Same delay if I issue this query in the sqlite3 shell (~ 5 seconds). Table schema: CREATE TABLE geometry (id INTEGER PRIMARY KEY ASC, object_id INTEGER, timestep INTEGER, rank INTEGER, idx INTEGER, type INTEGER, data_type INTEGER, size INTEGER, count INTEGER, width INTEGER, minimum REAL, maximum REAL, dim_factor REAL, units VARCHAR(32), labels VARCHAR(2048), properties VARCHAR(2048), data BLOB, FOREIGN KEY (object_id) REFERENCES object (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (timestep) REFERENCES timestep (id) ON DELETE CASCADE ON UPDATE CASCADE); Any ideas what has changed that could cause this and if there's a way I can work around it? Thanks in advance, Owen. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. 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. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6bp94ACgkQmOOfHg372QRsSwCfbFOBZtNiS9/yLDFLSgNliV6D AakAnjwXBV5MRvlbKv5MnOwzdCImXpmd =NkfC -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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 set > > and tje subsequent steps get the results from the cache? > > 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. > > If it turns out that one of the available indexes will produce the rows in > exactly the order wanted, then it will not be necessary to store all the > rows which should be returned. SQLite just stores the query plan: it > remembers how to find each next row, rather than remembering the data in > those rows. > > On the other hand, if it is necessary for SQLite to sort the rows itself, > because no good index has been created, then yes, it will have to sort the > rows in order to figure out which one to return first, and having sorted > them it will store them so that subsequent _step()s can just return the next > row. > > 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] 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 the first execution of step cache the entire result set > and tje subsequent steps get the results from the cache? 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. If it turns out that one of the available indexes will produce the rows in exactly the order wanted, then it will not be necessary to store all the rows which should be returned. SQLite just stores the query plan: it remembers how to find each next row, rather than remembering the data in those rows. On the other hand, if it is necessary for SQLite to sort the rows itself, because no good index has been created, then yes, it will have to sort the rows in order to figure out which one to return first, and having sorted them it will store them so that subsequent _step()s can just return the next row. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 first result row. Subsequent steps do only the work necessary to get the next row. However if your query needs the rows to be sorted then all of the result rows will have to be found first in order to do the sorting. (Some sorts can be satisfied from an index.) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6botAACgkQmOOfHg372QS+1gCeNPAie5rgEcUH+2gFJAT8iZVq n5UAoMl0HkipR2qN5Vr9dLg+jc+3dYhT =rHPf -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 set and tje subsequent steps get the results from the cache? Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 kind of errors? It would then be easier to track down the source of the issue if such error code existed, rather than being confronted to the uninformative SQLITE_ERROR "SQL error or missing database". Returning NULL silently is not the best choice in some cases (math functions for instance but it could serve other cases as well), where it is desirable to keep the distinction between a NULL argument (here returning NULL makes sense) and an out of range argument (here no return value makes sense). SQLITE_MISMATCH, SQLITE_MISUSE and SQLITE_RANGE already exist but have precise, completely different meanings and should certainly not be used in those cases. Something like: #define SQLITE_DOMAIN 27 /* Type, format or domain error in extension function */ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 that I applied it correctly. Thanks, Jeremy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 ignore matches when we have exactly
>> one match before and we joined to *previous* table with JOIN USING/NATURAL
>> JOIN.
>> So,
>> CREATE TABLE a(i, j);
>> CREATE TABLE b(j);
>> CREATE TABLE c(i);
>> CREATE TABLE d(j);
>> SELECT * FROM a JOIN b USING(j) JOIN c USING(i) JOIN d USING(j)
>> should work properly.
>>
>> The author or authors of this code dedicate any and all copyright interest
>> in this code to the public domain. We make this dedication for the benefit
>> of the public at large and to the detriment of our heirs and successors.
>> We intend this dedication to be an overt act of relinquishment in perpetuity
>> of all present and future rights to this code under copyright law.
>>
>> Signed-off-by: Yuriy M. Kaminskiy
>>
>> Index: sqlite3-3.7.8/src/resolve.c
>> ===
>> --- sqlite3-3.7.8.orig/src/resolve.c2011-10-16 09:18:20.0 +0400
>> +++ sqlite3-3.7.8/src/resolve.c 2011-10-16 09:39:53.0 +0400
>> @@ -190,33 +190,34 @@ static int lookupName(
>> for(j=0, pCol=pTab->aCol; jnCol; j++, pCol++){
>> if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
>> IdList *pUsing;
>> -cnt++;
>> -pExpr->iTable = pItem->iCursor;
>> -pExpr->pTab = pTab;
>> -pMatch = pItem;
>> -pSchema = pTab->pSchema;
>> -/* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY
>> */
>> -pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
>> -if( inSrc-1 ){
>> - if( pItem[1].jointype & JT_NATURAL ){
>> +if( cnt == 1 ){
out of paranoia:
+ assert( i>0 );
+ assert( pItem->jointype!=0 );
(but both should be completely impossible)
>> + /* We already met this name once in some previous table(s),
>> + ** but... */
>> + if( pItem->jointype & JT_NATURAL ){
>> /* If this match occurred in the left table of a natural
>> join,
>> ** then skip the right table to avoid a duplicate match */
>> -pItem++;
>> -i++;
>> - }else if( (pUsing = pItem[1].pUsing)!=0 ){
>> +continue;
>> + }else if( (pUsing = pItem->pUsing)!=0 ){
>> /* If this match occurs on a column that is in the USING
>> clause
>> ** of a join, skip the search of the right table of the join
>> ** to avoid a duplicate match there. */
>> int k;
>> for(k=0; knId; k++){
>> if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){
>> -pItem++;
>> -i++;
>> break;
>> }
>> }
>> +if( k!=pUsing->nId )
>> + continue;
fwiw, I think both "continue;" can be replaced with "break;"
>> }
>> }
>> +cnt++;
>> +pExpr->iTable = pItem->iCursor;
>> +pExpr->pTab = pTab;
>> +pMatch = pItem;
>> +pSchema = pTab->pSchema;
>> +/* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY
>> */
>> +pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
>> break;
>> }
>> }
>
> I tried this patch and it does appear to fix the issue, but I'm also
> getting occasional segfaults in lookupName after applying it.
Thanks for testing.
Have no idea what can trigger sigsegv here.
Patch applied with any rejects/offsets/fuzziness?
`make test` shown no problem. `make fulltest` got some problems:
prepare.analyze3-1.1.8...
Expected: [999 999 499500]
Got: [2000 0 499500]
prepare.analyze3-1.1.9...
Expected: [999 999 499500]
Got: [2000 0 499500]
...
11 errors out of 2630788 tests
Failures on these tests: prepare.analyze3-1.1.8 prepare.analyze3-1.1.9
prepare.analyze3-1.2.8 prepare.analyze3-1.2.9 prepare.analyze3-1.3.8
prepare.analyze3-1.3.9 prepare.analyze3-2.4 prepare.analyze3-2.6
prepare.analyze3-2.7 prepare.analyze3-2.8 prepare.analyze3-2.9
... but they fails for me even with patch reverted.
And no segfaults.
Running few simple tests under valgrind have not produced anything suspicious.
> Haven't built a debug version of SQLite yet to determine exactly where.
I think SQL statement triggering segv would be enough.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 rows in the group." http://www.sqlite.org/lang_aggfunc.html If this is what you mean, then stick to it :) http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better/2710703#2710703 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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); sqlite> SELECT * FROM b; a 1 sqlite> SELECT b.a FROM b; a 1 sqlite> SELECT c.a FROM b AS c; a 1 sqlite> SELECT c.a FROM (SELECT * FROM b) AS c; c.a 1 sqlite> SELECT c.a FROM b JOIN b AS c USING(a); a 1 sqlite> SELECT c.a FROM b JOIN (SELECT * FROM b) AS c USING(a); c.a 1 Note how if the column comes from a subselect, the subselect alias qualifier is included in the column header. I would expect a column header of a for all of these queries, and all other databases I've tested with use a column header of a. Is this a bug in SQLite? Jeremy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 have exactly
> one match before and we joined to *previous* table with JOIN USING/NATURAL
> JOIN.
> So,
> CREATE TABLE a(i, j);
> CREATE TABLE b(j);
> CREATE TABLE c(i);
> CREATE TABLE d(j);
> SELECT * FROM a JOIN b USING(j) JOIN c USING(i) JOIN d USING(j)
> should work properly.
>
> The author or authors of this code dedicate any and all copyright interest
> in this code to the public domain. We make this dedication for the benefit
> of the public at large and to the detriment of our heirs and successors.
> We intend this dedication to be an overt act of relinquishment in perpetuity
> of all present and future rights to this code under copyright law.
>
> Signed-off-by: Yuriy M. Kaminskiy
>
> Index: sqlite3-3.7.8/src/resolve.c
> ===
> --- sqlite3-3.7.8.orig/src/resolve.c 2011-10-16 09:18:20.0 +0400
> +++ sqlite3-3.7.8/src/resolve.c 2011-10-16 09:39:53.0 +0400
> @@ -190,33 +190,34 @@ static int lookupName(
> for(j=0, pCol=pTab->aCol; jnCol; j++, pCol++){
> if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
> IdList *pUsing;
> - cnt++;
> - pExpr->iTable = pItem->iCursor;
> - pExpr->pTab = pTab;
> - pMatch = pItem;
> - pSchema = pTab->pSchema;
> - /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY
> */
> - pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
> - if( inSrc-1 ){
> - if( pItem[1].jointype & JT_NATURAL ){
> + if( cnt == 1 ){
> + /* We already met this name once in some previous table(s),
> + ** but... */
> + if( pItem->jointype & JT_NATURAL ){
> /* If this match occurred in the left table of a natural join,
> ** then skip the right table to avoid a duplicate match */
> - pItem++;
> - i++;
> - }else if( (pUsing = pItem[1].pUsing)!=0 ){
> + continue;
> + }else if( (pUsing = pItem->pUsing)!=0 ){
> /* If this match occurs on a column that is in the USING
> clause
> ** of a join, skip the search of the right table of the join
> ** to avoid a duplicate match there. */
> int k;
> for(k=0; knId; k++){
> if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){
> - pItem++;
> - i++;
> break;
> }
> }
> + if( k!=pUsing->nId )
> + continue;
> }
> }
> + cnt++;
> + pExpr->iTable = pItem->iCursor;
> + pExpr->pTab = pTab;
> + pMatch = pItem;
> + pSchema = pTab->pSchema;
> + /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY
> */
> + pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
> break;
> }
> }
I tried this patch and it does appear to fix the issue, but I'm also
getting occasional segfaults in lookupName after applying it. Haven't
built a debug version of SQLite yet to determine exactly where.
Jeremy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 to get a grip on that LIMIT construct :) The fine manual is there to help, please see "ORDER BY and LIMIT/OFFSET Clauses": http://www.sqlite.org/lang_select.html In the meantime: select count( * ) from ( select 1 fromtable limit 5000 ) ___ 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 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 a bit like Fabian both wants to have the total number of records available and at the same time limit the count. It is just not possible to do both at the same time as far as I can see. Thus, you are left with following options as far as I can see: 1. As Slavin mentions: Read a certain number of records, e.g. 101 and change your code so that the user can ask for e.g. 100 additional records at a time. Possibly you could have an extra function that the user could activate to have a total count if he so wishes. This would then read all records and you would not have to worry about performance as all records (unless there are very many) will be in the memory cache and subsequent request for these records should be fast. 2. If there are many concurrent users and the query requests from these are quite predictable, e.g. straight reads of all records in certain tables, you could perhaps have a local background job regularly count the number of records for the relevant tables/queries and store these count results in a special status table. Your client program could then read the number of records from the special status table as well as the first 101 records from the actual data carrying table. The user could then get the first 100 records, and continue with batches of 100 as needed. If these options does not seem to fit, I think that you have to explain a bit more about how your solution is, what you expect and how you access the database. /Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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. ___ I can't tell if this is more efficient but it's one query select *,(select count(*) from table) as total_record_count from table limit 100; /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 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. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 >> >>> 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. > > You're using a programming language. So do this: > > numberOfRows = SELECT count(*) FROM myTable > if (numberOfRows > 100) then numberOfRows = 100 > > 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] 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 execute a separate COUNT() query (without LIMIT) once. You're using a programming language. So do this: numberOfRows = SELECT count(*) FROM myTable if (numberOfRows > 100) then numberOfRows = 100 Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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. ___ Maybe GROUP BY and HAVING can help you? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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) 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
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 [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabian Sent: zondag 16 oktober 2011 13:09 To: General Discussion of SQLite Database Subject: [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 after it reached the upper limit, in order to save performance. ___ 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] 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. You could instead try: select count(*) from (select * from table limit 500) ; That would give you the number 500. But it is kind of meaningless. You might as well write: select * from table limit 500 ; And get the actual 500 first rows. In this case the result set is build up as the select statement processing loops through the records in the table, and thus it only needs to take 500 rows. Again, if you add a count(*) it needs to traverse all records to get the first and only row of the result set that states the number of records in the table. It is the same if you specify ORDER BY together with limit. You will only get the x number of records in the LIMIT statement but before that all records will have to be traversed and sorted before you the 500 rows, unless perhaps there already is an index with one component fitting the ORDER BY perfectly. What do you want to attain with the count? /Frank > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Fabian > Sent: 16 October 2011 19:09 > To: General Discussion of SQLite Database > Subject: [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 after it reached > the upper limit, in order to save performance. > ___ > 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] 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 after it reached the upper limit, in order to save performance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 value '1', not integer value 1 (since it assumes that all > values had the TEXT affinity applied to them when they were inserted). And > the query would return no rows. You are right. However, in my case it is a bit special in that the values in the fields actually already are stored correctly as REAL numbers but because the declared data type is DECIMAL (which is used in some databases), Excel will not treat the real numbers as such. Changing the declared data type does the trick! As for text to integer as in your example, I guess it could also be done. First the declared data type could be changed as I have described. After that an update statement could set the = CAST( as INTEGER). But you are right that indexes in this case would have to be rebuilt with the REINDEX statement. However, that might still be better than having to recreate the whole table and build indexes. /Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 disallow changing the declared > data type of an existing column since the actual stored data of a column > might be of any type - again, according to the basic philosophy of SQLite. > Thus changing a declared data type of an existing column should not break > anything between the schema and the stored data. But I am not sure if it is > even possible. If not, I will move my many Gigabytes of data around, but I > thought, it would be worth just checking first. Well, I looked a bit more into it and found that indeed the declared data type of a column in an existing table can be changed without breaking anything. In the example below the data type "DECIMAL" is changed to "REAL" (which will make the real numbers transferred to Excel via the ODBC driver behave as such): 1. Backup the database 2. SQL statement: pragma writable_schema = 1; 3. SQL statement: update sqlite_master set sql = replace(sql, 'DECIMAL', 'REAL ') where type = 'table' and sql like '%decimal%' ; 4. 2. SQL statement: pragma writable_schema = 0; Notice the three spaces after REAL. They are not really needed but in case there are extra field attributes after the data type in the CREATE TABLE statement and the attributes are column aligned, the alignment will be retained after the change. I guess other aspects of the tables can also be changed, e.g. renaming columns -- but here one has to remember any indexes, constraints, etc. So that is a bit more daring but probably doable. For dropping columns one needs to copy the data to a new table as far as I can see. I take a bow: Hurrah for SQLite. This is truly magnificent! WARNING: Be sure make a backup copy of the database before doing anything as changing the schema can corrupt it. Any tinkering with the sqlite_master table is done at your own risk. Don't blame me if anything goes wrong :-). Also, I tried out the above on a small test database of the same structure as a production database and after changing contents of sqlite_master I tested various statements against the database to see that everything worked as it should. I will wait a while before changing my production databases, just in case someone might see anything wrong with the above. /Frank Missel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 many programming languages that I have worked with. However, I have come to appreciate SQLite as a powerful data container that you can use to handle data in various ways according to need. With e.g. check restraints one can enforce data types on columns at a basic level. On the other hand, it is sometimes more useful to just let data enter the database and then later on use data validation to the extent that it is needed. With this open philosophy of the SQLite database I thought that perhaps there really isn't any compelling reason to disallow changing the declared data type of an existing column since the actual stored data of a column might be of any type - again, according to the basic philosophy of SQLite. Thus changing a declared data type of an existing column should not break anything between the schema and the stored data. But I am not sure if it is even possible. If not, I will move my many Gigabytes of data around, but I thought, it would be worth just checking first. I'm not sure I completely understand the question (is there one?), but consider this: CREATE TABLE t(a INTEGER); CREATE INDEX i ON t(a); INSERT INTO t VALUES(1); INSERT INTO t VALUES(2); Then: SELECT * FROM t WHERE a=1; 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 value '1', not integer value 1 (since it assumes that all values had the TEXT affinity applied to them when they were inserted). And the query would return no rows. If you use 3.7.8, and delay creating any indexes until after all the data has been inserted into the new tables, SQLite will use an external merge-sort to build the new indexes. This should be much faster than using an earlier version. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 not perceived as the right type and so e.g. numerical summary functions will not work. The issue is covered by this thread: http://www.mail-archive.com/sqlite-users@sqlite.org/msg64714.html This is the reason why I would want to change the data type of existing columns. Otherwise, I guess it would not matter that much as the types of SQLite are bound to the actual data values and not the columns in the schema which only have a data type affinity. Instead of changing the data type I could of course just enclose all columns in CAST statements when selecting data to Excel. However, I prefer to only have to do that when including expressions. Also, I have updated the frame work that I use to handle SQLite databases so that any future tables will only declare data types that will work correctly towards Excel. Thus I now wish to make sure that all existing databases conform with these data types. I was just about to write some code to browse through a data base, find all tables with columns declared as a certain data type to be changed and then move the data over to a new identical table (but defined with the new data type for the relevant columns). However, before going through this exercise I thought I would just ask if any one knew of a better way to do this, as there are several quite large databases to be so handled. 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 many programming languages that I have worked with. However, I have come to appreciate SQLite as a powerful data container that you can use to handle data in various ways according to need. With e.g. check restraints one can enforce data types on columns at a basic level. On the other hand, it is sometimes more useful to just let data enter the database and then later on use data validation to the extent that it is needed. With this open philosophy of the SQLite database I thought that perhaps there really isn't any compelling reason to disallow changing the declared data type of an existing column since the actual stored data of a column might be of any type - again, according to the basic philosophy of SQLite. Thus changing a declared data type of an existing column should not break anything between the schema and the stored data. But I am not sure if it is even possible. If not, I will move my many Gigabytes of data around, but I thought, it would be worth just checking first. /Frank Missel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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
days','weekday 0') and date('2011-12-18','weekday 6');
18|2011-12-18
19|2011-12-19
20|2011-12-20
21|2011-12-21
22|2011-12-22
23|2011-12-23
24|2011-12-24
sqlite> select * from fechas where fecha between date('2011-12-24','-6
days','weekday 0') and date('2011-12-24','weekday 6');
18|2011-12-18
19|2011-12-19
20|2011-12-20
21|2011-12-21
22|2011-12-22
23|2011-12-23
24|2011-12-24
sqlite> select * from fechas where fecha between date('2011-12-21','-6
days','weekday 0') and date('2011-12-21','weekday 6');
18|2011-12-18
19|2011-12-19
20|2011-12-20
21|2011-12-21
22|2011-12-22
23|2011-12-23
24|2011-12-24
Igor... I thought I was faced with a syntax problem, thanks for putting me
on track. ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
