Uploading a Log (.txt file) vs inserting 1 record per event on database, Efficiency on recording Logs

Question

I'm trying to record log files on my database. My question is which has the less load on making logs on the database. I'm thinking of storing long term log files ,maybe 3-5 years maximum, for an Inventory Program.

Process: I'll be using a barcode scanner. After scanning a barcode, I'll get all the details of who is logged in, date and time, product details then saved per piece.

I came up with two ideas.

1. After the scanning event, It will be saved on a DataTable then after finishing a batch.. DataTable will be written on a *.txt file and then uploaded to my database.
2. After every scanned barcode, an INSERT query will be executed. I suspect this option will be heavy on the server side since I'm not the only one using this server

What are the pros and cons of the two options?
Are there more efficient ways of storing logs?

Answer 1

Based on your use case, I also think you need to consider at least 2 additional factors, the first being how important is it that the scanned item is logged in the database immediately. If you need the scanned item to be logged because you'll be checking to see if its been scanned, for example to prevent other scans, then doing a single insert is probably a very good idea. The second thing to consider is will you ever need to "unscan" an item, and at which part of the process? If the person scanning needs the ability to revert the scan immediately, it might be a good idea to wait until theyre done all their scannings before dumping the data to the database, as this will let you avoid ever having to delete from the table.

Overall I wouldnt worry too much about what the database can handle, sql-server is very good at handling simultaneous single inserts into a table thats designed for that use case. If youre only going to be inserting new data to the end of the table, and not updating or deleting existing records, performance is going to scale very well. The same goes for larger batch inserts, theyre very efficient no matter how many rows you want to bring in, assuming your table is designed for that purpose.

So overall I would probably pick the more efficient solution from the application side for your specific use case, and then once you have decided that, you can shape the database around the code, rather than trying to shape your code around suspected limitations of the database.

Answer 2

Go with the second option, and use transactions. This way the data will not be sent to the db until you call the transaction to complete. (Which can be scheduled.) This will also prevent broken data from getting into your database when a crash or something occurs.

Transactions in .net

Transaction Tutorial in C#

Answer 3

What are the pros and cons of the two options?

Basically your question is which way is more efficient (bulk insert or multiple single insert)?

The answers is always depends and always be situation based. So unfortunately, I don't think there's a right answer for you

1. The way you structure the log table.
2. If you choose bulk insert, how many rows do you want to insert at 1 time?
3. Is it read-only table? And if you want to read from it, how often do you do the read?
4. Do you need to scale it up?
5. etc...

Are there more efficient ways of storing logs?

There're some possible ways to improve I can think of (not all of them can work together)

1. If you go with the first option, maybe you can schedule the insert to non-peak hours
2. If you go with the first option, chunk the log files and do the insert
3. Use another database to do the logging
4. If you go with the second option, do some load testing

Personally, I prefer to go with second option if the project is small to medium size and the logging is critical part of the project.

hope it helps.