Optimizing SQL Query

Question

2.50/5 (2 votes)

See more:

I have more than 20K Inventory details and this query takes more than 40sec execute. Is there any possibilities to optimize it?

SQL

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 19-Aug-14 23:35pm

Gupta Poonam

Add a Solution

Comments

_Asif_ 20-Aug-14 8:36am

Where is the execution plan?

Gupta Poonam 20-Aug-14 8:50am

sorry i don't know much about it. I mean what do you mean by execution plan?

3 solutions

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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.

Posted 26-Aug-14 12:52pm

andyharman

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Magic Wonder · Accepted Answer · 2014-08-19T23:43:00

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

Posted 19-Aug-14 23:43pm

Magic Wonder

Comments

Gupta Poonam 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 20-Aug-14 6:00am

Yeah, surely it will help you.

Sharmanuj · Accepted Answer · 2014-08-20T02:18:00

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.

Posted 20-Aug-14 2:18am

Sharmanuj