Search code examples
sql.netsql-serverdatabaseservice

MS SQL Server Query caching

One of my projects has a very large database on which I can't edit indexes etc., have to work as it is.

What I saw when testing some queries that I will be running on their database via a service that I am writing in .net. Is that they are quite slow when ran the first time?

What they used to do before is - they have 2 main (large) tables that are used mostly. They showed me that they open SQL Server Management Studio and run a

SELECT * 
FROM table1 
JOIN table2

a query that takes around 5 minutes to run the first time, but then takes about 30 seconds if you run it again without closing SQL Server Management Studio. What they do is they keep open SQL Server Management Studio 24/7 so that when one of their programs executes queries that are related to these 2 tables (which seems to be almost all queries ran by their program) in order to have the 30 seconds run time instead of the 5 minutes.

This happens because I assume the 2 tables get cached and then there are no (or close to none) disk reads.

Is this a good idea to have a service which then runs a query to cache these 2 tables every now and then? Or is there a better solution to this, given the fact that I can't edit indexes or split the tables, etc.?

Edit: Sorry just I was possibly unclear, the DB hopefully has indexes already, just I am not allowed to edit them or anything.

Edit 2: Query plan

Solution

  • This could be a candidate for an indexed view (if you can persuade your DBA to create it!), something like:

    CREATE VIEW transhead_transdata
WITH SCHEMABINDING
AS
    SELECT
        <columns of interest>
    FROM
        transhead th
        JOIN transdata td
            ON th.GID = td.HeadGID;

GO

CREATE UNIQUE CLUSTERED INDEX transjoined_uci ON transhead_transdata (<something unique>);

    This will "precompute" the JOIN (and keep it in sync as transhead and transdata change).