CONTEXT_INFO and query plans - 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 2005 Forums

Transact-SQL (2005)

CONTEXT_INFO and query plans

Author

Topic

Jenda
Starting Member

29 Posts

Posted - 2007-08-27 : 10:37:51

If I run this query:


select * from dbo.Foo
UNION ALL
select * from dbo.Foo WHERE 1 = 0

with SET STATISTICS IO turned on I can see that it scans the dbo.Foo just once. If I define a function like this


CREATE FUNCTION dbo.CurrentUserId() RETURNS int
AS
BEGIN
	RETURN Convert(int, substring(CONTEXT_INFO(), 3, 4))
END

and run


select * from dbo.Foo
UNION ALL
select * from dbo.Foo WHERE dbo.CurrentUserId() = 0

while the CONTEXT_INFO contains some data and the UserId is not zero, the table is scanned just once as well. But when I run the complex query containing five subqueries merged with UNION ALL, one for each type of user as returned by dbo.CurrentUserType(), the table gets scanned five times. Why? Why does MSSQL2005 notice in the case above that the dbo.CurrentUserId() does not equal zero and thus the second select cannot ever return anything and doesn't in the other? I tried to add some more conditions to the WHERE clause, replace the SELECT * by SELECT Some, Columns, I tried to define a view for the query above and select * from the_query and ... no luck. Still one scan in the tests, five in the real query.

The scary query follows:


CREATE VIEW dbo.fSites
AS

	SELECT SiteID, PublisherID, SiteName, URL, Cost, Commission, Currency, Days, ModifyDelete, Notes, Sites.Active,
		PackageNotes, AllowsPackage,
		AllowsSingle, DirtyIsUsed, ReplId, ReplAllowed, ReplPending,
		LastExported, LastImported, Sites.CountryID,
		DaysPackage, DefaultDisciplineID, IsPublic,
		ComputeMembershipPoints, ComputeJobCost, ComputeParams,
		ComputeMembershipExplanation, ComputePriceExplanation,
		ComputeExpireDate, ComputeExpireDatePackage,
		SupportsAutopost, Niche
	  FROM dbo.Sites
	 WHERE 'SA' = dbo.CurrentUserType()

UNION ALL

	SELECT SiteID, PublisherID, SiteName, URL, Cost, Commission, Currency, Days, ModifyDelete, Notes, Sites.Active,
		PackageNotes, AllowsPackage,
		AllowsSingle, DirtyIsUsed, ReplId, ReplAllowed, ReplPending,
		LastExported, LastImported, Sites.CountryID,
		DaysPackage, DefaultDisciplineID, IsPublic,
		ComputeMembershipPoints, ComputeJobCost, ComputeParams,
		ComputeMembershipExplanation, ComputePriceExplanation,
		ComputeExpireDate, ComputeExpireDatePackage,
		SupportsAutopost, Niche
	  FROM dbo.Sites
	 WHERE dbo.CurrentUserType() = 'CA' and Sites.CountryID = dbo.CurrentCountryId()

UNION ALL

	SELECT SiteID, PublisherID, SiteName, URL, Cost, Commission, Currency, Days, ModifyDelete, Notes, Sites.Active,
		PackageNotes, AllowsPackage,
		AllowsSingle, DirtyIsUsed, ReplId, ReplAllowed, ReplPending,
		LastExported, LastImported, Sites.CountryID,
		DaysPackage, DefaultDisciplineID, IsPublic,
		ComputeMembershipPoints, ComputeJobCost, ComputeParams,
		ComputeMembershipExplanation, ComputePriceExplanation,
		ComputeExpireDate, ComputeExpireDatePackage,
		SupportsAutopost, Niche
	  FROM dbo.Sites
	 WHERE dbo.CurrentUserType() in ('BM', 'AE') and Sites.CountryID = dbo.CurrentCountryId()
	   and IsPublic = 1

