Fastest way to export millions of records to CSV from DB

[Chris Mary]

Hi,

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

Thanks

[Paul Clapham]

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]

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]

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.

[Matthew Bendford]

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]

Sure Matthew Bendford.

[Campbell Ritchie]

To continue from what MB said, why do you need a CSV in the first place?

[Matthew Bendford]

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]

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]

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]

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?

[fred rosenberger]

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.

[Tim Moores]

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]

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.

[Tim Holloway]

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.