database - SQL: My very short code times out; REGR_SLOPE is being super slow -


i'm pulling (read-only) information database has couple thousand rows in 2 different tables, 1 5 columns , 1 3 columns. here's code:

select distinct q1.machine_id, q1.signal_id, round(86400000*(regr_slope(ts.value, ts.epoch) on (partition ts.machine_signal_id))) rate, q1.last_value  tsd_sub ts,  ( select ms.machine_signal_id, ms.last_timestamp machine_signal ms   (ms.machine_id 'cv%' or ms.machine_id 'mt%')   , (ms.signal_id = any('wfrcount','wfrcntr') or ms.signal_id '%waferct%')) q1  ts.machine_signal_id = q1.machine_signal_id   , ts.epoch > (q1.last_timestamp - 604800000) 

now i'm not sure why should take long, code runs on fifteen minutes until times out. inner bit,

select * machine_signal       (machine_id 'cv%' or machine_id 'mt%')       , (signal_id = any('wfrcount','wfrcntr') or signal_id '%waferct%') 

runs quickly, pulling 5 columns , 3 hundred rows. full code using (relatively small) table along larger table 2 columns , few thousand rows.

this database oracle database, , running regr_slope works fine. additionally, if try run outer code little @ time, works fine:

select distinct round(86400000*(regr_slope(ts.value, ts.epoch) on (partition ts.machine_signal_id))) rate  tsd_sub ts  ts.machine_signal_id = '366625' -- taken machine_signal_id of first row of previous code   , ts.epoch > (1436855226000 - 604800000) -- last_timestamp of first row previous code 

runs in less 2 seconds , gives me one-cell result. want every entry in inner table, not 1 entry @ time. , that's not running @ all.

i know it's possibly taking long because i'm pulling many entries, i'm asking 2 things don't understand why it's not running @ all. it's it's going infinite loop, except don't see why that.

thank time , help!


edit:

the table machine_signal has variable machine_signal_id appears once in machine_signal, appears many times in tsd_sub. i'm trying calculate regr_slope of multiple rows in tsd_sub correspond 1 row in machine_signal. yeah, can't join tables, because tsd_sub has many rows each value of machine_signal_id in machine_signal.


the following takes far long run:

select machine_signal_id,         round(86400000*(regr_slope(ts.value, ts.epoch))) rate  tsd_sub ts,       (select distinct (max(ts0.epoch) - 604800000) yesterday       tsd_sub ts0       rownum < 100) ts1  ts.epoch > ts1.yesterday  group machine_signal_id 

this means joining machine_signal won't work @ all, unless can restrict machine_signal_id values in machine_signal, can't values without looking @ machine_signal first. feels catch-22 problem.


the following code work, hurray thank help.

select ms.machine_id, ms.signal_id, ms.machine_signal_id, ms.last_value, ts.epoch, ts.value  machine_signal ms join      (select ts.machine_signal_id, ts.epoch, ts.value tsd_sub ts,                      (select (max(ts0.epoch) - 604800000) yesterday                       tsd_sub ts0                       rownum < 1000) ts1                ts.epoch > ts1.yesterday) ts      on ts.machine_signal_id = ms.machine_signal_id  (ms.machine_id 'cv%' or ms.machine_id 'mt%') ,       (ms.signal_id in ('wfrcount', 'wfrcntr') or signal_id '%waferct%') 

now problem figuring out how regr_slope new table per each machine_signal_id rather getting entire table. possibly new problem, on that.


yeah reason though code above joining tables works, surrounding bit calculating regr_slope still timing out. below full code, parts work marked comments.

select mytable.machine_id,         mytable.signal_id,         round(86400000*regr_slope(mytable.value, mytable.epoch)) rate,         mytable.last_value  -- part works -- (select ms.machine_id, ms.signal_id, ms.machine_signal_id, ms.last_value, ts.epoch, ts.value  machine_signal ms join      (select ts.machine_signal_id, ts.epoch, ts.value tsd_sub ts,                      (select (max(ts0.epoch) - 604800000) yesterday                       tsd_sub ts0                       rownum < 1000) ts1                ts.epoch > ts1.yesterday) ts      on ts.machine_signal_id = ms.machine_signal_id  (ms.machine_id 'cv%' or ms.machine_id 'mt%') ,       (ms.signal_id in ('wfrcount', 'wfrcntr') or signal_id '%waferct%')) mytable -- here valid table runs in less second --  group mytable.machine_signal_id, mytable.machine_id, mytable.signal_id, mytable.last_value 

the things of substance here added regr_slope , group when regr_slope has partition inside of (as had in beginning) still times out. implies regr_slope function doing i'm not expecting that's making terribly slow. if it's going through every row, how make stop, , if it's not, why being slow?

thank you!

i don't know if help, perhaps writing query more sensibly optimizer. guessing equivalent query:

select ms.machine_id, ms.signal_id,        round(86400000*(regr_slope(ts.value, ts.epoch))) rate,        ms.last_value machine_signal join      tsd_sub ts      on ts.machine_signal_id = ms.machine_signal_id (ms.machine_id 'cv%' or ms.machine_id 'mt%') ,       (ms.signal_id in ('wfrcount', 'wfrcntr') or signal_id '%waferct%') ,       ts.epoch > (q1.last_timestamp - 604800000) group ms.machine_id, ms.signal_id, ms.last_value; 

this assumes signal_id identifying machine_id , last_value. replacing analytic function aggregation function.

your version of query runs regression every row produced join and then distinct. use group by.


Comments

Popular posts from this blog

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -

javascript - Using jquery append to add option values into a select element not working -

Android soft keyboard reverts to default keyboard on orientation change -