 | Hi All
Here is a query involving 3 tables with simple joins. The query fetches
data for 12 month period range. As seen in the query plan, indexes are
being used on each of the tables. Table CustomerForecastSourcing has
around 14 lakh records, table art has around 7 lakh records and table
own has around 6 thousand records
INSERT INTO #tmpcfc (agglvl, percod, forecast, logforecast, dsptyp,
dspcod, mdlcod, artcod, owncod, supcod, liocod, hmsfct,cntcod,
Indicator)
SELECT 'ART'
, FS.Period
, sum(IsNull(FS.Forecast,0))
, sum(FS.LogisticsForecast)
, FS.SupplierType
, FS.SupplierCode
, a.mdlcod
, FS.ForecastCode
, FS.CustomerCode
, IsNull(o.mstown, o.owncod)
, o.liocod
, o.hmscod
, o.cntcod
,FS.Indicator
FROM CustomerForecastSourcing FS
, art a
, own o
WHERE FS.Company = "AD"
AND FS.Division = "2"
AND FS.RunType = 'LST'
AND FS.ForecastType = 'ART'
AND FS.Period >= 200501
AND FS.Period <= 200512
AND a.compny = "AD"
AND a.dvscod = "2"
AND a.artcod = FS.ForecastCode
AND o.compny = "AD"
AND FS.SupplierType = o.owntyp
AND FS.SupplierCode = o.owncod
GROUP BY FS.Period, FS.SupplierType, FS.SupplierCode, a.mdlcod,
FS.ForecastCode, FS.CustomerCode, IsNull(o.mstown, o.owncod), o.liocod,
o.hmscod, o.cntcod,FS.Indicator
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
FROM TABLE
CustomerForecastSourcing
FS
Nested iteration.
Using Clustered Index.
Index : pk_CustomerForecastSourcing
Forward scan.
Positioning by key.
Keys are:
Division ASC
Company ASC
Period ASC
RunType ASC
ForecastType ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
art
a
Nested iteration.
Using Clustered Index.
Index : pk_art
Forward scan.
Positioning by key.
Keys are:
dvscod ASC
compny ASC
artcod ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
own
o
Nested iteration.
Using Clustered Index.
Index : pk_own
Forward scan.
Positioning by key.
Keys are:
compny ASC
owncod ASC
owntyp ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is INSERT.
The update mode is direct.
FROM TABLE
Worktable1.
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
#tmpcfc
Using I/O Size 2 Kbytes for data pages.
Parse and Compile Time 1.
SQL Server cpu time: 100 ms.
Table: #tmpcfc______00002620013775130 scan count 0, logical reads:
(regular=899546 apf=0 total=899546), physical reads: (regular=0 apf=0
total=0), apf IOs used=0
Table: CustomerForecastSourcing scan count 1, logical reads:
(regular=15192 apf=0 total=15192), physical reads: (regular=8 apf=150
total=158), apf IOs used=105
Table: art scan count 101595, logical reads: (regular=418989 apf=0
total=418989), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
Table: own scan count 101595, logical reads: (regular=322099 apf=0
total=322099), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
(101586 rows affected)
Table: Worktable1 scan count 1, logical reads: (regular=642606 apf=0
total=642606), physical reads: (regular=12 apf=2970 total=2982), apf
IOs used=2970
Total writes for this command: 55244
Execution Time 394.
SQL Server cpu time: 39400 ms. SQL Server elapsed time: 382456 ms.
Any ideas to improve this query further ????
Regards
Prateek
|
|
