Re: [sqlite] LAST() function not supported
On Sat, Jan 29, 2011 at 01:03:05PM -0800, Marian Cascaval wrote: > Here's the info source on LAST() function: > > http://www.w3schools.com/sql/sql_func_last.asp > > > > I needed to retrieve the last row from a table. If you need the "last row from a table" that's trivial to do efficiently in SQLite3: SELECT ... FROM ... ORDER BY rowid DESC LIMIT 1; (You should, but don't have to, replace rowid with whatever the INTEGER PRIMARY KEY column is, if there is one.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL for single user database on NFS and Solaris
I think a single process accessing a single Sqlite database at a time over NFS is supposed to be fine. But it is not working on Solaris. On Solaris, man page of mmap() has EAGAINThe file to be mapped is already locked using advisory or mandatory record locking. See fcntl(2). I found if the file on NFS has been locked with fcntl(), mmap() may fail. It doesn't fail if both l_start and l_end are 0. Thus, I got "Error: disk I/O error" for any operation after setting journal_mode to WAL. See https://bugzilla.mozilla.org/show_bug.cgi?id=629296 The problem is setting journal_mode to WAL is successful, but it cannot be set back. User has to move the db file to another filesystem and set journal_mode to delete. I guess -DSQLITE_SHM_DIRECTORY="/var/tmp" might be a solution, but it is unsupported. Any idea how can we make the behavior better on Solaris? Maybe fallback to another lock method if it is on NFS? BTW: With the same NFS server, sqlite3 works fine on Linux and Mac OS X. Thanks, Ginn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On 30 Jan 2011, at 2:16am, Jay A. Kreibich wrote: > You'll see the slow down anytime you access anything "past" the BLOB. > To avoid that, put the BLOBs at the end of the rows and avoid "SELECT *" > style queries. Avoiding 'SELECT *' unless you actually want * is good advice in any case. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On Sat, Jan 29, 2011 at 03:07:47PM -0800, Rael Bauer scratched on the wall: > It seemed strange that a simple "select * from table" that I was > doing was so slow. The table contained about 20 columns (fields) > and 300 rows. The select took about 1.5 seconds. (using SQLite Expert). > So my questions: > Is this standard behaviour for sql databases? (that I have only > found out now). No, it is specific to the way SQLite stores data on the disk. If you're interested in the specifics, you can read about the SQLite file format and the on-disk encoding and storing of rows. > Is there actually some way to bypass this "problem" (e.g. database setting..)? You'll see the slow down anytime you access anything "past" the BLOB. To avoid that, put the BLOBs at the end of the rows and avoid "SELECT *" style queries. You can also just put them in a different table. > Is it generally advisable to separate out blob fields into their own > table to ensure fast select speeds? Yes. If the BLOB column is not frequently accessed, and is "auxiliary" data to the rest of the row, it is a common practice to break them off into their own "detail" table (i.e. a one-to-one table). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On Saturday, January 29, 2011 at 5:54 PM, Rael Bauer wrote: > The table contains an id field with a unique index and another field with a > non-unique index. > > > > So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command > > (or some graphical equivalent) to duplicate that file. How long does it > > take ? > > > > > > You seemed to miss what I was saying: > If the blob field is positioned in the middle of the columns then even If I > don't include the blob field in the query the select is very slow (1.5 > seconds). > If the blob field is positioned at the end of the columns then if I don't > include the blob field in the query the select is very fast (140 ms). If I do > include the blob field, the select is about 400-500 ms. > > > > > That is a known feature (issue). I am sure it is written up somewhere, but definitely, on this list, Richard Hipp and others have emphasized this many times -- search the mailing list archives. Best design -- keep the blob in a separate, dedicated table, and join to that table only when the blob is to be retrieved. That way, sqlite doesn't have to plow through useless pages to find stuff that would fit in a single page but doesn't because of the intervening blob. -- Puneet Kishor Sent with Sparrow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
The table contains an id field with a unique index and another field with a non-unique index. >So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command >(or some graphical equivalent) to duplicate that file. How long does it take >? You seemed to miss what I was saying: If the blob field is positioned in the middle of the columns then even If I don't include the blob field in the query the select is very slow (1.5 seconds). If the blob field is positioned at the end of the columns then if I don't include the blob field in the query the select is very fast (140 ms). If I do include the blob field, the select is about 400-500 ms. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] slow select from table with data blob
On 29 Jan 2011, at 11:07pm, Rael Bauer wrote: > It seemed strange that a simple "select * from table" that I was doing was so > slow. The table contained about 20 columns (fields) and 300 rows. The select > took about 1.5 seconds. (using SQLite Expert). Do you have an indexes or UNIQUE restrictions on that table ? I'm not just talking about the BLOB field, although an index or UNIQUE on that would be particularly interesting. > The table contained a blob field, with a "fair" amount of data spread over > the rows (max was around 6 MB...). The sqlite file was about 40 MB. So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command (or some graphical equivalent) to duplicate that file. How long does it take ? Please time this carefully the first time you do it because after that the file will be cached and no reading of the file will actually take place. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] slow select from table with data blob
It seemed strange that a simple "select * from table" that I was doing was so slow. The table contained about 20 columns (fields) and 300 rows. The select took about 1.5 seconds. (using SQLite Expert). The table contained a blob field, with a "fair" amount of data spread over the rows (max was around 6 MB...). The sqlite file was about 40 MB. After some testing, I discovered that the problem was caused by the blob field being in the middle of the columns (e.g. column 8). Results were slow even if I didn't include the blob field in the select. If I moved this blob field to the end of the table (i.e. last column), then select was very fast if I didn't include the last field (140ms). If I did include the last field (i.e. the blob field), it was slower (400ms) but still significantly faster that results above, when blob field was in the middle of the table. So my questions: Is this standard behaviour for sql databases? (that I have only found out now). Is there actually some way to bypass this "problem" (e.g. database setting..)? Is it generally advisable to separate out blob fields into their own table to ensure fast select speeds? Thanks Rael Bauer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
Thanks for the professional explanation. I think I begin to see the importance of specificity of database query. Nevertheless, I found another info source on LAST() function: http://www.codesnout.com/SQLSample/SQL-LAST.php They also say "The LAST() function is not supported by certain databases.". It seems to me this function is a kind of wrapper which is valid for tables with a primary key. But I assume a pro would not use it anyway since LAST() it's not a general SQL function. As far as I could figure, this function is not a defined function in the SQL92 standard. Marian Cascaval From: Puneet Kishor To: General Discussion of SQLite Database Sent: Sat, January 29, 2011 11:12:52 PM Subject: Re: [sqlite] LAST() function not supported On Saturday, January 29, 2011 at 3:03 PM, Marian Cascaval wrote: > Here's the info source on LAST() function: > > http://www.w3schools.com/sql/sql_func_last.asp > > > > I needed to retrieve the last row from a table. > And the example (the workaround tip) in the above link solved my issue. > > > > While the "workaround" solved your problem, the workaround *is* the right way to solve the problem. Their original solution is absolutely goofy. From their docs, "The LAST() function returns the last value of the selected column." That sentence makes absolutely no sense. What does "last value of the selected column" mean? We happen to now know that they mean the last row in the result set. But, why not just say that? Their proposed solution is absolutely out of whack -- SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders Note that a SQL result set doesn't have a concept of first or last unless you impose an order on it using the ORDER BY clause. Interestingly, they don't say which databases support this LAST() function. I have not come across, yet, any database that supports a LAST() function the way they say it should. In other words, rest assured, LIMIT 1 is indeed the correct way to restrict the result set to one row. However, if you want the "last" row, you have to tell your database program what you mean by last. You do so by specifying ORDER BY . Then, you can take the last or the first by using LIMIT properly. For example, if the orders were to be ordered by, say, the OrderDate, and you wanted the earliest date, you could do SELECT OrderPrice FROM Orders ORBER BY OrderDate LIMIT 1; If you wanted the latest order, you could do SELECT OrderPrice FROM Orders ORDER BY DESC OrderDate LIMIT 1; Hope this helps. > > > From: Puneet Kishor > To: General Discussion of SQLite Database > Sent: Sat, January 29, 2011 10:47:44 PM > Subject: Re: [sqlite] LAST() function not supported > > > > > On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > > > > Hi! > > > > Is LAST() function going to be supported? > > > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround > > always > > > be > > > > enough? > > > > > > > Where did you get information on this "LAST()" function? > > > > > My concern is if there might be any speed improvement if LAST() function > > were > > > to > > > > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A > function > acts on a column or an expression for every row in the result set. It doesn't > modify the number of rows in a result set. On the other hand, the LIMIT > clause > does nothing to the entries that have been retrieved. Instead, it throttles > the > > size of the result set, that is, it controls the number of rows in the result > set. > > > > > -- > Puneet Kishor > Sent with Sparrow > > > > > ___ 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] LAST() function not supported
On Saturday, January 29, 2011 at 3:06 PM, Marian Cascaval wrote: > As I see it,from the point of view of just retrieving the last row from a > table, > no ORDER BY is necessary thus saving processor time. No, no, no, no. The db doesn't know what you mean by "last". If it happens to give you back what you wanted, good. But, don't get into the habit of depending on that. Get into a good habit -- tell the database exactly what you want. Specify the ORDER BY clause, and be assured of what you want. And, with regards to saving processor time, don't even waste any brain cycles on that. Unless you have a database table with upward of several hundreds of thousands of rows, your processor is not going to sweat or complain. Make sure you index your table on the columns you want to use for retrieval, and let the db do its job. Make sure to always tell the db what you want. Don't be lulled into thinking you are always gonna get the right results just because you happen to get the right results in a few cases. -- Puneet Kishor Sent with Sparrow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
On Saturday, January 29, 2011 at 3:03 PM, Marian Cascaval wrote: > Here's the info source on LAST() function: > > http://www.w3schools.com/sql/sql_func_last.asp > > > > I needed to retrieve the last row from a table. > And the example (the workaround tip) in the above link solved my issue. > > > > While the "workaround" solved your problem, the workaround *is* the right way to solve the problem. Their original solution is absolutely goofy. From their docs, "The LAST() function returns the last value of the selected column." That sentence makes absolutely no sense. What does "last value of the selected column" mean? We happen to now know that they mean the last row in the result set. But, why not just say that? Their proposed solution is absolutely out of whack -- SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders Note that a SQL result set doesn't have a concept of first or last unless you impose an order on it using the ORDER BY clause. Interestingly, they don't say which databases support this LAST() function. I have not come across, yet, any database that supports a LAST() function the way they say it should. In other words, rest assured, LIMIT 1 is indeed the correct way to restrict the result set to one row. However, if you want the "last" row, you have to tell your database program what you mean by last. You do so by specifying ORDER BY . Then, you can take the last or the first by using LIMIT properly. For example, if the orders were to be ordered by, say, the OrderDate, and you wanted the earliest date, you could do SELECT OrderPrice FROM Orders ORBER BY OrderDate LIMIT 1; If you wanted the latest order, you could do SELECT OrderPrice FROM Orders ORDER BY DESC OrderDate LIMIT 1; Hope this helps. > > > From: Puneet Kishor > To: General Discussion of SQLite Database > Sent: Sat, January 29, 2011 10:47:44 PM > Subject: Re: [sqlite] LAST() function not supported > > > > > On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > > > > Hi! > > > > Is LAST() function going to be supported? > > > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround > > always > > be > > > > enough? > > > > > > > Where did you get information on this "LAST()" function? > > > > > My concern is if there might be any speed improvement if LAST() function > > were > > to > > > > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A > function > acts on a column or an expression for every row in the result set. It doesn't > modify the number of rows in a result set. On the other hand, the LIMIT > clause > does nothing to the entries that have been retrieved. Instead, it throttles > the > size of the result set, that is, it controls the number of rows in the result > set. > > > > > -- > Puneet Kishor > Sent with Sparrow > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
As I see it,from the point of view of just retrieving the last row from a table, no ORDER BY is necessary thus saving processor time. From: Nicolas Williams To: General Discussion of SQLite Database Sent: Sat, January 29, 2011 10:51:30 PM Subject: Re: [sqlite] LAST() function not supported On Sat, Jan 29, 2011 at 12:38:37PM -0800, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always >be > > enough? > > My concern is if there might be any speed improvement if LAST() function were >to > > be implemented, comparing to the workaround. Use EXPLAIN QUERY PLAN... If there's enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? For that matter, if there aren't enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? What optimization could such a function implement that the query optimizer couldn't? Syntactically speaking, there's no additional information in "last()" -- it's just syntactic sugar. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
Here's the info source on LAST() function: http://www.w3schools.com/sql/sql_func_last.asp I needed to retrieve the last row from a table. And the example (the workaround tip) in the above link solved my issue. From: Puneet Kishor To: General Discussion of SQLite Database Sent: Sat, January 29, 2011 10:47:44 PM Subject: Re: [sqlite] LAST() function not supported On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always >be > > enough? > > > > Where did you get information on this "LAST()" function? > My concern is if there might be any speed improvement if LAST() function were >to > > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A function acts on a column or an expression for every row in the result set. It doesn't modify the number of rows in a result set. On the other hand, the LIMIT clause does nothing to the entries that have been retrieved. Instead, it throttles the size of the result set, that is, it controls the number of rows in the result set. -- Puneet Kishor Sent with Sparrow ___ 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] LAST() function not supported
On Sat, Jan 29, 2011 at 12:38:37PM -0800, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always > be > enough? > > My concern is if there might be any speed improvement if LAST() function were > to > be implemented, comparing to the workaround. Use EXPLAIN QUERY PLAN... If there's enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? For that matter, if there aren't enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? What optimization could such a function implement that the query optimizer couldn't? Syntactically speaking, there's no additional information in "last()" -- it's just syntactic sugar. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always > be > enough? > > > > Where did you get information on this "LAST()" function? > My concern is if there might be any speed improvement if LAST() function were > to > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A function acts on a column or an expression for every row in the result set. It doesn't modify the number of rows in a result set. On the other hand, the LIMIT clause does nothing to the entries that have been retrieved. Instead, it throttles the size of the result set, that is, it controls the number of rows in the result set. -- Puneet Kishor Sent with Sparrow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LAST() function not supported
Hi! Is LAST() function going to be supported? Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always be enough? My concern is if there might be any speed improvement if LAST() function were to be implemented, comparing to the workaround. Marian Cascaval ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
