What are the best practices when batch exporting from an application following DDD

Question

Let say you hav a database with alot of products/customers/orders and the codebase (java/c#) contains all the business logic. During the night several batches are needed to export data to flat-files and then ftp them to properitary systems.

how should we do this "write-database-into-a-flat-file? what are the best-practices?

Some thoughts:

• we could create a storedprocedure and use f.ex ssis to fetch the data? maybe we can do this if we have a "batch-output-database-table" but not if we have to do logic before the file is written?
• we could do all the logic in managed code using the same repositories/business logic as the rest of the domain? (this could be a slow process compared to the storeproc solution)
• What if the only interface for the domain-services are webservices (which could take "long" time for each request), will the "best practices" change ?

Answer 1

I personally prefer to use normal (managed) code to implement feeds instead of stored procs, mainly because: 1) It's usually easier to interface with the other system (even if it is only shared drive) 2) It is easy to log everything you need and debug if something goes wrong 3) You can reuse the same code you use for normal business logic (its beneficial even if you just reference the same projects, etc.) 4) Often you need to enrich data with some information from other systems and this again is much easier to do from managed code. 5) Its much easier to test managed code, have all the unit tests, automated builds, etc.

I am not sure why it needs to be that much slower than doing it all in a stored proc. You just need to write a good stored procedure to extract the data you need, and the C#/java app will do all the transformations, enrichment, etc.

EDIT: Answering the comment: I don't think it is possible to say if you should reuse the existing stored procs, tweak them, or create new ones. I think that is the performance hit or needed changes are not too big than I would try to use one set of procs, to avoid duplication of logic. But if the differences are substantial, then probably the cost of maintaining extra procs will be lower than changing and releasing existing ones.

Answer 2

Go with the repository code you've already got. Do a few performance tests and see if it meets the perf. requirements. If there is a significant perf. issue that can be nailed down to too much DB IO then go for the sproc or implement a bulk export repository.