Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Enhancement Requests and Suggestions » View Thread |
Messages 11 to 20 of 41 total |
LIMIT keyword for SELECT |
Sat, Feb 24 2007 12:29 AM | Permanent 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. MySQL 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 AM | Permanent 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 AM | Permanent Link |
David Loving | Hey Tim,
I didn't mean to have everyone pounce on you! 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. 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 PM | Permanent Link |
"Jan Derk" | David Loving wrote:
> Please take note of the replies, although some may seem extreme. 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 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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. 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Sam,
<< I'm glad no one told MySQL that. MySQL 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< I didn't mean to have everyone pounce on you! >> 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent 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 Page | Page 2 of 5 | Next Page » |
Jump to Page: 1 2 3 4 5 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |