Which database engine are you using? I am going to assume MySQL because your query contains
IF()
functions ...
Can we link using item_id?
Your query contains:
SELECT itm.item_code AS `itemcode`, SUM(ic.c_quantity) AS
`consume qty`
FROM inventory_consumption ic
INNER JOIN item_master itm ON itm.item_id = ic.item_id
WHERE ic.c_date<=todate AND ic.c_date>=fromdate
GROUP BY itm.item_code
... then you link on
cq.itemcode=os.itemcode
.
Is it possible to use:
SELECT ic.item_id, SUM(ic.c_quantity) AS `consume qty`
FROM inventory_consumption ic
WHERE ic.c_date BETWEEN todate AND fromdate
GROUP BY ic.item_id
... add
itm.item_id
to your
os
sub-query,
... then link on
cq.item_id=os.item_id
?
If that works then you will avoid the cost of the link to
item_master
.
Use a "covering" index
If you create a composite index on
inventory_consumption
that contains the columns (
c_date
,
item_id
,
c_quantity
) then most database engines will understand that the index contains all of the columns needed in your
cq
query - and will be able to resolve the entire query by looking only at the index (and not have to look at the table pages).
For MySQL, I found this link -
http://planet.mysql.com/entry/?id=661727[
^] that may help you.
Simplifying calculation of Closing Stock
Your query contained:
IF(cq.`consume qty` IS NULL AND iq.`issue qty` IS NULL,os.opening_stock,
IF(cq.`consume qty` IS NULL,(os.opening_stock+iq.`issue qty`),
IF(iq.`issue qty` IS NULL,(os.opening_stock-cq.`consume qty`),(os.opening_stock+iq.`issue qty`-cq.`consume qty`)))) AS `Closing Stock`
Most databases allow you to write something like:
os.opening_stock - IFNULL(cq.`consume qty`, 0) + IFNULL(iq.`issue qty`) AS `Closing Stock`
This probably will not improve performance - but it makes it easier to understand.
Learn more about optimization
There are lots of tutorials for performance optimization available on the internet. I really would recommend that you understand indexes properly.
If you are using MySQL then
http://dev.mysql.com/doc/refman/5.5/en/optimization.html[
^] may help. There are sections on indexing and on understanding the Query Execution Plan.
Hope this helps.