如何优化子查询 [英] How to optimize sub query

问题描述

  SELECT  cb.COLLECTIVE_BILLING_NBR，
 cb.COLLECTIVE_BILLING_ID，
 cc.CUSTOMER_ID，
 cc.CUSTOMER_NBR，
 
（
  SELECT  SUM（ CASE  < span class =code-keyword> WHEN  INVOICE_TYPE = '  OD'  OR  INVOICE_TYPE = '  SI'  THEN  ISNULL（INVOICE_AMT， 0 ） ELSE  ISNULL（INVOICE_AMT *（ -   1 ）， 0 ） END ）  AS  Expr1 
  FROM  dbo.MFS_AR_INVOICE  AS  ai 
  WHERE （AR_INV_ID  IN （
  SELECT   DISTINCT  AR_INV_ID 
  FROM  dbo.MFS_AR_COLLECTIVE_BILLING_LINE  AS  acbl 
  WHERE （COLLECTIVE_BILLING_ID = cb.COLLECTIVE_BILLING_ID））
）
） AS  TOTAL_AMOUNT，
 
 CodeDesctTemp .CODE_ID，
 CodeDesctTemp.CODE_DESCR，
 
  FROM  dbo.MFS_AR_COLLECTIVE_BILLING  AS  cb  INNER   JOIN  
 dbo.MFS_CUST_CUSTOMER  AS  cc  ON  cb.CUSTOMER_ID = cc.CUSTOMER_ID  INNER   JOIN  
（ SELECT  CODE_DESCR，CODE_ID 
  FROM  dbo.MFS_UTIL_STC_CODE_DESCR 
  WHERE （CODE_TYPE = '   APPROVAL_STATUS'） AND （LANGUAGE_CODE = '   en-US'）） AS  CodeDesctTemp  ON  CodeDesctTemp.CODE_ID = cb.APPROVAL_STATUS 

我有以上查询

因为你知道我选择内部选择（子查询）

任何建议让它更简单，更快？

解决方案

< blockquote>首先，如果不了解您拥有的数据结构和数据，就不可能说出如何修改查询。所以每次修改都需要验证。



但是有些事情需要考虑。子查询优化与任何查询一样，您确保拥有数据的快速访问路径。如果子查询的数据量很小，那么你应该有适当的索引。



例如 MFS_UTIL_STC_CODE_DESCR table可以有一个包含字段的索引：

- CODE_TYPE

- LANGUAGE_CODE

- CODE_ID

这个如果要获取的数据量与表中的整体数据相比较小，那么索引可能有助于获取相关数据。



同样你应该制作确保表 MFS_AR_COLLECTIVE_BILLING_LINE 在列 COLLECTIVE_BILLING_ID 上有一个索引，依此类推。



由于在字段列表的末尾有一个额外的逗号，查询可能无法执行。



如上所述知道数据后不可能说出可以进行哪种修改但你可以尝试修改主要的FROM子句来自

  FROM  dbo.MFS_AR_COLLECTIVE_BILLING  AS  cb  INNER   JOIN  
 dbo.MFS_CUST_CUSTOMER  AS  cc  ON  cb.CUSTOMER_ID = cc .CUSTOMER_ID  INNER   JOIN  
（ SELECT  CODE_DESCR，CODE_ID 
  FROM  dbo.MFS_UTIL_STC_CODE_DESCR 
  WHERE （CODE_TYPE = < span class =code-string>'  APPROVAL_STATUS'） AND （LANGUAGE_CODE = < span class =code-string>'  en-US'）） AS  CodeDesctTemp  ON  CodeDesctTemp.CODE_ID = cb.APPROVAL_STATUS 

to

  FROM  dbo.MFS_AR_COLLECTIVE_BILLING  AS  cb 
  INNER   JOIN  dbo.MFS_CUST_CUSTOMER  AS  cc  ON  cb.CUSTOMER_ID = cc .CUSTOMER_ID 
  INNER   JOIN  dbo.MFS_UTIL_STC_CODE_DESCR CodeDesctTemp  ON  CodeDesctTemp.CODE_ID = cb.APPROVAL_STATUS 
  WHERE  CodeDesctTemp.CODE_TYPE = '  APPROVAL_STATUS' 
  AND  CodeDesctTemp.LANGUAGE_CODE = '   en-US' 

该修改可以帮助从查询中删除一些复杂性我已经正确地解释了条件。

SELECT cb.COLLECTIVE_BILLING_NBR,
	cb.COLLECTIVE_BILLING_ID,
	cc.CUSTOMER_ID,
	cc.CUSTOMER_NBR,

	(
		SELECT SUM(CASE WHEN INVOICE_TYPE = 'OD' OR INVOICE_TYPE = 'SI' THEN ISNULL(INVOICE_AMT, 0) ELSE ISNULL(INVOICE_AMT * (- 1), 0) END) AS Expr1
		FROM dbo.MFS_AR_INVOICE AS ai
		WHERE (AR_INV_ID IN (
								SELECT DISTINCT AR_INV_ID
								FROM dbo.MFS_AR_COLLECTIVE_BILLING_LINE AS acbl
								WHERE (COLLECTIVE_BILLING_ID = cb.COLLECTIVE_BILLING_ID))
				)
	) AS TOTAL_AMOUNT,

	CodeDesctTemp.CODE_ID,
	CodeDesctTemp.CODE_DESCR,

FROM dbo.MFS_AR_COLLECTIVE_BILLING AS cb INNER JOIN
	dbo.MFS_CUST_CUSTOMER AS cc ON cb.CUSTOMER_ID = cc.CUSTOMER_ID INNER JOIN
	(SELECT CODE_DESCR, CODE_ID
		FROM dbo.MFS_UTIL_STC_CODE_DESCR
		WHERE (CODE_TYPE = 'APPROVAL_STATUS') AND (LANGUAGE_CODE = 'en-US')) AS CodeDesctTemp ON CodeDesctTemp.CODE_ID = cb.APPROVAL_STATUS

I have query as above
as u know i have select inner select (sub query)
any suggestion to make it simpler and faster?

解决方案

First of all it's quite impossible to say how the query should be modified without the knowledge of the data structures and data you have. So every modification need to be verified.

However there are things to consider. Sub query optimization is just like with any query, you make sure that you have a fast access path to the data. If the amount of data the subquery is fetching is small then you should have proper indexes in place.

For example the MFS_UTIL_STC_CODE_DESCR table could have an index containing fields:
- CODE_TYPE
- LANGUAGE_CODE
- CODE_ID
This kind of index would perhaps help to fetch the relevant data in case the amount of data to fetch is small compared to the overall data in the table.

Similarly you should make sure that the table MFS_AR_COLLECTIVE_BILLING_LINE has an index on column COLLECTIVE_BILLING_ID and so on.

What comes to the query it probably won't execute since there's an extra comma in the end of the field list.

As said without knowing the data it's impossible to say what kind of modifications can be done but you could try modifying the main FROM clause from

FROM dbo.MFS_AR_COLLECTIVE_BILLING AS cb INNER JOIN
	dbo.MFS_CUST_CUSTOMER AS cc ON cb.CUSTOMER_ID = cc.CUSTOMER_ID INNER JOIN
	(SELECT CODE_DESCR, CODE_ID
		FROM dbo.MFS_UTIL_STC_CODE_DESCR
		WHERE (CODE_TYPE = 'APPROVAL_STATUS') AND (LANGUAGE_CODE = 'en-US')) AS CodeDesctTemp ON CodeDesctTemp.CODE_ID = cb.APPROVAL_STATUS

to

FROM dbo.MFS_AR_COLLECTIVE_BILLING AS cb 
     INNER JOIN	dbo.MFS_CUST_CUSTOMER AS cc ON cb.CUSTOMER_ID = cc.CUSTOMER_ID 
     INNER JOIN dbo.MFS_UTIL_STC_CODE_DESCR CodeDesctTemp ON CodeDesctTemp.CODE_ID = cb.APPROVAL_STATUS
WHERE CodeDesctTemp.CODE_TYPE = 'APPROVAL_STATUS'
AND   CodeDesctTemp.LANGUAGE_CODE = 'en-US'

That modification could help to remove some complexity from the query, again taken that I've interpreted the conditions correctly.

这篇关于如何优化子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！