QUREY PERFORMANCE, PLEASE HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

QUREY PERFORMANCE, PLEASE HELP

Author

Topic

ferozkhan
Starting Member

5 Posts

Posted - 2007-02-26 : 19:15:15

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 00:02:50

What are the double pipes for? They are not standard T-SQL, so I have to ask...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 00:49:23

[code]SELECT AC.ACCOUNT_NUMBER,
AC.ACCOUNT_NAME,
AC.ACCOUNT_END_DATE,
P1.NAME,
D.DIVISION_NUMBER,
P.SURNAME_ISI + ' '+ P.FIRST_NAME AS Person_Name,
ISNULL(P.EMPLOYEE_ID, P.VISITOR_NUMBER) AS Employee_Number,
CONVERT(DATETIME, FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ + ' ' + FY.FISCAL_YEAR) AS Month_Year,
FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ + ' ' + FY.FISCAL_YEAR AS [Month/Year],
ROUND(LC.LCS_EFFORT, 2) AS LCS_Effort,
'ACTUAL' AS 'Labor Type',
(SELECT CODE_VALUE FROM CODES CD WHERE CODE_ID = P.STAFF_CATEGORY_CODE_ID) AS STAFF_CAT
FROM ACCOUNTS AS AC
INNER JOIN PERSONNEL AS P1 ON P1.PERSONNEL_ID = AC.ACCOUNT_MANAGER
INNER JOIN DIVISIONS AS D ON D.DIVISION_ID = P1.DIVISION_ID
INNER JOIN LABOR_COMMITED AS LC ON LC.ACCOUNT_ID = AC.ACCOUNT_ID AND LC.COMMITED_DATE IS NOT NULL
INNER JOIN PERSONNEL AS P ON P.PERSONNEL_ID = LC.PERSONNEL_ID
INNER JOIN FISCAL_SYSTEM_PERIODS AS FSP ON FSP.FISCAL_SYSTEM_PERIOD_ID = LC.FISCAL_PERIOD_ID
INNER JOIN FISCAL_YEARS AS FY ON FY.FISCAL_YEAR_ID = LC.FISCAL_YEAR_ID
INNER JOIN (
SELECT DISTINCT CHILD.PERSONNEL_ID
FROM PERSONNEL AS CHILD
INNER PERSONNEL AS 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
) AS pc ON pc.PERSONNEL_ID = P1.PERSONNEL_ID
WHERE CONVERT(DATETIME, FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ + ' ' + FY.FISCAL_YEAR) >= CONVERT(DATETIME,'February, 2006')
AND CONVERT(DATETIME, FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ + ' ' + FY.FISCAL_YEAR) <= CONVERT(DATETIME,'January, 2007')[/code]

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-02-27 : 07:41:51

"What are the double pipes for? They are not standard T-SQL, so I have to ask..."
Looks like DB2.......

ferozkhan
Starting Member

5 Posts

Posted - 2007-02-27 : 11:52:14

Thanks Peter for your reply . I ran your query , and it took 1 min 54 seconds .

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-02-27 : 18:20:50

A couple of things you might try to see if it affects performance:

1. Join to Codes to get the STAFF_CAT instead of a sub-query
2. Change the Join order to join the most restrictive clauses first and/or possibly the smallest tables.
3. Modify the WHERE clause to not cast FISCAL_SYSTEM_PERIOD_CAL_YR_EQ and FISCAL_YEAR to a date, but rather use the date-part and cast it to those value types.

Hopefully, that makes sense.

-Ryan

yonabout
Posting Yak Master

112 Posts

Posted - 2007-02-28 : 06:51:21

Might sound stupid, but have you checked the indexes on the tables?

I only mention this because I had an issue with this recently where a query that had been running happily for months began to slow down dramatically, until it was taking upward of 5 minutes and dragging the performance of the rest of the server down.

Because it used to run fine, it never crossed my mind that the indexing might need doing, and I only did it as an afterthought.

It reduced the query time from 5 minutes + to around a second.

Cheers,

Yonabout

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-02-28 : 06:52:14

Posting the graphical execution plan image onto a "neutral website" like www.imageshack.us and then linking that PIC back to here might get an answer faster. Reading the text version above can hard (and thus offputting for any volunteer in a rush) to follow.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 07:08:34

Two things are killing your performance


1) The subquery	(SELECT CODE_VALUE FROM CODES CD WHERE CODE_ID = P.STAFF_CATEGORY_CODE_ID) AS STAFF_CAT
2) The filter	CONVERT(DATETIME, FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ + ' ' + FY.FISCAL_YEAR) >= CONVERT(DATETIME,'February, 2006')
		AND CONVERT(DATETIME, FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ + ' ' + FY.FISCAL_YEAR) <= CONVERT(DATETIME,'January, 2007')

Try to rewrite them as well.

Peter Larsson
Helsingborg, Sweden

ferozkhan
Starting Member

5 Posts

Posted - 2007-02-28 : 20:48:15

Yonabout, I have index on the tables. the indexes are listed out int he showplan output , the IN_LC_COMBINED ( PERSONNEL_ID , ACCOUNT_ID) in the Labor_Commited Table.

Andrew, I promise, I ll put the graphical image tomorrow.

Peter , Even if I remove the subquery ( just to test it) (SELECT CODE_VALUE FROM CODES CD WHERE CODE_ID = P.STAFF_CATEGORY_CODE_ID) AS STAFF_CAT from the main query it still takes the same amount of time . And , the data is stored as Month name ( like January , February etc) and the Year number , so I have to concatinate them and compare with the date value . The date value "February, 2006" and "January, 2007" are passed as parameter from the report .

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-28 : 21:03:06

The query plan output does not look like SQL Server.

Are you using DB2 as AndrewMurphy suggested?

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-28 : 22:52:49

I guess once we see the graphical showplan we'll know what RDBMS this is

DB2, Oracle, and PostgreSQL all use || as string concatenation operators.

www.elsasoft.org

ferozkhan
Starting Member

5 Posts

Posted - 2007-03-01 : 11:08:51

Its Syabase ASE 12.5
-feroz

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-02 : 09:17:45

We are a SQLServer focussed site but the principles of code optimisation still apply across most platforms. You may wish to switch to something like www.dbforums.com or post the execution plan on that listed site and continue seeking assistance here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources