I wrote a query that takes about 4 seconds to run once and subsequent runs take 250 ms. I wrote another query that takes about 7 seconds to run once and 300 ms afterwards. If i UNION ALL them it'll take 90-120 seconds every time. I'm only producing a single result even. That's a tremendous increase in processing time for what seems like a simple operation. Is there a strategy to run it faster?
Code:
select oainv from TESTDATA.MR20100823, TESTDATA.VCODETL, TESTDATA.VCOHEAD, TESTDATA.VINITEM, TESTDATA.VININDEX, TESTDATA.VARCUST left outer join TESTDATA.VARSHIP on rvcmp = 1 and rvcust = oacust and rvship = oaship where obcmp = 1 and obdel = 'A' and oacmp = 1 and oadel = 'A' and oaord = obord and oabocd = obbocd and oaord = mrord and obitem = icitem and ictrln = i001trln and i001001 = 'manufacturer' and rmcmp = 1 and rmcust = oacust union all select sainv oainv from TESTDATA.MR20100823, TESTDATA.VSADETL, TESTDATA.VSAHEAD, TESTDATA.VINITEM, TESTDATA.VININDEX, TESTDATA.VARCUST left outer join TESTDATA.VARSHIP on rvcmp = 1 and rvcust = sacust and rvship = saship where sbcmp = 1 and sacmp = 1 and saord = sbord and sabocd = sbbocd and saindt = sbindt and saord = mrord and sbitem = icitem and ictrln = i001trln and i001001 = 'manufacturer' and rmcmp = 1 and rmcust = sacust