Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
I have more than 20K Inventory details and this query takes more than 40sec execute. Is there any possibilities to optimize it?
 
SELECT os.itemcode AS `Item Code`,os.item_description AS `Description`, 
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`,
os.uom AS `Unit`,
os.cat_name AS `Category`,
os.item_price AS `Price`
FROM
	(SELECT itm.item_code AS `itemcode`, mos.opening_stock, itm.uom, itm.item_description, icat.cat_name, itm.item_price
	FROM montly_opening_stock mos
	INNER JOIN item_master itm ON itm.item_id = mos.item_id
	INNER JOIN inventory_master invm ON invm.item_id=itm.item_id
	INNER JOIN item_priority ip ON ip.p_id=invm.p_id
	INNER JOIN item_category icat ON icat.cat_id=itm.cat_id
	WHERE mos.month=mnth AND mos.year=yr) AS os
 
LEFT JOIN (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) AS cq ON cq.itemcode=os.itemcode
 
LEFT JOIN (SELECT id.item_code AS `itemcode` ,SUM(id.issue_qty) AS `issue qty`
FROM issue_details id
INNER JOIN issue_master im ON im.issue_id=id.issue_id
WHERE im.issue_date<=todate AND im.issue_date>=fromdate
GROUP BY id.item_code) AS iq ON iq.itemcode=os.itemcode;
 
Posted 20-Aug-14 0:35am
Comments
Syed Asif Iqbal at 20-Aug-14 8:36am
   
Where is the execution plan?
Gupta Poonam at 20-Aug-14 8:50am
   
sorry i don't know much about it. I mean what do you mean by execution plan?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
First of all do/check proper indexing in your table.
 
Kindly take a note that sometimes more indexing / wrong indexing may slow your output query when your data in lacs.

Also, fetch limited data in #temp tables from your main tables as per requirements. Do indexing in #temp tables also. Write query on your #temp tables.

Avoid n nos of joins if you can.

Then check the query execution time.

Hope this will help you.
 

 

Cheers
  Permalink  
Comments
Gupta Poonam at 20-Aug-14 5:52am
   
Thanks for Quick reply...
Will learn more about indexing and try to implement it. Hope it will help me out.
Magic Wonder at 20-Aug-14 6:00am
   
Yeah, surely it will help you.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

you can do the following steps to better optimization.
 
1. Use Case statement in place of IF condition to get the column values
2. Make views for the inner queries which you are treating as temp tables e.g "os","cq" and "iq"
3. Use select statement by joining the views created in step 2.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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  /* is this bit needed? */
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.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 305
1 OriginalGriff 225
2 DamithSL 130
3 Kornfeld Eliyahu Peter 130
4 Peter Leow 95
0 OriginalGriff 7,355
1 DamithSL 5,199
2 Sergey Alexandrovich Kryukov 4,942
3 Maciej Los 4,906
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 26 Aug 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100