April 20, 2018 at 10:14 am
Is this
select (select top 1 organisation from sysfile) as org,* from area1
More efficient than a join ?
i.e. will it do the subquery to get the organisation and then release the share lock straight away on sysfile ?
organisation is the same for every row of area 1 so a join in not needed as such
Thanks
April 20, 2018 at 10:19 am
You'll probably get exactly the same execution plan either way.
But for ease of clarity, coding and maintenance, I'd write it this way:
SELECT sf.org, a1.*
FROM area1 a1
CROSS JOIN (
SELECT TOP (1) organisation AS org
FROM sysfile
) AS sf
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 23, 2018 at 3:05 am
Thanks
So you are saying it does not matter much where in the query the access to sysfile goes it's going to get a shared lock on it that will last as many rows as are in Area1 ?
April 23, 2018 at 3:32 am
If you're comfortable with the fact that if the subquery runs first and then releases its lock then its results may have changed by the time the main query has finished, then you could run it separately, just for peace of mind.
DECLARE @org sysname -- or whatever data type it is
SELECT @org = MAX(organisation) -- if you're looking for the highest value of organisation
FROM sysfile
Bear in mind that TOP without an ORDER BY isn't going to give you a deterministic result.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply