Improving performance over query with outer apply

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

Transact-SQL (2005)

Improving performance over query with outer apply

Author

Topic

sfortner
Yak Posting Veteran

63 Posts

Posted - 2011-08-08 : 17:07:02

I created a report in SSRS that shows company assets that haven't had either 10 miles or 10 hours logged for a given time period. I made an effort with this query to not use a cursor, so I deserve some sort of medal : ), but it's still running slow - around 28 seconds. Running the mileage (shown below) or hours piece by themselves only takes around 3 seconds, but when I union them, then wrap that in a derived query to get distinct unit numbers, it's 28 seconds. I feel that there's probably a slick way to get this to perform better, but not sure how. Maybe a CTE?

We have two tables and a view in this query that are used to retrieve asset year, type (Locomotive, Trailer, Dozer, etc.), ID, and unit number. The ID is used to hyperlink to our Fleet Manager site, and unit number is a unique number assigned to the asset or piece of equipment. The view is vw_AssetHistory and it is made up of 10 tables it looks like. Could probably create a simpler view, but since the individual queries run in 3 seconds, I don't think that's the core problem. I've applied statistics and indexes suggested by DTA, and it helped a lot, but not that much. Here's what I have so far - any suggestions much appreciated:

select a.asset_unit_number, x.history_newvalue, y.history_oldvalue from assets a
outer apply
	(select top 1 *
	from vw_AssetHistory where asset_report_date between '07/01/2010' and '07/31/2010' and new_property_description = 'mileage'
	and asset_unit_number = a.asset_unit_number and history_newvalue is not null
	order by asset_report_date desc, history_newvalue desc) as x
outer apply
	(select top 1 *
	from vw_AssetHistory where asset_report_date between '07/01/2010' and '07/31/2010' and new_property_description = 'mileage'
	and asset_unit_number = a.asset_unit_number and history_oldvalue is not null
	order by asset_report_date, history_oldvalue) as y
where x.history_newvalue is not null and y.history_oldvalue is not null and cast(replace(isnull(x.history_newvalue, 0), ',', '') as decimal(12,2)) 
- cast(replace(isnull(y.history_oldvalue, 0), ',', '') as decimal(12,2)) < 10

--Steve

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-08 : 20:15:34

select a.asset_unit_number, x.history_newvalue, y.history_oldvalue from assets a
outer apply
(select max(history_newvalue) as history_newvalue
from vw_AssetHistory where asset_report_date between '07/01/2010' and '07/31/2010' and new_property_description = 'mileage'
and asset_unit_number = a.asset_unit_number and history_newvalue is not null) as x
outer apply
(select max(history_oldvalue) as history_oldvalue
from vw_AssetHistory where asset_report_date between '07/01/2010' and '07/31/2010' and new_property_description = 'mileage'
and asset_unit_number = a.asset_unit_number and history_oldvalue is not null) as y
where x.history_newvalue is not null and y.history_oldvalue is not null and cast(replace(isnull(x.history_newvalue, 0), ',', '') as decimal(12,2))
- cast(replace(isnull(y.history_oldvalue, 0), ',', '') as decimal(12,2)) < 10

Not sure if there are columns in the x and y derived tables or not that you'd need to group by. I believe this will help IF there is no index.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources