Re: [sqlite] LAST() function not supported

[Nicolas Williams]

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

[Ginn Chen]

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

[Simon Slavin]

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

[Jay A. Kreibich]

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

[Puneet Kishor]

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

[Rael Bauer]

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

[Simon Slavin]

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

[Rael Bauer]

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

[Marian Cascaval]

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

[Puneet Kishor]

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

[Puneet Kishor]

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

[Marian Cascaval]

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

[Marian Cascaval]

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

[Nicolas Williams]

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

[Puneet Kishor]

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

[Marian Cascaval]

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