MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » August 2014

Re: Another SQL question


On 26-Aug-2014 06:58 -0500, Jim Oberholtzer wrote:
On 25-Aug-2014 17:45 -0500, CRPence wrote:

select YEAR(OrderDate) as YR
, MONTH(OrderDate) as MO
, SUM(SubTotal) as MOSUBTOT
, SUM(DscTotal) as MODSCTOT /* additional summary */
from Sales.SalesOrderHeader as SOH
group by YEAR(OrderDate) /* note: grouping unchanged */
, MONTH(OrderDate)
order by 1, 2

<<SNIP>> I believe all of the SQL presented would force a table scan

Each being an aggregate query against all data [no WHERE clause], all of the data will [necessarily] be read, thus almost surely via the arrival access path for the implementation. Irrespective the naming likely being /full table scan/ generically, that particular full-table read is performed without any actual /scan/ being performed to reduce the number of records selected [again, no WHERE clause], instead having aggregation effect a reduction to the number of rows returned. I suspect the only drastic improvement over that implementation, would come from having an EVI with aggregate support to have all of the [essentially pre-calculated] SUMs included [per having specified multiple INCLUDE values for the specific SUM()]. Perhaps the following [untested] CREATE INDEX?:

create encoded vector index Sales.IxSOHdr
ON Sales.SalesOrderHeader
( YEAR(OrderDate) as OrderDateYear for OrdYr ASC
, MONTH(OrderDate) as OrderDateMonth for OrdMo ASC
, SUM(DscTotal)

Derived key access paths, whether created by explicit CREATE INDEX requests or as Maintained Temporary Indexes (MTI), should enable the grouping queries [grouped by the date expressions] to operate more efficiently if either there were some selection added on those date derivations or the amount of data was large and optimization was either *FIRSTIO or _FOR few ROWS_.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact