December 6, 2011 at 9:00 am
I have following query running very very slow. Though I have some indexes created on this table but was wondering what new Index I can create for this query to improve the performance.
select sum(GBAPYC) as BAL00, sum(GBAN01) as BAL01, sum(GBAN02) as BAL02, sum(GBAN03) as BAL03, sum(GBAN04) as BAL04, sum(GBAN05) as BAL05, sum(GBAN06) as BAL06, sum(GBAN07) as BAL07, sum(GBAN08) as BAL08, sum(GBAN09) as BAL09, sum(GBAN10) as BAL10, sum(GBAN11) as BAL11, sum(GBAN12) as BAL12, sum(GBAN13) as BAL13, sum(GBAN14) as BAL14, sum(GBBORG) as BAL55, sum(GBBAPR) as BAL56, sum(GBBREQ) as BAL57
from JDE_PRODUCTION.PRODDTA.F0902
where ((((GBCTRY = 20 and GBFY = 11 and GBLT = 'AA')))) and (GBCO = '00075' AND GBMCU = ' 750' AND GBOBJ = '0600 ' AND GBSUB = '1000 ')
December 6, 2011 at 9:07 am
Can you upload the actual execution plan?
Along with the current indexe(s) on that table (including keys).
December 6, 2011 at 12:19 pm
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
SET STATISTICS PROFILE ON 5 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETSTATON 0 NULL
(1 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
---------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
SET SHOWPLAN_ALL ON 6 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETON 0 NULL
(1 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------
select sum(GBAPYC) as BAL00, sum(GBAN01) as BAL01, sum(GBAN02) as BAL02, sum(GBAN03) as BAL03, sum(GBAN04) as BAL04, sum(GBAN05) as BAL05, sum(GBAN06) as BAL06, sum(GBAN07) as BAL07, sum(GBAN08) as BAL08, sum(GBAN09) as BAL09, sum(GBAN10) as BAL10, sum(GBA 7 1 0 NULL NULL 1 NULL 1.0 NULL NULL NULL 6.4104451E-3 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1128]=0) then NULL else [Expr1129], [Expr1003]=If ([Expr1130]=0) then NULL else [Expr1131], [Expr1004]=If ([Expr1132]=0) then NULL else [Expr1133], [Expr1005]=If ([Expr1134]=0) then NULL else [Expr1135], [Exp 7 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1002]=If ([Expr1128]=0) then NULL else [Expr1129], [Expr1003]=If ([Expr1130]=0) then NULL else [Expr1131], [Expr1004]=If ([Expr1132]=0) then NULL else [Expr1133], [Expr1005]=If ([Expr1134]=0) then NULL else [Expr1135], [Expr1006]=If ([Expr1136 [Expr1002]=If ([Expr1128]=0) then NULL else [Expr1129], [Expr1003]=If ([Expr1130]=0) then NULL else [Expr1131], [Expr1004]=If ([Expr1132]=0) then NULL else [Expr1133], [Expr1005]=If ([Expr1134]=0) then NULL else [Expr1135], [Expr1006]=If ([Expr1136]=0) the 1.0 0.0 0.00000025 153 6.4104451E-3 [Expr1002], [Expr1003], [Expr1004], [Expr1005], [Expr1006], [Expr1007], [Expr1008], [Expr1009], [Expr1010], [Expr1011], [Expr1012], [Expr1013], [Expr1014], [Expr1015], [Expr1016], [Expr1017], [Expr1018], [Expr1019] NULL PLAN_ROW 0 1.0
|--Stream Aggregate(DEFINE:([Expr1128]=COUNT_BIG([F0902].[GBAPYC]), [Expr1129]=SUM([F0902].[GBAPYC]), [Expr1130]=COUNT_BIG([F0902].[GBAN01]), [Expr1131]=SUM([F0902].[GBAN01]), [Expr1132]=COUNT_BIG([F0902].[GBAN02]), [Expr1133]=SUM([F0902].[GBAN02]), 7 3 2 Stream Aggregate Aggregate NULL [Expr1128]=COUNT_BIG([F0902].[GBAPYC]), [Expr1129]=SUM([F0902].[GBAPYC]), [Expr1130]=COUNT_BIG([F0902].[GBAN01]), [Expr1131]=SUM([F0902].[GBAN01]), [Expr1132]=COUNT_BIG([F0902].[GBAN02]), [Expr1133]=SUM([F0902].[GBAN02]), [Expr1134]=COUNT_BIG([F0902].[GBAN 1.0 0.0 0.00000025 153 6.4104451E-3 [Expr1128], [Expr1129], [Expr1130], [Expr1131], [Expr1132], [Expr1133], [Expr1134], [Expr1135], [Expr1136], [Expr1137], [Expr1138], [Expr1139], [Expr1140], [Expr1141], [Expr1142], [Expr1143], [Expr1144], [Expr1145], [Expr1146], [Expr1147], [Expr1148], [Exp NULL PLAN_ROW 0 1.0
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([JDE_PRODUCTION].[PRODDTA].[F0902])) 7 4 3 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1000]), OBJECT:([JDE_PRODUCTION].[PRODDTA].[F0902]) [F0902].[GBAPYC], [F0902].[GBAN01], [F0902].[GBAN02], [F0902].[GBAN03], [F0902].[GBAN04], [F0902].[GBAN05], [F0902].[GBAN06], [F0902].[GBAN07], [F0902].[GBAN08], [F0902].[GBAN09], [F0902].[GBAN10], [F0902].[GBAN11], [F0902].[GBAN12], [F0902].[GBAN13], [F09 1.0 3.1249011E-3 0.0000011 356 6.410195E-3 [F0902].[GBAPYC], [F0902].[GBAN01], [F0902].[GBAN02], [F0902].[GBAN03], [F0902].[GBAN04], [F0902].[GBAN05], [F0902].[GBAN06], [F0902].[GBAN07], [F0902].[GBAN08], [F0902].[GBAN09], [F0902].[GBAN10], [F0902].[GBAN11], [F0902].[GBAN12], [F0902].[GBAN13], [F09 NULL PLAN_ROW 0 1.0
|--Index Seek(OBJECT:([JDE_PRODUCTION].[PRODDTA].[F0902].[F0902_4]), SEEK:([F0902].[GBCO]='00075' AND [F0902].[GBMCU]=' 750' AND [F0902].[GBOBJ]='0600 ' AND [F0902].[GBSUB]='1000 '), WHERE:(([F0902].[GBFY]=11 AND [F0902].[GBCTRY]=20) AND 7 5 4 Index Seek Index Seek OBJECT:([JDE_PRODUCTION].[PRODDTA].[F0902].[F0902_4]), SEEK:([F0902].[GBCO]='00075' AND [F0902].[GBMCU]=' 750' AND [F0902].[GBOBJ]='0600 ' AND [F0902].[GBSUB]='1000 '), WHERE:(([F0902].[GBFY]=11 AND [F0902].[GBCTRY]=20) AND [F0902].[GBLT]='AA') ORDERED FO [Bmk1000], [F0902].[GBLT], [F0902].[GBFY], [F0902].[GBCTRY] 1.0 3.2034011E-3 7.9612997E-5 118 3.2830141E-3 [Bmk1000], [F0902].[GBLT], [F0902].[GBFY], [F0902].[GBCTRY] NULL PLAN_ROW 0 1.0
(5 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
SET STATISTICS PROFILE OFF 8 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETSTATON 0 NULL
(1 row(s) affected)
AVAILABLE INDEXES
-----------------------
F0902_2nonclustered located on SECONDARYGBAID, GBCTRY, GBFY, GBFQ, GBLT, GBCRCD
F0902_3nonclustered located on SECONDARYGBSBL, GBSBLT, GBLT, GBMCU, GBOBJ, GBSUB, GBCTRY, GBFY, GBFQ, GBCRCD
F0902_4nonclustered located on SECONDARYGBCO, GBMCU, GBOBJ, GBSUB, GBSBL, GBSBLT, GBCTRY, GBFY, GBFQ, GBLT, GBCRCD
F0902_5nonclustered located on SECONDARYGBMCU, GBOBJ, GBSUB, GBCTRY, GBFY, GBFQ, GBLT, GBSBL, GBSBLT, GBCRCX, GBCRCD
F0902_6nonclustered located on SECONDARYGBMCU, GBOBJ, GBSUB, GBCTRY, GBFY, GBFQ, GBLT, GBCRCX, GBCRCD, GBSBL, GBSBLT
F0902_7nonclustered located on SECONDARYGBCO, GBMCU, GBAID, GBLT, GBSBL, GBSBLT, GBCTRY, GBFY
F0902_8nonclustered located on SECONDARYGBAID, GBCTRY, GBFY, GBFQ, GBLT, GBSBLT, GBSBL, GBCRCD
F0902_9nonclustered located on SECONDARYGBAID, GBLT, GBSBLT, GBSBL
F0902_PKclustered, unique, primary key located on PRIMARYGBAID, GBCTRY, GBFY, GBFQ, GBLT, GBSBL, GBSBLT, GBCRCD
December 6, 2011 at 12:26 pm
That's not exactly readable...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply