Alternate of materialized view to avoid excessive table reads where Min/Max is required

  • Hi,

    I have a similar situation as in the following code where i want to materialize future due orders to avoid excessive reads in the procedure which at the moment i am handling using co-related sub-queries or even APPLY.

    USE [AdventureWorks2012] -- SQL 2012 Std Edition

    GO

    --CREATE VIEW Sales.future_order_Due_Date

    --WITH SCHEMABINDING

    --AS

    SELECT [CustomerID]

    , [SalesPersonID]

    , MIN([OrderDate]) AS OrderDate

    , MIN([DueDate]) AS DueDate

    , MIN([ShipDate]) AS ShipDate

    FROM [Sales].[SalesOrderHeader]

    GROUP BY [CustomerID], [SalesPersonID]

    HAVING MIN([DueDate]) > '2007Jan01'--getdate()

    So what is the alternate option available to avoid excessive reads from table in every query and in many SPs wherever i need to use this logic?

    Thanks.

  • Post the actual execution plan for the query as a .sqlplan attachment.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • We need the actual table definition too, with indexes. Also, is that a character field stored to look like a date or an actual date data type?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I have prepared and pitched the question based on AdventureWorks2012 sample database.

    Question is for any solution to handle similar situation and not to optimize this or similar query.

    If you still need table structure and plan then i can send one - no issue.

    But i am just asking what is your approach to handle such scenarios.

    Thanks.

  • Order of preference:

    Tune the query

    Adjust the indexing

    Materialized view

    Staging table

    Analysis services

    But the order of preference mostly also reflects how often these are used and how well each works (although SSAS works very well, it's such a specialized approach to data, I don't put it higher on the list). You can also insert, at each step, validate the business requirements, because so often I've been asked to do stuff that the business doesn't really want, but has given me an ill-defined guess at what they do want.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • iBar (8/13/2013)


    So what is the alternate option available to avoid excessive reads from table in every query and in many SPs wherever I need to use this logic?

    Correct indexing can go a long way to solving these types of problems. Using the AdventureWorks query:

    -- A useful indexed view

    CREATE VIEW dbo.CustomersAndSalesPeople

    WITH SCHEMABINDING

    AS

    SELECT

    SOH.CustomerID,

    SOH.SalesPersonID,

    NumRows = COUNT_BIG(*)

    FROM Sales.SalesOrderHeader AS SOH

    GROUP BY

    SOH.CustomerID,

    SOH.SalesPersonID;

    GO

    CREATE UNIQUE CLUSTERED INDEX cuq

    ON dbo.CustomersAndSalesPeople

    (CustomerID, SalesPersonID);

    -- Useful indexes

    CREATE INDEX nc1

    ON Sales.SalesOrderHeader

    (CustomerID, SalesPersonID, DueDate);

    CREATE INDEX nc2

    ON Sales.SalesOrderHeader

    (CustomerID, SalesPersonID, OrderDate);

    CREATE INDEX nc3

    ON Sales.SalesOrderHeader

    (CustomerID, SalesPersonID, ShipDate);

    -- Rewritten query

    SELECT

    CSP.CustomerID,

    CSP.SalesPersonID,

    OD.OrderDate,

    DD.DueDate,

    SD.ShipDate

    FROM dbo.CustomersAndSalesPeople AS CSP WITH (NOEXPAND)

    CROSS APPLY

    (

    SELECT TOP (1)

    SOH2.DueDate

    FROM Sales.SalesOrderHeader AS SOH2

    WHERE

    SOH2.CustomerID = CSP.CustomerID

    AND EXISTS (SELECT SOH2.SalesPersonID INTERSECT SELECT CSP.SalesPersonID)

    ) AS DD

    CROSS APPLY

    (

    SELECT TOP (1)

    SOH3.OrderDate

    FROM Sales.SalesOrderHeader AS SOH3

    WHERE

    SOH3.CustomerID = CSP.CustomerID

    AND EXISTS (SELECT SOH3.SalesPersonID INTERSECT SELECT CSP.SalesPersonID)

    ) AS OD

    CROSS APPLY

    (

    SELECT TOP (1)

    SOH4.ShipDate

    FROM Sales.SalesOrderHeader AS SOH4

    WHERE

    SOH4.CustomerID = CSP.CustomerID

    AND EXISTS (SELECT SOH4.SalesPersonID INTERSECT SELECT CSP.SalesPersonID)

    ) AS SD

    WHERE

    DD.DueDate > '20070101';

    Execution plan:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply