优化SQL查询 [英] Optimizing SQL Query
问题描述
我有超过20K的库存详细信息，此查询执行时间超过40秒。是否有可能优化它？
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;
推荐答案
首先在表中执行/检查正确的索引。
请注意，有时更多索引/错误索引可能会减慢输出查询速度当您的数据以lacs为单位时。
此外，根据要求从主表中获取#temp表中的有限数据。在#temp表中也进行索引。在#temp表上写下查询。
如果可以，请避免使用n个连接。
然后检查查询执行时间。
希望这会对你有所帮助。
干杯
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
您可以执行以下步骤以更好地进行优化。
1.使用Case语句代替IF条件来获取列值
2.为内部查询创建视图，将其视为临时表，例如os，cq和iq
3.通过加入在步骤2中创建的视图来使用select语句。
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.
您使用的是哪个数据库引擎？我将假设MySQL，因为您的查询包含IF（）函数...
可以我们使用item_id链接？
您的查询包含：
Which database engine are you using? I am going to assume MySQL because your query containsIF()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
...然后你链接 cq.itemcode = os.itemcode 。
是吗可以使用：
... 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 到您的 os 子查询，
...然后链接 cq.item_id = os.item_id ？
如果是这样的话然后你可以避免链接到 item_master 的费用。
使用覆盖 index
如果在 inventory_consumption 上创建包含列的复合索引（ c_date ， item_id ， c_quantity ）然后大多数数据库引擎都会理解索引包含了所需的所有列您的 cq 查询 - 并且只能通过查看索引（而不必查看表格页面）来解析整个查询。
对于MySQL，我找到了这个链接 - http://planet.mysql.com / entry /？id = 661727 [ ^ ]这可能会对你有所帮助。
简化结算股票的计算
您的查询包含：
... 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`
这可能不会提高性能 - 但它可以更容易理解。
了解有关优化的更多信息
有许多可用于性能优化的教程在网上。我真的建议你正确理解索引。
如果你使用MySQL那么http://dev.mysql.com/doc/refman/5.5/en/optimization.html [ ^ ]可能有所帮助。有关索引和理解查询执行计划的部分。
希望这会有所帮助。
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.
这篇关于优化SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
