1

We are working on a fulfillment system for an online book store. Everyday we receive tens of CSV files (about 90 files) with data about purchased orders.
Example:

OrderId    |    BookTitle    |    Quantity    |    Price    |    BuyerName    |    ...
1          |The hunger games |        1       |     10      |    John Doe     |    ...
1          | The Exorcist    |        2       |     25      |    John Doe     |    ...
2          |   Superman      |        1       |     5.5     |    Jane Nose    |    ...

What we currently do:
The data need to be translated to our domain to Order entity, OrderItem entity,...etc. We load the file into dummy tables then use very complex queries to populate the Orders table and OrderItems table.

Problem:
All the business logic is in the database (in the queries) so unit testing them is a nightmare. In addition we were requested to change the database we are using (not exactly, but something terrifyingly similar)

What we tried:
We are moving to a DDD approach where the domain model is the core of the application that holds all the logic and validation.

We tried to parse the file line by line and create the respective entities and validate them accordingly then save them to the database.
This resulted in thousands of calls to the database and sometimes the server runs out of memory coz of the thousands of records (around 90 thousand record/file) in a single file.

I know this domain is dead old and someone must have found a better way to handle such task. How do you process such huge files in your domain without suffering from performance?

UPDATE

  • We are using a MySql database with PHP and Apache.
  • The CSV file is loaded into a dummy table using something similar to this:
load data local infile 'orders.csv' into table dummyOrders fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
    (orderId, bookkTitle,Quantity, Price, BuyerName,...)
  • How are you inserting them in the database? Is this a execute statement x 1000? or a prepare x 1, execute x1000? or some form of bulk data loading? – user40980 Mar 18 '14 at 21:07
  • Hm, "thousands of records" in a single file, and "tens of files per day" - does not sound very huge for me. And only two tables to fill (Orders and OrderItems)? Does not sound very complex. The server runs out of memory? What kind of database server are you using? Maybe you just have to increase some transaction buffer sizes? – Doc Brown Mar 18 '14 at 21:41
  • 2
    Your original approach seems preferable. I sympathize with your desire to have unit tests and validation, but this is essentially an ETL scenario. – Robert Harvey Mar 18 '14 at 22:20
  • @MichaelT please check the update. – Songo Mar 18 '14 at 22:21
  • 1
    It also sounds like you need to change the commit frequency of your transactions (like every 100 or 1000 records inserted/updated). Your DBA should be able to help you tune that. – Adam Zuckerman Mar 19 '14 at 3:27
3

You were doing it right - load the file into a dummy table (preferably on a staging DB), then manipulate the data with stored procedures (which are easy to test - they're little more than a single function, and you can put test data in the tables and run your sprocs using that data in a transaction that can be rolled back so they can be very isolatable regardless of what data exists in there).

Once you have your data in a good state, you migrate it to the production DB. That one will not need to have all the loading SQL in it, so can be simpler to manage.

So basically, split your operations into 2, staging and production. I've used this approach with millions of records and very good performance (though with SqlServer, not MySQL).

  • +1 I never thought about splitting the process into 2 databases. Gonna talk with our database admin and see if that is feasible. – Songo Mar 18 '14 at 22:33
  • +1, though I think your remark about stored procedures is not precise. SP are not easier or as harder to test than most other code "per se". They probably can be designed to be easier to test than complex SQL queries. – Doc Brown Mar 19 '14 at 7:08
  • @Songo: note that this gist of this answer is not primarily about using two databases - its about splitting the process into more intermediate steps (maybe two databases can help you to make this easier, but maybe some temporary tables are enough). – Doc Brown Mar 19 '14 at 7:14
  • @DocBrown oh yes, sprocs aren't magic, but if you write a SP then it is contained in the DB and not spread over a load of files spaghetti-like. That's really what I meant. Also, if you've designed a system that uses them, you can mock them out in the business logic easier than if you have SQL embedded into your code. – gbjbaanb Mar 19 '14 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.