UNION ALL

	SELECT Sites.SiteID, PublisherID, SiteName, URL, Cost, Commission, Currency, Days, ModifyDelete, Sites.Notes, Sites.Active,
		PackageNotes, AllowsPackage,
		AllowsSingle, DirtyIsUsed, ReplId, ReplAllowed, ReplPending,
		LastExported, LastImported, Sites.CountryID,
		DaysPackage, DefaultDisciplineID, IsPublic,
		ComputeMembershipPoints, ComputeJobCost, ComputeParams,
		ComputeMembershipExplanation, ComputePriceExplanation,
		ComputeExpireDate, ComputeExpireDatePackage,
		SupportsAutopost, Niche
	  FROM dbo.Sites
	  LEFT JOIN dbo.GroupSites WITH (NOLOCK) ON GroupSites.GroupID = dbo.CurrentGroupId() and GroupSites.SiteID = Sites.SiteID
	 WHERE dbo.CurrentUserType() = 'GM' and Sites.CountryID = dbo.CurrentCountryId()
	   and not exists (
		SELECT *
		  FROM dbo.ATSSiteFilter WITH (NOLOCK)
		 WHERE Sites.SiteID = ATSSiteFilter.SiteID
		   AND ATSID = (SELECT ATSID FROM dbo.ATSUserMapping WITH (NOLOCK) WHERE ViperUserId = dbo.CurrentUserId() AND Main = 1)
		)
	   and
		(
		   dbo.CurrentUserCanSeePublic() = 1
		   and (
				Sites.IsPublic = 1 -- is public by default
				and (
					Allow is null -- no special rights for this group
					or Allow = 1  -- allowed to this group
				)
			or
				Sites.IsPublic = 0 -- is private by default
				and (Allow = 1) -- and is explicitely allowed to the group
			)
		or
		   dbo.CurrentUserCanSeePublic() = 0	-- the group cannot see site
			and (Allow = 1) -- that is not explicitely allowed to the group
		)

UNION ALL

	SELECT Sites.SiteID, PublisherID, SiteName, URL, Cost, Commission, Currency, Days, ModifyDelete, Sites.Notes, Sites.Active,
		PackageNotes, AllowsPackage,
		AllowsSingle, DirtyIsUsed, ReplId, ReplAllowed, ReplPending,
		LastExported, LastImported, Sites.CountryID,
		DaysPackage, DefaultDisciplineID, IsPublic,
		ComputeMembershipPoints, ComputeJobCost, ComputeParams,
		ComputeMembershipExplanation, ComputePriceExplanation,
		ComputeExpireDate, ComputeExpireDatePackage,
		SupportsAutopost, Niche
	  FROM dbo.Sites
	  LEFT JOIN dbo.GroupSites WITH (NOLOCK) ON GroupSites.GroupID = dbo.CurrentGroupId() and GroupSites.SiteID = Sites.SiteID
	  LEFT JOIN dbo.UserSites WITH (NOLOCK) ON UserSites.UserID = dbo.CurrentUserId() and UserSites.SiteID = Sites.SiteID
	 WHERE dbo.CurrentUserType() = 'GU' and Sites.CountryID = dbo.CurrentCountryId()
	   and (
			AllowsSingle = 1
			and (
				Cost = 0 and dbo.CurrentUserCan(0x10000/*=UserRights.CanPostFreeJobs*/) = 1
				or
				Cost <> 0 and dbo.CurrentUserCan(0x10/*=UserRights.CanPostSingleJobs*/) = 1
			)
		or
			AllowsPackage = 1
			and (
				dbo.CurrentUserCan(0x8/*=UserRights.CanPurchasePackage*/) = 1
				or
				Cost = 0 and dbo.CurrentUserCan(0x4/*=UserRights.CanAddFreePackage*/) = 1
				or
				Cost <> 0 and dbo.CurrentUserCan(0x1000/*=UserRights.CanAddPaidPackage*/) = 1
				or
				dbo.CurrentUserCan(0x20/*=UserRights.CanPostPackageJobs*/) = 1
				 and exists (SELECT *
							   FROM dbo.qPackages_ATS_Valid as P with (nolock)
							  WHERE P.UserId = dbo.CurrentUserId() and P.UserType = dbo.CurrentUserType()
							    and P.SiteId = Sites.SiteId)
			)
		)
	   and not exists ( -- is it filtered because of the ATS?
		SELECT *
		  FROM dbo.ATSSiteFilter as ASF WITH (NOLOCK)
		  JOIN dbo.ATSUserMapping as ASM WITH (NOLOCK) ON ASF.ATSID = ASM.ATSID
		 WHERE Sites.SiteID = ASF.SiteID and ASM.ViperUserId = dbo.CurrentUserId() AND ASM.Main = 1
		)

	   and
		(
		   dbo.CurrentUserCanSeePublic() = 1
		   and (
				Sites.IsPublic = 1 -- is public by default
				and (
					GroupSites.Allow is null -- no special rights for this group
						and (UserSites.Allow = 1 or UserSites.Allow is NULL)
					or GroupSites.Allow = 1  -- allowed to this group
					and (
						GroupSites.AllowToAll = 1 and (UserSites.Allow = 1 or UserSites.Allow is NULL)
							-- is allowed to all user and not explicitely forbiden to this user
						or
						GroupSites.AllowToAll = 0 and UserSites.Allow = 1
							-- is allowed only to this user
					)
				)
			or
				Sites.IsPublic = 0 -- is private by default
				and (GroupSites.Allow = 1) -- and is explicitely allowed to the group
				and (
					GroupSites.AllowToAll = 1 and (UserSites.Allow = 1 or UserSites.Allow is NULL)
						-- is allowed to all users and not explicitely forbiden to this user
					or
					GroupSites.AllowToAll = 0 and (UserSites.Allow = 1)
						-- is allowed only to this user
				)
			)
		or
		   dbo.CurrentUserCanSeePublic() = 0	-- the group cannot see site
			and (GroupSites.Allow = 1) -- that is not explicitely allowed to the group
			and (
				GroupSites.AllowToAll = 1 and (UserSites.Allow = 0 or UserSites.Allow is NULL)
					-- is allowed to all users and not explicitely forbiden to this user
				or
				GroupSites.AllowToAll = 0 and (UserSites.Allow = 1)
					-- is allowed only to this user
			)
		)

