SSRS: Performance Improvements: SELECT TOP

What?
This article details a method we used on a search form in a SQL Server Reporting Services (SSRS) environment. The requirement (amongst others) was that the report ran in less than a few seconds.

Analyzing the report through the ReportServer databases (internal auditing) only showed how long the SQL query would take to recover the data and present it in a report, which would average about 0.4 seconds.

We're obviously not interested in shaving off time from 0.4 seconds; no, we're more interested in the fact that 0.4 Microsoft seconds seems to translate to about 10 minutes in the real world. Where do the extra 9 minutes come from? Not the SQL as this runs in 00:00 seconds in SQL Server Management Studio. Not the internal processing and rendering as the report server says it did this in 0.4 seconds. So where's the problem?

How?
Left to its own devices, a search for "SMITH" as a surname would make the report spin for about 10 minutes without the possibility to cancel (link not working!?) and eventually would display circa 3000 matching results. If I attempted to check this in "preview" mode using Business Intelligence Development Studio (BIDS / VS2008), the IDE would crash and close without warnings or notifications.

We found that if the query only returned 1000 rows, it completed and displayed in just under 1 minute. If limited to 500 rows, it would complete in just under 40 seconds. and if it was told to return no more than 100 rows, it would complete in under 10 seconds (7.5 seconds).

We played with hardware memory, report layouts and a few days worth of other solutions dotted about the web but the biggest improvement was the "SELECT TOP 100" brain wave:
copyraw
-- DataSet1:
SELECT TOP 100 
	Reference, Forenames, Surname, DOB
FROM
	PersonsTable
WHERE
	(Reference = @GivenRef OR @GivenRef ='')
	AND (Forenames LIKE @GivenForenames + '%' OR @GivenForenames = '')
	AND (Surname LIKE @GivenSurname OR @GivenSurname = '')
	AND (CONVERT(VARCHAR(10),DOB, 103) = @GivenDOB OR @GivenDOB = '')
	AND (@GivenRef<>'' OR @GivenForenames<>'' OR @GivenSurname<>'' OR @GivenDOB<>'')

-- The TOP 100 is what improved our performance.
-- If you can't find the person in your database in the top 100 of your search
-- then you must be limited on what you know about this person... Refine your search.

-- This "where" clause returns zero rows if dob, reference,
-- forename and surname parameters are not populated.
  1.  -- DataSet1: 
  2.  SELECT TOP 100 
  3.      Reference, Forenames, Surname, DOB 
  4.  FROM 
  5.      PersonsTable 
  6.  WHERE 
  7.      (Reference = @GivenRef OR @GivenRef ='') 
  8.      AND (Forenames LIKE @GivenForenames + '%' OR @GivenForenames = '') 
  9.      AND (Surname LIKE @GivenSurname OR @GivenSurname = '') 
  10.      AND (CONVERT(VARCHAR(10),DOB, 103) = @GivenDOB OR @GivenDOB = '') 
  11.      AND (@GivenRef<>'' OR @GivenForenames<>'' OR @GivenSurname<>'' OR @GivenDOB<>'') 
  12.   
  13.  -- The TOP 100 is what improved our performance. 
  14.  -- If you can't find the person in your database in the top 100 of your search 
  15.  -- then you must be limited on what you know about this person... Refine your search. 
  16.   
  17.  -- This "where" clause returns zero rows if dob, reference, 
  18.  -- forename and surname parameters are not populated. 

In addition, we also need a second dataset in our report which gets the total count of all the rows matching the selection criteria (the parameters). The concern was that this would make the report just as slow but the count only returns a number to be used internally and did not need rendering (only 1 row returned for a count). The dataset for this is the same as for DataSet1 but just the count is extracted:
copyraw
-- DataSet2:

SELECT 
	COUNT(*) AS myCount
FROM
	PersonsTable
WHERE
	(Reference = @GivenRef OR @GivenRef ='')
	AND (Forenames LIKE @GivenForenames + '%' OR @GivenForenames = '')
	AND (Surname LIKE @GivenSurname OR @GivenSurname = '')
	AND (CONVERT(VARCHAR(10),DOB, 103) = @GivenDOB OR @GivenDOB = '')
	AND (@GivenRef<>'' OR @GivenForenames<>'' OR @GivenSurname<>'' OR @GivenDOB<>'')
  1.  -- DataSet2: 
  2.   
  3.  SELECT 
  4.      COUNT(*) AS myCount 
  5.  FROM 
  6.      PersonsTable 
  7.  WHERE 
  8.      (Reference = @GivenRef OR @GivenRef ='') 
  9.      AND (Forenames LIKE @GivenForenames + '%' OR @GivenForenames = '') 
  10.      AND (Surname LIKE @GivenSurname OR @GivenSurname = '') 
  11.      AND (CONVERT(VARCHAR(10),DOB, 103) = @GivenDOB OR @GivenDOB = '') 
  12.      AND (@GivenRef<>'' OR @GivenForenames<>'' OR @GivenSurname<>'' OR @GivenDOB<>'') 

For usability, we put a message just below the header which was an expression detailing what the report was displaying, so for example:
copyraw
-- If setting the limit to 100 records
=IIf(
	Sum(Fields!myCount.Value, "DataSet2")>100, 
	"» Displaying the first " & 
		CountRows("DataSet1") & 
		" of " & 
		Sum(Fields!myCount.Value, "DataSet2") & 
		" result" & 
		IIF(Sum(Fields!myCount.Value, "DataSet2")=1, "", "s") & 
		" returned.", 
	"» " & CountRows("DataSet1") & 
		" matching record" & 
		IIF(Sum(Fields!myCount.Value, "DataSet2")=1, "", "s") & 
		" found."
)

-- Search for Joel Lipman yields "1 matching record found."
-- Search for SMITH yields "Displaying the first 100 of 2732 results returned."
  1.  -- If setting the limit to 100 records 
  2.  =IIf( 
  3.      Sum(Fields!myCount.Value, "DataSet2")>100, 
  4.      "» Displaying the first " & 
  5.          CountRows("DataSet1") & 
  6.          " of " & 
  7.          Sum(Fields!myCount.Value, "DataSet2") & 
  8.          " result" & 
  9.          IIF(Sum(Fields!myCount.Value, "DataSet2")=1, "", "s") & 
  10.          " returned.", 
  11.      "» " & CountRows("DataSet1") & 
  12.          " matching record" & 
  13.          IIF(Sum(Fields!myCount.Value, "DataSet2")=1, "", "s") & 
  14.          " found." 
  15.  ) 
  16.   
  17.  -- Search for Joel Lipman yields "1 matching record found." 
  18.  -- Search for SMITH yields "Displaying the first 100 of 2732 results returned." 
We would not offer the feature to browse to the second set of 100 results. The end-user should be able to refine the search enough to get the records they need.

Granted this may not be a viable solution for your case, but this did what we were asking and is a serious improvement to the user experience. For one thing, the accidental search for "Jane" and pressing the Enter key, no longer goes off into the interminable loop that is "loading".
Category: SQL Server Reporting Services :: Article: 552

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.