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

• iBar

  SSCertifiable

  Points: 5872

  More actions

  August 13, 2013 at 9:09 am

  #281847

  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.

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  August 13, 2013 at 9:46 am

  #1641021

  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]

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  August 13, 2013 at 11:02 am

  #1641046

  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

• iBar

  SSCertifiable

  Points: 5872

  More actions

  August 14, 2013 at 2:00 am

  #1641246

  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.

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  August 14, 2013 at 4:44 am

  #1641293

  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

• Paul White

  SSC Guru

  Points: 150467

  More actions

  August 16, 2013 at 5:21 am

  #1642091

  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:

  

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi