Re: [sqlite] sqlite-users Digest, Vol 39, Issue 13
On Sun, Mar 13, 2011 at 10:31 AM, Igor Tandetnik wrote: > Garry Watkins wrote: >> Not sure why you are using a subselect with a union all. >> >> SELECT * >> FROM multiturnTable >> WHERE (player1 ='?' OR player2 ='?') > > Because OR prevents SQLite from using an index on either player1 or player2, > and turns the query into full table scan. Recent releases can optimize ORs into UNION ALLs. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Amalgamation code doesn't supoort limit on update / delete
On Sun, 2011-03-13 at 14:53 -0400, Pavel Ivanov wrote: > On Sun, Mar 13, 2011 at 2:46 PM, Steven Hartland > wrote: > > After spending many hours banging my head trying to figure out why > > sqlite-jdbc was erroring on a delete with a limit even when compiled > > with SQLITE_ENABLE_UPDATE_DELETE_LIMIT I finally tracked it down > > to the fact that the Amalgamation download is simply missing half > > the code to support it :( > > > > Compiling from standard source, in this case from FreeBSD ports > > results in a sqlite3.c which has much more code that references > > the options to support update / delete limits than the standard > > Amalgamation version. > > > > I can't find anything that says this is a know limitation so can > > only assume its an oversight / bug, is this the case? > > http://lmgtfy.com/?q=SQLITE_ENABLE_UPDATE_DELETE_LIMIT+site%3Asqlite.org&l=1 > > If you look for description of SQLITE_ENABLE_UPDATE_DELETE_LIMIT on > the resulting page you'll see that it's not a bug or oversight, it's a > documented feature. You could write something like this instead: UPDATE some_table SET some_field = ? WHERE the_id IN ( SELECT the_id FROM some_table WHERE [...] LIMIT [...]); assuming that "the_id" is either the primary key column or else has a unique index. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Amalgamation code doesn't supoort limit on update / delete
http://lmgtfy.com/?q=SQLITE_ENABLE_UPDATE_DELETE_LIMIT+site%3Asqlite.org&l=1 If you look for description of SQLITE_ENABLE_UPDATE_DELETE_LIMIT on the resulting page you'll see that it's not a bug or oversight, it's a documented feature. Pavel On Sun, Mar 13, 2011 at 2:46 PM, Steven Hartland wrote: > After spending many hours banging my head trying to figure out why > sqlite-jdbc was erroring on a delete with a limit even when compiled > with SQLITE_ENABLE_UPDATE_DELETE_LIMIT I finally tracked it down > to the fact that the Amalgamation download is simply missing half > the code to support it :( > > Compiling from standard source, in this case from FreeBSD ports > results in a sqlite3.c which has much more code that references > the options to support update / delete limits than the standard > Amalgamation version. > > I can't find anything that says this is a know limitation so can > only assume its an oversight / bug, is this the case? > > Regards > Steve > > > > This e.mail is private and confidential between Multiplay (UK) Ltd. and the > person or entity to whom it is addressed. In the event of misdirection, the > recipient is prohibited from using, copying, printing or otherwise > disseminating it or any information contained in it. > > In the event of misdirection, illegible or incomplete transmission please > telephone +44 845 868 1337 > or return the E.mail to postmas...@multiplay.co.uk. > > ___ > sqlite-dev mailing list > sqlite-...@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strange UB detected
On Sun, Mar 13, 2011 at 06:14:49PM +0200, Eugene N scratched on the wall: > uchar* pblah[1]; > pblah[0] = (uchar*)malloc(10); > pblah[1] = (uchar*)malloc(10); // notice the order > Any ideas why? Because pblah is a *one*-element array. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strange UB detected
Thanks! I forgot about C array numeration... Sorry for being an arse. Eugene 2011/3/13 Drake Wilson > Quoth Eugene N , on 2011-03-13 18:14:49 > +0200: > > uchar* pblah[1]; > > > > pblah[0] = (uchar*)malloc(10); > > > > pblah[1] = (uchar*)malloc(10); // notice the order > > > > sqlite3* db; > > Your C code is broken. pblah is an array of 1 element, which is > accessible (among other ways) as pblah[0]. pblah[1] is out of bounds, > and depending on how the compiler allocates those vars it may wind up > aliasing the db pointer. This is not an SQLite problem. > > ---> Drake Wilson > ___ > 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] strange UB detected
Quoth Eugene N , on 2011-03-13 18:14:49 +0200: > uchar* pblah[1]; > > pblah[0] = (uchar*)malloc(10); > > pblah[1] = (uchar*)malloc(10); // notice the order > > sqlite3* db; Your C code is broken. pblah is an array of 1 element, which is accessible (among other ways) as pblah[0]. pblah[1] is out of bounds, and depending on how the compiler allocates those vars it may wind up aliasing the db pointer. This is not an SQLite problem. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] strange UB detected
Hi
I detected a strange "feature" of sqlite3. Somebody called it Pointer
Liberation army strike.
uchar* pblah[1];
pblah[0] = (uchar*)malloc(10);
pblah[1] = (uchar*)malloc(10); // notice the order
sqlite3* db;
// this call magically kills pblah[1] by making it 0x0
int ret = sqlite3_open("./points.db", &db);
Any ideas why?
Thanks
Egene
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a query with several criteria
To paraphase Forrect Gump, reasonable is as reasonable does. It's computed by cachesize*pagesize Pagesize is limited to 65536 but I don't know what cachesize is lmited to (docs don't say). You can make it abosolutely huge if you have the memory for it. And 40MB sounds awfully small to me. Try cachesize=10 and see what happens. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ian Hardingham [i...@omroth.com] Sent: Sunday, March 13, 2011 8:40 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Optimising a query with several criteria Hi Michael, thanks for this. My database is 40 megabytes (and growing slowly) - is that a reasonable cachesize? On 13/03/2011 13:07, Black, Michael (IS) wrote: > You don't say how big your database is. > > My guess is when you see the server using a lot of RAM (and exactly how are > you measuring this?) that it's flushing its disk cache. If you're on Unix > use vmstat to see what your OS cache is doing. > > So...perhaps if you increase SQLite's internal cache it might help. > > pragma cachesize=2000 is the default with a default pagesize of 1024 I think > so it's 2MB by default. > > Make your cache as big as your database is and see what happens. That way > the OS will be forced into swap if it needs more RAM and the low-priority > items will get swapped out instead of you losing disk cache. > > > Michael D. Black > Senior Scientist > NG Information Systems > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Ian Hardingham [i...@omroth.com] > Sent: Sunday, March 13, 2011 6:43 AM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] Optimising a query with several criteria > > Hey guys. > > I've optimised most of my queries to work effectively, but I have one > which is sometimes causing me problems. It is: > > SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM > multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM > multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 > OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > multiturnTable has about 100,000 rows. > > (My apologies if I keep harping on about this same general area). > > This query needs to run only once per client session - which isn't very > often. However, when the server is taking up a lot of RAM I've seen > this query take 30 seconds. When there's plenty of RAM it only takes in > the region of 100ms. Does anyone have any advice? > > Thanks, > Ian > ___ > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 39, Issue 13
Garry Watkins wrote: > Not sure why you are using a subselect with a union all. > > SELECT * > FROM multiturnTable > WHERE (player1 ='?' OR player2 ='?') Because OR prevents SQLite from using an index on either player1 or player2, and turns the query into full table scan. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a query with several criteria
On 13 Mar 2011, at 11:43am, Ian Hardingham wrote: > SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM > multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM > multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 > OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > multiturnTable has about 100,000 rows. Do you have an index on (p1Declined,p2Declined) to make that part of the search trivial ? Do you need both the 'completed' and the 'submitted' clauses or does one imply the other ? Ignoring the subSELECTs, how many rows satisfy all the zeros clauses ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a query with several criteria
Hi Michael, thanks for this. My database is 40 megabytes (and growing slowly) - is that a reasonable cachesize? On 13/03/2011 13:07, Black, Michael (IS) wrote: > You don't say how big your database is. > > My guess is when you see the server using a lot of RAM (and exactly how are > you measuring this?) that it's flushing its disk cache. If you're on Unix > use vmstat to see what your OS cache is doing. > > So...perhaps if you increase SQLite's internal cache it might help. > > pragma cachesize=2000 is the default with a default pagesize of 1024 I think > so it's 2MB by default. > > Make your cache as big as your database is and see what happens. That way > the OS will be forced into swap if it needs more RAM and the low-priority > items will get swapped out instead of you losing disk cache. > > > Michael D. Black > Senior Scientist > NG Information Systems > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Ian Hardingham [i...@omroth.com] > Sent: Sunday, March 13, 2011 6:43 AM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] Optimising a query with several criteria > > Hey guys. > > I've optimised most of my queries to work effectively, but I have one > which is sometimes causing me problems. It is: > > SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM > multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM > multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 > OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > multiturnTable has about 100,000 rows. > > (My apologies if I keep harping on about this same general area). > > This query needs to run only once per client session - which isn't very > often. However, when the server is taking up a lot of RAM I've seen > this query take 30 seconds. When there's plenty of RAM it only takes in > the region of 100ms. Does anyone have any advice? > > Thanks, > Ian > ___ > 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] sqlite-users Digest, Vol 39, Issue 13
Not sure why you are using a subselect with a union all. Try this: SELECT * FROM multiturnTable WHERE (player1 ='?' OR player2 ='?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > Message: 1 > Date: Sun, 13 Mar 2011 11:43:30 + > From: Ian Hardingham > Subject: [sqlite] Optimising a query with several criteria > To: General Discussion of SQLite Database > Message-ID: <4d7cade2.5050...@omroth.com> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hey guys. > > I've optimised most of my queries to work effectively, but I have one > which is sometimes causing me problems. It is: > > SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM > multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM > multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 > OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > multiturnTable has about 100,000 rows. > > (My apologies if I keep harping on about this same general area). > > This query needs to run only once per client session - which isn't very > often. However, when the server is taking up a lot of RAM I've seen > this query take 30 seconds. When there's plenty of RAM it only takes in > the region of 100ms. Does anyone have any advice? > > Thanks, > Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a query with several criteria
You don't say how big your database is. My guess is when you see the server using a lot of RAM (and exactly how are you measuring this?) that it's flushing its disk cache. If you're on Unix use vmstat to see what your OS cache is doing. So...perhaps if you increase SQLite's internal cache it might help. pragma cachesize=2000 is the default with a default pagesize of 1024 I think so it's 2MB by default. Make your cache as big as your database is and see what happens. That way the OS will be forced into swap if it needs more RAM and the low-priority items will get swapped out instead of you losing disk cache. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ian Hardingham [i...@omroth.com] Sent: Sunday, March 13, 2011 6:43 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Optimising a query with several criteria Hey guys. I've optimised most of my queries to work effectively, but I have one which is sometimes causing me problems. It is: SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 multiturnTable has about 100,000 rows. (My apologies if I keep harping on about this same general area). This query needs to run only once per client session - which isn't very often. However, when the server is taking up a lot of RAM I've seen this query take 30 seconds. When there's plenty of RAM it only takes in the region of 100ms. Does anyone have any advice? Thanks, Ian ___ 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] Optimising a query with several criteria
Hey guys. I've optimised most of my queries to work effectively, but I have one which is sometimes causing me problems. It is: SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 multiturnTable has about 100,000 rows. (My apologies if I keep harping on about this same general area). This query needs to run only once per client session - which isn't very often. However, when the server is taking up a lot of RAM I've seen this query take 30 seconds. When there's plenty of RAM it only takes in the region of 100ms. Does anyone have any advice? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
