January 26, 2011 at 2:21 pm
So, I have this query:
Select
l.Brand + Cast(l.trkey_cd as varchar) "provider-listingid"
,(
Select
dbo.fn_GetEpochFromDateTime (Cast(cast(yearof as varchar) + '/' + cast(monthof as varchar) + '/' + cast(dayof as varchar) as datetime)) epoch
,action_cd
,price
From
dbo.rtsListingActions a
Where
a.trkey_cd = l.trkey_cd
anda.action_cd = 'CP'
Order By
YearOf Desc, MonthOf Desc, DayOf Desc
For XML Path ('event'), type
) events
From
dbo.vwListings_Base l
Where Exists
(Select * from dbo.rtsListingActions la
Where la.trkey_cd = l.trkey_cd and la.action_cd = 'CP')
For XML Path ('listing'), Root ('listingevents')
Note that I've included a 'where exists' subquery that points to the same rtsListingActions table as the XML type subquery does, as I don't want the outer rows to appear where the subquery is empty.
But this seems very inefficient to me, so I wondered if there's a better means to do this, as rtsListingActions is a really huge table.
I was hoping to replace the Exists subquery w/something like
Where events.event.Action_cd is not null
Which of course doesn't work, but I don't know the syntax to access my subquery when it's XML type like it is.
Is there a better way to do this, or did I basically do it as 'right' as possible already?
TIA,
Brett
January 26, 2011 at 3:38 pm
At a first glance there should be an easier solution.
But, like others, I prefer to test my solution before posting.
So, please provide table def, sample data and expected result as described in the first link in my signature.
As a side note: I'm not sure what's inside your dbo.fn_GetEpochFromDateTime() function. But that might be the next bottleneck, since it'll be called for each and every row. I strongly recommend to evaluate if a conversion into an iTVF is an option.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply