Icon View Thread

The following is the text of the current message along with any replies.
Messages 11 to 20 of 41 total
Thread LIMIT keyword for SELECT
Sat, Feb 24 2007 12:29 AMPermanent Link

Sam Davis
Tim Young [Elevate Software] wrote:

> David,
>
> << I've always wanted a LIMIT = <number of records> keyword added to SELECT
> that would allow me to limit the number of records in the result set.
> Combined with the proper WHERE filter, it would be easy to request pages of
> data instead of all data at once.  A poor man's way of speeding up results
> or limiting resources consumed.  >>
>
> Yes, except that for all except the most basic cases (no ORDER BY), TOP or
> LIMIT has to process the entire result set before it can grab the TOP or the
> LIMITed rows that you need.  Therefore, you're not saving any time at all.
> In most cases it is better to simply cache the result set as a temporary
> table and perform the TOP and LIMIT operations navigationally on the result
> set.
>

Tim,
    I'm glad no one told MySQL that. SmileMySQL can use "LIMIT
[offset,]n" to get the top most rows even with an Order By clause in
just ms. They use the index and it's extremely fast on million row tables.

Sam
Sat, Feb 24 2007 7:36 AMPermanent Link

"Jan Derk"
Tim Young [Elevate Software] wrote:

> Yes, except that for all except the most basic cases (no ORDER BY),
> TOP or LIMIT has to process the entire result set before it can grab
> the TOP or the LIMITed rows that you need.  Therefore, you're not
> saving any time at all. In most cases it is better to simply cache
> the result set as a temporary table and perform the TOP and LIMIT
> operations navigationally on the result set.

Is this the real Tim Young posting?

Using LIMIT is one of the first things millions learns when doing
MYSQL/PHP. All those familiar with it will quickly dismiss
DBISAM/ElevateDB, because it does not support it.

Indexes is what one uses to make sorted tables fast. LIMIT is what is
required to prevent pulling huge amounts of data through the cable from
the server to the client. The combination of both is required to get
good performance when scrolling through large result sets.

Imagine Google using ElevateDB:
"1,233,545,344 results found. Please wait while we upload them to your
computer..."

The absence of a LIMIT statement (there is a TOP but it comes without
the OFFSET) is a huge disadvantage in DBISAM. Taking away TOP from
EvelevateDB is, let's put it politely, not the smartest move if you
want to grow beyond a desktop database. Sorry to sound a bit harsh.

Jan Derk
Mon, Feb 26 2007 11:30 AMPermanent Link

David Loving
Hey Tim,

I didn't mean to have everyone pounce on you!  Wink You know I prefer your products over MS or MySQL.  DBISAM has helped us lead our
portion of the industry.

I understand your reply in the case where the engine is bound in the application.  I am currently investigating the C/S version of ElevateDB
for the use case I described.  I am pleased to read you will support the WINDOW functionality of SQL 2003 in a future release.  I hope what
you say in your reply won't be an issue in the C/S version when it is implemented.  That is the Enhancement Suggestion I was after... it just
took me some time to find mention of it in your online documenation.

Please take note of the replies, although some may seem extreme.  Wink MySQL LIMIT and MS TOP are both valuable features in their
respective products for those of us needing to transport a result set somewhere between one and all records across the net.

David L.
Mon, Feb 26 2007 12:43 PMPermanent Link

"Jan Derk"
David Loving wrote:

> Please take note of the replies, although some may seem extreme.  Wink

You are probably referring to my post. It does indeed read like a
grumpy old man's post. My apologies to Tim. In real life there are
times when I am actually nice to other humans Wink

Maybe it is because it is somewhat of a pet peeve. I requested support
for the LIMIT keyword six years ago in 2001:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=searchopenmsg&grou
p=5&msg=13098#msg13098

Let's say I hope that a way to reduce result set traffic will make it
into DBISAM soon.

Jan Derk
Tue, Feb 27 2007 6:05 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

David,

<< When working on a very, very, very large datasets of Gas and Oil data,
the time saved is not in processing the SQL on the server but on
transferring this smaller result set of data back to the client application
from each request.  Some of our clients want to live and work on the coast
but have their data servers physically many miles further inland, away from
hurricanes.  Wink They are trying citrix and remote desktop solutions that
are not working to our mutual satisfaction. >>

Yes, but that isn't what TOP does.  TOP is strictly a server-side construct.
What you're referring to is the design of the fetching and how/where the
result set rows are stored as they are fetched.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Feb 27 2007 6:07 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Sam,

<< I'm glad no one told MySQL that. SmileMySQL can use "LIMIT [offset,]n" to
get the top most rows even with an Order By clause in just ms. They use the
index and it's extremely fast on million row tables. >>

Provided that there is an index.  That's not always a guarantee, so the
fall-back is that the rows need to be selected, sorted, and then the limit
be retrieved.  There's no way around it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Feb 27 2007 6:12 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Jan,

<< Is this the real Tim Young posting?  >>

Yes, why would you think it wasn't ?

<< Using LIMIT is one of the first things millions learns when doing
MYSQL/PHP. All those familiar with it will quickly dismiss DBISAM/ElevateDB,
because it does not support it. >>

Your opinion, not fact.

<< Indexes is what one uses to make sorted tables fast. LIMIT is what is
required to prevent pulling huge amounts of data through the cable from the
server to the client. The combination of both is required to get good
performance
when scrolling through large result sets. >>

Again, as I told David.  This is not correct.  LIMIT and TOP do nothing to
affect how many rows come across the wire when data is fetched.  The two
operations are completely separate and have nothing to do with one another.

<< Imagine Google using ElevateDB:
"1,233,545,344 results found. Please wait while we upload them to your
computer..." >>

That's not what ElevateDB does.  Did you even read what I said ?

"In most cases it is better to simply cache the result set as a temporary
table and perform the TOP and LIMIT operations navigationally on the result
set."

This means using CREATE TABLE AS to create a temporary table from a query
expression, and then fetching on that temporary table as required to do
pagination.   This has nothing, I repeat, nothing to do with how many rows
are fetched to the client from the database server.

<< The absence of a LIMIT statement (there is a TOP but it comes without the
OFFSET) is a huge disadvantage in DBISAM. Taking away TOP from EvelevateDB
is, let's put it politely, not the smartest move if you want to grow beyond
a desktop database. Sorry to sound a bit harsh. >>

You're simply way off the mark here, so I'll leave it at that.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Feb 27 2007 6:15 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

David,

<< I didn't mean to have everyone pounce on you!  Wink>>

Well, so far no one seems to understand how the whole thing works.

<< You know I prefer your products over MS or MySQL.  DBISAM has helped us
lead our portion of the industry. >>

Sure, and we have always appreciated your loyalty in return.

<< I understand your reply in the case where the engine is bound in the
application. >>

It applies to all situations, local or C/S.  Please see my last reply.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Feb 27 2007 6:17 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Jan,

<< Maybe it is because it is somewhat of a pet peeve. I requested support
for the LIMIT keyword six years ago in 2001: >>

And the reason that it wasn't done was the same that I stated at the
beginning of this thread.  The perceived benefits are either a) completely
wrong, or b) at best, only there in very specific situations where there is
an index available for the ORDER BY that can speed up the operation.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Feb 27 2007 8:03 AMPermanent Link

"Jan Derk"
Tim Young [Elevate Software] wrote:
> << Using LIMIT is one of the first things millions learns when doing
> MYSQL/PHP. All those familiar with it will quickly dismiss
> DBISAM/ElevateDB, because it does not support it. >>

> Your opinion, not fact.

You are right, so I grabbed my copy of what is probably the most
popular book on PHP/MySQL development: "PHP and MySQL Web Development".
It shows up at the #1 spot when you search Amazon for PHP and MYSQL.

The first chapter about SELECT is called "Retrieving data from the
Database". It mentions LIMIT in the very first paragraph in the very
first SELECT example as part of what it calls the "basic form of the
SELECT statement".


> << Imagine Google using ElevateDB:
> "1,233,545,344 results found. Please wait while we upload them to
> your computer..." >>
>
> That's not what ElevateDB does.  Did you even read what I said ?
> This means using CREATE TABLE AS to create a temporary table from a
> query expression, and then fetching on that temporary table as
> required to do pagination.   This has nothing, I repeat, nothing to
> do with how many rows are fetched to the client from the database
> server.

Building a temporary table requires parsing through the complete result
set which is not an option to keep things fast on very large result
sets. You mention in your other posts that LIMIT only works efficiently
when proper indexes are available. The same thing can said for any SQL
statement that uses indexes. The availability of LIMIT would provide
me, the developer, with the power to create snappy views of large
result sets provided that I set the proper indexes. I am grateful for
TOP which solves part of the problem, but I could really do with an
OFFSET option.

Another argument for OFFSET is that it provides cleaner code, because a
temporary table is not needed for the pagination of result sets.

Jan Derk
« Previous PagePage 2 of 5Next Page »
Jump to Page:  1 2 3 4 5
Image