I have a console app which processes upto 100 message queue items (xml) every second
Each item needs to do at least 1 sql statement lookup
this will obviously hit the DB quite hard, so I was wondering if its possible to store the SQL Server table in memory (its a relatively small number of rows (500-1000)
However, if someone adds something to this lookup table, I need the console app to get the latest version of it to stop it being out of sync
Performance is my number 1 objective, each row needs to be processed separately, checked against the lookup table and then i can do what i need with it (insert records)
My question is
1. Is it possible to cache a dataset locally which updates automatically when the table is edited?
2. Can you help with code samples to get me going