[sqlite] newbie question regarding my sqlite code
Hello, I am a new sqilte user and I am learning sqlite code in my spare time.. I have always used sql code with Microsoft Access and Base (openoffice). Therefore, sorry to ask a question very simple . When I write the very simple code: select sum(età) from dipendenti everything works fine and the result is 100. When I try a bit longer query the result for the sum regarding the column età from the table dipendenti changes and it is wrong. That is 25200? (instead of the right value 100!). The query is: select avg(age), avg(durata), sum(età) from acoda, main, dipendenti In the above query the avg results for the column age (table acoda) and the column durata (table main) are right. The only value wrong is the third, that it, sum (for the table dipendenti, column age, 25200 instead of the right value 100). The column age in the table dipendenti is not present in the other two tables (acoda, main). What's wrong with the second query? Thanks in advance. Best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation to learn sqlite code for newbies?
Hello everybody, Does anyone know a link (Internet) where I can find some simple tutorials regarding sqlite code? I am looking for some code to apply using the select condition. I am an accountant as trade and I have many big .csv files. I would like to learn sqlite code to query them. At present, needless to say, I have already checked the sqlite web site of course. I have started reading this mailing list as well :-) I read the documentation for newbies should must be improved in the sqlite web page but who knows when :-) I "googled" a bit but most examples I found are for software developers and not for end-users (e.g I found code for using sqlite with python, or ruby and so on). For Mysql, for example, the books with code for end user are endless. I know I could use Mysql but it is really too much for my needs (I don't need a Ferrari to go around...). I suppose sqlite, at present, is still more oriented toward software developers (e.g using it for the Firefox 3 bookmars engine)? I bought the book: "Sqlite: the definitive guide" (by M. Owens). The book is great but, in my opinion, there are very few examples. Of course it is impossible to write everything in a singlo book :-) Thanks in advance Best regards, Silvio ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question regarding my sqlite code
Try this: select avg(age) from acoda union all select avg(durata) from main union all select sum(età) from dipendenti RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of silvio grosso Sent: 10 January 2009 10:41 To: sqlite-users@sqlite.org Subject: [sqlite] newbie question regarding my sqlite code Hello, I am a new sqilte user and I am learning sqlite code in my spare time.. I have always used sql code with Microsoft Access and Base (openoffice). Therefore, sorry to ask a question very simple . When I write the very simple code: select sum(età) from dipendenti everything works fine and the result is 100. When I try a bit longer query the result for the sum regarding the column età from the table dipendenti changes and it is wrong. That is 25200? (instead of the right value 100!). The query is: select avg(age), avg(durata), sum(età) from acoda, main, dipendenti In the above query the avg results for the column age (table acoda) and the column durata (table main) are right. The only value wrong is the third, that it, sum (for the table dipendenti, column age, 25200 instead of the right value 100). The column age in the table dipendenti is not present in the other two tables (acoda, main). What's wrong with the second query? Thanks in advance. Best regards ___ 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] newbie question regarding my sqlite code
Am Samstag, 10. Januar 2009 schrieb silvio grosso: > The query is: > select avg(age), avg(durata), sum(età) from acoda, main, dipendenti This is "cross join" over all three tables. The result is a "monster table", consisting of every possible combination of the records of those three tables. And in this "monster table" the sum most probably is correct. Please read your SQL-documention about different types of joins and what they are good for. For debugging purposes you can have a look at select * from acoda, main, dipendenti to see the raw result of your join. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the difference in these 2 SQLite files?
Some progress in this. Doing a trivial file write (set Read-Only to True and then back to False) gives me the same speed benefit. So at least this takes SQLite out of the equation in solving this problem. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 22:15 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? OK, any DB write to this file on the desktop (not the mobile device) will make the query go fast. A completely trivial update will do it, so it looks somehow the db write on the desktop does makes it go fast. I can't check now if the same applies when doing this db write on the first PC and hopefully it does. Maybe I need some app to compare the 2 db files to see what is going on here. Baffling me. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:54 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? Have now also looked at the full EXPLAIN and although understand that it looks indentical to me for both databases: Slow - addropcode p1 p2 p3 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 keyinfo(1,BINARY) 16 SetNumColumns 1 2 17 Integer 823 0 18 IsNull -1 30 19 MakeRecord 1 0 d 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 + 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 397 36 Goto0 1 37 Noop0 0 Fast - addropcode p1 p2 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 16 SetNumColumns 1 2 17 Integer 831 0 18 IsNull -1 30 19 MakeRecord 1 0 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 400 36 Goto0 1 37 Noop0 0 So, what possibly could explain this? There can be no cache effect as I can reverse it from fast to slow and vice versa by copying one or the other database. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:00 To: sqlite-users@sqlite.org Subject: [sqlite] Why the difference in these 2 SQLite files? Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device I access the db file with an application written with Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC is 3.3.12. Now the thing I don't understand. I run the following query (on the mobile device): SELECT EXISTS( SELECT PATIENT_ID FROM TABLE1 WHERE PATIENT_ID = 123) There is an
Re: [sqlite] 600ms for simple query: How to optimize it?
D. Richard Hipp schrieb: > On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote: >> SELECT t.topic, t.length >> FROM printgroup AS pg1 >> LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup >> LEFT JOIN topics AS t ON t.topicID = pg2.topicID >> LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID >> WHERE ti.topic_textID = '' >> ORDER BY pg2.topicID ASC; > > You seem very fond of using LEFT JOINs in places where they do not > make good sense. Yes, I started with mySQL 3 many years ago. At the beginning I only knew about LEFT JOINs and used them. Now I think I also know the other types of JOINs but I still use LEFT JOINs very often, just by habit. And with mySQL I never had performance problems with them. > What is it that you think a LEFT JOIN does? (A LEFT JOIN B) joins together table A and B while all records are taken from A and only records that match both are takes from B. If a record from A has no corresponding data in B, the values are NULL. > How is > a LEFT JOIN different than an ordinary inner JOIN? INNER JOIN takes *all* records from both tables, A and B. Generally, the resultset will be larger. > I ask because I > suspect that your answer will reveal misconceptions about LEFT JOINs > which, when rectified, will cause most of your performance issues to > go away. Maybe my I think too much in "left joining" but I did not know that there is so much difference in performance. Best Regards, Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 600ms for simple query: How to optimize it?
Kees Nuyt schrieb: > On Fri, 09 Jan 2009 21:16:03 +0100, Lukas Haase > wrote in General Discussion of SQLite > Database : > >> Hello Richard! >> >> Thank you very much!! It works! :-) >> >> >> Indeed. 0-10 milliseconds instead of 500-800 :-) >> >> But may you tell me why this works and where you have this information? >> I know the O-notation but I do not know /why/ this boosts down to log(n)... > > Use EXPLAIN SELECT . > to see the virtual machine instructions > and EXPLAIN SELECT QUERY PLAN . > to see which index is used. > > http://www.sqlite.org/lang_explain.html > > Each JOIN is implemented as nested loops. The virtual > machine code can tell a lot about what part of the database > has to be scanned. Thank you, I know this and I did try it already. But unfortunately I do not know how to interpret the results. Best regards, Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation to learn sqlite code for newbies?
On Sat, 10 Jan 2009 11:01:01 + (GMT), silvio grosso wrote in General Discussion of SQLite Database : > Hello everybody, > > Does anyone know a link (Internet) where I can find > some simple tutorials regarding sqlite code? http://sqlzoo.net/ http://www.w3schools.com/sql/default.asp http://www.sqlcourse.com/ http://freshmeat.net/articles/view/1428/ > I am looking for some code to apply using the select > condition. I am an accountant as trade and I have many > big .csv files. I would like to learn sqlite code to > query them. SQLite uses SQL as its language, and it tries to be standards compliant, so any generic SQL tutorial will do. > At present, needless to say, I have already checked the > sqlite web site of course. I have started reading this > mailing list as well :-) > > I read the documentation for newbies should must be > improved in the sqlite web page but who knows when :-) > > I "googled" a bit but most examples I found are for > software developers and not for end-users (e.g I found > code for using sqlite with python, or ruby and so on). > > For Mysql, for example, the books with code for end > user are endless. I know I could use Mysql but it is > really too much for my needs (I don't need a Ferrari to > go around...). > > I suppose sqlite, at present, is still more oriented > toward software developers (e.g using it for the > Firefox 3 bookmars engine)? > > I bought the book: "Sqlite: the definitive guide" > (by M. Owens). > > The book is great but, in my opinion, there are very > few examples. Of course it is impossible to write > everything in a singlo book :-) > > Thanks in advance > > Best regards, > > Silvio Hope this helps. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 600ms for simple query: How to optimize it?
When you have an inner join like this: SELECT * FROM a JOIN b ON a.x=b.y WHERE b.z=123; then the SQL engine is free to interchange the order of the tables in the join. For example, the join might be implemented as: SELECT * FROM b JOIN a ON a.x=b.y WHERE b.z=123 Giving the SQL engine the freedom to interchange tables will often create significant performance improvements. For example, if there are indexes on b.z and a.x, then by interchanging the two tables, the join can be satisfied by first looking up entries where b.z=123 then finding corresponding b.y values and using them to look up entries matching a.x=b.y. Note that the tables can only be interchanged if you use a inner JOIN. Change the order of tables in a LEFT JOIN creates a different answer. So when you use LEFT JOIN, that forces a particular ordering of tables, and greatly restricts the query engines opportunities to optimize. So you should avoid using LEFT JOIN if you don't really need it. In the example above, because the tables can be reordered, the query will run in O(logN). But if you us a LEFT JOIN forcing the original a- before-b order, the runtime will be O(N*N). Quite a bit slower. And in the example above, the LEFT JOIN is not really needed. Because of the "b.z=123" test in the WHERE clause, the terms of the LEFT JOIN where table b is NULL will never make it to the output. So you will get the same result using either an inner JOIN or a LEFT JOIN. So since an inner JOIN gives the query engine more optimization opportunities, you might as well use it. On Jan 10, 2009, at 8:58 AM, Lukas Haase wrote: > D. Richard Hipp schrieb: >> On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote: >>> SELECT t.topic, t.length >>> FROM printgroup AS pg1 >>> LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup >>> LEFT JOIN topics AS t ON t.topicID = pg2.topicID >>> LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID >>> WHERE ti.topic_textID = '' >>> ORDER BY pg2.topicID ASC; >> >> You seem very fond of using LEFT JOINs in places where they do not >> make good sense. > > Yes, I started with mySQL 3 many years ago. At the beginning I only > knew > about LEFT JOINs and used them. Now I think I also know the other > types > of JOINs but I still use LEFT JOINs very often, just by habit. And > with > mySQL I never had performance problems with them. > >> What is it that you think a LEFT JOIN does? > > (A LEFT JOIN B) joins together table A and B while all records are > taken > from A and only records that match both are takes from B. If a record > from A has no corresponding data in B, the values are NULL. > >> How is >> a LEFT JOIN different than an ordinary inner JOIN? > > INNER JOIN takes *all* records from both tables, A and B. Generally, > the > resultset will be larger. > >> I ask because I >> suspect that your answer will reveal misconceptions about LEFT JOINs >> which, when rectified, will cause most of your performance issues to >> go away. > > Maybe my I think too much in "left joining" but I did not know that > there is so much difference in performance. > > Best Regards, > Luke > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting the "position" (like LIMIT) for a query
Hello, I use an SQLite database to fill a virtual list control in Windows. In this control, I just tell the control the numer of my elements and the control tells me for which range data is needed. For example I set: SELECT COUNT(*) FROM keywords; to tell the control the numer of elements. Now, I get "caching messages" which tell me what range will be needed next, for example iFrom=500 and iTo=520. In this case I do just a: "SELECT keyword FROM keywords ORDER BY keyword LIMIT %d, %d", iFrom, iTo-iFrom. The result is stored into a std::map as a cache, and when requested, the appropriate element is displayed. So far, so good. But I have the problem that I want to be able to search in the list. In this case, Windows sends a message with "te" (if the string "te" should be found) and I need to hand back the *position* of the found data. I have no idea how to do this. In fact, I need to know the position (the same position that I would give the LIMIT statement) for a specific result set. I hope you can understand me. Is there a way to implement this? Thank you very much! Luke PS: The data may also be composed of a more complex statement (with JOINs). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 600ms for simple query: How to optimize it?
On Sat, Jan 10, 2009 at 02:58:57PM +0100, Lukas Haase wrote: > > What is it that you think a LEFT JOIN does? > > (A LEFT JOIN B) joins together table A and B while all records are taken > from A and only records that match both are takes from B. If a record > from A has no corresponding data in B, the values are NULL. That's what a LEFT JOIN is, but why do you think you need it? > > How is > > a LEFT JOIN different than an ordinary inner JOIN? > > INNER JOIN takes *all* records from both tables, A and B. Generally, the > resultset will be larger. The result of an INNER JOIN will be smaller than or equal to that of a LEFT JOIN since rows from A that can't be joined to any rows from B don't appear in the result of the INNER JOIN but do appear in the LEFT JOIN. Were you thinking of cross joins? Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the "position" (like LIMIT) for a query
"Lukas Haase" wrote in message news:gkat07$n2...@ger.gmane.org > I use an SQLite database to fill a virtual list control in Windows. In > this control, I just tell the control the numer of my elements and the > control tells me for which range data is needed. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 600ms for simple query: How to optimize it?
On Sat, Jan 10, 2009 at 7:58 AM, Lukas Haase wrote: > D. Richard Hipp schrieb: >> On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote: >>> SELECT t.topic, t.length >>> FROM printgroup AS pg1 >>> LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup >>> LEFT JOIN topics AS t ON t.topicID = pg2.topicID >>> LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID >>> WHERE ti.topic_textID = '' >>> ORDER BY pg2.topicID ASC; >> >> You seem very fond of using LEFT JOINs in places where they do not >> make good sense. > > Yes, I started with mySQL 3 many years ago. At the beginning I only knew > about LEFT JOINs and used them. Now I think I also know the other types > of JOINs but I still use LEFT JOINs very often, just by habit. And with > mySQL I never had performance problems with them. > >> What is it that you think a LEFT JOIN does? > > (A LEFT JOIN B) joins together table A and B while all records are taken > from A and only records that match both are takes from B. If a record > from A has no corresponding data in B, the values are NULL. > >> How is >> a LEFT JOIN different than an ordinary inner JOIN? > > INNER JOIN takes *all* records from both tables, A and B. Generally, the > resultset will be larger. all the rows from both tables A and B *that match* the join condition... in other words, unlike a LEFT (or a RIGHT) JOIN, which would include even those rows where only A (or B) match but show a NULL value for the B (or A) table, an INNER JOIN, aka, just JOIN, would usually have a smaller result set. Unless and until both tables provided a match (and stuffing NULL is not a match), a row would not be included in the result set. > >> I ask because I >> suspect that your answer will reveal misconceptions about LEFT JOINs >> which, when rectified, will cause most of your performance issues to >> go away. > > Maybe my I think too much in "left joining" but I did not know that > there is so much difference in performance. > > Best Regards, > Luke > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the "position" (like LIMIT) for a query
On Sat, Jan 10, 2009 at 04:33:19PM -0500, Igor Tandetnik wrote: > "Lukas Haase" wrote in > message news:gkat07$n2...@ger.gmane.org > > "SELECT keyword FROM keywords ORDER BY keyword LIMIT %d, %d", iFrom, > > iTo-iFrom." > > > > I use an SQLite database to fill a virtual list control in Windows. In > > this control, I just tell the control the numer of my elements and the > > control tells me for which range data is needed. > > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor One thing I've done before is to use a rowid to track each "page" of results: SELECT rowid, keyword FROM keywords WHERE rowid > :pagestart ORDER BY keyword LIMIT :pagesize then remember the last rowid from the result set and use it to start the next result set. If you have a JOIN then you can use this trick for one table in the query, just pick it carefully. Paging backwards efficiently is not as easy. You want to come up with a query that can start at some rowid and scan _backwards_ through the table. This: SELECT rowid, keyword FROM keywords WHERE rowid < :pagestart ORDER BY keyword LIMIT :pagesize doesn't do what you want, of course (EXPLAIN will show that it uses the Next instruction instead of Prev). This: SELECT rowid, keyword FROM (SELECT rowid, keyword FROM keywords WHERE rowid < :pagestart ORDER BY rowid DESC LIMIT :pagesize) ORDER BY keyword; does use the Prev instruction, instead of Next, to scan the table in the sub-select, so it will process the fewest possible rows. Using rowid makes this very fast. I'm surprised that the wiki page for scrolling cursors doesn't mention this. Incidentally, I think many, if not all queries that have an outer table scan, and many, if not all joins that have an inner table scan but not an outer table scan, could be programmatically modified to create a scrolling cursor, forward and backwards. All that has to be done is: pick one table whose rowid to extract, add the where clause for rowid < or > than some variable, add the limit clause, and the sub-select for paging backwards. But parsing the select just do that is hard, while the SQLite3 parser is uniquely positioned to do it for the user. So perhaps there's a small RFE here? Cheers, Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the "position" (like LIMIT) for a query
Igor Tandetnik schrieb: > "Lukas Haase" wrote in > message news:gkat07$n2...@ger.gmane.org >> I use an SQLite database to fill a virtual list control in Windows. In >> this control, I just tell the control the numer of my elements and the >> control tells me for which range data is needed. > > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor Thank you, I did not know this. But unfortunately this does not help in my situation. I do not simply "page" up and down a fixed numer of rows. The problem is that *Windows* tells me which indexes need to be shown. This means: Windows just tells me: "From your 1000 elements, I want to have number 599". To get it a little bit better, Windows *additionally* tells me which elements I should cache (e.g. if possible, cache number 450 to 500). However, I solved this, even if not so performant, according to your link. The actual problem is the find-function: From my 1000-elements resultset, I need to pass to Windows that index (between 1 and 1000!) that fits best to the search criterion. And there I have exactly no clue how to do this. If I could add a kind of auto index from 1...1000 to my resultset, I could try something like this: SELECT AUTO_ID(), keyword, '%search' FROM keywords WHERE keyword = '%search' but the problem is the missing AUTO_ID() functionality. Is there any hope to successfully implement the search function? Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the "position" (like LIMIT) for a query
That is what I did, did my select into a temp table filtered and sorted just the way I wanted it and used the rowid since it would match the index in the list control. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nicolas Williams Sent: Saturday, January 10, 2009 4:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Getting the "position" (like LIMIT) for a query On Sat, Jan 10, 2009 at 04:33:19PM -0500, Igor Tandetnik wrote: > "Lukas Haase" wrote in message > news:gkat07$n2...@ger.gmane.org > > "SELECT keyword FROM keywords ORDER BY keyword LIMIT %d, %d", iFrom, iTo-iFrom." > > > > I use an SQLite database to fill a virtual list control in Windows. > > In this control, I just tell the control the numer of my elements > > and the control tells me for which range data is needed. > > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor One thing I've done before is to use a rowid to track each "page" of results: SELECT rowid, keyword FROM keywords WHERE rowid > :pagestart ORDER BY keyword LIMIT :pagesize then remember the last rowid from the result set and use it to start the next result set. If you have a JOIN then you can use this trick for one table in the query, just pick it carefully. Paging backwards efficiently is not as easy. You want to come up with a query that can start at some rowid and scan _backwards_ through the table. This: SELECT rowid, keyword FROM keywords WHERE rowid < :pagestart ORDER BY keyword LIMIT :pagesize doesn't do what you want, of course (EXPLAIN will show that it uses the Next instruction instead of Prev). This: SELECT rowid, keyword FROM (SELECT rowid, keyword FROM keywords WHERE rowid < :pagestart ORDER BY rowid DESC LIMIT :pagesize) ORDER BY keyword; does use the Prev instruction, instead of Next, to scan the table in the sub-select, so it will process the fewest possible rows. Using rowid makes this very fast. I'm surprised that the wiki page for scrolling cursors doesn't mention this. Incidentally, I think many, if not all queries that have an outer table scan, and many, if not all joins that have an inner table scan but not an outer table scan, could be programmatically modified to create a scrolling cursor, forward and backwards. All that has to be done is: pick one table whose rowid to extract, add the where clause for rowid < or > than some variable, add the limit clause, and the sub-select for paging backwards. But parsing the select just do that is hard, while the SQLite3 parser is uniquely positioned to do it for the user. So perhaps there's a small RFE here? Cheers, Nico -- ___ 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] Using Eclipse on Windows XP
Is there an easy way to develop on sqlite using eclipse for C/C++ on a Windows XP machine? I have eclipse open on a source directory but can not compile. Is there a possible faq or wiki on this? Thanks. Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
