当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Simple Query Performance Issues

问题描述:

I have two queries that run back to back and fill two separate grids. They don't run in the same instance of an sql query, but back to back no less.

I have ran these two queries in SSMS and it's about 5 seconds per query. BO_HRO has ~600,000 rows and BO_HParts has ~1,200,000 rows with a one-to-many relationship respectively. I also have an index as suggested by SSMS on BO_HRO of deleted_by, RO_NO, and BName as well as an index on BO_HParts of deleted_by, RO_NO, Quanity, PartNo, and PartDesc.

SELECT TOP 10000 bo_hparts.partno,

bo_hparts.partdesc,

SUM(bo_hparts.quanity) AS qtysum

FROM bo_hparts INNER JOIN bo_hro ON bo_hparts.ro_no = bo_hro.ro_no

WHERE bo_hparts.deleted_by < 0 AND bo_hro.deleted_by < 0

GROUP BY bo_hparts.partno, bo_hparts.partdesc

ORDER BY SUM(bo_hparts.quanity) DESC, bo_hparts.partno;

SELECT TOP 10000 bo_hro.bname,

bo_hparts.partno,

bo_hparts.partdesc,

SUM(bo_hparts.quanity) AS qtysum

FROM bo_hparts INNER JOIN bo_hro ON bo_hparts.ro_no = bo_hro.ro_no

WHERE bo_hparts.deleted_by < 0 AND bo_hro.deleted_by < 0

GROUP BY bo_hro.bname, bo_hparts.partno, bo_hparts.partdesc

ORDER BY SUM(bo_hparts.quanity) DESC, bo_hro.bname, bo_hparts.partno

My boss is still asking for increased performance and I'm not sure what else to do to pull that off. Is there any other way to boost the speed?

I have an execution plan here https://dl.dropboxusercontent.com/u/99733863/BO_HParts%20Join%20BO_HRO.sqlplan

网友答案:

Here are some ideas:

Change the where clause to an explicit comparison:

WHERE bo_hparts.deleted_by = -1 AND bo_hro.deleted_by = -1

This will allow you to use composite indexes, such as bo_hparts(deleted_by, ro_no). That might help the query.

Create a covering index for bo_hro. This would be bo_hro(ro_no, deleted_by, quantity). If the records are wide, this might help.

A covering index for bo_hparts might also help, but this is probably a smaller table, so it might not be as important.

If there is a minimum quantity that you care about, then try adding:

having sum(bo_hparts.quantity) > 1000

This could make the order by more efficient.

In the end, though, what you are trying to do is computationally intensive. If you need real-time performance for this type of query, you might need to think about using a trigger to keep the summary data up-to-date. This would reduce the query just to an order by -- which you could even optimize by having an index in the summary data.

网友答案:

Perhaps change your group by clause, so use table aliases/names?

分享给朋友:
您可能感兴趣的文章:
随机阅读: