Referencing within nested select query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Referencing within nested select query?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-29 : 09:25:38

I have a query that has a nested select statement within the Where clause. The way how I have it takes forever for it to retreive the records and not very efficient. Can anybody look at the query I have here and see if there's a way to reference a field from the main query within that nested query? The join in bold is what I'm trying to reference it with the Product_Code from the main query, but this is taking forever. If I put in the ProductCode parameter, it takes 2 seconds. If I use the second query method, it takes about 30 minutes. Any ideas.

Actual Query:

SELECT SUM(ACT_MATERIAL_COST + ACT_LABOR_COST + ACT_BURDEN_COST + ACT_SERVICE_COST) AS TOTALCOST,
COL.PRODUCT_CODE
FROM INVENTORY_TRANS IT INNER JOIN CUST_ORDER_LINE COL
ON IT.CUST_ORDER_ID = COL.CUST_ORDER_ID
WHERE TRANSACTION_ID IN
(SELECT MAX(transaction_id)
FROM cust_order_line, inventory_trans, customer_order
WHERE cust_order_line.cust_order_id = INVENTORY_TRANS.cust_order_id AND
customer_order.id = cust_order_line.cust_order_id AND
cust_order_line.line_no = INVENTORY_TRANS.cust_order_line_no AND INVENTORY_TRANS.type = 'O' AND
INVENTORY_TRANS.class = 'I' AND cust_order_line.product_code = @ProductCode AND
cust_order_line.last_shipped_date BETWEEN '01/01/2004' and '12/31/204'
GROUP BY cust_order_line.CUST_ORDER_ID, cust_order_line.LINE_NO)
GROUP BY COL.PRODUCT_CODE

What I want to achieve:

SELECT SUM(ACT_MATERIAL_COST + ACT_LABOR_COST + ACT_BURDEN_COST + ACT_SERVICE_COST) AS TOTALCOST,
COL.PRODUCT_CODE
FROM INVENTORY_TRANS IT INNER JOIN CUST_ORDER_LINE COL
ON IT.CUST_ORDER_ID = COL.CUST_ORDER_ID
WHERE TRANSACTION_ID IN
(SELECT MAX(transaction_id)
FROM cust_order_line, inventory_trans, customer_order
WHERE cust_order_line.cust_order_id = INVENTORY_TRANS.cust_order_id AND
customer_order.id = cust_order_line.cust_order_id AND
cust_order_line.line_no = INVENTORY_TRANS.cust_order_line_no AND INVENTORY_TRANS.type = 'O' AND
INVENTORY_TRANS.class = 'I' AND cust_order_line.product_code = COL.PRODUCT_CODE AND
cust_order_line.last_shipped_date BETWEEN '01/01/2004' and '12/31/204'
GROUP BY cust_order_line.CUST_ORDER_ID, cust_order_line.LINE_NO)
GROUP BY COL.PRODUCT_CODE

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-29 : 10:39:40

By adding a reference in the nested query to an outer column, you've turned the nested statement into a correlated subquery. The sub-query runs 1 time FOR EACH matching product_code in the outer query. That can be very inefficient. One idea would be to change the nested sub-query into a derived table and include product_code as a joined column.

btw, you should change the sub-query's old style syntax to the newer ansi sql-92 standard like you have in the outer query.

I don't know if I've got your business logic correct but this is the general idea. I'm sure you'll need to tweak stuff around based on your record counts, indexes, etc:


SELECT	SUM(ACT_MATERIAL_COST + ACT_LABOR_COST + ACT_BURDEN_COST + ACT_SERVICE_COST) AS TOTALCOST
	,COL.PRODUCT_CODE
FROM	INVENTORY_TRANS IT 
JOIN	UST_ORDER_LINE COL
		ON IT.CUST_ORDER_ID = COL.CUST_ORDER_ID
JOIN
	(
	SELECT	cust_order_line.CUST_ORDER_ID
		,cust_order_line.LINE_NO
		,cust_order_line.product_code
		MAX(transaction_id) transaction_id
	FROM	cust_order_line
	JOIN	inventory_trans
			ON cust_order_line.cust_order_id = INVENTORY_TRANS.cust_order_id
			AND cust_order_line.line_no = INVENTORY_TRANS.cust_order_line_no
	JOIN	customer_order
			ON customer_order.id = cust_order_line.cust_order_id
	WHERE	 INVENTORY_TRANS.type = 'O'
	AND	INVENTORY_TRANS.class = 'I' 
	--AND	cust_order_line.product_code = @ProductCode
	AND	cust_order_line.last_shipped_date BETWEEN '01/01/2004' and '12/31/204'
	GROUP BY cust_order_line.CUST_ORDER_ID
		,cust_order_line.LINE_NO
		,cust_order_line.product_code
	) t
		ON t.cust_order_id = id.cust_order_id
		and t.line_no = col.Line_no
		and t.transaction_id = it.transaction_id
		and t.product_code = col.product_code
GROUP BY COL.PRODUCT_CODE

Be One with the Optimizer
TG

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-29 : 10:47:16

Thanks TG. I did what you provided, and removed and added a couple of stuff and now it's working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources