Temporary View using Common Table Expression In Sql server 2005

In this post, I will discuss on how to optimize join. Based on my experience. When you join record you need to join as fewer as possible otherwise it will result in performance. And please make sure you put the index on the key that you are using for join.

Within SQL Server 2005, You can use temporary view, It is not a table variable and it is not a temporary table. You also need to try to avoid cursor as well if possible.

First sample is a traditional join where I joined all the tables that required. In this sample EvenResult table has 20 million rows and I need to count the evenresult table based on the EventID that I get from the join criteria.

1. Join all tables./Traditional Way and it takes about 21secs

    SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            INNER JOIN
                EventResults er
            ON
                E.EventID = er.EventID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))

2. Using Temporary Views, In this query. I try to filter down all the required eventID based on the criteria and insert it into a temporary view(tblEvents). Once i get the filtered result then I joined it with EventResults table and It takes only 16secs considering the number of records.

WITH tblEvents(EventID) AS
(
            SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))
)

            SELECT
                COUNT(*) As Total
            FROM
                tblEvents te
            INNER JOIN
                EventResults er
            ON
                te.EventID = er.EventID
            GROUP BY
                er.MemberID
            HAVING
                COUNT(*) >= @MinGame
            AND
                COUNT(*) <= @MaxGame