Difference in Execution timing [message #163670] |
Fri, 17 March 2006 15:37 |
feroze
Messages: 144 Registered: September 2005 Location: chicago
|
Senior Member |
|
|
Hi
This is the SQL
SELECT l.LOCKBOX_NB ,SUM(bd.VOL_CT) VOL_CT , dd.DDA_NB, sc.GPC_ID, dd.BNK_ID
FROM BILL_DATA bd, LOCKBOX l, LOCKBOX_DDA ldda, DDA dd, GPC_CD sc
WHERE bd.LOCKBOX_ID = l.LOCKBOX_ID
AND l.SITE_ID NOT IN (SELECT SITE_ID FROM SITE WHERE SITE_CD = '06')
AND l.LOCKBOX_ID = ldda.LOCKBOX_ID
AND ldda.DDA_ID = dd.DDA_ID
AND bd.GPC_CD_ID = sc.GPC_ID
AND ldda.DDA_TYPE_CD = 'B'
AND bd.BILL_DT BETWEEN '01-dec-2005' AND '21-jan-2006'
GROUP BY l.LOCKBOX_NB, dd.DDA_NB, sc.GPC_ID, dd.BNK_ID
ORDER BY l.LOCKBOX_NB ASC
and the PLAN
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 3 K 95
SORT GROUP BY 3 K 261 K 95
NESTED LOOPS 3 K 261 K 43
HASH JOIN ANTI 3 K 240 K 43
HASH JOIN 3 K 227 K 37
HASH JOIN 1 K 46 K 16
HASH JOIN 1 K 30 K 9
TABLE ACCESS FULL R1APP60.LOCKBOX_DDA 1 K 12 K 4
TABLE ACCESS FULL R1APP60.DDA 1 K 29 K 4
TABLE ACCESS FULL R1APP60.LOCKBOX 2 K 32 K 6
TABLE ACCESS FULL R1APP60.BILL_DATA 7 K 164 K 19
TABLE ACCESS FULL R1APP60.SITE 1 8 2
INDEX UNIQUE SCAN R1APP60.XPK_SRVCD 1 6
This will get 6500 rows in Developement and 35000 rows in Production.
Time taken in Dev is 3 sec and in Production 30 sec, why so much Difference .
The plan is from Development i can't take it form Production since i don't have access, In Development i see that it's doing FTS, STATS are updated, Tables and Indexes are analyzed,
Is some thing wrong in SQL, any thing can be done or written better..
Thanks.
|
|
|
Re: Difference in Execution timing [message #163689 is a reply to message #163670] |
Sat, 18 March 2006 00:52 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Tuning on a development-database is a complete waste of time if your development-database has other data (volume and distribution) than your production-database. Better take the day off, because you are tuning for the _wrong_ data.
|
|
|