CODEWITHSUNDEEP
sql-server-2005joinfiltersubquery
Dib
Dib

Reputation: 2083

SQL Server 2005 - Filter on Inner or Outer Query

Taking a SQL Server 2005 stored procedure which has an outer query that selects from a sub query can any one advise me if it the query is likely to return quicker if the parameter filters are applied to the inner query, or just apply the outer query and let the SQL query engine work out the best execution plan?

I can't put the actual query up as it contains business table/ view namesand columns, and quite a lot of joins, but in essence the general architecture is...

Psuedo Sql...

CREATE PROCEDURE usp_Blah_GetForDateRange
(
    @DateFrom   smalldatetime
,   @DateTo     smalldatetime
)
AS
BEGIN
    SELECT
        InnerQuery.MyField
    FROM
        (
            SELECT
                MyField
            FROM
                MyView
            JOIN SomeTableLargeTable ON ....

            JOIN AReferenceTable ON ...

            /* Apply date restriction here on the inner query? */
            WHERE
                EffectiveDate BETWEEN @DateFrom AND @DateTo
        ) InnerQuery

    JOIN
        YetAnotherLargeTable ON ....

    JOIN SomeMoreRefTables ON ....

    /* Or here on the outer? */
    WHERE
        EffectiveDate BETWEEN @DateFrom AND @DateTo;
END

MyView, SomeTableLargeTable and YetAnotherLargeTable all have around five or six million rows.

Thanks.

Upvotes: 0

Views: 67

Answers (1)

Dbloch
Dbloch

Reputation: 2366

A rule of thumb is the sooner you can exclude data from a query, the better. So without actually running the queries, I would say inner join

Upvotes: 1

Related Questions