We have a global database that has a table that points our application to the correct database depending on the client. This table is currently queried about 400 times per second
SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[ConnectionString] AS [ConnectionString], [Extent1].[DateAdded] AS [DateAdded], [Extent1].[DateEdited] AS [DateEdited], [Extent1].[AddedBy] AS [AddedBy], [Extent1].[EditedBy] AS [EditedBy], [Extent1].[IsDeleted] AS [IsDeleted], [Extent1].[DateDeleted] AS [DateDeleted] FROM [dbo].[Databases] AS [Extent1] WHERE [Extent1].[Id] = '2EC4A235-BBF2-406C-8E90-195AFAEG9D42'
While the data is very small (5 rows), it seems like an unnecessary load on the server. Any suggestions on how to do this better:
- Materialized view
- App caching
- In memory table (sadly, don't have Enterprise Edition)