Hi:
Some days ago I suggested a solution to fix the problem about how to
trigger the <esql:more-results> tag. The problem in question was related
to the use of the LIMIT clausule of SQL.
The proposed solution works fine in case you really have a big table and
need to check if there are (as the name of the tag) more results inside
the table.
But this solution have a side efect: a time penalization (downgrade of
database performance) in case you know the exactly the number of rows you
need to retrieve and does not care if there are more or not. Why? I will
try to explain it below:
<example A>
DESCRIPTION: You need to build pages containing 10 registers from a table
that currently have 10,000 registers and continue growing.
SOLUTION: Use,
<esql:max-rows>10</esql:max-rows>
COMMENTS: Currently the ESQL code will ask for 10+1 register in order to
know if there are more registers. He will write a query adding "LIMIT 11"
You will get your 10 registers and the 11th register is used to shot the
<esql:more-results> tag. You have no access to the 11th register. It is
used for internal purpose.
</example A>
<example B>
DESCRIPTION: You need to build a page and you know that there are EXACTLY
10 registers from a table that currently have 10,000 registers and
continue growing.
SOLUTION: Same as example A. Use,
<esql:max-rows>10</esql:max-rows>
COMMENTS: Same as A.
</example B>
WHAT IS WRONG?
In example A all is OK.
In example B we are not using the power of the Database Manager.
WHY?
The LIMIT clause was designed to tell the database engine:
"Let find just X rows", then the database engine when it got the X rows
stop searching and return the X rows. It improves the response time, since
does not to continue searching!
Now think in a 10 million row table and YOU KNOW you need only 5 rows!
PROPOSED SOLUTION:
Change the code to something like
if (exist <esql:more-results>) /* we need fine if there are more rows */
LIMIT X+1
else
LIMIT X /* Since the user does not write a <esql:more-results> tag, he
does not care if there are more results or not. Lets find as many rows
as he asked */
Also we need to check the form that the <esql:more-results> is triggered.
I can write the Java code in detail and post it to review if you agree
with this proposal.
Best Regards,
Antonio Gallardo
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]
