query for optimisation [message #557620] |
Thu, 14 June 2012 05:41 |
|
raja859
Messages: 2 Registered: June 2012
|
Junior Member |
|
|
Hi below query is causing performance issue.Is there any other way of doing it so by procedure or with in sql statement itsef.
please do needful
SELECT DISTINCT rt.result_value_1 rsv
FROM t_fms_ratetable rt,
t_fms_rt_column_def rtd ,
t_fms_col_value rtv
WHERE rtd.ratetable_id LIKE ?
AND rt.column_value LIKE
(SELECT rtv.col_value_id
FROM t_fms_rt_col_value rtv
WHERE rtv.rt_column_id LIKE ?
AND rtv.col_value_desc LIKE ?
)
||'-'
||
(SELECT rtv.col_value_id
FROM t_fms_rt_col_value rtv
WHERE rtv.rt_column_id LIKE ?
AND rtv.col_value_desc LIKE ?
)
||'-'
||?)
Input:
Rate TABLE ID : GS_33_000000900001760
Column_id : GS_33_000000900002400
Col_value_desc : 1 TO 5 ANY value
Column_id : GS_33_000000900002401
Col_value_desc : 500,1000,1500,2000
Daterange : GS_33_000000900439240
|
|
|
|
|
|
Re: query for optimisation [message #557650 is a reply to message #557638] |
Thu, 14 June 2012 10:11 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
raja859 wrote on Thu, 14 June 2012 15:19please help me with help of writing a procedure else a sql statement using join as i tried this way causing a bit more time.
At the first look, that query contains DISTINCT keyword, which is just masking insufficient/non-existent join conditions. You should deffinitely remove it.
For next corrections, it is necessary to know current table definition with their relationship (CREATE TABLE statements, including definition of primary/foreign keys) and detailed requirements for the output (ideally in words). Some sample data illustrating it (INSERT statements with expected result set) would help in understanding it. Michel asked you twice for providing it.
Unfortunately your post contains only one unformatted query. I am afraid that nobody is willing to reverse engineer it for reconstructing (rather guessing) missing parts.
Good luck in your effort.
|
|
|