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

Question

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?

Answer 1

Somehow, nobody mentioned an indexed view. A very brief intro to the indexed views can be found at What You Can (and Can’t) Do With Indexed Views.

In essence it is a cache, which is maintained by the engine automatically behind the scenes. Indexed view is stored on disk and updated automatically when the underlying table changes.

So, updates, deletes and inserts into the main table would become somewhat slower, but querying the indexed view would be instant, because it will not scan 10M rows of the main table. In any case, the engine is smart enough not to scan the whole 10M row table when it is updated to adjust the values stored in the indexed view.

Besides, the question title says "Alternatives to running query for rarely changed data", so I assume that this large table doesn't change often anyway. I think, indexed view would be perfect here.

You can't have DISTINCT in an indexed view, but your query can be rewritten without it like this:

SELECT PortName, COUNT_BIG(*) AS cc 
FROM Ports 
GROUP BY PortName

If an indexed view contains GROUP BY it needs COUNT_BIG(*), so I added it.

Answer 2

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.

Answer 3

For data that doesn't change often, you can use a caching layer where those queries go. There are many alternatives, such as [memcached], and many discussions already exist:

• Cache Comparison: Memcached vs. Sql Server ……… (wait…, what?)
• can I get Memcached running on a Windows (x64) 64bit environment?

You can also do this quite easily yourself, and depending on the scope and size of the data, you can do it on the cheap. I did this kind of thing in a previous life, where I placed a SQL Server Express instance on each app/web server, and wrote my own scripts to swap out the data in those instances periodically with minimal disruption. This kept all that heavy read activity off the primary instance and also offered the flexibility of how stale those cached copies of the data could get (simply by changing the frequency of the refresh jobs). I wrote about this process here:

• Schema Switch-a-Roo
• Schema Switch-a-Roo : Part 2

Another thing you can do is use log shipping to implement a poor man's Availability Group. Basically you have a set of log shipped targets, cycle through them restoring the latest logs on a schedule, and a dynamic app that knows which target to use for the next read request it gets. I wrote about that process here:

• Readable Secondaries on a Budget

If your data is larger than 10GB, or will exceed that in the future, then Express won't work, and you'll have to use at least Standard Edition. But this type of operation, where you scale OUT reads onto commodity hardware, is much less expensive than increasing cores/memory/disk on the primary server to scale UP.

If isolating reads from writes isn’t the primary goal, then for this very specific case you can use other local solutions like indexed views. Just remember they create overhead, and you can’t be flexible with those, like adjusting how often the data is replicated (and therefore how stale the read copies are). Other query scenarios won’t lend themselves to indexed views.

Answer 4

This sounds like you have a table where each entry has a port, but only from a small pool of ports. In this case it is usually good practice to create a second table that contains every port once and link it via foreign key. Then you can query this much smaller table.

This also makes it impossible to insert a row with a misspelled port, since it has to be linked to an existing row in the second table.

However, if you don't want to change up your database architecture, you could create a new table with just the name of the port (e.g.: CREATE TABLE portnames (name varchar(50));). Then you fill it with content from your first table (INSERT INTO portnames (SELECT DISTINCT PortName FROM Ports);). Now you can query this table instead! Remember, if you want to keep it updated you have to recreate (or truncate/insert) it everytime you add an entry to the first table .

Answer 5

The other comments and answers by Flourid, RBarryYoung, and DForck42 have already highlighted that this looks like a normalization issue. You should not be querying millions of rows to pull out just 80 unique records. Normalize the data first so you can populate the drop downs from the 80 rows. Then apply indexing as a performance enhancement if warranted. Then apply caching so that you can populate the drop downs from the cache if warranted.

That said (and the reason this is an answer rather than a comment), if you don't have control of the schema (normalization) or infrastructure (caching) to implement those changes... look at sys.dm_db_index_usage_stats.last_user_update. This should be a relatively inexpensive query you can use to check the last insert/update/delete for the table. Wrap your expensive query with the cheap one so that you only refresh from the table if the data has changed. It's effectively a poor man's cache.

Note: there is an edge case that when the server is restarted it will not have a last_user_update until the first insert/update/delete occurs.

Answer 6

A query to return only 80 distinct values can finish almost instantaneously with the right index and the right physical implementation strategy. The run time of the query is determined by the number of distinct values as opposed to the size of the table.

