Alternatives to running query for rarely changed data everytime on large table

I have an internal web application running and everytime a user goes to the "search" view it queries three different tables in the db to generate values for three dropdowns in the view.

It's basically running a

SELECT DISTINCT (PortName)
FROM Ports
ORDER BY PortName ASC

But the table contains ~10'000'000 rows and is under quite heavy load which means that from time to time the loading time for the page (due to loading the dropdowns with data) can be upwards of 10-15 seconds.

So, is there a better way to do this, for example running some script at certain intervals and creating a table/view/whatever at a different location so as to offload querying the big table just to have 80 rows returned from the 10'000'000 in the main table?

I am assuming from the DISTINCT that PortNames are duplicated in your table and that there are not 10 million different portnames being returned.

The minimal effort solution is to just place an index on that column:

CREATE INDEX IX_Ports_PortName ON Ports(PortName);

Of course there is still some DB load with this and storage overhead, so you may want a more sophisticated solution such as Caching, which Aaron Bertrand covers quite well in his answer.

You could also employ more Normalization: If portnames are duplicated and knowing them distinctly is important, then you could make a [PortNames] table, and use a PortNameID in the [Ports] table. That way you could just scan the [PortNames] table which would presumably be much smaller and faster. Of course that may have additional costs and considerations of its own.

StackExchange Database Administrators Q#203130, answer score: 11