On 26-Aug-2014 06:58 -0500, Jim Oberholtzer wrote:
On 25-Aug-2014 17:45 -0500, CRPence wrote:
<<SNIP>>
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>>
<<SNIP>> I believe all of the SQL presented would force a table scan
<<SNIP>>
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
)
INCLUDE( SUM(SubTotal)
, 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_.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.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 on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.