I'll start by throwing 10 million rows into a table:

DROP TABLE IF EXISTS dbo.[Ports];

CREATE TABLE dbo.[Ports] (
    PortName VARCHAR(100),
    Filler VARCHAR(200)
);

INSERT INTO dbo.[Ports] WITH (TABLOCK)
SELECT TOP (10000000)
'Port ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 80 AS VARCHAR(2))
, REPLICATE('Z', 200)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);

CREATE INDEX IX_PortName ON [Ports] (PortName);

If I write the query like this:

SELECT DISTINCT (PortName)
FROM [Ports]
ORDER BY PortName ASC;

It takes 1329 ms of CPU to finish on my machine with the following plan:

That query plan isn't necessarily bad, but SQL Server is scanning all ten million rows just to return 80 distinct ones. There's a more efficient algorithm available but it takes some effort to get. It's very fast to get the minimum value from an index. It's also very fast to get the next value from an index. Both are just a handful of logical reads. Instead of reading the entire index what if we could effectively read just the distinct values and skip ahead to the next ones?

That can be done with recursive SQL. Paul White describes the approach here. Below is T-SQL written against your table:

WITH RecursiveCTE
AS
(
    -- Anchor
    SELECT TOP (1)
        PortName
    FROM dbo.[Ports] AS T
    ORDER BY
        T.PortName

    UNION ALL

    -- Recursive
    SELECT R.PortName
    FROM
    (
        -- Number the rows
        SELECT 
            T.PortName,
            rn = ROW_NUMBER() OVER (
                ORDER BY T.PortName)
        FROM dbo.[Ports] AS T
        JOIN RecursiveCTE AS R
            ON R.PortName < T.PortName
    ) AS R
    WHERE
        -- Only the row that sorts lowest
        R.rn = 1
)
SELECT *
FROM RecursiveCTE
OPTION (MAXRECURSION 0);

The query takes 0 ms of CPU time on my machine. IO is very low as well:

Table 'Ports'. Scan count 81, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If I increase the number of rows in the table to 100 million, the original query now takes 17203 ms of CPU time. The recursive query still takes 0 ms of CPU time. You can solve your query performance problem just by creating an index and writing some fancy code. In this case, there's no real need to implement caching in some other layer.

Answer 7

You mention running the query can take 10-15 seconds, but suggest "running some script at certain intervals... to offload querying". This stands out as meeting two very different requirements:

• your current solution provides real-time data in the UI
• your proposal allows the data to be delayed in the UI

You need to determine the requirement, specifically your target latency for a new distinct PortName to appear in the UI dropdown.

• If it is "real-time", you need some variation of a synchronous query. Other answers cover optimizing the query performance but your UI will always be as slow as your best query optimization.

• If it is "X seconds", you should modify the code backing your internal web site to use an asynchronous query. Populate the UI from an application variable, not from the database. Use a background task to update the variable based on your desired latency. The task trigger can be:

  • a simple check that updates the data only when the data is stale (you still get the 10-15 second delay, but only once every X seconds until it is stale)
  • a timer every X seconds (assuming X is large as we should not swamp the database with continuous refreshes)
  • something more sophisticated based on both of the above plus other application hints that someone may navigate to the query page soon (they login, a page loads, it's the morning reporting rush hour, etc.)

  You should still optimize your query using the other answers provided, and perhaps including a where clause so that you only inspect new records added since the last refresh and then merge the results in your application code.

  Blasphemy to propose an application change in a database forum... but the ultimate database optimization is to not use the database at all.

Answer 8

Normalization seems the answer, but there's your comment "The main issue regarding normalization is that I'm sadly not entitled to change the database structure itself in this project. It's basically a replicated and indexed version coming out of another system. :) –"

So, someone just copied the data and dumped it into a table. But why does it need to stay that way? This is a neat little project: Sit down with your teams your team and discuss which tables and relationships would serve your users best. Then take your time cleaning up a copy of the imported table.

Once you have a clean copy, import the data into the tables you decided upon. Set up a test environment and test the new tables thoroughly, fine-tuning with indexes etc. When the tests are successful, offer you users the opportunity to test out the new setup, or keep on using the old one. Once the majority of your users prefer the new data, discontinue the use of the old table but keep it around.

When you are notified that the other system has made updates, use the old table to pinpoint the changes and add them to your new data structure.