1

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.

  • 3
    Can you give us some clues about "all the summary queries"? Maybe even some code? – Aaron Bertrand Oct 18 '15 at 11:30
  • I'm on my phone now, but there's nothing fancy, just a main query (select fields from tables inner join...) then I use a ; to end it, then I use this same query but in a group query to count how many items are available in each color (return colorId and count), same for cityId, there are some slightly different queries made to return which features are available (wifi, usb3, etc).. Thats it! The difference between the query that returns the rows is that in this case, I use offset X rows fetch next... For pagination. Thanks! – Daniele Oct 18 '15 at 21:05
  • One option is using a temp table for your initial result set and run the summary query against it. That would limit the calls directly to the db. You will need to – tommyfly Oct 19 '15 at 7:22
  • Sorry, posted too soon. You need to consider memory if you use temp objects,depending on the size of the results and number of users. – tommyfly Oct 19 '15 at 7:23
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.