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:
-- 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.
- -- 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.
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:
-- 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<>'')
- -- 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<>'')
For usability, we put a message just below the header which was an expression detailing what the report was displaying, so for example:
-- 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."
- -- 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."
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".