This module discusses the different techniques presented throughout this course, including the grouping techniques and functions.These can be combined to create a query that will produce some very meaningful results.
In this module, you will be presented with the class project, in which you will retrieve a listing of sales by sales associate.
To make it more challenging, you will only want those sales associates that have sold the product with the highest sales based on overall quantity sold.
(SSRS) SQL Server Reporting Services is a server-based report generation software system from Microsoft which is administered by means of a web interface. SSRS can be used to prepare and deliver a variety of interactive and printed reports. SSRS competes with Crystal Reports and other business intelligence tools like Microstrategy and Cognos.
Tuning an individual query means examining the structure of the query (and subqueries), the SQL syntax, to discover whether you have designed your tables to support fast data manipulation and written the query in an optimum manner, allowing your DBMS to manipulate the data efficiently.
Queries do many things, from
- adding entirely new records to tables,
- to updating existing records,
- to pulling data out of multiple related tables for reporting purposes.
It is this last part that many people think of when discussing
optimizing queries, but in fact, a database must be viewed as a whole entity or you risk speeding up one piece while negatively impacting another.
As this chapter demonstrates, there are techniques (particularly when optimizing the queries of other people) that are essentially free, while others (specifically creating new indexes) can be applied only at a price.
Adding indexes may very well speed up whatever it is you are trying to speed up, but it may cripple some other piece of the application. Deleting an index may speed up one part of the application but cripple another.
The intention here is not to cripple you with fear of doing anything because you may cause more problems than you fix, but simply to cause you to always think about the implications of your changes, beyond the area you are working on.
Whenever you make a change, particularly on a large system, you need to keep your ears open to users complaining that the application seems slower lately. Where possible, record the changes that you make so that they can be backed out if necessary.