websss
asked on
How to cache local dataset and auto update if sql Table changes
Hi
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
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
What edition/version of SQL Server?
ASKER
2014 enterprise
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.