• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
  • Paul Clapham
Sheriffs:
  • paul wheaton
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Piet Souris
Bartenders:
  • Mike London

Fastest way to export millions of records to CSV from DB

 
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

What is the Fastest way to export millions of records to CSV from DB ?

Thanks
 
Marshal
Posts: 27531
88
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Make sure you have the fastest possible hardware, and if possible use built-in features of the database to export the data to CSV format. Also make sure that the target of the export process is on the same machine; transferring data over a network will be much slower.
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul Clapham,

Assume my application server runs in 192.168.1.1
My DB server runs in 68.1.1.1

#1 - Is it possible to export via my application server and export the CSV in to my application server instead of in DB server? i want the file to be placed in application server

#2 - Is it possible to append the delta changes to export to CSV when needed?

How to process csv file after exported to make a faster search?

Thanks
 
Paul Clapham
Marshal
Posts: 27531
88
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sure, you can copy the resulting CSV over the network if you like. It's just, you asked for the fastest way without providing any description of your actual requirements. You ask a bad question, you're likely to get bad answers.
 
Rancher
Posts: 326
14
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Chris Mary wrote:How to process csv file after exported to make a faster search?


Although I still don't get the bigger picture from your dozen topics around this problem - at least to this question the answer is simple: You don't!
Why? Because even if you have enough RAM to hold the entire CSV in it (which defeats the purpose of using a database in the first place) you still would need a way of indexing all the data. Otherwise it's only a very long string (which could exceed some language specific limits) you have to traverse for whatever you search every time you doca search - which in turn slows the search down even further as you rescan it every time.
Better: Build a proper index in your database and have it handle an index based search. That's what databases designed for and this whole topic is resolved by a simple "Let the database do the job.".

Few personal words: Although it's meant offending, mods know me, to me the way you scatter a dozen questions all over this forum in such an unorganized style looks like you either not the right person for the task or try to do something "the bad way" due to lack of experience. Maybe srep back one or two steps and try to rephrase what's the objective to be done in two or three simple short sentences. And please - don't open another topic with that but rather reply here. As you already got: The type and the way of questions you ask obviously lead to answers inappropriate to the solution you're seeking after. Please keep in mind that everybody here is spending her/his personal free time and either replies to best of her/his knowledge or skip to other topics. Demanding answers by spamming often result in you getting ignored.
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sure Matthew Bendford.
 
Marshal
Posts: 77199
370
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
To continue from what MB said, why do you need a CSV in the first place?
 
Matthew Bendford
Rancher
Posts: 326
14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Matthew Bendford wrote:Few personal words: Although it's meant offending, mods know me, ...


Well ... THAT's a typo on my end - of course my intention was to write
... it's NOT meant (to be/sound)
Sorry about that.
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are absolutely fine Matthew Bendford. Need corrections when ever someone does a mistake. It was not intentional. i will follow the protocol/rules.
 
Paul Clapham
Marshal
Posts: 27531
88
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Chris Mary wrote:How to process csv file after exported to make a faster search?



Faster than what? To search a CSV file you have to go through it sequentially; you're better off throwing it away and searching the original database, which at least can have indexes to make a faster search.
 
Campbell Ritchie
Marshal
Posts: 77199
370
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Some databses create an index when they are searched; that means subsequent searches for anything similar will run much faster. And, having read Paul C's post, I shall repeat what I said yesterday:-

. . . why do you need a CSV in the first place?

 
lowercase baba
Posts: 13086
67
Chrome Java Linux
  • Likes 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I second Campbell's question.  Are you assuming a CSV file will be faster for some reason?  The first rule of optimization is NEVER ASSUME one way will be better.

Databases are a pretty mature technology.  The have been honed and tuned for decades to give excellent performance, doing things you are often not even aware.  I would be surprised if parsing a CSV file would be faster than a DB query.

Also, if you are only exporting the data once, does it matter how fast it is?  Had you just started an export three days ago when you created this thread, it might be done by now.  Does the speed of the EXPORT really matter, when compared to the searching time?

and as to your "is it possible..." question, the answer is always "yes, but....".  Yes, you can, given enough time, effort, and money.  A better question is "Is it worth it...", and honestly it seems like "No" is the answer.
 
Bartender
Posts: 7488
171
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

fred rosenberger wrote:as to your "is it possible..." question, the answer is always "yes, but....".  Yes, you can, given enough time, effort, and money.  A better question is "Is it worth it..."


Quoted for emphasis. If I had a dollar for every time I've said  this to not-so-technical people, I'd be a thousandaire by now
 
Campbell Ritchie
Marshal
Posts: 77199
370
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

fred rosenberger wrote:. . . an export three days ago . . . might be done by now. . . . .

Three experienced monks with 1,000 sheets of parchment and one quill pen each would easily have managed it in that time.
 
Saloon Keeper
Posts: 26541
187
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:. . . why do you need a CSV in the first place?



Indeed. For many years, the primary consumer of CSV's — Microsoft Excel — could only hold 65535 rows in an entire sheet. There aren't many things I can think of to do with millions of rows, unless you need a portable transport of raw data values to some other site or storage media.

I mentioned in another very similar   thread that there exist a class of applications called ETL tools. The Pentaho DI tool, which is the one I'm most familiar with (alas, I don't get paid for plugging them. ) has the ability to run multiple parallel streams for cases where that will allow better performance. And it doesn't require custom coding. The jobs are defined in XML files that you can create/edit with a DDD (Drag, Drop, Drool) GUI editor called Spoon.
 
reply
    Bookmark Topic Watch Topic
  • New Topic