Click here to Skip to main content
14,978,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
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?
Posted

1 solution

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
SQL
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
SQL
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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900