Link to home
Create AccountLog in
Avatar of websss
websssFlag for Kenya

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

What edition/version of SQL Server?
Avatar of websss

ASKER

2014 enterprise
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account