If you have a report query that joins multiple data sets together then consider using table variables to speed up query performance.
Here’s an example: I have a report with a Sales Team parameter that allows the user to select one more Sales Team to report on. The report then displays the open appointments, quotes, and opportunities owned by the Account Managers in those Sales Teams.
Typically I would write my query like this:
select metric_name = ‘Appointments’, metric_count = count(*)
from filteredappointment
where statecodename = ‘Open’
and createdby in (select systemuserid from filteredsystemuser where new_saleteamid in @Team)
union all
select metric_name = ‘Opportunities’, metric_count = count(*)
from filteredopportunity
where statecodename = ‘Open’
and createdby in (select systemuserid from FilteredSystemUser where new_saleteamid in @Team)
union all
select metric_name = ‘Quotes’, metric_count = count(*)
from filteredquote
where statecodename = ‘Open’
and createdby in (select systemuserid from filteredsystemuser where new_saleteamid in @Team)
But recently I’ve found with more complex queries I’ve needed to take a slow performing component of my query and specifically direct SQL to process that component and to then place the results in a table variable for me to refer to later in the query. In the above query each of the 3 query components refer to the list of users that should be reported on. By using a Table variable I can query for and store that user list as the first step in my query and then join that result set into each of the 3 queries. This approach looks like this:
DECLARE @USERS_SELECTED TABLE(USERS varchar(50))
INSERT INTO @USERS_SELECTED (USERS)
(select systemuserid from filteredsystemuser where new_saleteamid in @Team)
select metric_name = ‘Appointments’, metric_count = count(*)
from filteredappointment a
inner join @USERS_SELECTED x on a.createdby = x.USERS
where statecodename = ‘Open’
union all
select metric_name = ‘Opportunities’, metric_count = count(*)
from filteredopportunity o
inner join @USERS_SELECTED x on o.createdby = x.USERS
where statecodename = ‘Open’
union all
select metric_name = ‘Quotes’, metric_count = count(*)
from filteredquote q
inner join @USERS_SELECTED x on q.createdby = x.USERS
where statecodename = ‘Open’
Now the above example is very simplistic, but this approach has proved very successful for me. If your SQL statement contain a number of selects which then get aggregated up and joined together, then you will benefit from this too. If your query is performing poorly run each of the selects in isolation, find the slow running one and try running it at the start of your SQL and place the result set into a table variable. You might start to find that these sorts of complex SQL statements become a lot more readable as well if you follow this approach. I have one query where I have followed this approach for each of the ten selects embedded in the query giving me ten of these work tables which the query then joins together in a very simple SQL statement at the end of the query. Very easy to read and very easy to troubleshoot.
I hope this helps.
GT // CRM 