1

I'm currently in the process of developing a payroll system using SQL Server and PHP. However, the system involves a multitude of processing rules.

During this processing, I may receive files ranging from 1GB and upwards, in formats such as xlsx, csv, and txt. I need to update the database virtually in real-time. Subsequently, individuals will have their current margins updated, enabling them to make contributions.

I'm contemplating utilizing stored procedures in SQL Server for processing, as I believe it might be faster than using PHP.

Additionally, I'm considering having one database for the application with already processed data, and another for processing. However, I'm unsure about how to maintain consistency and provide real-time access to this data for the main database. Also, I anticipate the need to generate numerous reports, as I'll be using Power BI along with some reports from the system itself. This leads me to think I'll need a data warehouse as well.

My concern lies in how to efficiently carry out these processes in SQL Server and make them available as quickly as possible. I've thought about data streaming, but I'm not very familiar with it. If anyone has any tips on this, I would greatly appreciate it.

Thank you!

2
  • 1
    "Virtually real-time" is the crucial term here. There will always be some time involved in uploading the files. How crazy you need to get depends on the interpretation of that. Do they need the data in the database within 5 minutes of the file being uploaded? a day? 100ms?
    – Xedni
    Oct 26, 2023 at 18:55
  • files being loaded up to 30m would be fine, but 5, 10 would be ideal
    – Flavio
    Oct 26, 2023 at 22:03

1 Answer 1

0

So this is a very broad question, and you need to take anything I say with a grain of salt. There are innumerable factors which could invalidate the advice I'm giving you here.

That said, an approach I have used repeatedly and to great effect is to create raw (i.e. unindexed) tables that match the schema of the incoming files, then using some means of loading them from the files. The fastest ways to load data into SQL is going to be through SSIS, BCP or C# SqlBulkCopy

Once the data is loaded into your raw tables, have "workoff" procedures whose job it is to merge the raw data into your presentation tables. You want to use stored procedures because you want to avoid doing row-by-row modifications (which if you're processing updates, SSIS, or C# would likely impose upon you).

You'll also need to take downtime into consideration. Any time you're updating the tables people are querying, you will run into blocking, as SQL ensures someone can't read a row which is currently being modified (i.e. by an Update).

Try to minimize the time you are actually updating the live table; make use of temporary tables for any pre-processing you need to do, and try to only do a single set of insert/updates at the very end.

There are ways to prevent readers from being blocked such as using nolock, but that comes with it's own set of caveats. Basically, you can prevent readers from being blocked, at the risk of them reading bad data; probably not something you want in a payroll system.

If you find your blocking is getting out of hand, consider batching your insert/update/deletes so that the locks you take out are very short in duration. This will increase the time it takes to get the data merged into your presentation tables, but will keep end users happier if blocking is a problem.

The part I can give you the least advice on is how to know when to kick off your process. Your options are poll, or be notified. Polling involves constantly checking a file location to see if you have new files to process. If you don't, silently exit, and run the process again as quickly as feasible. If you do, process the files, then move them somewhere they won't be picked up again. Alternatively, if you have some means of listening for events saying a new file was delivered, you can act on that and kick off your process on demand.

I'd also recommend keeping track of what files you processed, and if possible, how many rows you processed. It's one of those things which is very useful for debugging when things go wrong, or if you have to re-process files, or figure out which files you never processed.

So to sum up:

  • Try to act on new files as quickly as possible. Events allow you to act quickly and only when needed, but can add complexity. Polling is simple, but introduces a small delay, and most of your runs will end in a soft failure
  • Load data into un-indexed raw tables as quickly as you can. Aim for something that loads data in bulk like SSIS, BCP, or SqlBulkCopy over systems which have to loop over every row in the source data and execute a separate insert statement
  • Once your data is staged, use stored procedures to merge the data in where you want it
  • Think about logging what files you run to save future-you some hair loss

Good luck to you!

Edit Adding some context to your follow up question

Again, please take this with a grain of salt, because this is fairly generic advice. Whether you create one database or more is going to depend on your particulars; depending on if you only need a single unified live customer database, or if you need to do distinct things with the live data for each customer. At my company, we have a bunch of different clients who send us healthcare data (where we follow a similar model as described above). We need to ingest all the differently shaped files, then merge them into a single unified schema.

We use a single database, and a distinct schema for every set of [uniquely shaped] files. We load the raw tables, then have a procedure for each one which loads the cleaned up data into our live core schema. So basically one unified live database, and several differently shaped schemas on the same database.

In another corner of our system though, we do it slightly differently. We have a dedicated database for each set of files in which we have raw tables, and a set of live tables of basically the same shape. The reason we do these differently is that we have the need to query those datasets independently of the unified set of tables mentioned above. We still wind up merging those distinct databases into the unified core schema, we just do that off of the live, customer-specific databases, rather than working right off the raw tables in the same database.

So in short, I think whether you have different databases or not is probably going to depend on whether you need to have multiple live versions of each distinct customer's data, or if your end product is really just the unified schema.

2
  • Man, thank you very much for the tips, it's a new project, I'm practically redoing a legacy. I have another question, today I'm thinking about two database models, a unified one containing all my customers and another separate one. Since I have several clients who will send many lines and I have to generate many lines in these processes, which modeling is most ideal?
    – Flavio
    Oct 26, 2023 at 22:47
  • Added a bit of flavor above.
    – Xedni
    Oct 27, 2023 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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