Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

performance in different ways, but always in the wrong way—a query from a poorly designed or badly
designed table will always take longer to execute than on from a table that is well designed and
maintained.
You may not have appropriate permissions to manage indexes, but one thing you can do is to design
a query to use the indexes that do exist. Here are a few tips to help you increase performance and
decrease the time it takes to return results to your reports.

• SELECT: Only return the columns that are absolutely needed in your reports.


• JOIN: Only join tables that you need and use existing indexed columns in join
  conditions.


• WHERE: Use indexed columns in the order in which they are defined and
  eliminate the use of the LIKE operator using the wildcard (%) in front of the filtered
  value.

If your query is not performing as well as you would like, you may be able to ask your DBAs to run
some performance checks on the table(s) that you are using in your query. They may just need to modify
existing indexes to include the columns used in your queries. The cure could even be as simple as
creating a maintenance plan on the database to update the statistics. Either way, try to use these tips
when writing your queries. Performance-tuning queries is a large subject and many books are available
to help you write optimized queries, such as SQL Server 2012 Query Performance Tuning by Grant
Fritchey (Apress 2012).

Column and Table Aliasing

Column and table aliasing does not actually make your queries run more efficiently. However, it does
make them easier to read and quicker to write. Aliasing lets you use an abbreviated (or more descriptive)
label for columns and tables. This way, you don’t have to type out the entire name of a table every time
you use a column from it, or you can assign a more appropriate name for a column. Using the AS
keyword, we tell SQL Server to alias the field or table as some other label.

SELECT
...
, DATENAME(mm, T.ChargeServiceStartDate) AS [Month]
...
INNER JOIN ServicesLogCtgry AS SLC ON S.ServicesLogCtgryID = SLC.ServicesLogCtgryID

This statement uses the DATENAME function to alias the Trx table’s ChargeServicesStartDate as the
Month. The next line shows how you can alias a table named ServicesLogCtgry so you can refer to it as
SLC. This way, whenever you need a column from the ServicesLogCtgry table, you can reference the alias
followed by a period, then the column name.

Testing the Procedure

The next step is to grant execute privileges for the stored procedure in SSMS by navigating to the
database Pro_SSRS and then expanding the Programmability folder. From here, select Stored Procedures,
right-click Emp_Svc_Cost, and finally select Properties. A Permissions property page will allow you to add
the public role and grant execute permission to any group or user you desire. In this case, click Add on
the Permissions page, find Public in the list of available users, and grant the Execute permission. (We’re
sure the humor of this wasn’t lost on the developer, who knew someone would grant a public execution.)