Database VS. Flat File

cbrknight

I posted another topic on % of script run...

Part of the problem with it is the code takes too long to run and people cannot tell if it has stalled or is still running.

Someone else wrote the code.

I was tracing the code and noticed that the function call writes the data to a flat csv file while executing the loop.

Would it be faster executing if it wrote it to a database table and then took that data and wrote it to a .csv file after completion?

laserlight

Would it be faster executing if it wrote it to a database table and then took that data and wrote it to a .csv file after completion?

Would it be feasible to simply keep the data in memory until it needs to be stored?
Reading and writing to disk are expensive operations, after all.

cbrknight

We are talking 70,000 + records in a .csv file

I don't know about keeping that in memory...

AstroTeg

Can you ditch the CSV file and save it all in a database. And then maybe have an export option which creates the CSV as needed?

70k records in a CSV file is a bit much. What's the file size? 70k records, if they're ok in size, may still fit in memory.

cbrknight

from what I know... the file sizes range from 2 - 5 megs...

Not too bad but they do take time to process...

From feedback I am getting from users... sometimes they process with no problems other times they dont...

I am going to try going the way of the database.

I just needed some feedback to give to my boss to justify rewriting another programmers code...

AstroTeg

It depends on what you're doing with a CSV file. Like searching, CSVs do NOT scale well.

laserlight

Not too bad but they do take time to process...

Well, the conversion from CSV file to database is a very expensive operation.
But you only have to do it once, and maybe even without having to use PHP via the webserver.

cbrknight

basically they are customer lists. I have to make sure they are not existing customers. If they are I need to make a csv file of the existing customers. If they are not I need to add them to the main database and then make a csv file of the new customers.

Weedpacket

Well, if individual records are 2-5MB (what are you storing?!) and all you need is enough to determine whether the record belongs to an existing customer or not, then reading the entire file just for that minuscule fraction of the total information is spectacularly wasteful. A DBMS would maintain separate (much smaller) index files pointing straight into the relevant locations in the data file so that it can skip straight there without having to read through everything else first.

Is there some use for these CSVs that means they have to be kept as CSVs? Could this need be met by being able to request CSV generation on demand? CSV format was never intended for live storage, but purely as a transport medium.

cbrknight

Sorry... maybe I am not explaining well enough.

The files are sent to us in txt files. we have to check to make sure they are not existing customers. If they are we have to remove the existing customers from the file and add it to a separate file to send back to the broker. The ones that dont exist we have to write the none existant customers to another file and also write them to our main database.

the program we are using now was written by another programmer. I have to try to speed up the process... the program tends to stall sometimes with bigger files.

What he has done is read the data into an array.. he is then checking the data in the array against the database. So that every loop he is writing to a .csv file.

I am wondering if it would be faster to write all records to a database. Use a join to send existing records to another table.

Then pull New records to another another table then write the new customers into the main table..

then create the .csv files from the two temporary tables.

Weedpacket

Originally posted by cbrknight
I am wondering if it would be faster to write all records to a database. Use a join to send existing records to another table.

I'd say so; you should be able to create a temporary table and inhale the CSV into it. Then do all the SELECT INTO and DELETE WHERE stuff within the database, and only afterwards write back to a CSV to send back to the broker.

There ought to be a more elegant solution which eliminates such bulk, but that depends on the fine details of your operation.