I have a problem query that I could use some help. This code ran in 20 minutes last year and there is not much change in the input data this year....perhaps 10% growth. Currently this is running 8+ hours and it is unacceptable. This must run in Production next weekend, so there is little time to figure out what is wrong & fix it.
The tables we are running against are in Integration newly loaded in key order, or just recently reorg'd and have current stats.
Explain follows the query and doesn't reflect long run times. Internal monitoring shows it using DSNDB07 DSN4K02 a great deal of the time it is running.
QUERY:
SELECT A1.NEXT_NODE_ID
, A2.NEXT_NODE_ID
INTO :H , :H
FROM H6_ASSOC A1
, H6_ASSOC A2
, H6_ASSOC A3
, H6_NODE N1
, H6_NODE N2
WHERE A1.PREV_NODE_ID = :H
AND A1.ASSOC_TYPE = 3324
AND A2.PREV_NODE_ID = A1 . NEXT_NODE_ID
AND A2.ASSOC_TYPE = 3324
AND A3.PREV_NODE_ID = :H
AND A3.NEXT_NODE_ID = A2.NEXT_NODE_ID
AND A3.ASSOC_TYPE = 3323
AND N1.NODE_TYPE_ID = 3433
AND N1.NODE_ID = A1.NEXT_NODE_ID
AND N2.NODE_TYPE_ID = 3432
AND N2.NODE_ID = A2.A2.NEXT_NODE_ID ;
EXPLAIN:
STMTNO COST*RATE SQL-STATEMENT
0 109.302246 SELECT A1.NEXT_NODE_ID ,A2.NEXT_NODE_ID INTO :H, :H FRO
COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME IXNAME
20.307190 1 1 0 SELECT 0 I 2 Y H6_ASSOC H6ASOCX3
26.128784 1 2 0 SELECT 1 I 1 N H6_NODE H6NODEX1
21.607697 1 3 0 SELECT 1 I 2 Y H6_ASSOC H6ASOCX2
13.491821 1 4 0 SELECT 1 I 3 Y H6_ASSOC H6ASOCX3
27.766754 1 5 0 SELECT 1 I 1 N H6_NODE H6NODEX1
Any suggetions as to why run times have increased?
Any ideas on recode or what ekse to look at?
Thanks.