Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Enhancement Requests and Suggestions » View Thread |
Messages 21 to 30 of 41 total |
LIMIT keyword for SELECT |
Tue, Feb 27 2007 10:58 AM | Permanent Link |
David Loving | Tim,
I agree... we do not understand how this works in DBISAM / ElevateDB. But, like Jan... I can quote from a book on the Microsoft side. The book title is "Building Web Solutions with ASP.NET and ADO.NET" from Microsoft Press 2002. Chapter 2 - "Pageable Data Grids." Subtitle "Custom Pagination." Subtitle "Obtaining the Page Content." "With custom paging, the amount of memory allocated is limited to the number of items that fit in a single grid page. The rub is, how can you get all and only the records that fit in a single grid page? Once again, the way you handle this issue is strictly application specific. The SQL language provides no support for pagination. {true back in 2002 but you know WINDOW came in SQL 2003} The only way to get records from a SQL based data management system is by using the SELECT statement. The only way to restrict the set of rows returned is by using the WHERE clause. Therefore, you must figure out a way to retrieve the contents of a particular page based on some sort of field condition. Because no official SQL based solution exists, any assumption you can make in your own project given the structure of your own database is valid. In the remainder of this section, I'll examine a SQL case scenario. Consider the following SQL statement: SELECT TOP n Fields FROM Table WHERE Key > page_related_info" "Caution Very few databases support the TOP clause in the SELECT statement. The TOP clause was introduced with SQL Server version 7 and has no counterpart in SQL Server 6.5 and Oracle. Informix SQL does provide a similar behavior through the FIRST clause. Although less flexible, the SET ROWCOUNT statement can be used as a rougher counterpart for TOP when this clause is not supported." The rest goes on to show how to set up the SqlDataReader to use this SELECT TOP statement to retrieve pages of records. Jan and I may not understand how TOP or LIMIT work under the covers but we do have documentation of how Microsoft and MySQL suggest we use these features to limit the number of records retrieved to fill a data grid. Our suggestion is based on some knowledge, some experience, some documented facts and should not be dismissed as simply our own personal opinion or wild ideas. This is the literature your new, wider customer base (Microsoft developers) have read and the demos they have seen for the last five years. Let me rephrase my Enhancement Suggestion for the client / server engine then... 1. I would like to connect to an ElevateDB server engine on a remote system. 2. I would like to send a SQL command requesting a result set limited to an offset and number of records returned. 3. I would like the ElevateDB server engine to select the best INDEX to use to build the result set. That's its job. 4. I would like the ElevateDB server to cache the result set for a period of time to satisfy any additional requests for a different offset and number of records. 5. I would like a variable we can set for the amount of time it will hold the result set between requests so we can tune it. 6. If we take too long to request the next page (or the engine is running out of resources because of the number of users served), the penalty is the result set is flushed and we have to wait for the result set to be built and cached again on the server before we get the next page... same as we have to do now when using a creative WHERE clause to limit our result set (because of no available LIMIT keyword). This splits the work between the client and server resources. We all know we make our choice of database engine based entirely on economics. No one is going to jump ship to a $5k per processor solution per customer to solve this problem. We will be creative with our WHERE clause to get the functionality we need for now... but we ask kindly for an Enhancement that will make our life as easy as those who use the more expensive engines. Thank You for listening (reading) as this is what makes Elevate different from the others... we communicate directly with the man. David L. "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote: David, Well, so far no one seems to understand how the whole thing works. 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 11:22 AM | Permanent Link |
David Loving | Jan,
I think we both want the same feature. I'm just glad Tim will listen (read) and have a reasonable debate on the subject. Try that with Microsoft or MySQL. I get the sense his current implementation of the server engine will not allow what we request. We will have to continue with creative WHERE clauses to reduce the result set and hope for the future. David L. "Jan Derk" <none@none.none> wrote: 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 2:36 PM | Permanent Link |
David Loving | Tim,
You can also read about what we are requesting at the following link... http://msdn2.microsoft.com/en-us/library/aa479006.aspx Default paging is simpler to implement because you don't have to do anything fancy when a user navigates from one page of data to the next. That is, you just bind the results of a SQL query to the DataGrid and let the DataGrid worry about what records to display. With custom paging, you have to use either tricky SQL statements or complicated stored procedures to pick out the precise set of records you want to display for a particular page. Custom paging offers better performance over default paging because only those records that need to be displayed for a particular page of data are accessed. With default paging, every time a user views a different page of data, all records are retrieved. Additionally, default paging requires that you bind either a DataTable or DataSet object to the DataGrid; that is, you can't use a DataReader. This is because the DataGrid needs to be able to determine how many records are in the DataSource to be able to determine how many total pages of data exist. Once you release ADO.NET support for ElevateDB, you will have more developers wanting to do exactly what is described in Jan's book, my book and this MSDN technical article. To quote the X-Files... We are not alone! I want to repeat one of the lines... "Custom paging offers better performance over default paging because only those records that need to be displayed for a particular page of data are accessed." That's what we are asking for... so there! ;-p David L. |
Tue, Feb 27 2007 3:01 PM | Permanent Link |
David Loving | Tim,
I swear, this is the last one. We could go on and on, beating you over the head with technical articles and books. http://msdn2.microsoft.com/en-us/library/ms972960.aspx Creating a Pager Control for ASP.NET "To make a really generic SQL pager component, you should generalize the data access layer and build a sort of factory class that creates connections, commands, and adapters using the appropriate data provider. On the other hand, bear in mind that setting up a paging engine for various SQL sources is worse than your worst nightmare. The approach presented here works only for SQL Server 7.0 and newer. The TOP clause is the discriminatory feature. Using server cursors and temporary tables, it can be adapted to a larger range of DBMS systems. But that would make your code significantly more complex." I'd like ElevateDB to make a paging engine that will wake up the rest of the world from their coding nightmare. David L. |
Wed, Feb 28 2007 7:44 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jan,
<< 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". >> And what's your point ? The fact that LIMIT is mentioned in a popular book doesn't make your understanding of the situation correct. The fact is that you are incorrect in what you assume to be the function of LIMIT or TOP. << 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. >> Parsing ? There is no parsing going on. Navigation of a result set works just like any cursor navigation - first, last, next, prior, goto, etc. And it is extremely fast, much faster than TOP or LIMIT because the result set need not be regenerated or reprocessed in any way. << 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. >> Yes, but we're not talking about a general optimization issue. We're talking about simply stinking in terms of performance and being virtually unusable on large tables for pagination purposes if there isn't an index available to satisfy the ORDER BY clause and the query is only on one table. IOW, the only way to do really fast pagination with TOP or LIMIT is on a query result set that is live/sensitive and has an index available for the ORDER BY. You'd be better off just opening the table and setting the active index or simply running a: SELECT * FROM MyTable ORDER BY MyColumn query that generates a live/sensitive result set and setting the RecNo property of the TEDBQuery component to the desired starting row. << 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. >> Only in the very limited circumstances described in my last paragraph. << Another argument for OFFSET is that it provides cleaner code, because a temporary table is not needed for the pagination of result sets. >> Only if the query result set is live/sensitive. Temporary tables are created for canned/static result sets anyways. All you're doing with CREATE AS is simply directing the results to a specific temporary table that you have named. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Feb 28 2007 8:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< I agree... we do not understand how this works in DBISAM / ElevateDB. But, like Jan... I can quote from a book on the Microsoft side. >> Please, no more quotes. You're not convincing me of anything, and frankly, it's a little insulting. Do you guys think that I haven't thought this through or that I'm not aware of what's involved here ? << Jan and I may not understand how TOP or LIMIT work under the covers but we do have documentation of how Microsoft and MySQL suggest we use these features to limit the number of records retrieved to fill a data grid. Our suggestion is based on some knowledge, some experience, some documented facts and should not be dismissed as simply our own personal opinion or wild ideas. This is the literature your new, wider customer base (Microsoft developers) have read and the demos they have seen for the last five years. >> Again, what point are you trying to make ? That what I'm saying is not correct ? Just because MS or MySQL says or does something does not make it either correct or optimal for EDB. My experience with TOP in DBISAM is that it is vastly overrated and most people are extremely disappointed when they realize what the limitations of it are, and LIMIT would simply be the same situation. I am interested in providing our customers with the optimal solution to their problems *given the existing architecture of our products*. << 1. I would like to connect to an ElevateDB server engine on a remote system. 2. I would like to send a SQL command requesting a result set limited to an offset and number of records returned. 3. I would like the ElevateDB server engine to select the best INDEX to use to build the result set. That's its job. 4. I would like the ElevateDB server to cache the result set for a period of time to satisfy any additional requests for a different offset and number of records. 5. I would like a variable we can set for the amount of time it will hold the result set between requests so we can tune it. 6. If we take too long to request the next page (or the engine is running out of resources because of the number of users served), the penalty is the result set is flushed and we have to wait for the result set to be built and cached again on the server before we get the next page... same as we have to do now when using a creative WHERE clause to limit our result set (because of no available LIMIT keyword). >> EDB already requires that connections be stateful if you want a) decent performance and b) you want to maintain the session state for whatever purposes. If you're writing a web application, then what you need is an application server that can cache the sessions and reconcile the stateless aspect of the web connections coming from the web server with the database server. That would be the proper and most efficient way to handle such a situation. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Feb 28 2007 8:46 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< You can also read about what we are requesting at the following link... >> I understand the ADO.NET data provider architecture and I understand that it only provides for forward-only and static, client-side cursors. None of this changes the fact that you'll need to maintain the session state with ElevateDB (or DBISAM, for that matter) if you want decent performance with a web application that is accessing an ElevateDB Server. IOW, the issue is stateful vs. non-stateful with respect to web requests and database access. Paging is the least of the issues, although ADO.NET certainly does make it harder because it doesn't support server-side, scrollable cursors. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Feb 28 2007 9:52 AM | Permanent Link |
David Loving | Tim,
Understood. The solution I was investigating with ElevateDB C/S has been nulled. How soon will you support two way database replication? David L. |
Wed, Feb 28 2007 11:14 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< Understood. The solution I was investigating with ElevateDB C/S has been nulled. >> I'm not sure what you mean by "nulled" ? Are you saying that it won't do what you want ? As long as you're not creating a web application (more specifically, an ASP.NET web application), there should be no problem with EDB C/S in terms of controlling pagination from the client and how many rows are fetched at one time from the EDB Server. << How soon will you support two way database replication? >> It really depends upon how long it takes to get the rest of the initial rollout of EDB completed. It will probably be late summer at this stage of the game. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Feb 28 2007 12:54 PM | Permanent Link |
David Loving | Tim,
You probably don't remember conversations with my boss (Tom) regarding DBISAM 3.x and Petra in the past. If you did, you would remember the lengths he has gone to maintain his own cache of data locally and why. If I understand you correctly, ElevateDB does not give us the control we need when specifying the data we want from a table of millions of records. We'll be interested in seeing your implementation of database replication as a briefcase model is one option we are investigating for dealing with a shared project of multiple tables from a very remote server. David L. "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote: David, << Understood. The solution I was investigating with ElevateDB C/S has been nulled. >> I'm not sure what you mean by "nulled" ? Are you saying that it won't do what you want ? As long as you're not creating a web application (more specifically, an ASP.NET web application), there should be no problem with EDB C/S in terms of controlling pagination from the client and how many rows are fetched at one time from the EDB Server. << How soon will you support two way database replication? >> It really depends upon how long it takes to get the rest of the initial rollout of EDB completed. It will probably be late summer at this stage of the game. -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 3 of 5 | Next Page » |
Jump to Page: 1 2 3 4 5 |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |