Non-deterministic functions and view indexing

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

Transact-SQL (2000)

Non-deterministic functions and view indexing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-19 : 09:52:50

Alan writes "Hi,

Is there some deterministic function I can use to get the current system user?

I'm using views to return information to a decision support app. Based on the users' Windows login name I return the appropriate data to the view using a table function. My problem is that as the amount of data gets larger, the views are taking longer to return the data.

I want to index the views, but I know I need to make everything deterministic to get this working. Unfortunately, I use system_user and ismember in my current logic to determine who is calling the view and if they are a memeber of the correct role, and I can't figure out how to get around the determinstic/non-deterministic aspect of SQL Server.

Any help would be appreciated.

I'm running Windows 2000 Version 5.0 Terminal Server and
Microsoft SQL Server 2000 - 8.00.760 (Build 2195: Service Pack 3)

Thanks."

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-19 : 10:57:10

You can look at sysprocesses and syslogins to get the system_user.
ismember - don't know - sysmembers maybe?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 12:59:02

I gave that a shot, but I can't seem to make it work. These sys... tables are not deterministic. I can copy their contents into temp tables and make SQL Server think they're deterministic that way, but sysprocesses only resides in the master database and I can't get schemabinding to work when I try to join tables in other databases.

I've been playing with this for a couple of days now, and I feel there's something I'm just not grasping.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 13:07:18

SELECT CURRENT_USER

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 13:39:36

I tried it, but current_user is nondeterministic so it won't work. Any other ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 13:49:41

Do you really need to use indexed views? How about just indexing the tables?

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 13:58:18

Do indexes on tables feed through to views? I was under the impression that they didn't, but it wouldn't be the first time I was wrong.

I definitely need to use views to return the data. I have indexes on the tables, but I would not be surprised if they can be further optimized. Will optimizing the indexes on the tables increase the performance of the views?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 14:00:11

Yes indexes on the tables will help out the views. Have you looked at the execution plan for the view?

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 14:20:55

I hate to admit that I had not recently looked at the execution plan for any of the queries pulling from these views.

I looked at a representative query and note that the plan begins with table scans both in the main query and the subquery. If I change the query to pull from the table instead of the view, it uses the clustered index on the table. Does this mean I won't get the view any more optimized?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 14:24:00

No. Could you post the query and the view?

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 14:35:53

Here's the gist of the query. I've eliminated some of the columns to save space:

SELECT Family,
sum (Amount) as 'Total',
sum (Amount) / (SELECT sum (Amount)
FROM viewData
WHERE Amount <> 0
AND Customer not like 'XX%'
AND YearNo = 2003
AND Quarter = 'Q1') as '% of Total',
FROM viewData
WHERE Amount <> 0
AND Customer not like 'XX%'
AND YearNo = 2003
AND Quarter = 'Q1'

GROUP BY Family
HAVING sum (Amount) <> 0
ORDER BY Family

The view pulls data from one table only, and the structure of the view is exactly the same as the table. What data is returned by the view is controlled the NT role of the user. Some usersget all the data returned, but others have the dataset limited by date (based on their NT role).

The clustered index is on a field not used in this query. There is an index on Amount; an index on Customer, Amount; an index on Family; an index on YearNo; and an index on Quarter.

I appreciate the help. What else do you need from me?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 15:30:18

You mention that the view can limit the dataset, so we'll need to see the view and know if those columns (the ones that limit the result set) are indexed as well.

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 16:02:40

I don't think I'm understanding you. The structure of the view is exactly the same as the table. The limited dataset is limited in that it may return fewer rows. The columns remain the same.

The date field on which I key to limit the dataset is indexed, but right now I'm dealing with queries that return the entire dataset. In other words, I'm looking at a user who will not be limited in any way. The dataset he gets when querying the table is exactly the same as the dataset he gets when querying the view (viewData). The problem is that the query against the view takes longer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 16:05:28

Ok, when you query the view, which indexes is it using?

Have you run SQL Profiler to determine what SQL Server recommends for the indexes?

Although not recommended, have you tried index hints?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 16:12:24

Also, why are you using a view if it is the same as the table?

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 16:43:31

According to the Execution Plan, the query isn't using any indexes. It just does a table scan.

I tried putting in index hints ( with index(0) ), but the Plan still comes back with a table scan.

I've never used the SQL Profiler. I'm trying it now, but I'm not sure how to see what indexes it recommends.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 16:52:24

You have to create a trace file first. Then load the trace file into the index tuning wizard. The file can be a file, table, or workspace. Just make sure that you filter on only your spid or login id.

Why are you using a view if it is the same as the table?

BOL says that "View hints can be used only for indexed views." So index hints aren't going to help it turns out.

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 17:48:15

Thanks for the instructions. I put everything in a trace table, but the Index Tuning Wizard comes back with no recommendations at all.

I'm using a view instead of a table because I need my front-end queries to be the same for all users, but I want the data returned to be limited based on the user's ID. The query asks for data from the view ViewData. This view is populated from a table function that is called with the user's ID as a parameter. The table function calls another function to retrieve the user's access level (based on their NT role) and then populates the data in the ViewData view accordingly.

Does that make sense?

Do you know of some less-convoluted way that will allow me to return different data to different users utilizing the same front-end query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 17:57:00

Perhaps if you showed us the view and the functions, then we'd be able to help.

It sounds like you just need to use a stored procedure.

Tara

achisum
Starting Member

10 Posts

Posted - 2004-01-19 : 18:25:29

I'd love to use stored procedures, but our main developer on the front-end is one of the executives and this is his pet system. I don't think he'd put up with turning his queries over to IT to make stored procedures out of them.

I am attaching all the various functions, etc. that go into making this process work. There has to be an easier way, but I don't know what it is.

First, here's the query from the front-end app:
SELECT Family,
sum (Amount) as 'Total',
sum (Amount) / (SELECT sum (Amount)
FROM viewData
WHERE Amount <> 0
AND Customer not like 'XX%'
AND YearNo = 2003
AND Quarter = 'Q1') as '% of Total',
FROM viewData
WHERE Amount <> 0
AND Customer not like 'XX%'
AND YearNo = 2003
AND Quarter = 'Q1'

GROUP BY Family
HAVING sum (Amount) <> 0
ORDER BY Family

Here's the view definition for ViewData:
create view ViewData
as
select * from tf_ShowData(NULL)

Now the definition for tf_ShowData:
create FUNCTION tf_ShowData (@SysUser varchar(128) = NULL)
RETURNS @MyTable TABLE (Ordnum char(6),
LineNum char(2),
DelNum char(2),
OrdNumFull char(10),
OrderDate smalldatetime,
ShipDate smalldatetime,
Amount float,
YearNo int,
Quarter char(2),
Customer char(15),
Family varchar(25),
AccessLevel int
)

begin
declare @RetVal int,
@AccessLevel int

select @AccessLevel = dbo.fn_GetAccessLevel(@SysUser)

IF @AccessLevel = 1
BEGIN
INSERT INTO @MyTable
SELECT OrdNum, LineNum, DelNum, OrdNumFull, OrderDate,
ShipDate, Amount, YearNo, Quarter, Customer, Family,
@AccessLevel as AccessLevel
FROM dbo.ViewDataGeneral
END
ELSE IF @AccessLevel in (2, 3, 4)
BEGIN
--Return full TableData table
INSERT INTO @MyTable
SELECT OrdNum, LineNum, DelNum, OrdNumFull, OrderDate,
ShipDate, Amount, YearNo, Quarter, Customer, Family,
@AccessLevel as AccessLevel
FROM dbo.ViewDataAll
END
ELSE
BEGIN
--Return an empty recordset
INSERT INTO @MyTable
SELECT OrdNum, LineNum, DelNum, OrdNumFull, OrderDate,
ShipDate, Amount, YearNo, Quarter, Customer, Family,
@AccessLevel as AccessLevel
FROM dbo.ViewDataGeneral WHERE 1 = 2
END
RETURN
END

