如何加快此视图的速度，该视图对大量报价行行进行求和？

发布于 2024-10-15 02:44:44 字数 933 浏览 2 评论 0原文

我正在编写一个视图，根据报价行表中的值求和来显示报价总计。我需要将视图限制为仅显示特定“价格类型”客户的报价。然而，当我这样做时，视野会变慢很多。

对价格求和的 SQL

SELECT dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM dbo.quoteline LEFT OUTER JOIN dbo.pricelist ON dbo.quoteline.prodcode = dbo.pricelist.prodcode GROUP BY dbo.quoteline.qid

添加“pricetype”约束后

SELECT     dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM         dbo.pricelist RIGHT OUTER JOIN
                      dbo.client RIGHT OUTER JOIN
                      dbo.quote ON dbo.client.cid = dbo.quote.cid RIGHT OUTER JOIN
                      dbo.quoteline ON dbo.quote.qid = dbo.quoteline.qid ON dbo.pricelist.prodcode = dbo.quoteline.prodcode
WHERE     (dbo.client.pricetype = 'V')
GROUP BY dbo.quoteline.qid

也许已经晚了，我有时间，但这里的任何帮助将不胜感激。

原文

I am writing a view to show quote totals based on summing the values in a quote line table. I need to restrict the view to only show quotes for customers of a particular 'pricetype'. However when I do this the view slows down a lot.

SQL to sum the prices

SELECT dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM dbo.quoteline LEFT OUTER JOIN dbo.pricelist ON dbo.quoteline.prodcode = dbo.pricelist.prodcode GROUP BY dbo.quoteline.qid

SQL once 'pricetype' constraint is added

SELECT     dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM         dbo.pricelist RIGHT OUTER JOIN
                      dbo.client RIGHT OUTER JOIN
                      dbo.quote ON dbo.client.cid = dbo.quote.cid RIGHT OUTER JOIN
                      dbo.quoteline ON dbo.quote.qid = dbo.quoteline.qid ON dbo.pricelist.prodcode = dbo.quoteline.prodcode
WHERE     (dbo.client.pricetype = 'V')
GROUP BY dbo.quoteline.qid

Maybe its late and I am having a moment but any help here would be gratefully appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜妞爱困 2024-10-22 02:44:44

有两件事：首先，您能否在 dbo.client.pricetype 列上放置索引而不干扰插入/更新？其次，内部联接通常比外部联接更快，并且由于您的结果和 where 子句依赖于其他表，我怀疑您无论如何都会想要进行内部联接，除非您需要从视图中返回 NULL 记录。尝试以下查询，看看是否能得到您需要的结果：

SELECT dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM dbo.quoteline 
INNER JOIN dbo.pricelist ON dbo.quoteline.prodcode = dbo.pricelist.prodcode 
INNER JOIN dbo.quote ON dbo.quote.qid = dbo.quoteline.qid 
INNER JOIN dbo.client ON dbo.client.cid = dbo.quote.cid 
WHERE     (dbo.client.pricetype = 'V')
GROUP BY dbo.quoteline.qid

Two things: First, can you put an index on the dbo.client.pricetype column without it interfering with inserts/updates? Secondly, inner joins are generally faster than outer joins and since your results and where clause depend on the other tables, I suspect you will want to do inner joins anyways unless there are NULL records you need back from your view. Try this following query to see if it gets you the results you need:

SELECT dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM dbo.quoteline 
INNER JOIN dbo.pricelist ON dbo.quoteline.prodcode = dbo.pricelist.prodcode 
INNER JOIN dbo.quote ON dbo.quote.qid = dbo.quoteline.qid 
INNER JOIN dbo.client ON dbo.client.cid = dbo.quote.cid 
WHERE     (dbo.client.pricetype = 'V')
GROUP BY dbo.quoteline.qid

回复收藏 0 原文

心奴独伤 2024-10-22 02:44:44

如果你这样做会发生什么：

SELECT     dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM         dbo.pricelist RIGHT OUTER JOIN
                      dbo.client ON dbo.client.pricetype = 'V' RIGHT OUTER JOIN
                      dbo.quote ON dbo.client.cid = dbo.quote.cid RIGHT OUTER JOIN
                      dbo.quoteline ON dbo.quote.qid = dbo.quoteline.qid AND dbo.pricelist.prodcode = dbo.quoteline.prodcode
GROUP BY dbo.quoteline.qid

What does happen if you do it like this :

SELECT     dbo.quoteline.qid, SUM((dbo.pricelist.listprice - dbo.quoteline.voff) * dbo.quoteline.quantity) AS total
FROM         dbo.pricelist RIGHT OUTER JOIN
                      dbo.client ON dbo.client.pricetype = 'V' RIGHT OUTER JOIN
                      dbo.quote ON dbo.client.cid = dbo.quote.cid RIGHT OUTER JOIN
                      dbo.quoteline ON dbo.quote.qid = dbo.quoteline.qid AND dbo.pricelist.prodcode = dbo.quoteline.prodcode
GROUP BY dbo.quoteline.qid

回复收藏 0 原文

~没有更多了~