Creating and Managing SQL Scripts - Microsoft Business Intelligence Tools for Excel Analysts - page 207

Additional tips and tricks
Several other factors are involved in performance optimization:
➤ Temp DB usage: As the name indicates, Temp DB is a temporary database that the SQL
Server database engine uses for performing database operations. It's important to under-
stand the role this database plays in queries and SQL scripts, because it impacts your system
performance. For example, if you have a large query with a Union operator, SQL Server may
create a temp table for that operation. If the Union is used in an Insert operation, you can
easily achieve the same result by breaking your Insert statement into two.
➤ Break up complex queries: Avoid building very complex queries in one SQL statement.
Generally, the SQL Server optimizer is quite advanced and can handle a large number of joins
in a query; however, it helps to break things down into multiple steps if you notice slow per-
formance. Find the statements that are performing badly and experiment by breaking them
into several scripts.
➤ Avoid multiple passes: Avoid updating the same table over and over in the same script. For
example, if a table has several columns that need to be updated, a common approach begin-
ners take is to write five update statements for each column. Following this approach means
you're deleting and inserting the data in the same table five times (because each update is
effectively a delete and an insert). When you have situations like these, take a step back and
consider a more streamlined design.
SQL Solutions to Common
Analytics Problems
As you start diving into using SQL for building analytics, you'll find a lot of requests and questions
have a similar pattern. Business users often want to spin the data in similar ways to slice and dice the
output and tease out answers that can result in better business decisions. We outline some of the
more common questions in this section and describe how you can answer them using SQL.
Creating an Active Members Report
The value of a company often depends on the number of active members it has. Take the example of
a social media site. Analysts always talk about monthly active members on the site and the growth,
reduction, or flattening out of that figure.
Producing an Active Members Report may seem like an easy undertaking on the surface, but that is not
the case. Things get even more complex if you want that report to show a running count of active mem-
bers over time. To produce this type of report, it's necessary to effectively take a count of active members
every day and store that number somewhere. Luckily there is a solution to this problem using SQL.
For the purposes of this example, consider the table shown in Figure 10-12. It represents a table of all
the members in the system with the MemberKey along with the StartDate and CancelDate. A Cancelled
flag also indicates if the member is active.