SQL HELP - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

SQL HELP

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-06-11 : 10:25:47

I think this query very poor by performance,select a b c d e f from product cost table how can i control null control in one null or another operator.

SELECT ISNULL((
SELECT TOP 1

A+B
FROM
PRODUCT_COST WITH (NOLOCK)
WHERE
START_DATE <= {ts '2012-05-31 00:00:00'}
AND PRODUCT_ID = S.PRODUCT_ID

ORDER BY
START_DATE DESC,
RECORD_DATE DESC,
PRODUCT_COST_ID DESC
),0) EX_1,
ISNULL((
SELECT TOP 1

C+D

FROM
PRODUCT_COST WITH (NOLOCK)
WHERE
START_DATE <= {ts '2012-06-11 00:00:00'}
AND PRODUCT_ID = S.PRODUCT_ID

ORDER BY
START_DATE DESC,
RECORD_DATE DESC,
PRODUCT_COST_ID DESC
),0) EX2,
ISNULL((
SELECT TOP 1

D+E

FROM
PRODUCT_COST WITH (NOLOCK)
WHERE
START_DATE <= {ts '2012-06-11 00:00:00'}
AND PRODUCT_ID =S.PRODUCT_ID

ORDER BY
START_DATE DESC,
RECORD_DATE DESC,
PRODUCT_COST_ID DESC
),0) EX3

FROM
STOCKS S WITH (NOLOCK)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-11 : 12:53:51

please post some sample data with ddl and dml

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-11 : 14:25:16

without knowing you data, table structure or indexing, it's hard to say. Here is a guess that may or may not be faster:

SELECT
	COALESCE(A.A + A.B, 0) AS EX_1,
	COALESCE(A.C + A.D, 0) AS EX2,
	...
FROM 
	STOCKS S WITH (NOLOCK)
LEFT OUTER JOIN
	(
		SELECT 
			A,
			B,
			C,
			D,
			E,
			PRODUCT_ID,
			ROW_NUMBER() OVER 
			(
				PARTITION BY 
					PRODUCT_ID 
				ORDER BY 
					START_DATE DESC, 
					RECORD_DATE DESC,
					PRODUCT_COST_ID DESC
			) AS RowNum
		FROM 
			PRODUCT_COST WITH (NOLOCK) 
		WHERE 
			START_DATE <= CAST('2012-06-11T00:00:00' AS DATETIME)
	) AS A
ON
	S.PRODUCT_ID = A.PRODUCT_ID
	AND RowNum = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:38:57

i didnt understand what you mean by
how can i control null control in one null or another operator

can you elaborate with an example

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources