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.
| Author |
Topic |
|
Buzzard724
Yak Posting Veteran
66 Posts |
 Posted - 2009-11-24 : 08:08:18
|
| The question I have is it "better" (faster, more efficient) to use Cross Apply and Outer Apply rather than sub-queries in the following cases. If so then what are the benefits? Basically I want to work out whether it is worth re-writing the sub-queries. It seems to make sense to rewrite them using cross apply when there are multiple sub-queries used to return different fields from the same table(s) - I am less sure e.g. with the Count example below - when the one cross apply statement replaces just one sub-query.EXAMPLE 1e.g. I have a View with about 50 columns. Many of the columns use a sub-query such as (Select Count(createdBy) from Jobdetail J where J.People_ID = P.People_ID And J.Currentrecord = 'Yes') AS CountActiveJobs,the same result can be achieved with Outer ApplyOUTER APPLY (Select Count(createdBy) AS CountActiveJobs from Jobdetail J where J.People_ID = P.People_ID And J.Currentrecord = 'Yes') JCCOAEXAMPLE 2 in the same view(Select Top 1 O2.ABBREVIATION from JobDetail J Inner Join OrgLevel1 O2 on O2.OrgLevel1_id = J.AuthorisationLocationWhere J.People_id = P.People_id AND J.Currentrecord = 'Yes' AND J.SCRPRIMARYROLE = 'T' order by J.DateCommencementInJob desc)AS AuthorisationlocationSCRJob,this sub-query is repeated several times to bring back a different field. And the same result can be achieved using Cross Apply - with the cross apply bringing back all of the fieldsCROSS APPLY (Select Top 1 O2.* from JobDetail J Inner Join OrgLevel1 O2 on O2.OrgLevel1_id = J.AuthorisationLocationWhere J.People_id = P.People_id AND J.Currentrecord = 'Yes' AND J.SCRPRIMARYROLE = 'T' order by J.DateCommencementInJob desc) JOCAthanks for your help |
|
|
|
|
|
Optimising sub queries using outer apply