Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 41 total
Thread LIMIT keyword for SELECT
Wed, Feb 21 2007 4:22 PMPermanent Link

David Loving
Hey Tim,

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.  Combined with the proper index and you have something similar to TOP in MS SQL...

David L.
Wed, Feb 21 2007 10:40 PMPermanent Link

"Donat Hebert \(Worldsoft\)"
Select *
from tablename Top n

is supported now.  fyi.


"David Loving" <david.loving@ihs.com> wrote in message
news:92F8324D-4661-4BC3-BD1C-3B7878762561@news.elevatesoft.com...
> Hey Tim,
>
> 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.  Combined with the proper index
> and you have something similar to TOP in MS SQL...
>
> David L.
>

Thu, Feb 22 2007 8:59 AMPermanent Link

David Loving
Hmm... didn't find it in the docs for ElevateDB... I'll give it a try.

"Donat Hebert \(Worldsoft\)" <donat.hebert@worldsoft.ca> wrote:

Select *
from tablename Top n

is supported now.  fyi.


"David Loving" <david.loving@ihs.com> wrote in message
news:92F8324D-4661-4BC3-BD1C-3B7878762561@news.elevatesoft.com...
> Hey Tim,
>
> 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.  Combined with the proper index
> and you have something similar to TOP in MS SQL...
>
> David L.
>

Thu, Feb 22 2007 9:34 AMPermanent Link

David Loving
I just found this in "statement changes" for ElevateDB...

"The TOP clause is no longer supported. ElevateDB will introduce standard WINDOW clause support for selecting ranges of rows in a later
release."

David L.


"Donat Hebert \(Worldsoft\)" <donat.hebert@worldsoft.ca> wrote:

Select *
from tablename Top n

is supported now.  fyi.


"David Loving" <david.loving@ihs.com> wrote in message
news:92F8324D-4661-4BC3-BD1C-3B7878762561@news.elevatesoft.com...
> Hey Tim,
>
> 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.  Combined with the proper index
> and you have something similar to TOP in MS SQL...
>
> David L.
>

Thu, Feb 22 2007 10:18 AMPermanent Link

"Donat Hebert \(Worldsoft\)"
We'll have to be clearer on which versions Smile Got it ...

Thu, Feb 22 2007 11:13 AMPermanent Link

David Loving
Sorry about that!  I assumed "Enhancement Suggestions" was for the most current release of product and not a previous version.  It seemed a
very important part of SELECT was missing from ElevateDB... and looks like it is!  Wink I just had to read the correct online doc to find mention of
it.

I also assumed Tim would have to offer the enhancement before he could release an ADO.Net version but wanted to mention it to him... just in
case.  Wink

David L.


"Donat Hebert \(Worldsoft\)" <donat.hebert@worldsoft.ca> wrote:

We'll have to be clearer on which versions Smile Got it ...

Fri, Feb 23 2007 7:21 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

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 Young
Elevate Software
www.elevatesoft.com

Fri, Feb 23 2007 7:22 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

David,

<< I also assumed Tim would have to offer the enhancement before he could
release an ADO.Net version but wanted to mention it to him... just in case.
Wink>>

Just curious - why would you assume that ?  An ADO.NET data provider doesn't
have anything to do with what SQL is or isn't supported.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Feb 23 2007 5:28 PMPermanent Link

David Loving
Tim,

It was once (and may still be) a very common demonstration at Microsoft events to use the TOP feature of MS SQL to provide a small page of
data through an ADO.NET recordset to fill a grid on a web page then request the next page with a new starting record id as the customer requests
the next page of data in a browser and so on.

David L.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

David,

<< I also assumed Tim would have to offer the enhancement before he could
release an ADO.Net version but wanted to mention it to him... just in case.
Wink>>

Just curious - why would you assume that ?  An ADO.NET data provider doesn't
have anything to do with what SQL is or isn't supported.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Feb 23 2007 5:49 PMPermanent Link

David Loving
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.

We have a choice when working with large datasets from a remote client... we can make many singleton calls (too slow on the round trip) or wait
for a large download and try to hold the entire result set in the client memory (probably have to page most to disk in low memory
environments).  I'd like to be in control of requesting a starting record and a block of records at a time, do my own local cache or snapshot of the
data and then apply changes to those records before moving on to the next block.  I bet web based solution programmers will want something
similar.

It's no different than the current problems in the XML world... SAX vs. DOM vs. a newly proposed way to work with smaller chunks of the file...


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> 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 Young
Elevate Software
www.elevatesoft.com

Page 1 of 5Next Page
Jump to Page:  1 2 3 4 5
Image