I am going to throw a monkey wrench into the equation here....select
count(*) from xxx where "...." on a table that has over 15,000,000 is taking
10-15 minutes. These queries are heave duty, using Intermedia search with
oracle, near operators, etc. Unfortunately the way that Oracle is dealing
with the count(*) is to do a full table scan :(
We are searching for a better way to store the data.
If I take your advice of the start/end range thing, then I am going to be be
waiting a long long time. Any thoughts on this?
Matt
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Filip Hanik
Sent: Tuesday, June 19, 2001 8:19 PM
To: [EMAIL PROTECTED]
Subject: RE: [JBoss-user] JDBC / ResultSet Question
>You are correct...we are not pulling out all 10,000+ rows, we are
>retrieving
>say 1-100 for the first page they view and then fetch the next 100 when the
>user clicks on a next link.
yes that is good way of doing it, just don't hold on to a connection while
doing this.
instead write your query so that it can execute like this
runMyQuery() --returns 100 results and the total count
runMyQuery(int startrange, int endrange) --returns the results between the
range
now the only thing that remains is to optimize the query to be able to
quickly run a filter for a search range.
if you hold on to the connection on the server side you are screwing up the
way the system will work between client-server calls it screws it up
Filip
~
Namaste - I bow to the divine in you
~
Filip Hanik
Software Architect
[EMAIL PROTECTED]
www.filip.net
>-----Original Message-----
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED]]On Behalf Of Matt Veitas
>Sent: Tuesday, June 19, 2001 5:03 PM
>To: [EMAIL PROTECTED]
>Subject: RE: [JBoss-user] JDBC / ResultSet Question
>
>
Is this the most efficient way of doing this
>type of query/view?
>
>Matt
>
>-----Original Message-----
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED]]On Behalf Of Filip Hanik
>Sent: Tuesday, June 19, 2001 7:51 PM
>To: [EMAIL PROTECTED]
>Subject: RE: [JBoss-user] JDBC / ResultSet Question
>
>
>>Currently in our project we have well over 15,000,000 data
>>items in the database and a user will do a search for certain criteria.
>Some
>>of the queries are returning 10,000+ rows.
>
>are you using all these rows (10,000+).
>if not. why even pull them out of the database. if these results are
>intended for a user. only retrieve as many as the user wants to see.
>
>Filip
>
>~
>Namaste - I bow to the divine in you
>~
>Filip Hanik
>Software Architect
>[EMAIL PROTECTED]
>www.filip.net
>
>>-----Original Message-----
>>From: [EMAIL PROTECTED]
>>[mailto:[EMAIL PROTECTED]]On Behalf Of Matt Veitas
>>Sent: Tuesday, June 19, 2001 4:40 PM
>>To: [EMAIL PROTECTED]
>>Subject: RE: [JBoss-user] JDBC / ResultSet Question
>>
>>
>>CachedRowSet is a great way to implement sending a "resultset" over the
>>wire, but what about in the case where you may have thousands of
>records to
>>list.
>>
>>I am not using JBoss as of now, just using plain servlets/jsp on
>>my project,
>>but I am always looking for a better way to code the idea of
>paging through
>>a ResultSet. Currently in our project we have well over 15,000,000 data
>>items in the database and a user will do a search for certain
>>criteria. Some
>>of the queries are returning 10,000+ rows. Right now we have a
>>piece of code
>>that maintains the connection to the DB, along with the ResultSet
>>so that we
>>can page through the data set. The jsp page will access it and
>get the data
>>straight from the ResultSet.
>>
>>We will be starting to convert our web app over to EJB in the next few
>>months, but was how others are handling cases such as this with
>EJB...I was
>>thinking about a stateful session bean to take care of this, but the only
>>question is how to maintain the connection, statement, and ResultSet when
>>ejbPassivate() needs to be called? Any other suggestions on how to do this
>>or experiences with such a thing would be great.
>>
>>Matt
>>
>>--
>>[EMAIL PROTECTED]
>>703-304-6988
>>____________________________________
>>Emerging Paradigms Corporation
>>"innovative ideas for smarter solutions"
>>http://www.emerging-paradigms.com/
>>
>>
>>-----Original Message-----
>>From: [EMAIL PROTECTED]
>>[mailto:[EMAIL PROTECTED]]On Behalf Of David Ward
>>Sent: Tuesday, June 19, 2001 7:00 PM
>>To: [EMAIL PROTECTED]
>>Subject: Re: [JBoss-user] JDBC / ResultSet Question
>>
>>
>>Look at CachedRowSet implementation from Sun - it is Serializable and
>>can be disconnected:
>>http://developer.java.sun.com/developer/Books/JDBCTutorial/chapter5.html
>>
>>David
>>
>>--
>>
>>Mike korcynski wrote:
>>
>>> Hello:
>>> I am currently working on a project using JBoss
>>> 2.2. I have stumbled upon a strange problem I was
>>> hoping someone could help me with. I have a method in
>>> a session bean that will perform an ad hoc query on a
>>> database and return the results to the client in a
>>> ResultSet. However, it appears that the
>>> implementation of ResultSet is not serializable:
>>> "Caused by: java.io.NotSerializableException:
>>> org.opentools.minerva.jdbc.ResultSetInPool"
>>>
>>> Am I doing something wrong or is this some sort of
>>> bug? It would seem useful to be able to return a
>>> ResultSet to a client. Can someone advise? Thank you
>>> for your time.
>>>
>>> Mike Korcynski
>>>
>>>
>>>
>>>
>>>
>>>
>>> __________________________________________________
>>> Do You Yahoo!?
>>> Spot the hottest trends in music, movies, and more.
>>> http://buzz.yahoo.com/
>>>
>>> _______________________________________________
>>> JBoss-user mailing list
>>> [EMAIL PROTECTED]
>>> http://lists.sourceforge.net/lists/listinfo/jboss-user
>>>
>>
>>
>>
>>
>>_______________________________________________
>>JBoss-user mailing list
>>[EMAIL PROTECTED]
>>http://lists.sourceforge.net/lists/listinfo/jboss-user
>>
>>
>>_______________________________________________
>>JBoss-user mailing list
>>[EMAIL PROTECTED]
>>http://lists.sourceforge.net/lists/listinfo/jboss-user
>>
>
>
>_______________________________________________
>JBoss-user mailing list
>[EMAIL PROTECTED]
>http://lists.sourceforge.net/lists/listinfo/jboss-user
>
>
>_______________________________________________
>JBoss-user mailing list
>[EMAIL PROTECTED]
>http://lists.sourceforge.net/lists/listinfo/jboss-user
>
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
