Subquery kills performance - 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 2000 Forums

Transact-SQL (2000)

Subquery kills performance

Author

Topic

Rliss
Starting Member

31 Posts

Posted - 2006-07-28 : 15:37:38

Can anybody suggest an alternative? For each year_month I have a record for each day. What I want is to select only the 10 records for each month with the 10 highest octet_counts. This pulls the right data but the wait time is unacceptable...

SELECT TOP (100) PERCENT A.LINKSET_NAME, SUBSTRING(A.START_DATE_TEXT, 1, 6) AS YEAR_MONTH,
CASE [high_Trans_or_recv] WHEN 'T' THEN ([Trans_ISUP_Oct] + [Trans_SCCP_Oct] + [Trans_Other_Oct])
ELSE ([Recv_ISUP_Oct] + [Recv_SCCP_Oct] + [Recv_Other_Oct]) END AS HIGH_OCTET_COUNT,
CASE [high_Trans_or_recv] WHEN 'T' THEN B.TRANS_BANDWIDTH ELSE B.RECV_BANDWIDTH END AS TODAYS_BANDWIDTH
FROM dbo.LINKSET_DAILY_HIGH_VIEW AS A INNER JOIN
dbo.LINKSET_CURRENT_BANDWIDTH_VIEW AS B ON A.LINKSET_NAME = B.LINKSET_NAME
WHERE ((SELECT COUNT(1) AS Expr1
FROM dbo.LINKSET_DAILY_HIGH_VIEW
WHERE (LINKSET_NAME = A.LINKSET_NAME) AND (CONVERT(CHAR(6), START_DATE_TIME, 112) = CONVERT(CHAR(6), A.START_DATE_TIME, 112))
AND (HIGH_PCT_OCCUPANCY >= A.HIGH_PCT_OCCUPANCY)) <= 10)
ORDER BY A.LINKSET_NAME, year_month, HIGH_OCTET_COUNT DESC

The subquery in the where clause is what kills the performance..

Thanks,

RLiss

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-28 : 18:18:07

You sure its not in the Order By?? Did the execution plan show most of the hold up was in the Where statement or the Sorting??
--Nick

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-28 : 19:13:42

try
and HIGH_PCT_OCCUPANCY in (select top 10 b.HIGH_PCT_OCCUPANCY FROM dbo.LINKSET_DAILY_HIGH_VIEW b where b.LINKSET_NAME = A.LINKSET_NAME) AND (CONVERT(CHAR(6), b.START_DATE_TIME, 112) = CONVERT(CHAR(6), A.START_DATE_TIME, 112) order by b.HIGH_PCT_OCCUPANCY desc)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-07-29 : 04:57:56

I would have used a JOIN, but I don't know if it is faster. Also I would use Date Maths, rather than CONVERT, to compare dates without the time - that's definitely much faster - but in my solution I think you can get away with MIN/MAX which keeps everything in a date/time datatype:


SELECT TOP (100) PERCENT
	A.LINKSET_NAME, 
	SUBSTRING(A.START_DATE_TEXT, 1, 6) AS YEAR_MONTH, 
	CASE [high_Trans_or_recv] 
		WHEN 'T' 
			THEN ([Trans_ISUP_Oct] + [Trans_SCCP_Oct] + [Trans_Other_Oct]) 
		ELSE ([Recv_ISUP_Oct] + [Recv_SCCP_Oct] + [Recv_Other_Oct]) 
		END AS HIGH_OCTET_COUNT, 
	CASE [high_Trans_or_recv] 
		WHEN 'T' 
			THEN B.TRANS_BANDWIDTH 
			ELSE B.RECV_BANDWIDTH 
		END AS TODAYS_BANDWIDTH
FROM	dbo.LINKSET_DAILY_HIGH_VIEW AS A
	INNER JOIN dbo.LINKSET_CURRENT_BANDWIDTH_VIEW AS B
		ON A.LINKSET_NAME = B.LINKSET_NAME
	JOIN
	(
		SELECT	LINKSET_NAME, 
			HIGH_PCT_OCCUPANCY,
			MIN(START_DATE_TIME) AS dtStart,
			MAX(START_DATE_TIME) AS dtEnd
		FROM	dbo.LINKSET_DAILY_HIGH_VIEW
		GROUP BY LINKSET_NAME, 
			HIGH_PCT_OCCUPANCY
		HAVING	COUNT(*) > 10
	) X
		 ON X.LINKSET_NAME = A.LINKSET_NAME
		AND X.HIGH_PCT_OCCUPANCY >= A.HIGH_PCT_OCCUPANCY
		AND X.dtStart <= A.START_DATE_TIME
		AND X.dtEnd >= A.START_DATE_TIME
ORDER BY A.LINKSET_NAME, year_month, HIGH_OCTET_COUNT DESC

A composite index on LINKSET_NAME, HIGH_PCT_OCCUPANCY, START_DATE_TIME would help too.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources