mercredi 19 décembre 2018

Multithreaded application with database read - each thread unique records

I have a .net application which basically reads about a million of records from database table each time (every 5 minutes), does some processing and updates the table marking the records as processed.

Currently the application runs in single thread taking about top 4K records from DB table, processes it, updates the records, and takes the next.

I'm using dapper with stored procedures. I'm using 4K records for retrieval to avoid DB table locks.

What would be the most optimal way for retrieving records in multiple threads and at the same time ensuring that each thread gets a new 4K records?

My current idea is that i would first just retrieve the ids of the 1M records. Sort the ids by ascending, and split them into 4K batches remembering lowest and highest id in a batch. Then in each thread i would call another stored procedure which would retrieve full records by specifying the lowest and highest ids of records retrieved, process that and so on.

Is there any better pattern i'm not aware of?

Aucun commentaire:

Enregistrer un commentaire