Hai,
I have huge number of rows retrive from database using ResultSet but it seems to be slow to get records. When it retrive data from database can we read data from ResultSet?.
Advance wishes,
Daiesh
> What do you mean by huge?
there are 500000 number of rows in the table. how can i retrive fast?
Thank you.
> What do you mean by huge?
there are 500000 number of rows in the table. how can i retrive fast?
Thank you.
> > What do you mean by huge?
>
> there are 500000 number of rows in the table. how can
> i retrive fast?
>
> Thank you.
What are you going to do with 500K rows?
Can't go any faster than the network and database will allow.
The real question is: "Why do you think you need all that data?"
%
We are imporing data from Oracle to MsAccess. using thirdparty drivers. anyway to increase the speed of do that process?
Obviously the first step is to make sure your query is as fast as possible. You won't get the data across JDBC faster than the DB can read it off disk. A word of caution, if you're pulling up entire tables or even large portions of tables, indexed access is often slower than full table scans due to extra I/O reading the indexes and random vs. linear access to the table data itself. That can make tuning counterintuitive.
Second is to improve your JDBC speed. Access columns by index, not name. e.g. rs.getString("foo") becomes rs.getString(1). If you're using ResultSetMetaData to get a column count and then loop through the columns then make sure you're only calling getMetaData once, not for every row. I hope you're not going through an ODBC connection, that's probably slow. Finally, depending on the database, see about other JDBC drivers. Try different type 2 or type 4 drivers. From experience, there's two main drivers for Oracle (OCI and Thin), and another if you use WebLogic, and each will perform better or worse in different scenarios, so see if there's other drivers available for your database and try them out.
Jemiah
Lock the table, update your indexes before the import, unless you're doing "select * from" try to optimize your query.
Java wise there's not much you can do, 500K records is not supposed to be fast.
For that amount of data, you're better off doing and off-line import. export the data on one side, copy file(s), import on the other side.
I'd use Oracle batch tools. If there was a way to import a .csv file, I'd export Access to that format and then import into Oracle that way.
Java's not the thing for bulk data transfers.
%
how can i choose some other driver to connect with database? like type1 and type2 which drivers is best?. Thanks for your valuable ans.
advance wishes !!!
> Can't go any faster than the network and database
> will allow.
No, he can't, but who's to say that's what's slowing him down? Could be bad JDBC code, old JDBC drivers, or bad queries.
> The real question is: "Why do you think you need all
> that data?"
Honestly why does it matter to you? Did you have an answer all prepared to go so long as he gave you a reason that was up to your rigorous standards? You could have said "make sure you really need all that data, and if you do, then try X, Y, and Z to speed it up". But to just throw that out with no evidence you're prepared to help regardless? Why bother?
Oh wait... I get it ... did I mistake your submission to "The Most Useless, Unhelpful, and Arrogant Post Contest" for a real post? My bad...
Jemiah
> No, he can't, but who's to say that's what's slowing
> him down? Could be bad JDBC code, old JDBC drivers,
> or bad queries.
Could be.
> Honestly why does it matter to you?
It matters a great deal.
If he's talking about a web page, and LOTS of people who ask this question are bringing that data down to the browser, my answer would be that no user wants to deal with 500K records all at once. A well-placed WHERE clause or filter, or paging through 25 at a time a la Google, would be my recommendation in that case.
> Did you have an
> answer all prepared to go so long as he gave you a
> reason that was up to your rigorous standards?
Yes, see above.
> You
> could have said "make sure you really need all that
> data, and if you do, then try X, Y, and Z to speed it
> up". But to just throw that out with no evidence
> you're prepared to help regardless? Why bother?
See above. There was a reason for the question.
> Oh wait... I get it ... did I mistake your submission
> to "The Most Useless, Unhelpful, and Arrogant Post
> Contest" for a real post? My bad...
Nope, that would be yours, dumb @ss.
%
> We are imporing data from Oracle to MsAccess. using
> thirdparty drivers. anyway to increase the speed of
> do that process?
In that case you're reading from one database and writing to another. Check if your program is CPU or Network bound. If it is not, then try implementing your program as a threaded producer/consumer. One thread (producer) reads from the database and puts records into a queue, and another thread (consumer) takes those and writes them to the second database. Be sure to bound the size of your queue to maybe a thousand records or so, so that the producer pauses when the queue is full. Otherwise you can run out of memory if the producer gets too far ahead of the consumer.
Also, you do know that Access already has a built in import feature, right?
Jemiah
I'll also point out that asking that question elicited the response that it was a data migration from Access to Oracle, which is an important detail to know. The OP didn't say anything about how many records or what was being done with them in the first post. I think both pieces of data were important.
Now my question is: Why are you so upset about somebody asking questions to get more information?
%
> Oh wait... I get it ... did I mistake your submission
> to "The Most Useless, Unhelpful, and Arrogant Post
> Contest" for a real post? My bad...
Let's see - been registered for a month and has 22 posts to his/her name. Unless you've had your nic yanked for bad behavior, that hardly qualifies you as an expert on forum etiquette. Relax.
%
> > We are imporing data from Oracle to MsAccess.
> using
> > thirdparty drivers. anyway to increase the speed
> of
> > do that process?
>
> In that case you're reading from one database and
> writing to another. Check if your program is CPU or
> Network bound. If it is not, then try implementing
> your program as a threaded producer/consumer. One
> thread (producer) reads from the database and puts
> records into a queue, and another thread (consumer)
> takes those and writes them to the second database.
> Be sure to bound the size of your queue to maybe a
> thousand records or so, so that the producer pauses
> when the queue is full. Otherwise you can run out
> of memory if the producer gets too far ahead of the
> consumer.
>
> Also, you do know that Access already has a built in
> import feature, right?
>
> Jemiah
I know Access has import datas from oracle. But i need a way to handle large numbers of rows table to retrive from database.
i am using third party drivers.It does not support to handle to get fixed number of rows from table. It only retrive total number of rows from a table.This is my major problem. So only i need to procees that functionality thru Java coding.
> > Can't go any faster than the network and database
> > will allow.
>
> No, he can't, but who's to say that's what's slowing
> him down? Could be bad JDBC code, old JDBC drivers,
> or bad queries.
>
> > The real question is: "Why do you think you need
> all
> > that data?"
>
> Honestly why does it matter to you? Did you have an
> answer all prepared to go so long as he gave you a
> reason that was up to your rigorous standards? You
> could have said "make sure you really need all that
> data, and if you do, then try X, Y, and Z to speed it
> up". But to just throw that out with no evidence
> you're prepared to help regardless? Why bother?
>
> Oh wait... I get it ... did I mistake your submission
> to "The Most Useless, Unhelpful, and Arrogant Post
> Contest" for a real post? My bad...
>
> emiah
Wow... Did I miss something here?
I also answered to the OP with a question. Do you want to spank me?
Don't go multi thread on the Access connection.
After the 3rd or 4th connection all other will be rejected.
Buffer your transactions, do 10-100 at a time, you have to set autocommit off (conn.setAutocommit(false);) to be able to manage your transaction manually.
Still, i believe if you do a dump to file of the data you require of the Oracle db, and load that data on the Access latter might help a bit.
By splinting the operation in 3 (dump, transfer file, load) you'll probably have better results, and that way you can use Oracle and Access tools for importing / exporting data.
As far as the drivers go, you'll have a problem with the Access driver, Access uses JET and there are very few drivers out there that support this, fewer are good and fewer are free.
For Oracle, try the manufacturer's page for the drivers, they should have a few good ones there (and free).
