Home » RDBMS Server » Performance Tuning » optimize queries (oracle 10g)
optimize queries [message #334909] |
Fri, 18 July 2008 10:59 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
acarella
Messages: 21 Registered: July 2008 Location: Latham, NY
|
Junior Member |
|
|
Can anyone help me to rewrite/optimize these queries
Query #1
SELECT DISTINCT
v.F_PRODUCT,
(SELECT F_PRODUCT_NAME FROM T_PRODUCTS WHERE F_PRODUCT = v.F_PRODUCT) AS F_PRODUCT_NAME,
'' AS F_CAS_NUMBER,
'' AS F_COMPONENT_ID,
'' AS F_CHEM_NAME,
v.F_TYPE,
'' AS F_PERCENT,
'' AS F_REACH_FUCNTION,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 1
AND F_START_DATE >= '01-Jan-2005'
AND F_STOP_DATE <= '31-Dec-2005' ) Y1IMP,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 2
AND F_START_DATE >= '01-Jan-2005'
AND F_STOP_DATE <= '31-Dec-2005' ) Y1PROD,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 1
AND F_START_DATE >= '01-Jan-2006'
AND F_STOP_DATE <= '31-Dec-2006' ) Y2IMP,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 2
AND F_START_DATE >= '01-Jan-2006'
AND F_STOP_DATE <= '31-Dec-2006') Y2PROD,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 1
AND F_START_DATE >= '01-Jan-2007'
AND F_STOP_DATE <= '31-Dec-2007') Y3IMP,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1, T_REACH_VENDORS vd
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 2
AND F_START_DATE >= '01-Jan-2007'
AND F_STOP_DATE <= '31-Dec-2007') Y3PROD , '' AVG3IMP, '' AVG3PROD, '' AVG3TOTAL
FROM
T_REACH_VOLUMES v, T_REACH_VENDORS vd
WHERE
F_LEO_ID = 1
AND v.F_VENDOR_ID = vd.F_VENDOR_ID
Query #2
SELECT v.F_PRODUCT,
p.F_PRODUCT_NAME,
'' AS F_CAS_NUMBER,
'' AS F_COMPONENT_ID,
'' AS F_CHEM_NAME,
v.F_TYPE,
'' AS F_PERCENT,
'' AS F_REACH_FUCNTION,
sum(decode(greatest(v.F_START_DATE,to_date('20050101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20051231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,1,v.f_quantity,0), 0), 0) ) y1imp_new,
sum(decode(greatest(v.F_START_DATE,to_date('20050101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20051231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,2,v.f_quantity,0),
0), 0) ) y1prod_new,
sum(decode(greatest(v.F_START_DATE,to_date('20060101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20061231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,1,v.f_quantity,0), 0), 0)
) y2imp_new,
sum(decode(greatest(v.F_START_DATE,to_date('20060101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20061231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,2,v.f_quantity,0), 0), 0) ) y2prod_new,
sum(decode(greatest(v.F_START_DATE,to_date('20070101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20071231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,1,v.f_quantity,0), 0), 0) ) y2imp_new,
sum(decode(greatest(v.F_START_DATE,to_date('20070101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20071231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,2,v.f_quantity,0), 0), 0)) y3prod_new,
'' AVG3IMP,
'' AVG3PROD,
'' AVG3TOTAL
FROM T_REACH_VOLUMES v, T_REACH_VENDORS vd, T_PRODUCTS p
WHERE v.F_LEO_ID = 1
AND v.F_VENDOR_ID = vd.F_VENDOR_ID
and p.F_PRODUCT = v.F_PRODUCT
group by v.F_PRODUCT,
p.F_PRODUCT_NAME,
v.F_TYPE
Query #3
SELECT DISTINCT v.F_PRODUCT,
(SELECT F_PRODUCT_NAME FROM T_PRODUCTS WHERE F_PRODUCT = v.F_PRODUCT) AS F_PRODUCT_NAME,
'' AS F_CAS_NUMBER, '' AS F_COMPONENT_ID, '' AS F_CHEM_NAME, v.F_TYPE, '' AS F_PERCENT,
'' AS F_REACH_FUCNTION,
(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1,
T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID
AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 1 AND F_START_DATE >= ? AND F_STOP_DATE <= ? ) Y1IMP,
(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID
AND rvd.F_IS_EU = 1 AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ? ) Y1PROD,
(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 1 AND F_START_DATE >= ? AND F_STOP_DATE <= ? ) Y2IMP,
(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ?) Y2PROD,
(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 1 AND F_START_DATE >= ? AND F_STOP_DATE <= ?) Y3IMP,
(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd , T_REACH_VENDORS vd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ?) Y3PROD ,
'' AVG3IMP, '' AVG3PROD, '' AVG3TOTAL
FROM T_REACH_VOLUMES v, T_REACH_VENDORS vd
WHERE F_LEO_ID = ? AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND vd.F_IS_EU = 1 AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
GROUP BY v.F_LEO_ID, v.F_TYPE, v.F_PRODUCT
Query #4
SELECT DISTINCT v.F_CAS_NUMBER,
(SELECT MIN(F_CHEM_NAME) FROM T_COMPONENTS WHERE F_CAS_NUMBER = v.F_CAS_NUMBER ) F_CHEM_NAME,
(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 1 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_IMP <> 0 ) Y1IMP,
(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_PROD <> 0 ) Y1PROD,
(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1 , T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 1 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_IMP <> 0 ) Y2IMP,
(SELECT SUM(F_QUANTITY) FROM T_REACH_VOLUMES v1 , T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 2 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_PROD <> 0 ) Y2PROD,
(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01') AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 1 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ? AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_IMP <> 0 ) Y3IMP, (SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01') AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 2 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ? AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_PROD <> 0 ) Y3PROD, '' AVG3IMP, '' AVG3PROD, '' AVG3TOTAL
FROM T_REACH_VOLUMES v, T_COMPONENTS c, T_REACH_VENDORS vd WHERE
F_LEO_ID = ? AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND v.F_CAS_NUMBER = c.F_CAS_NUMBER
AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND vd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND F_LOCATION_ID = ? GROUP BY v.F_LEO_ID, v.F_TYPE, v.F_CAS_NUMBER,v.F_Product
Where ‘MIN’(ChemName) is taken because of possibility to have many Component Names with the same CAS#
Thank you.
AC
|
|
|
|
Re: optimize queries [message #334982 is a reply to message #334909] |
Sat, 19 July 2008 01:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Many times, the solution to a better performing query is to re-write it into something else.
The DISTINCT should be your first clue that this query will give you problems. Distinct is a valid operation, but nine times out of ten, developers use it when they don't need it.
The second clue that there may be a problem is the t_Reach_Vendors vd table reference. You join to it, but then you never select anything from it or use it to control any of your scalar sub-selects. One must ask therefore, why is it included. Certainly it could be performing an existential check. But it might also simply be one extra join not needed. Indeed, it might even be adding to your "duplicate rows" problem if there is a one-to-many between volumns and vendors.
Lastly scalar sub-selects are handy, but not always efficient. Many developers use them as any easy way to write code. This is not bad, easier to understand code is a good thing, but scalar sub-selects are a finicky coding construct which can make your code faster, or slower depending upon what the alternative formulations are. Some good uses of a scalar sub-select can be to avoid otherwise doing an outerjoin, or avoid using a table function. None-the-less, it is prudent to evaluate a query's performance with them and without them in order to assess their cost. Then at least you are making an informed decision. Your query neither avoids outerjoin, nor avoids table functions, by use of its sub-selects; thus they are immediately suspect as to their value.
So, one should try to re-write your query without distinct, and without scalar sub-selects, and without unnecessary joins (if they exist).
Consider this alternative formulation of your query. It I think does the same thing but much more efficiently. You need to think about how the rows in your tables will be accessed. Try to understand how this query works in terms of how it accesses rows. Then do the same for your query. You will grasp why this might be much better. It only passes the data once. Yours passes the data at least twice.
with
some_dates (
select to_date('01-Jan-2005','dd-mon-rrrr') start_date
,to_date('31-dec-2005 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
from dual union all
select to_date('01-Jan-2006','dd-mon-rrrr') start_date
,to_date('31-dec-2006 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
from dual union all
select to_date('01-Jan-2007','dd-mon-rrrr') start_date
,to_date('31-dec-2007 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
from dual
)
, product_type_sum as (
select v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date,sum(v.f_quantity) f_quantity
from t_Reach_Volumes v
,some_dates sd
,t_products t
where v.f_type in (1,2)
and v.f_start_date >= sd.start_date
and v.f_stop_date <= sd.end_date
and v.f_product = t.f_product
group by v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date
)
select *
from product_type_sum
/
Or, for those not yet familiar with the WITH CLAUSE:
select v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date,sum(v.f_quantity) f_quantity
from t_Reach_Volumes v
,(
select to_date('01-Jan-2005','dd-mon-rrrr') start_date
,to_date('31-dec-2005 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
from dual union all
select to_date('01-Jan-2006','dd-mon-rrrr') start_date
,to_date('31-dec-2006 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
from dual union all
select to_date('01-Jan-2007','dd-mon-rrrr') start_date
,to_date('31-dec-2007 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
from dual
) sd
where v.f_type in (1,2)
and v.f_start_date >= sd.start_date
and v.f_stop_date <= sd.end_date
group by v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date
/
I have not tested any of this code (how can I, I do not have your table scripts), so there are likely several syntax errors, but you can find them and make this code run. You should do this to validate if this query alternative(s) is actually sematically equivelant to your original.
Good luck, Kevin
|
|
|
Re: optimize queries [message #335204 is a reply to message #334982] |
Mon, 21 July 2008 07:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
acarella
Messages: 21 Registered: July 2008 Location: Latham, NY
|
Junior Member |
|
|
thank you so much Kevin.
I am not familiar with the WITH CLAUSE (as I am just learning -- taking courses vs. actual hands on is a biggey). I will learn this. Thank you.
Arlene
|
|
|
Re: optimize queries [message #335535 is a reply to message #334909] |
Tue, 22 July 2008 08:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
query #1:
look at in-line select:
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1, T_REACH_VENDORS vd
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 2
AND F_START_DATE >= '01-Jan-2007'
AND F_STOP_DATE <= '31-Dec-2007') Y3PROD
I don't see any JOIN conditions to T_REACH_VENDORS vd table. Do you?
HTH.
Michael
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:05:42 CDT 2024
|