Consider Indexes on your table variables.
As the report writer you can’t control the indexes in your database, but you can create indexes on temporary tables and table variables.
You may think that it really does not matter because you only have 50 rows in your table variable, at the time you create your report, in your development environment.
But the other consideration is that it does not cost much to create an index on a small table, either, and it helps ensure that columns you thought are unique really are unique.
Here is a sample index on a table variable:
declare @t table
(OrderID int primary key nonclustered,
RequiredDate datetime not null,
ShippedDate datetime null,
unique clustered (RequiredDate, OrderID))
Use Select Top 1 when possible
When querying tables or table variables without indexes, there are many times when you know that the result is unique within that table. In this case, you can explicitly document this fact and make your queries twice as fast on average by specifying the Top 1 clause.
On average this makes your query twice as fast because it on average has to only scan half the table before finding the row it you are looking for.
Render as little data as possible to SQL Server Reporting Services.
In other words the Where statements should be in SQL Server rather than Reporting Services as much as possible.
This can save a large amount of time by saving on network traffic for some reports.
Avoid select *
You should specify just the columns you need unless you really need all the columns.
Use exists instead of count(*) > 0
Have you ever thought about this?
The SQL Server optimizer is a compromise between smart and fast. It can do many things, but some are left to you. This is an example. It is different from a C++ compiler in this way–the optimizer can be invoked at run-time,
but the C++ compiler is never run by the end user of the compiled application, so it can be as slow and optimizing as it wants to be, in contrast to SQL Server.
Schedule similar reports to run concurrently
This is my favorite trick.
I have three reports that run hourly. They differ only by the parameter, which specifies the unit in the hospital that the report is interested in. I have all three reports scheduled concurrently. Why? Because that way the data that is pulled from the disk into memory can be shared between all three reports. Have you ever noticed that when you run a query the second time it’s faster? This is the same principle.
For temporary tables, Cast to the smallest datatype you need
Our database stores dates as DateTime, but I only need to display the date as a SmallDateTime because the exact second does not matter on the report. Therefore whenever I build a temporary table, I look for opportunities to save memory (and the associated costs of allocation, moving within memory, bandwidth, and deallocation) by converting to the smallest datatype that I need in my report. I also do some string-truncations (specific to my application) before writing to the temp table variable.
For the same reason I look to cast to the smallest numeric types that I need.
Use business knowledge in Where clauses when possible
For example if I know that widget B is only made at location C you may use that in a where clause to speed things up.
Conclusion
Please note that thios is not an all-inclusive list. I have shared a few ideas that I have encountered to speed up your reports, especially ideas that I have not read about often.
]]>