Sql-server – How to efficiently a query to return rows plus a summary

performancequery-performancesql server

I need to write a query for a SQL Server 2012 database that will return rows to be presented to a search results page, this page has 12 filters that the user can use to narrow the results.

I create the query that returns the rows dynamically, then I use this same query as a sub query for all the summary queries that I need. The final piece becomes a pretty big string that gets sent to the server just once, it returns a multiple result set, instead of multiple calls, but I think there should be a more efficient way for doing this.

Right now it seems to be working ok, but I'm not sure what would happen when it goes live and hundreds of users starts to hit it.

Any suggestion or code samples would be very appreciated.

Best Answer

Assuming you're not return millions of rows, you could output the results into a temporary table, then run the "summary" query on that temp table.

As an example, I'll create a table with 10,000 rows of data, then return 3,333 rows, with a summary query:

USE tempdb;

IF OBJECT_ID('dbo.MyData') IS NOT NULL
DROP TABLE dbo.MyData;

CREATE TABLE dbo.MyData
(
    MyDataID int NOT NULL
        CONSTRAINT PK_Mydata
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(30) NOT NULL
);

;WITH cte AS (
    SELECT v.num
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.MyData (SomeData)
SELECT CONVERT(varchar(30), CRYPT_GEN_RANDOM(c1.num + 1))
FROM cte c1
    CROSS JOIN cte c2
    CROSS JOIN cte c3
    CROSS JOIN cte c4;

This is the code your client would send. It first creates a temp table, then populates the rows into the temp table, using the OUTPUT clause to return the rows immediately to the client. Then a simple COUNT() of the rows is returned.

IF OBJECT_ID('tempdb..#MyData') IS NOT NULL
DROP TABLE #MyData;
CREATE TABLE #MyData
(
    MyDataID int NOT NULL
        PRIMARY KEY CLUSTERED
    , SomeData varchar(30) NOT NULL
);

INSERT INTO #MyData (MyDataID, SomeData)
OUTPUT inserted.*
SELECT *
FROM dbo.MyData md
WHERE md.MyDataID % 3 = 0;

SELECT COUNT(1)
FROM #MyData;

Comparing the query plans for the the two possible "summary" queries shows any possible improvements; which would only be more exaggerated with a more complicated source query:

SELECT COUNT(1)
FROM #MyData;

SELECT COUNT(1)
FROM dbo.MyData md
WHERE md.MyDataID % 3 = 0;

The plans:

enter image description here

Related Question