Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    5

    Unanswered: Query Performance, Please Help

    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):

  2. #2
    Join Date
    Feb 2007
    Posts
    4
    hi,
    try to modify the subquery u have written. That will take more time for processing. try to convert that as a join.

  3. #3
    Join Date
    Aug 2005
    Posts
    5
    I did that , but its the same itme , no performance gain .
    -feroz

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    One - you are forcing an index (IN_AC_COMBINED), which may not be optimal.

    Two - run this with SET STATISTICS IO on, and see where the query is spending its time. Then try to reduce the number of IOs. Off hand I'd say that you have deeply nested table scans (on small tables, but still) and this can generate a LOT of IO.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •