Thus far, I have only seen only two suggestions for getting the number of
rows returned from a database query.
1) Count the number of rows in the results set.
2) Send a select count to the database.
The complexity of the first seems obvious:
You pay some price A1 for the database query and then theta(n) to count the
result set
= A1 + theta(n)
This way certainly seems to have the largest memory impact; however, it has
the property that if you are going to use the row count as a conditional to
determine if the row set is too large to render (ex. the row set will
produce too much HTML for a browser when rendered) then you do not have to
require the database if the row count meets the criteria.
The second way seems as though it would depend on both the database and the
query.
You pay some price A2 for the database query and then O(1) to get the count
from the result set
= A2 + O(1) = A2
What I do not know, is if A2 << A1. Can most databases always return the row
count *MUCH* faster than actually executing the entire query and returning
the values?
If yes, then this might be a good route to take if you only needed the row
count value and not the data. To actually get the data would take another
A2', thus giving you A2 + A2'.
If not, then let us assume that A1 = A2. Then it will take A2 to get the
count, and another A2 to get the data ==> 2(A2) = A1 + A1. Comparing this
with the first , A1 + theta(n), then this method is only beneficial if A2 <
theta(n). How easy is this to determine? - well it unfortunately seems like
it depends heavily on the database implementation.
Thus, I will conclude is that the "best" way to get the row count depends on
exactly what you want to do with the count value, the data, and the database
optimization engine. The first method seems best if your database is slow or
your query is extremely complex and time consuming. The second method seems
like it could run faster if the query is not complex and you have you
database well optimized.
Just my .02
--shawn
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
