I believe it is a known problem (feature?) with Oracle that COUNT(*)
has to scan the whole table (even *deleted* rows), but if you just
choose a column name (probably should be an indexed column) and do
COUNT(colname) you'll get a drastic improvement in performance. I
haven't tested this but I have heard about it before. Good luck.
-Tom
On Tuesday 19 June 2001 08:42 pm, Matt Veitas wrote:
> 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
--
---------------------------------------------------------------------
Tom Green 244 Brighton Ave.
Director of Data Engineering Allston, MA 02134
Digital Media On Demand, Inc. (DMOD) USA
TEL: (617) 254-1024 x207
FAX: (617) 254-0686 [EMAIL PROTECTED]
---------------------------------------------------------------------
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
