[sqlite] Index generation efficiency
Hi everyone, I'm using sqlite3 for my project, Nihongo Benkyo, which is a japanese dictionary and learning tool. This program can import data from files in various formats in the sqlite database. Generally speaking, one import does about 500,000 INSERT queries in a single transaction and it is working fine. The main benefit from using a transaction is to reduce the number of disc accesses if my understanding is correct. However, if I put indexes on some columns, the import becomes very slow. I don't know the sqlite code but it sounds to me like the indexes are generated on the fly which seems to require a lot of work. I found out it is much more efficient to drop all the indexes, do my big transaction and then recreate all the indexes. Dropping indexes is not a really long process and just creating the indexes after the end of the transaction seems quicker. But if think it is quite dirty to do so everytime I import new data in the database. I guess there may be good reasons not to generate the indexes all at once at the end of the transaction. So first I would be glad to have some explanation about this. Then, do you have a better solution than mine to speed up the indexes generation ? Is it for example possible to ask explicitly sqlite not to update an index and then ask explicitly "by hand" to update the indexes. Thanks, Mathieu.
[sqlite] F.W.A. van Leeuwen is out of the office.
I will be out of the office starting 2005-07-25 and will not return until 2005-08-19. I will respond to your message when I return.
[sqlite] No Timeout during commit?
Hi, I have 2 process accessing the DB, one reading and the other writing. Often the process reading the DB could take long and could block the other process from committing a bunch of records to the DB. I noticed that when the reader process has the lock and the writer process tries to commit a bunch of records in the DB, the writer blocks forever till it obtains the lock? Is this intended? I could also always try to commit a little later and the approach works well for my particular app. Can the behavior be changed? Thanks.
[sqlite] problems to install pysqlite
SKORPIO-INFO ha scritto: Accidents!! I do not succeed re-install pysqlite!!! they give always this error to me!! You can help me? p.s. with all the versions, also the previous ones!!
Re: [sqlite] Limit how much data to read
> Where in the documentation that explains how to use the sqlite substr() > function? Go to the SQLite Syntax page, and click on 'expression'.
RE: [sqlite] Limit how much data to read - substr syntax
Regarding: Where in the documentation that explains how to use the sqlite substr() function? Hi Shawn, >From the main sqlite.org page, take the SYNTAX link, then EXPRESSIONS http://www.sqlite.org/lang_expr.html substr(X,Y,Z) Return a substring of input string X that begins with the Y-th character and which is Z characters long. The left-most character of X is number 1. If Y is negative the the first character of the substring is found by counting from the right rather than the left. If SQLite is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes. Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors.
Re: [sqlite] Iterating a result from a query
> > I cache the items I'm displaying in an STL vector (C++). If the user moves > > to a page where I have no data I load it from the database as needed. I was > > considering loading data in the background in case the user might need it > > since I can predict what they might do relatively easily. > > Prefetching sounds like a wonderfull idea, it is definitely in a phase +2 from > current phase, but if the concept is working it might make the flow a lot > smoother. On one project they demanded response time be below 3 seconds. I had to do all of these things to get it that fast. Good luck!
Re: [sqlite] Iterating a result from a query
> > I cache the items I'm displaying in an STL vector (C++). If the user moves > to a page where I have no data I load it from the database as needed. I was > considering loading data in the background in case the user might need it > since I can predict what they might do relatively easily. Prefetching sounds like a wonderfull idea, it is definitely in a phase +2 from current phase, but if the concept is working it might make the flow a lot smoother. Cheers Kim B.
Re: [sqlite] Iterating a result from a query
Thank you for your thoughts. > You can build the result set into an array, then step through, back and > around the array. > > SQLite already provides this functionality: > http://www.sqlite.org/capi3ref.html#sqlite3_get_table Having the entire result in memory would be an easy solution but unfortunately it will take up to much memory. I think I will keep an array of rowIds for the entries in the result, sort of a cache. Whenever the user scrolls to a position where I don't have the data, then I will fetch it for him and cache the id. That way it is easy to create random access via the cached rowIds and I only need to store. > The only downside is that you'll have the full result set in memory, even > if you are only using a small portion of it. > > If you want to limit the amount of data at any time, you can use the: > > SELECT ... LIMIT n OFFSET m; > > form of select, where n is the number of rows you want, and m is the > offset you want to start from. For a table view, this may be appropriate, > but you have to recompute the results for each change of page, potentially > slowing your app down for large queries. For the relatively simple tables that we have at the moment, I don't think its an issue to make one lookup per item when scrolling. But tests will show that. > >Cheers > >Kim B.
Re: [sqlite] Limit how much data to read
Where in the documentation that explains how to use the sqlite substr() function? Cory Nelson wrote: Try the substr() function On 7/25/05, Shawn Walker <[EMAIL PROTECTED]> wrote: Is there a way to tell sqlite to read up to X bytes? For example, there are some data that can be quite large, but I don't need all of them, just a little bit of it to show the user some of the data and they can select that data to get the rest of the data from the DB.
Re: [sqlite] Query locking up SQLite
> SQLite Version 3.2.2. > Is this a bug, or is my SQL that bad? > Query 1: > SELECT * FROM table1, table2 > WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id); > This query works perfectly, can't fault it. > But when I do this, SQLite locks out (Command line interface, and PHP5) > Query 2: > SELECT * FROM table1, table2 > WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id); > OR (table1.value LIKE "%different_value%" AND table1.table2_id = > table2.id)); > This query (and even more complex versions of it) works in MySQL (Haven't > tried another DB yet) and I'm trying to migrate to SQLite, this is really > holding me back. The like clause is going to force it to do a full table scan (it will have to read the whole database every time). If you can find a way around that it will help a lot. Is there an index on the id column(s)? That might help some too. == I don't think the LIKE is the problem, I used = there too. All the id columns a primary keys, so I assume that means they're indexed well enough. Update on 5 table query: I can now do the query with INNER JOINs, and it returns instantly with the correct results. The problem appears to be the order of the tables in the JOINs. The first table is large (4000 odd rows) and it has absolutely no conditions on it (the second doesn't help either). What I should have done was have the tables that result in not many rows first, then add others as I go. Thanks for the ideas, I think I have it now (think). - Very odd, this email was originally addressed to the poster, not the list e-mail address
Re: [sqlite] Iterating a result from a query
On 7/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Any thoughts/brainstorming would be great from anyone :) > > Personally I don't have much experience with programming a database and > the theory in it. But I suppose that in order to be able to go backward, > the results already iterated through must be kept(at least a reference to > it, something like rowId). If setToLast is used, I must use the > sqlite3_step in order to find the last entry and then cache the other > results. > > Is there an easier way?? I cache the items I'm displaying in an STL vector (C++). If the user moves to a page where I have no data I load it from the database as needed. I was considering loading data in the background in case the user might need it since I can predict what they might do relatively easily.
Re: [sqlite] Multi-threading.
> Computer _Science_ is just that: Science. It's not opinion. What they taught me at university was the current collection of "best practices" for solving problems. "Here's a common problem, here are the known good algorithms for solving it. Here are the techniques we use for creating 'good' programs. You are now educated". They didn't teach us the scientific method for solving problems. The theory has been proposed that threads aren't better than separate processes, or application implemented context switching. Does anyone have an experiment that will prove the point either way? It will have to be OS specific though, since I'm sure not all thread implementations are equal.
Re: [sqlite] Query locking up SQLite
> SQLite Version 3.2.2. > Is this a bug, or is my SQL that bad? > Query 1: > SELECT * FROM table1, table2 > WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id); > This query works perfectly, can't fault it. > But when I do this, SQLite locks out (Command line interface, and PHP5) > Query 2: > SELECT * FROM table1, table2 > WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id); > OR (table1.value LIKE "%different_value%" AND table1.table2_id = > table2.id)); > This query (and even more complex versions of it) works in MySQL (Haven't > tried another DB yet) and I'm trying to migrate to SQLite, this is really > holding me back. The like clause is going to force it to do a full table scan (it will have to read the whole database every time). If you can find a way around that it will help a lot. Is there an index on the id column(s)? That might help some too.
RE: [sqlite] Multi-threading.
Hi Ben, You're right about the native vs pseudo threads-own schedule issue and in fact this is exactly how most Java Virtual Machines handle it. The JVM's that support native threads contain a runtime time switch that allows you to turn it on and off - this means that your application can be built without knowledge of whether the threading is controlled by the OS or by the JVMs own scheduler. Here's a nice comparison of the two types:- http://www.mit.edu/afs/athena.mit.edu/software/java_v1.1.6/www/native_thread s.html I think I'm right is saying that most multi-user database kernels will have started out life as being natively single threaded (except the asynch I/O), but containing their own pseudo thread scheduler. That's certainly still the case with the database I most heavily use, Livelink for Collections Server. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Ben Clewett Sent: 25 July 2005 13:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multi-threading. Steve O'Hara wrote: > I think the point about multi-threaded apps is not that there might be a few > instances where they are useful, but how easy is it to debug and support > them and is it worth the price for a little more concurrency? Good point. The ones I have used are terrible. I hope things will improve in time. This is hopefully not a reason to give up. > In my experience, most debugging IDE's can't handle multiple threads and > actually turn the application into a pseudo threaded version prior to > running it. I wouldn't mind betting that's what's going on in Java (it's > worth pointing out that Java didn't used to even support native threads and > I bet most Java programmers have no idea whether they are actually using > Native or pseudo threads) > > Because of this, you get the frustrating situation where an application > works fine in debug but fails sporadically in production. Another good real > world example of this is the IIS/ASP server side debugging. IIS allocates a > new thread per request which are autonomous but do some semaphoring to share > the Session object (I think). There's a handy switch you can set on your > IIS virtual directory that turns on server side debugging - great! . > only, not so great!! Suddenly IIS starts allocating the same thread to every > request i.e. it queues your requests and the very problem you were trying to > solve goes away! :) > The moral of the story is as Richard says, if there isn't a burning need for > multiple threads then don't use them. I have noticed something. There are two lines of thought here. The two ideas may not be too different. (Here is where I am sure I will be flamed, but I am sure I'll learn something from it :) - The single-thread multi-task option. - The multiple-thread single thread-task. The first option involves building a context scheduler into our own programs. So GUI events, TCP listen, and everything else can occur without blocking. A virtual thread environment. As you say, some Java and ISS can sometimes be this. The second option involves letting the OS do the context changes. Letting the programmer just code each thread in isolation. (Programming in a thread-safe way.) So in essence, the two become the same. A switch could even be added to use own context switch, or use the OS. The OS could be virtual or physical. Externally it's not important: any option should yields the same function. Therefore, programming ones own context scheduler is a bit like not using SQL and accessing the data file directly. It may be faster, you may have more control. But you may just be giving your self a lot of work where SQL does it all for you. This is why we all like SQLite so much. I do not know if this is the case today. My assumption is that thread safe programming is easy, by using good tools and good methodology. I truly believe this, and I have not experienced some of the thread problems other people have reported. Considering the future of processor design, should we be getting more excited about threads and how we can make them work for us? > By the way, Mrs Brisby is being facetious when he says that he doesn't > regard Windows as a "modern" GUI - there's no such thing! They've all got > their origins in concepts put forward over 20 years ago. :) Ben. > > Steve > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > rg]On Behalf Of Ben Clewett > Sent: 25 July 2005 09:01 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Multi-threading. > > > Dear Mrs Brisby, > > Thanks for your passionate replies to my original posting. You have > much information here. It's obvious I don't know everything about > threading. > > I like what you say about computer science being a Science. This is > exactly my point. A science is a collection of theories which hold true > within a domain until otherwise dissproven. For instance Isac Newtons
Re: [sqlite] Limit how much data to read
Try the substr() function On 7/25/05, Shawn Walker <[EMAIL PROTECTED]> wrote: > Is there a way to tell sqlite to read up to X bytes? For example, there > are some data that can be quite large, but I don't need all of them, > just a little bit of it to show the user some of the data and they can > select that data to get the rest of the data from the DB. > -- Cory Nelson http://www.int64.org
Re: [sqlite] Query locking up SQLite
Might I suggest the following -- make your query in steps, and note where it actually starts slowing down. For example, Step 1. SELECT COUNT(*) FROM t1 (took a fraction of a pico second... good) Step 2. SELECT COUNT(*) FROM t1 WHERE t1.c1 = 'c1' (took a couple of fractions of a pico second... still good) Step 3. SELECT COUNT(*) FROM t1 INNER JOIN t2 ON t1.c2 = t2.c2 WHERE t1.c1 = 'c1' (still just a couple of fractions of a pico second... still good) and so on... you get the picture. SQL gets complicated very rapidly, and only by building it step by step can we optimize it best. That will pin-point for you where the query goes haywire. Then you can concentrate on fixing that. Perhaps you are not using your indexes, perhaps you don't have indexes, whatever. Then you can come back and ask for help on the specific problem. Maybe the SQLite designers might even discover a legitimate place for improvement in their code, and then everyone will benefit. Comparing two databases is pointless because they all do things so differently internally even though we as users expect the same external results. -- Puneet Kishor = Thanks for the pointer, I was just doing this myself (not intentionally though) by constructing my query out of nested SELECTs rather than JOINs and I noticed that the inner SELECTs were taking fractions of seconds, what I was doing in the next level up was taking a lot longer. I think the reason for it is table1 and table2s JOIN created alot of rows, no matter what conditions I placed in the ON statement, because table1 was so large and none of the conditions applied to it in a manner that made it more efficient. I guess MySQLs optimizer is really doing its job, and I need to do the optomising myself for SQLite. Thanks for the input Puneet.
[sqlite] Limit how much data to read
Is there a way to tell sqlite to read up to X bytes? For example, there are some data that can be quite large, but I don't need all of them, just a little bit of it to show the user some of the data and they can select that data to get the rest of the data from the DB.
Re: [sqlite] Query locking up SQLite
On Jul 25, 2005, at 6:06 AM, David Fowler wrote: On 2005-07-25 at 10:58:04 [+0200], David Fowler .. === Thanks for the pointer Charlie, but I was only using * for my example, and I normaly use fully named columns (table.column) when writing queries. My new query, looks like this (generified): SELECT count(*) FROM table1 INNER JOIN table2 ON (table1.id = table2.rel_id) INNER JOIN table3 ON (table3.rel_id = table2.id) INNER JOIN table4 ON (table3.id = table4.rel_id) INNER JOIN table5 ON (table5.rel_id = table4.id) WHERE table1.value = 'value1' AND ((table3.value LIKE '%value3%' AND table5.value = 'value5') OR (table3.value LIKE '%value3%' AND table5.value = 'value5')); This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 34 rows). I think the problem is the size of the temporary table the JOIN creates (37,434) which is probably the issue. There is (7,579) records in my largest table. I'm not sure what my best option would be to get this size down. But I'm thinking along the lines of some nested SELECTs to get needed rows in stages rather than all in one go at the end. My application is probably going to get much larger data wise than it already is and MySQL is not really an option anymore. Could possibly go back to UNIONs also if the OR operation isn't as efficient as two SELECTs (though I highly doubt that is the case). Might I suggest the following -- make your query in steps, and note where it actually starts slowing down. For example, Step 1. SELECT COUNT(*) FROM t1 (took a fraction of a pico second... good) Step 2. SELECT COUNT(*) FROM t1 WHERE t1.c1 = 'c1' (took a couple of fractions of a pico second... still good) Step 3. SELECT COUNT(*) FROM t1 INNER JOIN t2 ON t1.c2 = t2.c2 WHERE t1.c1 = 'c1' (still just a couple of fractions of a pico second... still good) and so on... you get the picture. SQL gets complicated very rapidly, and only by building it step by step can we optimize it best. That will pin-point for you where the query goes haywire. Then you can concentrate on fixing that. Perhaps you are not using your indexes, perhaps you don't have indexes, whatever. Then you can come back and ask for help on the specific problem. Maybe the SQLite designers might even discover a legitimate place for improvement in their code, and then everyone will benefit. Comparing two databases is pointless because they all do things so differently internally even though we as users expect the same external results. -- Puneet Kishor
Re: [sqlite] Query locking up SQLite
I think if you try this with MySQL MyISAM you will have the same result and I think this is to do with the SELECT * I remember I used to have something similar back in the days when I tried MySQL. Charlie == I just tried adding extra conditions in the ON clause, but its not helping, I think SQLite is still putting the tables all together before doing the conditions which is whats taking so long. Never mind though, I think I can manage it with subqueries and UNIONs faster, don't really wanna switch to somethin else, though access would be possible, I don't know if it would be at all efficient with all this data.
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 14:51:13 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > Well the query isn't that strange, I have 3 values that need matching > per-record returned, all in different tables (takes care of 3 tables) the > actual data I want is stored in another related table, and 2 of the values > that need to match are not related directly, so a 5th table is required for > that relationship. > Anyway, my original query does infact work, however it takes 10 minutes to > run. I'm guessing this is because it generates 1,530,200,100 rows of data to > do the WHERE clause on (just did a count(*) on it in mySQL) . Why MySQL can > do this so much faster, 1/10th of a second I dont know (though I am using > InnoDB not the default MyISAM engine. I image the BDB engine would be even > faster). > Just MySQL is to heavy to install with my application. Anyway, back to the > drawing board for an efficient way to get my data. Thanks again. I think if you try this with MySQL MyISAM you will have the same result and I think this is to do with the SELECT * I remember I used to have something similar back in the days when I tried MySQL. Charlie
Re: [sqlite] Iterating a result from a query
On Sun, 24 Jul 2005, Kim Bendtsen wrote: >Hi There > >After executing a query I get the result back and can traverse it using >sqlite3_step. This is fine for most systems, however for what I wish to use >SQLite for, I would need something similar to stepForward, stepBackward, >stepToFirst, stepToLast. > >The application where I'm going to use it is when showing a list items from a >database table in a window. If the window can display 5 items, there is no >need to load more than 5 rows in the beginning. Here sqlite3_step is >sufficient. But >if the users press up arrow, it should take them to the bottom of the list, >and I would like to use something similar to setToLast. If the user keep >pressing up, I would need to stepBackward. >I know this might not be something for the sqlite database. How would you go >about wrapping this interface? I know it is a broad question and your >suggestion is very much appriciated! :) You can build the result set into an array, then step through, back and around the array. SQLite already provides this functionality: http://www.sqlite.org/capi3ref.html#sqlite3_get_table The only downside is that you'll have the full result set in memory, even if you are only using a small portion of it. If you want to limit the amount of data at any time, you can use the: SELECT ... LIMIT n OFFSET m; form of select, where n is the number of rows you want, and m is the offset you want to start from. For a table view, this may be appropriate, but you have to recompute the results for each change of page, potentially slowing your app down for large queries. > > >Cheers >Kim B. > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 13:06:42 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > SELECT count(*) FROM table1 > INNER JOIN table2 ON (table1.id = table2.rel_id) > INNER JOIN table3 ON (table3.rel_id = table2.id) > INNER JOIN table4 ON (table3.id = table4.rel_id) > INNER JOIN table5 ON (table5.rel_id = table4.id) > WHERE table1.value = 'value1' > AND ((table3.value LIKE '%value3%' AND table5.value = 'value5') > OR (table3.value LIKE '%value3%' AND table5.value = 'value5')); > > This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 34 > rows). > I think the problem is the size of the temporary table the JOIN creates > (37,434) which is probably the issue. There is (7,579) records in my largest > table. I'm not sure what my best option would be to get this size down. But > I'm thinking along the lines of some nested SELECTs to get needed rows in > stages rather than all in one go at the end. My application is probably > going to get much larger data wise than it already is and MySQL is not > really an option anymore. Could possibly go back to UNIONs also if the OR > operation isn't as efficient as two SELECTs (though I highly doubt that is > the case). Run this through the analyser (EXPLAIN) which will show where the time is spent. I would also suggest trying PostgreSQL as my experience with MySQL is that performance degrades significantly with lots of JOINs as it just generates lots of Cartesian products and I think SQLite is the same, ie. a huge table is generated in memory and the conditions are applied to this row by row. Certainly seems a strange query, though. Charlie Well the query isn't that strange, I have 3 values that need matching per-record returned, all in different tables (takes care of 3 tables) the actual data I want is stored in another related table, and 2 of the values that need to match are not related directly, so a 5th table is required for that relationship. Anyway, my original query does infact work, however it takes 10 minutes to run. I'm guessing this is because it generates 1,530,200,100 rows of data to do the WHERE clause on (just did a count(*) on it in mySQL) . Why MySQL can do this so much faster, 1/10th of a second I dont know (though I am using InnoDB not the default MyISAM engine. I image the BDB engine would be even faster). Just MySQL is to heavy to install with my application. Anyway, back to the drawing board for an efficient way to get my data. Thanks again.
Re: [sqlite] Multi-threading.
Steve O'Hara wrote: I think the point about multi-threaded apps is not that there might be a few instances where they are useful, but how easy is it to debug and support them and is it worth the price for a little more concurrency? Good point. The ones I have used are terrible. I hope things will improve in time. This is hopefully not a reason to give up. In my experience, most debugging IDE's can't handle multiple threads and actually turn the application into a pseudo threaded version prior to running it. I wouldn't mind betting that's what's going on in Java (it's worth pointing out that Java didn't used to even support native threads and I bet most Java programmers have no idea whether they are actually using Native or pseudo threads) Because of this, you get the frustrating situation where an application works fine in debug but fails sporadically in production. Another good real world example of this is the IIS/ASP server side debugging. IIS allocates a new thread per request which are autonomous but do some semaphoring to share the Session object (I think). There's a handy switch you can set on your IIS virtual directory that turns on server side debugging - great! . only, not so great!! Suddenly IIS starts allocating the same thread to every request i.e. it queues your requests and the very problem you were trying to solve goes away! :) The moral of the story is as Richard says, if there isn't a burning need for multiple threads then don't use them. I have noticed something. There are two lines of thought here. The two ideas may not be too different. (Here is where I am sure I will be flamed, but I am sure I'll learn something from it :) - The single-thread multi-task option. - The multiple-thread single thread-task. The first option involves building a context scheduler into our own programs. So GUI events, TCP listen, and everything else can occur without blocking. A virtual thread environment. As you say, some Java and ISS can sometimes be this. The second option involves letting the OS do the context changes. Letting the programmer just code each thread in isolation. (Programming in a thread-safe way.) So in essence, the two become the same. A switch could even be added to use own context switch, or use the OS. The OS could be virtual or physical. Externally it's not important: any option should yields the same function. Therefore, programming ones own context scheduler is a bit like not using SQL and accessing the data file directly. It may be faster, you may have more control. But you may just be giving your self a lot of work where SQL does it all for you. This is why we all like SQLite so much. I do not know if this is the case today. My assumption is that thread safe programming is easy, by using good tools and good methodology. I truly believe this, and I have not experienced some of the thread problems other people have reported. Considering the future of processor design, should we be getting more excited about threads and how we can make them work for us? By the way, Mrs Brisby is being facetious when he says that he doesn't regard Windows as a "modern" GUI - there's no such thing! They've all got their origins in concepts put forward over 20 years ago. :) Ben. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Ben Clewett Sent: 25 July 2005 09:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multi-threading. Dear Mrs Brisby, Thanks for your passionate replies to my original posting. You have much information here. It's obvious I don't know everything about threading. I like what you say about computer science being a Science. This is exactly my point. A science is a collection of theories which hold true within a domain until otherwise dissproven. For instance Isac Newtons's law of gravety and Einstain's law. Both are true within a fixed domain. Both are used today. Neither are truelly corrrect. There will be another more complex theroy in time. This is the same with Threading. There may be places where this is useful. There may be places where it should not be used. Both are theories within computer science which hold true in a limited domain. So when I said this was my opinion, I should have stated this is my theory within a fixed domain. However when you stated that I was wrong, I don't think this is compatible of your statement that Computer Science is a Science. Nothing in any science is either right or wrong. That's a Religion. I don't want to spend ages on the argument, I can see there are passionate views. I only want to explore the idea that threading is a viable strategy for a modern high level language and can produce stable applications with low development time. For instance a call to a TCP blocking Wait. It's perfectly possible for one thread to cycle round handing GUI events, then checking on the port for new pac
Re: [sqlite] Iterating a result from a query
> Hi There > > After executing a query I get the result back and can traverse it using > sqlite3_step. This is fine for most systems, however for what I wish to > use > SQLite for, I would need something similar to stepForward, stepBackward, > stepToFirst, stepToLast. > > The application where I'm going to use it is when showing a list items > from a > database table in a window. If the window can display 5 items, there is no > need to load more than 5 rows in the beginning. Here sqlite3_step is > sufficient. But > if the users press up arrow, it should take them to the bottom of the > list, > and I would like to use something similar to setToLast. If the user keep > pressing up, I would need to stepBackward. > I know this might not be something for the sqlite database. How would you > go > about wrapping this interface? I know it is a broad question and your > suggestion is very much appriciated! :) Any thoughts/brainstorming would be great from anyone :) Personally I don't have much experience with programming a database and the theory in it. But I suppose that in order to be able to go backward, the results already iterated through must be kept(at least a reference to it, something like rowId). If setToLast is used, I must use the sqlite3_step in order to find the last entry and then cache the other results. Is there an easier way?? Cheers Kim B.
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 13:06:42 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > SELECT count(*) FROM table1 > INNER JOIN table2 ON (table1.id = table2.rel_id) > INNER JOIN table3 ON (table3.rel_id = table2.id) > INNER JOIN table4 ON (table3.id = table4.rel_id) > INNER JOIN table5 ON (table5.rel_id = table4.id) > WHERE table1.value = 'value1' > AND ((table3.value LIKE '%value3%' AND table5.value = 'value5') > OR (table3.value LIKE '%value3%' AND table5.value = 'value5')); > > This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 34 > rows). > I think the problem is the size of the temporary table the JOIN creates > (37,434) which is probably the issue. There is (7,579) records in my largest > table. I'm not sure what my best option would be to get this size down. But > I'm thinking along the lines of some nested SELECTs to get needed rows in > stages rather than all in one go at the end. My application is probably > going to get much larger data wise than it already is and MySQL is not > really an option anymore. Could possibly go back to UNIONs also if the OR > operation isn't as efficient as two SELECTs (though I highly doubt that is > the case). Run this through the analyser (EXPLAIN) which will show where the time is spent. I would also suggest trying PostgreSQL as my experience with MySQL is that performance degrades significantly with lots of JOINs as it just generates lots of Cartesian products and I think SQLite is the same, ie. a huge table is generated in memory and the conditions are applied to this row by row. Certainly seems a strange query, though. Charlie
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 10:58:04 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > Thanks Charlie, thats exactly how I should be doing it. I would imagine > there are some performance benefits from doing it this way too. Now I've > just got to make it work for my select that involves six not two tables! > Looks like I'll be getting my thick SQL book out for a while. SQL in a Nutshell is pretty good. There shouldn't be any performance benefits if the engine is working properly but it is emminently more readable. You may well hit problems with your SELECT * approach with names colliding, especially when you start adding UNIONs. It is always advisable to use explicit relational variable names (columns). Charlie === Thanks for the pointer Charlie, but I was only using * for my example, and I normaly use fully named columns (table.column) when writing queries. My new query, looks like this (generified): SELECT count(*) FROM table1 INNER JOIN table2 ON (table1.id = table2.rel_id) INNER JOIN table3 ON (table3.rel_id = table2.id) INNER JOIN table4 ON (table3.id = table4.rel_id) INNER JOIN table5 ON (table5.rel_id = table4.id) WHERE table1.value = 'value1' AND ((table3.value LIKE '%value3%' AND table5.value = 'value5') OR (table3.value LIKE '%value3%' AND table5.value = 'value5')); This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 34 rows). I think the problem is the size of the temporary table the JOIN creates (37,434) which is probably the issue. There is (7,579) records in my largest table. I'm not sure what my best option would be to get this size down. But I'm thinking along the lines of some nested SELECTs to get needed rows in stages rather than all in one go at the end. My application is probably going to get much larger data wise than it already is and MySQL is not really an option anymore. Could possibly go back to UNIONs also if the OR operation isn't as efficient as two SELECTs (though I highly doubt that is the case).
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 10:58:04 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > Thanks Charlie, thats exactly how I should be doing it. I would imagine > there are some performance benefits from doing it this way too. Now I've > just got to make it work for my select that involves six not two tables! > Looks like I'll be getting my thick SQL book out for a while. SQL in a Nutshell is pretty good. There shouldn't be any performance benefits if the engine is working properly but it is emminently more readable. You may well hit problems with your SELECT * approach with names colliding, especially when you start adding UNIONs. It is always advisable to use explicit relational variable names (columns). Charlie -- Charlie Clark Communications Manager yellowTAB GmbH Tel: +49-211-600-3657 http://www.yellowtab.com mailto: [EMAIL PROTECTED]
RE: [sqlite] Multi-threading.
I think the point about multi-threaded apps is not that there might be a few instances where they are useful, but how easy is it to debug and support them and is it worth the price for a little more concurrency? In my experience, most debugging IDE's can't handle multiple threads and actually turn the application into a pseudo threaded version prior to running it. I wouldn't mind betting that's what's going on in Java (it's worth pointing out that Java didn't used to even support native threads and I bet most Java programmers have no idea whether they are actually using Native or pseudo threads) Because of this, you get the frustrating situation where an application works fine in debug but fails sporadically in production. Another good real world example of this is the IIS/ASP server side debugging. IIS allocates a new thread per request which are autonomous but do some semaphoring to share the Session object (I think). There's a handy switch you can set on your IIS virtual directory that turns on server side debugging - great! . only, not so great!! Suddenly IIS starts allocating the same thread to every request i.e. it queues your requests and the very problem you were trying to solve goes away! The moral of the story is as Richard says, if there isn't a burning need for multiple threads then don't use them. By the way, Mrs Brisby is being facetious when he says that he doesn't regard Windows as a "modern" GUI - there's no such thing! They've all got their origins in concepts put forward over 20 years ago. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Ben Clewett Sent: 25 July 2005 09:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multi-threading. Dear Mrs Brisby, Thanks for your passionate replies to my original posting. You have much information here. It's obvious I don't know everything about threading. I like what you say about computer science being a Science. This is exactly my point. A science is a collection of theories which hold true within a domain until otherwise dissproven. For instance Isac Newtons's law of gravety and Einstain's law. Both are true within a fixed domain. Both are used today. Neither are truelly corrrect. There will be another more complex theroy in time. This is the same with Threading. There may be places where this is useful. There may be places where it should not be used. Both are theories within computer science which hold true in a limited domain. So when I said this was my opinion, I should have stated this is my theory within a fixed domain. However when you stated that I was wrong, I don't think this is compatible of your statement that Computer Science is a Science. Nothing in any science is either right or wrong. That's a Religion. I don't want to spend ages on the argument, I can see there are passionate views. I only want to explore the idea that threading is a viable strategy for a modern high level language and can produce stable applications with low development time. For instance a call to a TCP blocking Wait. It's perfectly possible for one thread to cycle round handing GUI events, then checking on the port for new packets. But an alternate method is to partition the code space into two autonomous threads using their own variable set. Two simple sets of code which are not coupled, and remain highly cohesive and encapsulated. Communicating through a thread-safe object. Such code is fast to write, easy to read, robust and does the job. I can complete this in c# in ten minutes, and it will not break. With large amounts of time, better methods may be available. But this theory is viable within its domain. > I wasn't about to consider Windows a modern GUI system :) Are you saying it's not modern, or not a GUI? It may not be prefect and it is very complex. But it's not about to go away, and it's installed on modern computers. More importantly, my views on threads remain. If you give the GUI it's own thread, you have implemented a simple solution to ensure that the correct cursor and mouse events are honoured. Then a worker thread is free to do what ever it likes without being coupled to the GUI. Simple robust coding using thread-safe objects. I am also interested in peoples views that threading is slower. Since all processors are likely to be hyperthreaded, multicore, or both. I can see no place where this will be true in the future. > Java uses references, not pointers. Is there any important difference? My point was not about language. It was a question to Dr Hipp about what he feels is missing from the Java pointer (reference) model. Perhaps I should have explained better. Yes, I understand that 'c' can make use of goto, and that goto is fast. There are also very very bad places to use goto. Exceptions, breaks and continue statements are linear code, easy to follow and more robust to code changes. Goto is a legacy of assembler pr
Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames
Just tested, seems to work :) Thanks! - Original Message - From: "Nuno Lucas" <[EMAIL PROTECTED]> To: Sent: Monday, July 25, 2005 10:38 AM Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames I'm not understanding what's the problem here. I always get the column names even on empty result sets (so I can show them in a grid header). I confess I haven't been using the latest sqlite versions to be sure it is still returned, but isn't this what you want? My guess is that maybe you are using the callback API, that need "PRAGMA empty_result_callbacks = 1" to return the columns on empty result sets, but I haven't used that API for a long time. Regards, ~Nuno Lucas [25-07-2005 9:08, Edwin Knoppert escreveu] I might be mistaken but you should never expect row-0 for data. What i meant is that the fieldnames are present when there is data. And not when there is no data. In either case, row-0 should never be threated as data row. So one can not expect data in there. The point is when there is data, SQLite returns the fieldnames from a (joined) query. For what reason it should not return the names when there was no data? (And not having an error of course) I'm pretty certain this is only a technical matter, not a logical one. schema table? I'm not familiar with that, i don't think it is realted to fields from join-ed queries. This question has not much to do with my earlier post, i know how to obtain the fields properly.
Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames
That's good news! Thanks, will try. :) - Original Message - From: "Nuno Lucas" <[EMAIL PROTECTED]> To: Sent: Monday, July 25, 2005 10:38 AM Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames I'm not understanding what's the problem here. I always get the column names even on empty result sets (so I can show them in a grid header). I confess I haven't been using the latest sqlite versions to be sure it is still returned, but isn't this what you want? My guess is that maybe you are using the callback API, that need "PRAGMA empty_result_callbacks = 1" to return the columns on empty result sets, but I haven't used that API for a long time. Regards, ~Nuno Lucas [25-07-2005 9:08, Edwin Knoppert escreveu] I might be mistaken but you should never expect row-0 for data. What i meant is that the fieldnames are present when there is data. And not when there is no data. In either case, row-0 should never be threated as data row. So one can not expect data in there. The point is when there is data, SQLite returns the fieldnames from a (joined) query. For what reason it should not return the names when there was no data? (And not having an error of course) I'm pretty certain this is only a technical matter, not a logical one. schema table? I'm not familiar with that, i don't think it is realted to fields from join-ed queries. This question has not much to do with my earlier post, i know how to obtain the fields properly.
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 10:15:14 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > SELECT * FROM table1, table2 > WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id); > This query works perfectly, can't fault it. > But when I do this, SQLite locks out (Command line interface, and PHP5) > Query 2: > SELECT * FROM table1, table2 > WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id); > OR (table1.value LIKE "%different_value%" AND table1.table2_id = > table2.id)); I think this is a good example of why I use explicit JOINS. SELECT * FROM table1 INNER JOIN table2 ON (table2.id = table1.table2_id) WHERE table1.value LIKE '%value%' OR table1.value LIKE '%value%') Doesn't this do what you want? -- Charlie Clark Communications Manager yellowTAB GmbH Tel: +49-211-600-3657 http://www.yellowtab.com mailto: [EMAIL PROTECTED] Thanks Charlie, thats exactly how I should be doing it. I would imagine there are some performance benefits from doing it this way too. Now I've just got to make it work for my select that involves six not two tables! Looks like I'll be getting my thick SQL book out for a while. Though my new code did look rather promising, I think this way will probably be better for my multiple tables. New Code: SELECT * FROM table1, table2 WHERE (table1.value LIKE "%value1%" AND table1.table2_id = table2.id) GROUP BY table2.id UNION SELECT * FROM table1, table2 WHERE (table1.value LIKE "%value2%" AND table1.table2_id = table2.id) GROUP BY table2.id; Now I can fix this problem and move on. Thanks again. Dave.
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 10:15:14 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > SELECT * FROM table1, table2 > WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id); > This query works perfectly, can't fault it. > But when I do this, SQLite locks out (Command line interface, and PHP5) > Query 2: > SELECT * FROM table1, table2 > WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id); > OR (table1.value LIKE "%different_value%" AND table1.table2_id = > table2.id)); I think this is a good example of why I use explicit JOINS. SELECT * FROM table1 INNER JOIN table2 ON (table2.id = table1.table2_id) WHERE table1.value LIKE '%value%' OR table1.value LIKE '%value%') Doesn't this do what you want? -- Charlie Clark Communications Manager yellowTAB GmbH Tel: +49-211-600-3657 http://www.yellowtab.com mailto: [EMAIL PROTECTED]
Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames
I'm not understanding what's the problem here. I always get the column names even on empty result sets (so I can show them in a grid header). I confess I haven't been using the latest sqlite versions to be sure it is still returned, but isn't this what you want? My guess is that maybe you are using the callback API, that need "PRAGMA empty_result_callbacks = 1" to return the columns on empty result sets, but I haven't used that API for a long time. Regards, ~Nuno Lucas [25-07-2005 9:08, Edwin Knoppert escreveu] I might be mistaken but you should never expect row-0 for data. What i meant is that the fieldnames are present when there is data. And not when there is no data. In either case, row-0 should never be threated as data row. So one can not expect data in there. The point is when there is data, SQLite returns the fieldnames from a (joined) query. For what reason it should not return the names when there was no data? (And not having an error of course) I'm pretty certain this is only a technical matter, not a logical one. schema table? I'm not familiar with that, i don't think it is realted to fields from join-ed queries. This question has not much to do with my earlier post, i know how to obtain the fields properly.
Re: [sqlite] Query locking up SQLite
this statement has an extra ; which may be the error. Another thought, when quoting string literals, it is better to use single quotes('), since double quotes(") means identifier --column name-- first, string literal second. John == Thanks for the very fast reply John. The extra (;) was a copying and pasting error on my part when writing out the email and unfortunatly swapping (") for (') has had no effect on the result of running the query (SQLite still locks up at 100% CPU usage). The problem appears to lie within the use of the OR operator when setting conditions accross two tables. So its either something to do with the JOIN or OR. Thanks again for the ideas.
Re: [sqlite] Query locking up SQLite
David Fowler wrote: SQLite Version 3.2.2. Is this a bug, or is my SQL that bad? Query 1: SELECT * FROM table1, table2 WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id); This query works perfectly, can't fault it. But when I do this, SQLite locks out (Command line interface, and PHP5) Query 2: SELECT * FROM table1, table2 WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id); this statement has an extra ; which may be the error. Another thought, when quoting string literals, it is better to use single quotes('), since double quotes(") means identifier --column name-- first, string literal second. OR (table1.value LIKE "%different_value%" AND table1.table2_id = table2.id)); This query (and even more complex versions of it) works in MySQL (Haven't tried another DB yet) and I'm trying to migrate to SQLite, this is really holding me back. Any ideas, I'm totaly stuck, currently trying to see if I can get the same results using another piece of SQL that SQLite will like, probably going to end up with a compound statement (UNION most likely I think). John
[sqlite] Query locking up SQLite
SQLite Version 3.2.2. Is this a bug, or is my SQL that bad? Query 1: SELECT * FROM table1, table2 WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id); This query works perfectly, can't fault it. But when I do this, SQLite locks out (Command line interface, and PHP5) Query 2: SELECT * FROM table1, table2 WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id); OR (table1.value LIKE "%different_value%" AND table1.table2_id = table2.id)); This query (and even more complex versions of it) works in MySQL (Haven't tried another DB yet) and I'm trying to migrate to SQLite, this is really holding me back. Any ideas, I'm totaly stuck, currently trying to see if I can get the same results using another piece of SQL that SQLite will like, probably going to end up with a compound statement (UNION most likely I think).
Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames
I might be mistaken but you should never expect row-0 for data. What i meant is that the fieldnames are present when there is data. And not when there is no data. In either case, row-0 should never be threated as data row. So one can not expect data in there. The point is when there is data, SQLite returns the fieldnames from a (joined) query. For what reason it should not return the names when there was no data? (And not having an error of course) I'm pretty certain this is only a technical matter, not a logical one. schema table? I'm not familiar with that, i don't think it is realted to fields from join-ed queries. This question has not much to do with my earlier post, i know how to obtain the fields properly. - Original Message - From: "Charlie Clark" <[EMAIL PROTECTED]> To: Sent: Sunday, July 24, 2005 4:26 PM Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames On 2005-07-24 at 16:11:26 [+0200], Edwin Knoppert <[EMAIL PROTECTED]> wrote: Why defending it? It's prob. a simple issue. Besides, why i want the names, why should a system need to parse a query? You approach this way to 'static' imo (what you enter is your result). You can easily say to also remove the fieldnames when we *have* data. An 0-row containing the relational variables is a *special* case and non-relational and causes problems for results which do not expect the names of the values to be included in the result set. .schema(table) gives you all you need. Charlie
Re: [sqlite] Multi-threading.
Dear Mrs Brisby, Thanks for your passionate replies to my original posting. You have much information here. It's obvious I don't know everything about threading. I like what you say about computer science being a Science. This is exactly my point. A science is a collection of theories which hold true within a domain until otherwise dissproven. For instance Isac Newtons's law of gravety and Einstain's law. Both are true within a fixed domain. Both are used today. Neither are truelly corrrect. There will be another more complex theroy in time. This is the same with Threading. There may be places where this is useful. There may be places where it should not be used. Both are theories within computer science which hold true in a limited domain. So when I said this was my opinion, I should have stated this is my theory within a fixed domain. However when you stated that I was wrong, I don't think this is compatible of your statement that Computer Science is a Science. Nothing in any science is either right or wrong. That's a Religion. I don't want to spend ages on the argument, I can see there are passionate views. I only want to explore the idea that threading is a viable strategy for a modern high level language and can produce stable applications with low development time. For instance a call to a TCP blocking Wait. It's perfectly possible for one thread to cycle round handing GUI events, then checking on the port for new packets. But an alternate method is to partition the code space into two autonomous threads using their own variable set. Two simple sets of code which are not coupled, and remain highly cohesive and encapsulated. Communicating through a thread-safe object. Such code is fast to write, easy to read, robust and does the job. I can complete this in c# in ten minutes, and it will not break. With large amounts of time, better methods may be available. But this theory is viable within its domain. > I wasn't about to consider Windows a modern GUI system :) Are you saying it's not modern, or not a GUI? It may not be prefect and it is very complex. But it's not about to go away, and it's installed on modern computers. More importantly, my views on threads remain. If you give the GUI it's own thread, you have implemented a simple solution to ensure that the correct cursor and mouse events are honoured. Then a worker thread is free to do what ever it likes without being coupled to the GUI. Simple robust coding using thread-safe objects. I am also interested in peoples views that threading is slower. Since all processors are likely to be hyperthreaded, multicore, or both. I can see no place where this will be true in the future. > Java uses references, not pointers. Is there any important difference? My point was not about language. It was a question to Dr Hipp about what he feels is missing from the Java pointer (reference) model. Perhaps I should have explained better. Yes, I understand that 'c' can make use of goto, and that goto is fast. There are also very very bad places to use goto. Exceptions, breaks and continue statements are linear code, easy to follow and more robust to code changes. Goto is a legacy of assembler programming. I don't think it's time to teach new coders about goto. Which is my theory within a fixed domain. Regards, Ben. Mrs. Brisby wrote: On Wed, 2005-07-20 at 17:26 +0100, Ben Clewett wrote: Dr Hipp, I am just playing devils advocate here because I have completed much Java programming in a multi-threaded application. :) I understand the problems of multi-threading. I am reminded that it took nearly 20 years of development to get multi-processor support in a modern OS stable. Much success for this can be attributed to Semaphore Flags. With CPU hardware support to ensure that the semaphore it's self cannot be controlled by more than one process. ITC in 1970 supported multiple threads trivially. Multi-thread applications suffer the same problems. Without semaphore flags or 20 years of development. A novice programmer can happily create a second thread and quickly create terribly applications. However the need for multi-threads is compelling. Especially in a GUI environment. For instance a Mail reader. Where one thread is needed to ensure the GUI is drawn correctly and respond to GUI events. Another to download and dispatch mail. (My Thunderbird has 10 threads. This may be a bit of overkill :) No. Threads are not a need. They allow you to use blocking system calls in parallel without extra page table loads. History has demonstrated that programmers building multithreaded applications tend to produce buggier code, and code that touches more pages than a non-threaded version. As a result, the non-threaded version is easier to write, safer, and runs faster. As another user also mentioned, a Windows system works better with few processes