Any chance to give MSSQL a hint to first test the dbo.CurrentUserType() = 'xx' and skip the SELECTs that can't return anything?

Thanks a lot, Jenda

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-27 : 23:56:09

I think MSSQL does first test the dbo.CurrentUserType() = 'xx' .

Jenda
Starting Member

29 Posts

Posted - 2007-08-28 : 04:51:35

quote:
Originally posted by Koji Matsumura

I think MSSQL does first test the dbo.CurrentUserType() = 'xx' .

That's what I'd love it to do. But it doesn't. According to the IO statistics the query

SELECT * FRMO dbo.fSites

causes four or five scans of dbo.Sites no matter whether the CONTEXT_INFO is set to cause the dbo.CurrentUserType() to return 'SA' or 'GU' :-(

For 'GU':
Table 'UserSites'. Scan count 312, logical reads 624, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GroupSites'. Scan count 312, logical reads 624, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ATSSiteFilter'. Scan count 0, logical reads 654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ATSUserMapping'. Scan count 327, logical reads 6924, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Sites'. Scan count 4, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 921, lob physical reads 0, lob read-ahead reads 1.

for 'SA':
Table 'UserSites'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GroupSites'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ATSSiteFilter'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ATSUserMapping'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Sites'. Scan count 5, logical reads 75, physical reads 0, read-ahead reads 0, lob logical reads 1408, lob physical reads 0, lob read-ahead reads 3.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-28 : 22:23:33

You have
FROM dbo.Sites WHERE 'SA' = dbo.CurrentUserType()
and
FROM dbo.Sites WHERE dbo.CurrentUserType() = 'GU'

It seems normal to me that there are four or five scans for dbo.Sites.

Jenda
Starting Member

29 Posts

Posted - 2007-08-29 : 05:14:52

quote:
Originally posted by Koji Matsumura

You have
FROM dbo.Sites WHERE 'SA' = dbo.CurrentUserType()
and
FROM dbo.Sites WHERE dbo.CurrentUserType() = 'GU'

It seems normal to me that there are four or five scans for dbo.Sites.

If I do


Declare @i int
set @i = 1

select top 10 * from sites where @i = 0
union all
select top 10 * from sites where @i = 1
union all
select top 10 * from sites where @i = 2

will it scan the Sites table just once or three times? JUST ONCE!
There is no point in scanning dbo.Sites when the

@i = 0

returns false, the SELECT cannot return any rows anyway.

There is no point why would


select top 10 * from sites where dbo.CurrentUserType() = 'SA'
union all
select top 10 * from sites where dbo.CurrentUserType() = 'CA'
union all
select top 10 * from sites where dbo.CurrentUserType() = 'GU'

be any different. The dbo.CurrentUserType() returns a string that doesn't in any way depend on the "current" row in dbo.Sites, the

dbo.CurrentUserType() = 'GU'

will always be either true or false within the query so there is no point in scanning dbo.Sites in at least two of the three cases.

Which is exactly what happens with the simple query above, based on what type of user I "register" (store data in CONTEXT_INFO) I get zero or 1 scan of dbo.Sites. In this case.

But for the actual, complex, query this optimization doesn't happen :-(
And the question is why and whether there is a chance to force MSSQL to apply it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources