Best way to store fairly static data

1

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:

  1. Materialized view
  2. Nothing
  3. App caching
  4. In memory table (sadly, don't have Enterprise Edition)
  5. Other

Thanks,
Craig

2

if the table is so small, it will likely remain in cache and the load on the server should be likewise small. since it is static data, you could change queries to use with(readuncommitted) to reduce locking, since no locking is required. A view (non materialized) is also an option. Why materialize it?

3

Unless the rows are 1600 bytes or more each, the entire table should fit in one page.

If all the data is not in a single page now, rebuild the table. Once all the rows are in one page, don't see what else you could do to speed it up.

4

Why? Just to get the connection string for a specific dbo.Databases.Id ? i.e. it isn't 400 different people a second, its a modest number of people who happen to be repeatedly connecting to one of the database (presumably the same one for a reasonable amount of time, and then perhaps SWITCH to a different one?)

if you only have a modest number of dbo.Databases.Id rows perhaps that could be handled by the filesystem instead?

I'm guessing, but in my case: we have multiple databases for clients, and each has its own website. The only difference in the website is the connection string (well ... they can have different Images and Style Sheets etc. but in principle they could be identical).

So my browser connects to Client1.MyDomain.Com and I get that website, which has a CONFIG file with a connection string for (say) DatabaseClient1. All that is cached by the Web Server so performs very quickly (well ... I presume it does! We have thousands of concurrent connections and huge numbers of queries on the DBs and the hardware is relatively modest, and we have no performance complaints (on a normal day!!)