>>>Uhm...no....no SQL Server on each client machine, but that's a funny one :)
>
>Caching the lookup dbfs on each machine allows blazing quick real-time feedback as the user is working. Other vendors using traditional C/S say it's not possible in real time so they wait until the user saves. Admittedly we've done some dbf sneaks to accelerate performance but there's no known downside for read only. If you're laughing at the idea of installing SQL Server locally with the lookup tables to try to mimic the results- certainly I agree you'd have to ask why. Also, to avoid impacting the live database you probably could have a separate server to process each user selection and return a result- but it all seems to fail the KISS test. Local dbfs pass KISS with flying colors and I don't think I can remember a support ticket this century relating to the lookups.


I hate to give short answers, but the honest truth is that Hekaton (the in-memory optimized table feature in SQL 2014) accomplishes what you just described, as a server-based solution.

It's being used for heavy random reads of session based data and also for lookup tables with far greater volumes than what you've described. Also speeds up ETL processing of staging tables. So there are several scenarios where you can get anywhere from 2-10x increase in performance over regular SQL tables. It's basically an optimized row format using a separate buffer pool where you can have either hash or range indexes. Obviously, it requires a server configuration with lots of memory, but when it's indicated, it works extremely well.

What you've got is perfectly fine - for client/server.