• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

retrieving million of records from database

 
Claire Gout Thee
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's the efficient way to select million of records from two tables which have related primary key & foreign key. My "select statement" can retrieve those records. However, I feel that it would take longer if there are million records inside two tables. Thus, I want to find more efficient and faster way
 
Carey Brown
Bartender
Posts: 1936
24
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'll need some more information. What are the schemas for the two tables? Are there a million records in each table? Is there a one-to-one relationship between the two tables? What columns are indexed? Does your select include a join? -- Basically, you haven't given us enough information to help you.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And are you sure you actually need to select millions of rows?

That's often a warning sign that a dev is attempting to crunch data in Java that possibly ought to be crunched in the database.
 
Claire Gout Thee
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, there's a relationship between two tables. There are million of records in each table. For example, there are two tables. Customer and Borrowed Books. Customer includes customer id as unique key. Borrowed Book has those customer id which has borrow books so. one customer can have more than one records inside 'Borrowed Books' table. It's one-to-many relationship. Customer ID '001' borrowed 10 books. So there are 10 records inside 'Borrowed Books'. However, I would like to retrieve those customer who borrowed books over a week. Thus I need to add 'where' clause.

I already have working 'sql statement'. what i'm not sure is if it's efficient way to use normal 'select statement with required condition' to retrieve million of records at one time.

Note: 'Customer' & 'Borrowed Books' are just an example.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, presumably not all those Customers borrowed a book during that week, so it won't be millions of rows.

What are you intending on doing with all this data?

If you need the data then how else would you plan on getting it apart from SELECT..FROM..JOIN..WHERE...?

As I say, from my position I would question the need to actually retrieve millions of rows in the first place.
 
Claire Gout Thee
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
of course, there will not be million of records from my example. what I share you is only example and relationship between two tables. My question is what's the efficient way to retrieve million of records from database. i asked this question because I want to work with million of records inside database in the first place.
 
fred rosenberger
lowercase baba
Bartender
Posts: 12264
36
Chrome Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It partially depends on what you mean by "efficient", and what you plan to do with the data once you pull it out. Often, much of the work can be done by the database. Often, you don't really need to pull millions of records all at once, but perhaps in batches of 100 or 1000 at a time. It's possible you can write something that is faster to pull the data out, but would take longer to process - or you could write something that takes longer to pull data out, but would be faster to process.

So the answer is always "it depends". I don't think anyone here has enough info to guide you for your specific situation.
 
Claire Gout Thee
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After I got those records from database, I need to update another table base on the record id I got from previous statement. So in this case, it would better use Select & Update statement in the same query?
 
Paul Clapham
Sheriff
Posts: 21559
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Better than what alternative?

As a general rule it's better to let the database do what databases are good at doing.
 
fred rosenberger
lowercase baba
Bartender
Posts: 12264
36
Chrome Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Claire Chan Myae wrote:I need to update another table base on the record id I got from previous statement.

Note that this:

retrieve millions of records
update records based on the records retrieved

is VERY different from

retrieve one record
update a record based on the above
repeat millions of times.



 
Claire Gout Thee
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok what i want to do is "retrieve millions of records ,update records based on the records retrieved"
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Claire Chan Myae wrote:ok what i want to do is "retrieve millions of records ,update records based on the records retrieved"


Then try and do that in a single query.

Retrieving millions of records and crunching them in Java is going to be slow.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic