I should have given you the full query. Here it is
Select a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber,
a.expirydate, a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk,
Sum(a.quantity) as quantity, Sum(a.freequantity) as freequantity,
b.beforetaxrate as beforetaxrate, (b.rate / b.rateper) as purchaserate,
b.netrate as netrate, b.netsalesrate as netsalesrate, b.effectiverate
as effectiverate, b.rateper, a.reportformat, g.standardcost,
g.defaultpurchaserate, g.salesrateone, g.salesratetwo,
g.salesratethree, g.salesratefour, g.salesratefive, g.salesratesix,
g.salesrateseven, g.salesrateeight, g.salesratenine, 'N' as negativerate From
(Select k.locationname, a.usercode, a.itemname, c.uomname, j.batchnumber,
j.expirydate, j.itemnamefk, j.itemuomfk, k.locationpk,
j.receivedquantity as quantity, j.receivedfreequantity as freequantity,
c.reportformat From in_item_name a Join in_item_uom b
On b.itemnamefk = a.itemnamepk Join gl_uom c On
c.uompk = b.uomfk Join view_item_receipts j On
j.itemnamefk = a.itemnamepk And j.itemuomfk = b.itemuompk
Join in_location k On k.locationpk = j.locationfk Where
j.companycode = 'SDM' And j.branchcode = '001' And
j.accountperiodid = 1 And j.voucherdate <= '2022/09/17' And
j.billstatus <> 'C' And j.topparentcode <> 4 And
(j.receivedquantity <> 0 Or j.receivedfreequantity <> 0) UNION ALL
Select k.locationname, a.usercode, a.itemname, c.uomname, j.batchnumber,
j.expirydate, j.itemnamefk, j.itemuomfk, k.locationpk,
(j.issuedquantity * -1) as quantity, (j.issuedfreequantity * -1) as
freequantity, c.reportformat From in_item_name a Join
in_item_uom b On b.itemnamefk = a.itemnamepk Join gl_uom
c On c.uompk = b.uomfk Join view_item_issues j On
j.itemnamefk = a.itemnamepk And j.itemuomfk = b.itemuompk
Join in_location k On k.locationpk = j.locationfk Where
j.companycode = 'SDM' And j.branchcode = '001' And
j.accountperiodid = 1 And j.voucherdate <= '2022/09/17' And
j.billstatus <> 'C' And j.topparentcode <> 4 And
(j.issuedquantity <> 0 Or j.issuedfreequantity <> 0)) a Left Outer Join
view_item_receipts b <- It's actually a view
of 4 tables which tries to arrive the last purchase rate On
b.itemreceiptspk = (Select c.itemreceiptspk From
view_item_receipts c Where c.companycode = 'SDM'
And c.branchcode = '001'
And c.accountperiodid = 1 And c.voucherdate <=
'2022/09/17' And c.billstatus <> 'C'
And c.itemnamefk = a.itemnamefk And
c.itemuomfk = a.itemuomfk And c.batchnumber =
a.batchnumber And c.expirydate = a.expirydate
And (c.receivedquantity <> 0 Or
c.receivedfreequantity <> 0) Order by c.voucherdate
desc, c.vouchernumber desc, c.sequencenumber desc
Limit 1 ) Join in_item_name c On c.itemnamepk = a.itemnamefk Join
in_item_group f On f.itemgrouppk = c.itemgroupfk Left Outer Join
in_item_rate g On g.itemuomfk = b.itemuomfk And g.itemnamefk =
b.itemnamefk And '2022/09/17' between g.fromdate and g.todate Group By
a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber, a.expirydate,
a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk,
b.beforetaxrate, b.rate, b.netrate, b.netsalesrate, b.effectiverate, b.rateper,
a.reportformat, g.standardcost, g.defaultpurchaserate,
g.salesrateone, g.salesratetwo, g.salesratethree, g.salesratefour,
g.salesratefive, g.salesratesix, g.salesrateseven, g.salesrateeight,
g.salesratenine Having (sum(a.quantity) + sum(a.freequantity)) <> 0 Order
by 1, 3, 2, 5
Create a index for companycode, branchcode, c.accountperiodid, voucherdate,
billstatus, itemnamefk, itemuomfk, batchnumber, expirydate, receivedquantity,
receivedfreequantity
in all the 4 tables that this view got.
Happiness Always
BKR Sivaprakash
On Saturday, 17 September, 2022 at 03:33:48 pm IST, Peter J. Holzer
<hjp-pg...@hjp.at> wrote:
On 2022-09-17 05:28:25 +0000, sivapostg...@yahoo.com wrote:
> My query is like this
>
> Select a.field1, a.field2, a.field3
> From (Select a.field1, b.field2, c.field3
> From table1 a
> Join table2 b
> on b.something = a.something
> Join table3 c
> On c.something = a.something
> Where a.field7 = 'value'
>
> UNION ALL
>
> Select a.field4, a.field5, a.field6
> From table11 a
> Join table21 b
> On b.something = a.something
> Where a.field8 = 'something' ) a
> Join table10 b
> On b.field11 = (Select c.field11
> From table10 c
> Where c.field10 = a.field1 ) <-
> instead of a.field1, if I hardcode value (eg. '100') query runs faster
> Join table21 c
> On c.something = a.something
> ...
Well, you are now searching table10 for a constant value (which can be
done once) instead of the output of the union (which has to be done for
each line of the union, so I'm not surprised that it's faster.
What is the output of `explain (analyze)` for the two queries? Is there
an obvious place where an index would help? Can you restructure the
query?
BTW, it is (at least for me) very hard to give advice on a query with
only completely abstract names like `table11` or `field4`: I have no
idea what this is supposed to do, so it's hard to tell if there is a
better way. Using `a` to refer to 3 different things doesn't help
either.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
