How to optimize sub query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 12-Jul-15 21:32pm

gunkrr

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2015-07-12T21:56:00

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.