Now the defintion for ft_GetAccessLevel:
create FUNCTION fnft_GetFTAccessLevel (@SysUser varchar(128) = null)
RETURNS int
as
BEGIN
declare @RetVal int,
@AccessLevel int

Select @AccessLevel = 0

select @RetVal = fn_CheckRoleAccess ('RoleFour', @SysUser)
IF @RetVal = 1
SELECT @AccessLevel = 4

IF @AccessLevel = 0
begin
select @RetVal = fn_CheckRoleAccess ('RoleThree', @SysUser)
IF @RetVal = 1
SELECT @AccessLevel = 3
end

IF @AccessLevel = 0
begin
--Check to see if the user can see slightly more data
select @RetVal = fn_CheckRoleAccess ('RoleTwo', @SysUser)
IF @RetVal = 1
SELECT @AccessLevel = 2
end

IF @AccessLevel = 0
begin
--Check to see if the user can see limited data
select @RetVal = fn_CheckRoleAccess ('RoleOne', @SysUser)
IF @RetVal = 1
SELECT @AccessLevel = 1
end
return @AccessLevel
END

And the definition for fn_CheckRoleAccess:
create FUNCTION fn_CheckRoleAccess (@RoleID varchar(128),
@SysUser varchar(128) = null)
RETURNS int
as
BEGIN
declare @RetVal int,
@MemberID varchar(128)

--Validate Role
if not Exists ( Select *
from sysusers
where issqlrole = 1
and name = @RoleID)
begin
return NULL
end

--Determine ownership if User being checked is the one making the call
IF rtrim(ltrim(ISNULL(@SysUser,''))) = '' or (@SysUser = system_user)
begin
IF IS_MEMBER('dbo') = 1 OR is_member('db_Owner') = 1 --DBO gets a free ride
BEGIN
return 1
END
ELSE IF is_member(@RoleID) = 1
BEGIN
return 1
END
end
Else
BEGIN
--Check to see if relationship can be confirmed at this level
if exists ( select *
from tf_ShowRoleMembers(@SysUser)
where RoleID = @RoleID
or RoleID = 'db_owner')
BEGIN
--Association Found
Return 1
END
--Get First MemberID
SELECT @MemberID = MIN(RoleID)
FROM tf_ShowRoleMembers(@SysUser)
--Cycle through Roles until found
WHILE @MemberID IS NOT NULL --Don't loop if there are no Roles assigned to this member
BEGIN
begin
--Recursively call this procedure to check the next level down
select @RetVal = fn_CheckRoleAccess(@RoleID, @MemberID)
IF @RetVal = 1
begin
--Association Found
return 1
end
end
--Get next memberID
SELECT @MemberID = MIN(RoleID)
FROM tf_ShowRoleMembers(@SysUser)
WHERE RoleID > @MemberID
END
END
return 0
END

And now the logic for tf_ShowRoleMembers:
create FUNCTION tf_ShowRoleMembers (@MemberID varchar(128))
RETURNS @MyTable TABLE (RoleID varchar(128))
--return a result set of the Roles a Rolemember is a part of
as
BEGIN
INSERT INTO @MyTable
Select RoleID
From viewRoleMembers
Where MemberID = @MemberID
RETURN
END

Lastly, here is the logic for viewRoleMembers:
create view viewRoleMembers
as
select c.name AS RoleID, a.name as MemberID
from sysusers a
left join sysmembers b on a.uid = b.memberuid
left join sysusers c on b.groupuid = c.uid
where c.name is not null

Any ideas to simplify this beast are welcome.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 18:43:15

What is the purpose of viewRoleMembers? Applications should not get data from the system tables. From what I can tell, this application is relying on roles to handle the security. That's the correct approach, but it is not implemented correctly. Roles are there to provide permissions on objects to a group of users, The objects are typically just stored procedures. Since you aren't using this approach, your application should handle the security, not SQL Server.

There just isn't much that you can do with the design that was chosen. Before your performance gets even worse, I would work with the developer to get this designed correctly.

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources