QUREY PERFORMANCE, PLEASE HELP . Its in ASE 12.x.x

[FEROZKHAN]

Hi, I have written a query which is you can see below. The query qpproximately takes 50- 60 SEC to run, which I believe is way too high. The total rows returned is 10,000 . Any idead/lead what to do to improve the query timings ? The number of rows in each table are as follows :

ACCOUNTS : 1,580
PERSONNEL : 1,616
DIVISION : 15
LABOR_COMMITTED : 23,000
FISCAL_SYSTEM_PERIODS : 20
FISCAL_YEARS : 20

INDEXES :
ACCOUNTS TABLE- 1. ACCOUNT_ID ( Primary Key), 2. IN_AC_COMBINED(ACCOUNT_ID , ACCOUNT_MANAGER)

LABOR_COMMITTED TABLE- 1.LABOR_COMMIT_ID ( Primary Key) 2. IN_LC_COMBINED(PERSONNEL_ID, ACCOUNT_ID)

PERSONNEL TABLE- 1. PERSONNEL_ID ( Primary key)

The Actual Query :

SELECT AC.ACCOUNT_NUMBER , AC.ACCOUNT_NAME , AC.ACCOUNT_END_DATE ,
P1.NAME , D.DIVISION_NUMBER , P.SURNAME_ISI || ' '|| P.FIRST_NAME Person_Name,
ISNULL(P.EMPLOYEE_ID,P.VISITOR_NUMBER) Employee_Number, CONVERT(DATETIME ,
FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR) Month_Year ,
FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR Month/Year,
ROUND(LC.LCS_EFFORT,2) LCS_Effort , 'ACTUAL' AS 'Labor Type' ,
(SELECT CODE_VALUE FROM CODES CD WHERE CODE_ID = P.STAFF_CATEGORY_CODE_ID) STAFF_CAT
FROM ACCOUNTS AC(INDEX IN_AC_COMBINED), PERSONNEL P1 ,DIVISIONS D, LABOR_COMMITED LC (index in_lc_combined) , PERSONNEL P , FISCAL_SYSTEM_PERIODS FSP , FISCAL_YEARS FY
WHERE P1.PERSONNEL_ID IN (SELECT DISTINCT CHILD.PERSONNEL_ID FROM PERSONNEL CHILD, PERSONNEL PARENT WHERE PARENT.PERSONNEL_ID IN (7167,7041)
AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0)
AND AC.ACCOUNT_MANAGER = P1.PERSONNEL_ID AND P1.DIVISION_ID = D.DIVISION_ID AND AC.ACCOUNT_ID = LC.ACCOUNT_ID
AND LC.PERSONNEL_ID = P.PERSONNEL_ID AND FSP.FISCAL_SYSTEM_PERIOD_ID = LC.FISCAL_PERIOD_ID AND FY.FISCAL_YEAR_ID = LC.FISCAL_YEAR_ID
AND LC.COMMITED_DATE IS NOT NULL AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) <= CONVERT(DATETIME,'January, 2007')
AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) >= CONVERT(DATETIME,'February, 2006')

Here is the show plan :
W (1):
W (2): QUERY PLAN FOR STATEMENT 1 (at line 1).
W (3):
W (4):
W (5): STEP 1
W (6): The type of query is SELECT.
W (7):
W (8): FROM TABLE
W (9): ACCOUNTS
W (10): AC
W (11): Nested iteration.
W (12): Index : IN_AC_COMBINED
W (13): Forward scan.
W (14): Positioning at index start.
W (15): Using I/O Size 2 Kbytes for index leaf pages.
W (16): With LRU Buffer Replacement Strategy for index leaf pages.
W (17): Using I/O Size 2 Kbytes for data pages.
W (18): With LRU Buffer Replacement Strategy for data pages.
W (19):
W (20): FROM TABLE
W (21): PERSONNEL
W (22): P1
W (23): Nested iteration.
W (24): Index : PK_PERSONNEL_ID
W (25): Forward scan.
W (26): Positioning by key.
W (27): Keys are:
W (28): PERSONNEL_ID ASC
W (29): Using I/O Size 2 Kbytes for index leaf pages.
W (30): With LRU Buffer Replacement Strategy for index leaf pages.
W (31): Using I/O Size 2 Kbytes for data pages.
W (32): With LRU Buffer Replacement Strategy for data pages.
W (33):
W (34): FROM TABLE
W (35): PERSONNEL
W (36): CHILD
W (37): EXISTS TABLE : nested iteration.
W (38): Index : PK_PERSONNEL_ID
W (39): Forward scan.
W (40): Positioning by key.
W (41): Keys are:
W (42): PERSONNEL_ID ASC
W (43): Using I/O Size 2 Kbytes for index leaf pages.
W (44): With LRU Buffer Replacement Strategy for index leaf pages.
W (45): Using I/O Size 2 Kbytes for data pages.
W (46): With LRU Buffer Replacement Strategy for data pages.
W (47):
W (48): FROM TABLE
W (49): PERSONNEL
W (50): PARENT
W (51): EXISTS TABLE : nested iteration.
W (52): Using 2 Matching Index Scans
W (53): Index : PK_PERSONNEL_ID
W (54): Forward scan.
W (55): Positioning by key.
W (56): Keys are:
W (57): PERSONNEL_ID ASC
W (58): Index : PK_PERSONNEL_ID
W (59): Forward scan.
W (60): Positioning by key.
W (61): Keys are:
W (62): PERSONNEL_ID ASC
W (63): Using I/O Size 2 Kbytes for data pages.
W (64): With LRU Buffer Replacement Strategy for data pages.
W (65):
W (66): FROM TABLE
W (67): DIVISIONS
W (68): D
W (69): Nested iteration.
W (70): Table Scan.
W (71): Forward scan.
W (72): Positioning at start of table.
W (73): Using I/O Size 2 Kbytes for data pages.
W (74): With LRU Buffer Replacement Strategy for data pages.
W (75):
W (76): FROM TABLE
W (77): LABOR_COMMITED
W (78): LC
W (79): Nested iteration.
W (80): Index : IN_LC_COMBINED
W (81): Forward scan.
W (82): Positioning at index start.
W (83): Using I/O Size 2 Kbytes for index leaf pages.
W (84): With LRU Buffer Replacement Strategy for index leaf pages.
W (85): Using I/O Size 2 Kbytes for data pages.
W (86): With LRU Buffer Replacement Strategy for data pages.
W (87):
W (88): FROM TABLE
W (89): PERSONNEL
W (90): P
W (91): Nested iteration.
W (92): Index : PK_PERSONNEL_ID
W (93): Forward scan.
W (94): Positioning by key.
W (95): Keys are:
W (96): PERSONNEL_ID ASC
W (97): Using I/O Size 2 Kbytes for index leaf pages.
W (98): With LRU Buffer Replacement Strategy for index leaf pages.
W (99): Using I/O Size 2 Kbytes for data pages.
W (100): With LRU Buffer Replacement Strategy for data pages.
W (101):
W (102): FROM TABLE
W (103): FISCAL_YEARS
W (104): FY
W (105): Nested iteration.
W (106): Table Scan.
W (107): Forward scan.
W (108): Positioning at start of table.
W (109): Using I/O Size 2 Kbytes for data pages.
W (110): With LRU Buffer Replacement Strategy for data pages.
W (111):
W (112): FROM TABLE
W (113): FISCAL_SYSTEM_PERIODS
W (114): FSP
W (115): Nested iteration.
W (116): Table Scan.
W (117): Forward scan.
W (118): Positioning at start of table.
W (119):
W (120): Run subquery 1 (at nesting level 1).
W (121): Using I/O Size 2 Kbytes for data pages.
W (122): With LRU Buffer Replacement Strategy for data pages.
W (123): STEP 1
W (124):
W (125): NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
W (126):
W (127): QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).
W (128):
W (129): Correlated Subquery.
W (130): Subquery under an EXPRESSION predicate.
W (131):
W (132):
W (133): STEP 1
W (134): The type of query is SELECT.
W (135): Evaluate Ungrouped ONCE AGGREGATE.
W (136):
W (137): FROM TABLE
W (138): CODES
W (139): CD
W (140): Nested iteration.
W (141): Index : PK_CODE_ID
W (142): Forward scan.
W (143): Positioning by key.
W (144): Keys are:
W (145): CODE_ID ASC
W (146): Using I/O Size 2 Kbytes for index leaf pages.
W (147): With LRU Buffer Replacement Strategy for index leaf pages.
W (148): Using I/O Size 2 Kbytes for data pages.
W (149): With LRU Buffer Replacement Strategy for data pages.
W (150):
W (151): END OF QUERY PLAN FOR SUBQUERY 1.
W (152):
W (153):

 

[SantaMufasa]

Ferozkhan,

Did you resolve this issue?

If so, please post your resolution/workaround.

If not, please reply so that contributors can post suggestions.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]