Updating a db record every time I retrieve it from db is good practice?

Question

I am using a sql server and I have a specific table that can contain ~ 1 million ~ 10 million recrdords max.

In each record that I get, I do some checks (I run a few simple lines of code), and then I want to note that the records were marked in DateTime.Now; so what I do is get a record, check some things, run the "update" request to set the "last_checked_time" field in DateTime.Now, and then move on to the next record. Then I can get all the records sorted by last_checked_time (ascending), and then I can iterate over them sorted by check time.

Is this a good practice? Can it still stay fast as long as I have no more than 10 million entries in this table?

I read somewhere that every update request is actually deleting and creating a new record.

I would also like to mention that these entries will often be retrieved on my ASP.net website ..

I was thinking of writing "last_checked_time" in a local txt file / binary, but I guess that would mean implementing what the database can already do for you.

Answer 1

I would recommend getting the data or part of the data, performing all the checks and sending updates in the transaction in order to ensure more efficient operation of the database. This will provide fewer round trips.

As for if this is a good practice, I would say yes, especially because you use it in your queries. Definitely, do not save the last checked time in a file and try to combine after loading the database data. The RDBMS database is designed to effectively manage this for you. Do not reinvent the wheel with cubes.

Answer 2

If you need this value "last checked time", then the best, most effective place to keep it is in the row in the table. It doesn’t matter how many rows are in the table, each update only affects the updated rows.

How the upgrade to the DBMS is implemented, but usually this is not done by deleting and reinserting the row.

Answer 3

Personally, I do not see any problems with him. It seems quite reasonable to store the last checked time in the database, especially since it can be used in queries (for example, to search for records that have not been checked during the week).

Answer 4

Perhaps (perhaps) you could create a new table containing two rows: the row identifier in the first table and the validation date.

This way you will not change the original table, but depending on the data usage and key date, you will have to make a combined query, which you probably do not want to do either.

Answer 5

It makes sense to store the "checked time" as part of the row that you are updating, and not in a separate file or even a separate table in the database. This approach should ensure optimal performance and maintain consistency. Solutions that include more than one table or external data warehousing may introduce a requirement for distributed or multi-table transactional updates that involve significant blocking, which can negatively impact performance and make it difficult to guarantee consistency.

In general, solutions that minimize transaction volume and, consequently, blocking, deserve attention. In addition, simplicity itself is a useful goal.