Hi All,

I am new in sybase, recently we have encountered peformence issue in our database where a query is running very slow. same query is running good in Integration or test environment. but its taking around 11 hours time in production.
below is the query plan from db. Can anybody please provide some clue that what needs to be done.

================================================
QUERY PLAN FOR STATEMENT 9 (at line 111).


STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for DISTINCT.

FROM TABLE
employee
cl
Nested iteration.
Using Clustered Index.
Index : C
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
employee_id ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

FROM TABLE
employeeAccount
ca
Nested iteration.
Index : CBIC
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
employee_id ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

FROM TABLE
employeeAccountRule
car
Nested iteration.
Index : CBIC
Forward scan.
Positioning by key.
Keys are:
client_id ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
employeeAccount
catemp
EXISTS TABLE : nested iteration.
Index : CBIC
Forward scan.
Positioning at index start.
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Rules
r
Nested iteration.
Index : CL
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
client_id ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

FROM TABLE
Rules
r
Nested iteration.
Index : CL
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
client_id ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

FROM TABLE
Rules
r
Nested iteration.
Using Dynamic Index.
Forward scan.
Positioning by Row IDentifier (RID).
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
#s_i_employee
s
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 64 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
#tRules
tR
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 64 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.
This step involves sorting.

FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 64 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
#s_i_employeepf
Using I/O Size 8 Kbytes for data pages.

Total estimated I/O cost for statement 98 (at line 923): 7104.

================================================