Re: [sqlite] how to use group_concat uniquely
Igor Tandetnik wrote: > > On 1/19/2011 12:51 PM, Noah Hart wrote: >>> select E, replace(group_concat(distinct T), ',', ';'), >>> replace(group_concat(distinct P), ',', ';') from MyTable; >> >>Ahhh >> the DISTINCT keyword was what I'm missing. However, this may be a bug, >> when I use group_concat(DISTINCT T, ';') I get the error message >> DISTINCT aggregates must have exactly one argument > > That's why I had to muck around with replace(). The syntax only allows > DISTINCT keyword in aggregate functions taking exactly one parameter. > group_concat defaults to comma as a separator when called with one > parameter. > -- > Igor Tandetnik > Well, I went and read the page on the Aggregate Functions, and it is very clear in the documentation. Thanks again, Noah -- View this message in context: http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30716460.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How can we use POWER(2,32) like function in SQLite
Hi Please suggest, How can we use POWER(2,32) like function in SQLite? Thanks Sunil Bhardwaj Ext. 1125 (0120-2567001) 9818868910 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
Quoth Simon Slavin , on 2011-01-20 00:55:18 +: > Agreed. I was imagining that one file was on an external hard disk > and the other was on an internal disk. If the power to the external > hard disk goes off, one file will get updated but the other won't. > I don't know enough about the inner workings of SQLite to know if it > can cope. I suspect that for the files themselves it's okay so long as fsync is actually a hard sync (and not just a reordering barrier), which it's supposed to be anyway. I don't believe SQLite can be making any assumptions regarding the reordering of unsynchronized writes between files on a single device; if fsync is a hard sync then any ordering semantics are already propagated through the application to the other device in turn. However, if either file ever gets created or deleted during normal operation, then you have the problem of needing to sync the directory, and that probably won't work because SQLite won't hit the correct directory for whichever file is actually somewhere else. Also, if one file is a symlink and it gets deleted and recreated, then it will be recreated on the wrong filesystem. This suggests that actually the main database should be the symlink if one goes that route, and it should be precreated as an empty file (and synchronized to storage) before any operations begin. Either way, it's an unsupported configuration and should only be used with extreme caution, especially if the SQLite component may later be replaced with the expectation of backwards compatibility. > Simon. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 20 Jan 2011, at 12:49am, Scott Hess wrote: > The biggest concern I would have would be whether there are subtle > atomicity guarantees which are served by being on the same filesystem > which cannot be served by being split across multiple filesystems. Agreed. I was imagining that one file was on an external hard disk and the other was on an internal disk. If the power to the external hard disk goes off, one file will get updated but the other won't. I don't know enough about the inner workings of SQLite to know if it can cope. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Wed, Jan 19, 2011 at 12:15 PM, Dustin Sallings wrote: > This isn't a question so much about value judgment (I've already > argued that some, though mentioning maintenance tools is helpful > there, too). It comes down to whether reliability of SQLite itself > would be reduced if a WAL existed on a different partition -- whether > there are any assumptions WAL makes that would be invalid across > a filesystem boundary. You either would have to hack the filesystem namespace to look like you want it to look using mount tricks, or modify SQLite's VFS (or write your own) to accomplish what you want. Since you cannot trust the contents of the database itself, you would need to provide some means of specifying the alternate location in code. The biggest concern I would have would be whether there are subtle atomicity guarantees which are served by being on the same filesystem which cannot be served by being split across multiple filesystems. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 19 Jan 2011, at 8:15pm, Dustin Sallings wrote: > On Jan 19, 2011, at 11:01, Simon Slavin wrote: > >> If you do something special to keep your journal file in a different place, >> these other sqlite3 applications won't find it. So they'll just find a >> corrupt database file, and are less likely to be able to figure out how to >> restore to a COMMIT point or a SAVEPOINT. > > > I understand this concern and think it's a valid point. I can assume > for the purpose of this usage that only tools I provide will be used to > access the DB (I ship a sqlite3 binary since I'm using WAL and I've got users > on CentOS which ships sqlite 1.2 for all I know). This solution depends on what facilities your operating system has for creating an alias, soft link, shortcut, or whatever it calls them. If your operating system does these correctly, the sqlite3 open function should correctly understand it and use it. If CentOS is just another flavour of Linux you will probably want to use soft links. Experiment with soft links (or whatever is appropriate) first, to make sure you understand what they are and how they work. Given two different filespaces ... one you want the database file on (call it D) one you want the journal file on (call it J) Step 1 -- Set up your application to use J for both files. Start your application or whatever other tool you have to create the database file and put at least a little data in it. Quit the application. Step 2 -- Copy your database file from J to D. Delete (or rename) the original copy of the file in J. Create an alias/link/shortcut/whatever in J, linked to the file that is now in D. Step 3 -- Restart your application. Make sure it is correctly following the link and finding the data you have moved to D. Because your application thinks that the database file is on J, it should create the journal file on J. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Export sqlite table in textpad/notepad
On 19 Jan 2011, at 7:51pm, Alok Singh wrote: > how to Export sqlite table in textpad/notepad using VB.NET application ? > Any direct command which can help me. Ask in a VB forum. Your problem is more to do with VB than with SQLite. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Jan 19, 2011, at 11:01, Simon Slavin wrote: > If you do something special to keep your journal file in a different place, > these other sqlite3 applications won't find it. So they'll just find a > corrupt database file, and are less likely to be able to figure out how to > restore to a COMMIT point or a SAVEPOINT. I understand this concern and think it's a valid point. I can assume for the purpose of this usage that only tools I provide will be used to access the DB (I ship a sqlite3 binary since I'm using WAL and I've got users on CentOS which ships sqlite 1.2 for all I know). I'm a bit of a proxy of this question. I wrote software that uses SQLite under some pretty high volumes and I have a user wanting to split stuff up across multiple filesystems. I already have the ability to do data partitioning in the application, but the user is wanting to separate the WAL out as well. This isn't a question so much about value judgment (I've already argued that some, though mentioning maintenance tools is helpful there, too). It comes down to whether reliability of SQLite itself would be reduced if a WAL existed on a different partition -- whether there are any assumptions WAL makes that would be invalid across a filesystem boundary. -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Export sqlite table in textpad/notepad
Hi Sqlite Team, my project at its last stage. *can you help me on :* how to Export sqlite table in textpad/notepad using VB.NET application ? Any direct command which can help me. or shall i use select command and write into textpad/notepad line by line ? which one will be best way, or anyother way if anyone having please advice me. Kindly help me, its urgent for me, after 8 hour i am having demo presentation on my software. Thanks, Alok ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 19 Jan 2011, at 6:43pm, Dustin Sallings wrote: > On Jan 19, 2011, at 2:35, Richard Hipp wrote: > >> No. The WAL has to be in the same directory as the original database. >> Otherwise, the process that tries to recover from a crash or power failure >> won't know where to find the WAL file. > > I understand how it's opening it. This is more about how the file is > used. > > If I could convince SQLite to open the WAL in a location other than in > the same location as the main db, would this cause reliability problems? The problem is not with the session which creates the WAL file, its with the one that has to clear up after any crash. With things as they stand, every application which uses sqlite3 looks for a journal file in the same place. So if your application (or the computer) crashes and someone uses a different application next to open the database file, it will definitely be able to find the journal file and restore the database to an uncorrupted state, right after a COMMIT, just where you'd want it. This happens a lot: after a crash instead of restarting the normal application, someone uses the sqlite3 command-line utility or some sort of database viewer to see what state the data is in. With the journal in a predictable location SQLite will handle this correctly no matter what application they use. If you do something special to keep your journal file in a different place, these other sqlite3 applications won't find it. So they'll just find a corrupt database file, and are less likely to be able to figure out how to restore to a COMMIT point or a SAVEPOINT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Jan 19, 2011, at 2:35, Richard Hipp wrote: > No. The WAL has to be in the same directory as the original database. > Otherwise, the process that tries to recover from a crash or power failure > won't know where to find the WAL file. I understand how it's opening it. This is more about how the file is used. If I could convince SQLite to open the WAL in a location other than in the same location as the main db, would this cause reliability problems? -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?
On Jan 19, 2011, at 10:03 AM, Richard Hipp wrote: > On Wed, Jan 19, 2011 at 12:35 PM, James Berry wrote: > >> I'm trying to understand whether there's any problem with committing a >> transaction while in the process of stepping over results. > > The ability to do this was added to SQLite in version 3.6.5 in November of > 2008. Thanks Richard, for the clarification. James >> BEGIN DEFERRED TRANSACTION >> >> SELECT * FROM a >> sqlite3_step over select results >> { >> COMMIT >> BEGIN DEFERRED TRANSACTION >> >> INSERT INTO b >> } >> >> COMMIT ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On 1/19/2011 12:51 PM, Noah Hart wrote: >> select E, replace(group_concat(distinct T), ',', ';'), >> replace(group_concat(distinct P), ',', ';') from MyTable; > >Ahhh > the DISTINCT keyword was what I'm missing. However, this may be a bug, > when I use group_concat(DISTINCT T, ';') I get the error message > DISTINCT aggregates must have exactly one argument That's why I had to muck around with replace(). The syntax only allows DISTINCT keyword in aggregate functions taking exactly one parameter. group_concat defaults to comma as a separator when called with one parameter. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?
On 19 Jan 2011, at 5:35pm, James Berry wrote: > I'm trying to understand whether there's any problem with committing a > transaction while in the process of stepping over results. Does the transaction you're committing have anything at all to do with the SELECT you're stepping through ? If you do the same SELECT after the transaction might the results have changed ? If so, it's a bad idea to do the two things at the same time. Read the results of the SELECT into one or more variables. Once you've finalized the SELECT, then make your changes. Better still, if possible phrase your changes as an UPDATE command with a WHERE clause. That way you don't need any SELECT statement at all. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?
On Wed, Jan 19, 2011 at 12:35 PM, James Berry wrote: > I'm trying to understand whether there's any problem with committing a > transaction while in the process of stepping over results. > > The following loop, in some kind of weird hybrid pseudo-code, tries to > illustrate what I'm doing: while stepping over results from a select > statement, doing inserts into another table. During that process, I want to > break up the current transaction. > > Is there any effect on the outer select/step by committing and restarting > the transaction while the outer statement is still being used? (In my actual > use-case, the inner commit/begin is not performed on every iteration). > > The codes seems to be working for me, but I want to make sure it's not > working in the realm of undefined operation. > The ability to do this was added to SQLite in version 3.6.5 in November of 2008. > > James > > > BEGIN DEFERRED TRANSACTION > > SELECT * FROM a > sqlite3_step over select results > { >COMMIT >BEGIN DEFERRED TRANSACTION > >INSERT INTO b > } > > COMMIT > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?
On 1/19/2011 12:49 PM, James Berry wrote: > > On Jan 19, 2011, at 9:43 AM, Igor Tandetnik wrote: > >> On 1/19/2011 12:35 PM, James Berry wrote: >>> I'm trying to understand whether there's any problem with committing a >>> transaction while in the process of stepping over results. >> >> I believe COMMIT would fail while there is an outstanding statement on >> the connection. > > If that's the case, then is the following pseudo code any better? It should make no difference. You shouldn't be able to commit while there's a statement that wasn't reset or finalized. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On 1/19/2011 12:19 PM, Noah Hart wrote: > I am having difficulty trying to return data in a very compact form. Here > is > a simple example that will explain the problem: > > I have a table with column E, T and P containing data such as > A, 1, R > A, 1, S > A, 2, R > A, 2, S > > Trying the query > select E, group_concat(T,';'), group_concat(P,';') give me the expected > results > A 1;1;2;2 R;S;R;S > > However, I would like to create a query will return the results > > A 1;2 R;S select E, replace(group_concat(distinct T), ',', ';'), replace(group_concat(distinct P), ',', ';') from MyTable; Wouldn't work if values in T or P contain commas. Here's a more verbose query that doesn't have this limitation (but is likely much slower): select E, (select group_concat(T, ';') from (select distinct T from MyTable where E=AllE.E)), (select group_concat(P, ';') from (select distinct P from MyTable where E=AllE.E)) from (select distinct E from MyTable) AllE; -- Igor Tandetnik ___ Ahhh the DISTINCT keyword was what I'm missing. However, this may be a bug, when I use group_concat(DISTINCT T, ';') I get the error message DISTINCT aggregates must have exactly one argument Noah -- View this message in context: http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712310.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?
On Jan 19, 2011, at 9:43 AM, Igor Tandetnik wrote: > On 1/19/2011 12:35 PM, James Berry wrote: >> I'm trying to understand whether there's any problem with committing a >> transaction while in the process of stepping over results. > > I believe COMMIT would fail while there is an outstanding statement on > the connection. If that's the case, then is the following pseudo code any better? SELECT * FROM a count == 0 sqlite3_step over select results { if (count == 0) BEGIN DEFERRED TRANSACTION else if (count % transactionSize == 0) { COMMIT BEGIN DEFERRED TRANSACTION } ++count INSERT INTO b } COMMIT > >> The codes seems to be working for me > > Check return values from various SQLite calls. > -- > Igor Tandetnik > > ___ > 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] Effect of commit transaction while in process of sqlite3_step'ing?
On 1/19/2011 12:35 PM, James Berry wrote: > I'm trying to understand whether there's any problem with committing a > transaction while in the process of stepping over results. I believe COMMIT would fail while there is an outstanding statement on the connection. > The codes seems to be working for me Check return values from various SQLite calls. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On 1/19/2011 12:19 PM, Noah Hart wrote: > I am having difficulty trying to return data in a very compact form. Here is > a simple example that will explain the problem: > > I have a table with column E, T and P containing data such as > A, 1, R > A, 1, S > A, 2, R > A, 2, S > > Trying the query > select E, group_concat(T,';'), group_concat(P,';') give me the expected > results > A 1;1;2;2 R;S;R;S > > However, I would like to create a query will return the results > > A 1;2 R;S select E, replace(group_concat(distinct T), ',', ';'), replace(group_concat(distinct P), ',', ';') from MyTable; Wouldn't work if values in T or P contain commas. Here's a more verbose query that doesn't have this limitation (but is likely much slower): select E, (select group_concat(T, ';') from (select distinct T from MyTable where E=AllE.E)), (select group_concat(P, ';') from (select distinct P from MyTable where E=AllE.E)) from (select distinct E from MyTable) AllE; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Effect of commit transaction while in process of sqlite3_step'ing?
I'm trying to understand whether there's any problem with committing a transaction while in the process of stepping over results. The following loop, in some kind of weird hybrid pseudo-code, tries to illustrate what I'm doing: while stepping over results from a select statement, doing inserts into another table. During that process, I want to break up the current transaction. Is there any effect on the outer select/step by committing and restarting the transaction while the outer statement is still being used? (In my actual use-case, the inner commit/begin is not performed on every iteration). The codes seems to be working for me, but I want to make sure it's not working in the realm of undefined operation. James BEGIN DEFERRED TRANSACTION SELECT * FROM a sqlite3_step over select results { COMMIT BEGIN DEFERRED TRANSACTION INSERT INTO b } COMMIT ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On Wed, Jan 19, 2011 at 09:19:54AM -0800, Noah Hart wrote: > Any ideas? You have two columns to sub-group by independently, as it were. You need correlated sub-queries to get that done: sqlite> SELECT f1.e, (SELECT group_concat(f2.t, ';') ...> FROM foo f2 WHERE f1.e = f2.e GROUP BY f2.p), (SELECT group_concat(f2.p, ';') ...> FROM foo f2 WHERE f1.e = f2.e GROUP BY f2.t) FROM foo f1 GROUP BY f1.e; A|1;2|R;S B|1;2|R;S sqlite> Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to use group_concat uniquely
I am having difficulty trying to return data in a very compact form. Here is a simple example that will explain the problem: I have a table with column E, T and P containing data such as A, 1, R A, 1, S A, 2, R A, 2, S Trying the query select E, group_concat(T,';'), group_concat(P,';') give me the expected results A 1;1;2;2 R;S;R;S However, I would like to create a query will return the results A 1;2 R;S Any ideas? Noah Hart -- View this message in context: http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712025.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Patch: FTS3 Snawball Stemmer support
Hello! May be this will be useful for somebody too. The code is not well tested yet but demonstrate the solution. http://sqlite.mobigroup.ru/wiki?name=FTS3+Snowball+Stemmer -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 19 Jan 2011, at 11:53, Russell Leighton wrote: > Perhaps that could be the default and a pragma could be used to > override this default and specify the directory holding the WAL. > > This could be useful in cases that users want to put the WAL > someplace else (like an SSD). I think you're getting way beyond the 'Lite' world with that one. On modern Linux systems you can use mount --bind /path/to/existing/file /alternate/path/to/that/file Which makes any access of /alternate/path/to/that/file an access of /path/to/existing/file. Both must exist; I recommend using touch to create the file at the alternate path, as it's mostrously confusing if it contains any data when the mount has not been run. You could use this facility to make the SQLite database appear to be on your SSD. For safety's sake you should also make the WAL file appear to be colocated with the database, so that the WAL file can be found by an application which accesses the database with either path. This may or may not be safe depending on whether SQLite uses the path to the database to perform any locking. To be clear: I won't be doing this myself… Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query or table
Many thanks for the advice Phil, I'll follow it. Ian On 19/01/2011 13:06, Philip Graham Willoughby wrote: > Ian, > > On 18 Jan 2011, at 16:40, Ian Hardingham wrote: > >> In general, my server is too slow. It has to run many operations a >> second, and many DB operations, so the exact definition of "too slow" is >> a little arbitrary. > Nothing in that paragraph implies that the DB speed is a problem, or that > this specific query is relevant. > > For your own sake, measure the performance precisely and accurately so that > you know how much time is spent in each area of your application. If all you > know is that the overall performance is slow, you don't know enough. You also > need to give yourself a target speed that you regard as fast enough, not > least so you know when to stop working on this and do something else. > > Once you know that it is the database that's using most of the time and you > have identified which queries are using most of that time you can use EXPLAIN > QUERY PLAN to ask SQLite how it will execute your worst queries; you can use > that information to make a more informed decision about how best to optimise. > If any table in the query lacks an appropriate index, it would benefit you to > add one for instance. It is generally cheaper to run a CREATE INDEX command > than to redesign your tables/queries/application logic. And make sure you > have issued ANALYZE at least once since your database took the size/shape it > has now. > > Chapter 2 of O'Reilly's High Performance MySQL (Finding Bottlenecks: > Benchmarking and Profiling) is a good introduction to this field. > >> I'm intruiged that you feel a pre-computed table is not a good idea. Is >> there an expectation that that would not be especially faster than my >> current method? > It would be faster at query-time than your current method. It would be much > slower at insert/update time, and more likely to wind up out-of-step with the > original table and leave inaccurate results. It would consume vast amounts of > extra space making it less likely that your database will fit in caches > (because you haven't normalised your schema at all, see below). > > Since you have not shown us your schema there is a limited amount anyone on > this list can comment on: we do not know what indexes you already have, and > you have not shown us the output of EXPLAIN QUERY PLAN or EXPLAIN for either > of the queries which concern you. > > From what we can see in the query you posted it seems you have not > normalised your database at all. If you stored a table mapping INTEGER > PRIMARY KEY playerID numbers to player names with a UNIQUE INDEX over the > player names and used the IDs instead of the names in multiturnTable your > query would go much faster, even if it required a full-table scan or two. > This is because the engine would need to read and compare fewer bytes of data > per row to determine if the row was a match. You would get more rows per page > and therefore less I/O would be required. The entire database would also be > smaller and therefore more likely to fit in the OS and hardware caches in the > machine you're using. > > Best Regards, > > Phil Willoughby ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query or table
Ian, On 18 Jan 2011, at 16:40, Ian Hardingham wrote: > In general, my server is too slow. It has to run many operations a > second, and many DB operations, so the exact definition of "too slow" is > a little arbitrary. Nothing in that paragraph implies that the DB speed is a problem, or that this specific query is relevant. For your own sake, measure the performance precisely and accurately so that you know how much time is spent in each area of your application. If all you know is that the overall performance is slow, you don't know enough. You also need to give yourself a target speed that you regard as fast enough, not least so you know when to stop working on this and do something else. Once you know that it is the database that's using most of the time and you have identified which queries are using most of that time you can use EXPLAIN QUERY PLAN to ask SQLite how it will execute your worst queries; you can use that information to make a more informed decision about how best to optimise. If any table in the query lacks an appropriate index, it would benefit you to add one for instance. It is generally cheaper to run a CREATE INDEX command than to redesign your tables/queries/application logic. And make sure you have issued ANALYZE at least once since your database took the size/shape it has now. Chapter 2 of O'Reilly's High Performance MySQL (Finding Bottlenecks: Benchmarking and Profiling) is a good introduction to this field. > I'm intruiged that you feel a pre-computed table is not a good idea. Is > there an expectation that that would not be especially faster than my > current method? It would be faster at query-time than your current method. It would be much slower at insert/update time, and more likely to wind up out-of-step with the original table and leave inaccurate results. It would consume vast amounts of extra space making it less likely that your database will fit in caches (because you haven't normalised your schema at all, see below). Since you have not shown us your schema there is a limited amount anyone on this list can comment on: we do not know what indexes you already have, and you have not shown us the output of EXPLAIN QUERY PLAN or EXPLAIN for either of the queries which concern you. >From what we can see in the query you posted it seems you have not normalised >your database at all. If you stored a table mapping INTEGER PRIMARY KEY >playerID numbers to player names with a UNIQUE INDEX over the player names and >used the IDs instead of the names in multiturnTable your query would go much >faster, even if it required a full-table scan or two. This is because the >engine would need to read and compare fewer bytes of data per row to determine >if the row was a match. You would get more rows per page and therefore less >I/O would be required. The entire database would also be smaller and therefore >more likely to fit in the OS and hardware caches in the machine you're using. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert into DB fail on NAND flash
On 19 Jan 2011, at 9:10am, Gigin Jose wrote: > I am working on ARM9 based s3c2440 embedded platform on linux OS. The > database I use is SQLITE over QT application. The "insert into" query is > failing once I try to insert values into a database. The database resides on > a NAND flash with yaffs2 formated filesystem. I don't have time to explore all the peculiarities of a new file system, so you're going to have to narrow it down a little. Please try exactly the same procedure, including deleting the data file, on a conventional hard disk using a conventional file system, and tell us if it fails there. > The following is the query which I execute: > QString stat = QString("insert into user20 values('%0', '%1','%2' > )").arg(id).arg(name).arg(address); > k = query.exec(stat); > if(k == false)qDebug("K is false for record ,%s > \n",id.toAscii().data()); > After continuous read/write the query is failing. Your problem could be anything up to running out of space on the drive. SQLite returns a result status, not just true/false. Please tell us the result code: http://www.sqlite.org/c3ref/c_abort.html If possible please also obtain the text form of the error message: http://www.sqlite.org/c3ref/errcode.html since this often gives even more precise information about the problem. > Once I restart my target (s3c2440), the query is again executed fine. Before > the query is executed, I delete the database file, if it exist, so that > ideally the record insertion can happen. You delete the file while you have a handle to it open in your target ? That will upset anything trying to access the file. Delete the file only when your application does not have a connection to it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date operations when date is a TEXT
On 19 January 2011 10:06, Ian Hardingham wrote: > Hey guys. > > Probably unwisely, I store dates in the following format: > > "year month day hour minute" > > For example: > > "11 1 4 16 22" > > I wish to find all rows in a table which are more than 8 days old. Is > there a way of doing this in SQLite or should I just do it in my own code? You need to construct ISO8601 style date string fro SQLite's date functions... SQLite version 3.6.11 Enter ".help" for instructions sqlite> sqlite> sqlite> create table tst( id integer primary key, time text ); sqlite> sqlite> create view tstView1 as select *, ...> rtrim( rtrim( ...> rtrim( rtrim( ...> rtrim( rtrim( ...> rtrim( rtrim( time, ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ) ...> yr, ...> ltrim( ltrim( ...> rtrim( rtrim( ...> rtrim( rtrim( ...> rtrim( rtrim( time, ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ) ...> mth, ...> ltrim( ltrim( ...> ltrim( ltrim( ...> rtrim( rtrim( ...> rtrim( rtrim( time, ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ) ...> day, ...> ltrim( ltrim( ...> ltrim( ltrim( ...> ltrim( ltrim( ...> rtrim( rtrim( time, ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ) ...> hr, ...> ltrim( ltrim( ...> ltrim( ltrim( ...> ltrim( ltrim( ...> ltrim( ltrim( time, ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ), ...> '1234567890' ), ' ' ) ...> min ...> from tst; sqlite> sqlite> create view tstView2 as ...> select *, '20' || ...> case length( yr ) == 1 when 1 then '0' || yr else yr end || '-' || ...> case length( mth ) == 1 when 1 then '0' || mth else mth end || '-' || ...> case length( day ) == 1 when 1 then '0' || day else day end || ' ' || ...> case length( hr ) == 1 when 1 then '0' || hr else hr end || ':' || ...> case length( min ) == 1 when 1 then '0' || min else min end || ':00' dateStr ...> from tstView1; sqlite> sqlite> create view tstView3 as ...> select *, julianday( dateStr ) julian ...> from tstView2; sqlite> sqlite> sqlite> insert into tst( time ) values( '11 1 4 16 22' ); sqlite> insert into tst( time ) values( '1 11 14 6 2' ); sqlite> insert into tst( time ) values( '21 1 24 1 3' ); sqlite> insert into tst( time ) values( '13 3 24 16 22' ); sqlite> insert into tst( time ) values( '10 4 1 16 22' ); sqlite> sqlite> insert into tst( time ) values( '11 1 11 10 22' ); sqlite> insert into tst( time ) values( '11 1 12 16 22' ); sqlite> sqlite> select dateStr from tstview3 where julianday( 'now', '-8 days' ) > julian; 2011-01-04 16:22:00 2001-11-14 06:02:00 2010-04-01 16:22:00 2011-01-11 10:22:00 sqlite> select dateStr from tstview3 where julianday( 'now', '-8 days' ) < julian; 2021-01-24 01:03:00 2013-03-24 16:22:00 2011-01-12 16:22:00 sqlite> sqlite> select datetime( 'now' ); 2011-01-19 11:53:40 sqlite> > > Thanks, > Ian Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Jan 19, 2011, at 5:35 AM, Richard Hipp wrote: > On Wed, Jan 19, 2011 at 2:52 AM, Dustin Sallings > wrote: > >> >> Is it possible without violating any assumptions that would >> lead to >> reliability problems to have a DB's WAL exist on a separate >> filesystem? >> > > No. The WAL has to be in the same directory as the original database. > Otherwise, the process that tries to recover from a crash or power > failure > won't know where to find the WAL file. > Perhaps that could be the default and a pragma could be used to override this default and specify the directory holding the WAL. This could be useful in cases that users want to put the WAL someplace else (like an SSD). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date operations when date is a TEXT
On Wed, Jan 19, 2011 at 5:06 AM, Ian Hardingham wrote: > Hey guys. > > Probably unwisely, I store dates in the following format: > > "year month day hour minute" > > For example: > > "11 1 4 16 22" > Perhaps you could write an extension function (using sqlite3_create_function()) that will convert your data format into ISO8601: " -MM-DD HH:MM", or in your case, "2011-01-04 16:22". Then the strings will compare in date order, the dates will be more easily human-readable, you can leverage the built-in date/time functions of SQLite, and you will be in compliance with an international standard. > > I wish to find all rows in a table which are more than 8 days old. Is > there a way of doing this in SQLite or should I just do it in my own code? > > Thanks, > Ian > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL-query execution bug
On Tue, Jan 18, 2011 at 12:07 PM, Vadim Smirnov wrote: > Hello! > I've found a bug in execution queries like this: > SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master > WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value) > FROM table4 T4 WHERE T4.date_value<=T2.date_value GROUP BY group) G ON > G.group=T3.group) > Such queries returns empty resultset because of invalid evaluation WHERE > T4.date_value<=T2.date_value. It seems that SQLite evaluates WHERE > T4.date_value<=null in fact. > It works when I try it. Why don't you send us a specific example that does not work for you (including CREATE TABLE statements and INSERTs to fill the tables with data) and we'll have another look. > If we replace "WHERE T4.date_value<=T2.date_value" with "WHERE > T4.date_value<=" everything will be okey. > > Sincerely Yours, Wadim Smirnov > System architect > Positive Technologies CJSC, Russia > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Wed, Jan 19, 2011 at 2:52 AM, Dustin Sallings wrote: > >Is it possible without violating any assumptions that would lead to > reliability problems to have a DB's WAL exist on a separate filesystem? > No. The WAL has to be in the same directory as the original database. Otherwise, the process that tries to recover from a crash or power failure won't know where to find the WAL file. > > -- > dustin sallings > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL-query execution bug
Hello! I've found a bug in execution queries like this: SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value) FROM table4 T4 WHERE T4.date_value<=T2.date_value GROUP BY group) G ON G.group=T3.group) Such queries returns empty resultset because of invalid evaluation WHERE T4.date_value<=T2.date_value. It seems that SQLite evaluates WHERE T4.date_value<=null in fact. If we replace "WHERE T4.date_value<=T2.date_value" with "WHERE T4.date_value<=" everything will be okey. Sincerely Yours, Wadim Smirnov System architect Positive Technologies CJSC, Russia ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date operations when date is a TEXT
Hey guys. Probably unwisely, I store dates in the following format: "year month day hour minute" For example: "11 1 4 16 22" I wish to find all rows in a table which are more than 8 days old. Is there a way of doing this in SQLite or should I just do it in my own code? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] insert into DB fail on NAND flash
Hi , I am working on ARM9 based s3c2440 embedded platform on linux OS. The database I use is SQLITE over QT application. The "insert into" query is failing once I try to insert values into a database. The database resides on a NAND flash with yaffs2 formated filesystem. The following is the query which I execute: QString stat = QString("insert into user20 values('%0', '%1','%2' )").arg(id).arg(name).arg(address); k = query.exec(stat); if(k == false) qDebug("K is false for record ,%s \n",id.toAscii().data()); After continuous read/write the query is failing. Once I restart my target (s3c2440), the query is again executed fine. Before the query is executed, I delete the database file, if it exist, so that ideally the record insertion can happen. What can be the problem of query failure ? Is the continuous read/write in NAND flash corrupting the memory. ? Please help. With RegardsGIGIN ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users