Icon View Thread

The following is the text of the current message along with any replies.
Messages 21 to 30 of 41 total
Thread LIMIT keyword for SELECT
Tue, Feb 27 2007 10:58 AMPermanent 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.  Wink

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 AMPermanent 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.  Wink

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.  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 2:36 PMPermanent 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!  Wink

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 PMPermanent Link

David Loving
Tim,

I swear, this is the last one.  Wink 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.  Wink

David L.
Wed, Feb 28 2007 7:44 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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 PagePage 3 of 5Next Page »
Jump to Page:  1 2 3 4 5
Image