Any obvious optimisations in this view? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Any obvious optimisations in this view?

Author

Topic

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 06:44:39

The following SQL is used in a view (vwUserAccess). Basically, the resulting data contains information about users, and which areas of the site (or pages) they are permitted to access. Administrators have access to everything (hence the CROSS JOIN), and the various roles have limited access.

I was originally going to have all this in a table (for speed), but the maintenance overheads were too great.

Can anyone see any obvious optimisations that can be made to this?

I can't use an indexed view because of the use of UNION. I just want to be sure that the view generates data as quickly as is possible. The view is queried a lot.


-- Individual Users

SELECT		idu.ItemDiscussionId, id.ItemId, i.ProjectId, p.ClientId, idu.UserId, (ud.FirstName + ' ' + ud.Surname) UserFullName,
u.RoleId, ISNULL(idup.IsSubscribed, ud.AutoSubscribeToDiscussions) IsSubscribed, idup.DateLastRead
FROM		dbo.ItemDiscussionUser idu
INNER JOIN	dbo.ItemDiscussion id ON id.ItemDiscussionId = idu.ItemDiscussionId
INNER JOIN	dbo.Item i ON i.ItemId = id.ItemId
INNER JOIN	dbo.Project p ON p.ProjectId = i.ProjectId
INNER JOIN	dbo.Client c ON c.ClientId = p.ClientId AND c.IsEnabled = 1
INNER JOIN	dbo.[User] u ON u.UserId = idu.UserId AND u.IsApproved = 1
INNER JOIN	dbo.UserData ud ON ud.UserId = u.UserId
LEFT JOIN	dbo.ItemDiscussionUserProperties idup ON idup.ItemDiscussionId = idu.ItemDiscussionId
			AND idup.UserId = idu.UserId

UNION ALL

-- Clients

SELECT		ISNULL(id.ItemDiscussionId, 0) ItemDiscussionId, ISNULL(id.ItemId, 0) ItemId, p.ProjectId, c.ClientId, u.UserId, (ud.FirstName + ' ' + ud.Surname) UserFullName,
u.RoleId, ISNULL(idup.IsSubscribed, ud.AutoSubscribeToDiscussions) IsSubscribed, idup.DateLastRead
FROM		dbo.Project p
INNER JOIN	dbo.Client c ON c.ClientId = p.ClientId AND c.IsEnabled = 1
INNER JOIN	dbo.UserData ud ON ud.ClientId = p.ClientId
INNER JOIN	dbo.[User] u ON u.UserId = ud.UserId AND u.IsApproved = 1 AND u.RoleId = 4
LEFT JOIN	dbo.Item i ON i.ProjectId = p.ProjectId
LEFT JOIN	dbo.ItemDiscussion id ON id.ItemId = i.ItemId
LEFT JOIN	dbo.ItemDiscussionUserProperties idup ON idup.ItemDiscussionId = id.ItemDiscussionId
			AND idup.UserId = u.UserId

UNION ALL

-- Client Account Handlers

SELECT		ISNULL(id.ItemDiscussionId, 0) ItemDiscussionId, ISNULL(id.ItemId, 0) ItemId, p.ProjectId, c.ClientId, u.UserId, (ud.FirstName + ' ' + ud.Surname) UserFullName,
u.RoleId, ISNULL(idup.IsSubscribed, ud.AutoSubscribeToDiscussions) IsSubscribed, idup.DateLastRead
FROM		dbo.Project p
INNER JOIN	dbo.Client c ON c.ClientId = p.ClientId AND c.IsEnabled = 1
INNER JOIN	dbo.ClientAccountHandler cah ON cah.ClientId = c.ClientId
INNER JOIN	dbo.[User] u ON u.UserId = cah.AccountHandlerUserId AND u.IsApproved = 1 AND u.RoleId = 3
INNER JOIN	dbo.UserData ud ON ud.UserId = u.UserId
LEFT JOIN	dbo.Item i ON i.ProjectId = p.ProjectId
LEFT JOIN	dbo.ItemDiscussion id ON id.ItemId = i.ItemId
LEFT JOIN	dbo.ItemDiscussionUserProperties idup ON idup.ItemDiscussionId = id.ItemDiscussionId
			AND idup.UserId = u.UserId

UNION ALL

-- System Administrators and Administrators

SELECT		ISNULL(id.ItemDiscussionId, 0) ItemDiscussionId, ISNULL(id.ItemId, 0) ItemId, p.ProjectId, c.ClientId, u.UserId, (ud.FirstName + ' ' + ud.Surname) UserFullName, u.RoleId,
			ISNULL(idup.IsSubscribed, ud.AutoSubscribeToDiscussions) IsSubscribed, idup.DateLastRead
FROM		[User] u
INNER JOIN	UserData ud ON ud.UserId = u.UserId
CROSS JOIN	Project p
INNER JOIN	Client c ON c.ClientId = p.ClientId
LEFT JOIN	Item i ON i.ProjectId = p.ProjectId
LEFT JOIN	ItemDiscussion id ON id.ItemId = i.ItemId
LEFT JOIN	ItemDiscussionUserProperties idup ON idup.ItemDiscussionId = id.ItemDiscussionId
			AND idup.UserId = u.UserId
WHERE		u.RoleId < 3
AND			u.IsApproved = 1

Thanks for any help.

Daze.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 07:25:53

Are the tables that are used in the view appropriately indexed ? Check for the execution plan of the query you use to retrieve data.

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 08:01:38

All of the appropriate foreign key/primary keys are in place, but I'm embarrassed to say I'm not particularly clued up on the execution plan (or what to gleen from it). I'm a C# developer, and I simply don't know SQL Server 2005 as well as I possibly should do (i.e. I create tables and write sprocs/queries etc, but I don't really now the inner workings). The plan seemed pretty evenly spread, with the clustered index seeks/scans all consuming around 12%. I ran the "Database Engine Tuning Advisor" tool and it didn't suggest any improvements.

Daze.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:07:33

Press CTRL-M before running your query.
When query is done, change to EXECUTION PLAN tab, which is shown together with Results and Messages tab.

Save the query plan to your hard drive, open the file in notepad and post the text here.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:12:14

Please tell us if you need more assistance.

E 12°55'05.63"
N 56°04'39.26"

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 08:18:15

Thanks for the help Peso. The resulting file was too large to post here (well, I assume that was the cause of the error I was receiving), so I've zipped it up and uploaded it to my webspace:

http://www.curvenet.co.uk/files/vwUserAccess.zip

Daze.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:23:03

Well... The last query returns 1600 records, where the first returns only 1 record, and the second and third query returns 7 or 8 records only.

Concentrate on the 1600 record query. Complete query is returning 36 records only.
So somewhere the 1600 records are downsized to 20 record only, meaning that there are a lot of duplicate records in the last query.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:29:11

How many records are there in User, UserData, Project and Client tables?

SELECT COUNT(*) FROM User WHERE RoleID < 3 AND IsApproved = 1
SELECT COUNT(*) FROM UserData
SELECT COUNT(*) FROM Project
SELECT COUNT(*) FROM Client

-- System Administrators and Administrators
SELECT		ISNULL(id.ItemDiscussionId, 0) AS ItemDiscussionId,
		ISNULL(id.ItemId, 0) AS ItemId,
		p.ProjectId,
		c.ClientId,
		u.UserId,
		ud.FirstName + ' ' + ud.Surname AS UserFullName,
		u.RoleId,
		ISNULL(idup.IsSubscribed, ud.AutoSubscribeToDiscussions) AS IsSubscribed,
		idup.DateLastRead
FROM		[User] u
INNER JOIN	UserData ud ON ud.UserId = u.UserId
CROSS JOIN	Project p
INNER JOIN	Client c ON c.ClientId = p.ClientId
LEFT JOIN	Item i ON i.ProjectId = p.ProjectId
LEFT JOIN	ItemDiscussion id ON id.ItemId = i.ItemId
LEFT JOIN	ItemDiscussionUserProperties AS idup ON idup.ItemDiscussionId = id.ItemDiscussionId
			AND idup.UserId = u.UserId
WHERE		u.RoleId < 3
		AND u.IsApproved = 1

E 12°55'05.63"
N 56°04'39.26"

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 08:29:57

Thanks for the heads up (and for taking the time to look into this for me). I'll look at optimising this:


SELECT		ISNULL(id.ItemDiscussionId, 0) ItemDiscussionId, ISNULL(id.ItemId, 0) ItemId, p.ProjectId, c.ClientId, u.UserId, (ud.FirstName + ' ' + ud.Surname) UserFullName, u.RoleId,
			ISNULL(idup.IsSubscribed, ud.AutoSubscribeToDiscussions) IsSubscribed, idup.DateLastRead
FROM		[User] u
INNER JOIN	UserData ud ON ud.UserId = u.UserId
CROSS JOIN	Project p
INNER JOIN	Client c ON c.ClientId = p.ClientId
LEFT JOIN	Item i ON i.ProjectId = p.ProjectId
LEFT JOIN	ItemDiscussion id ON id.ItemId = i.ItemId
LEFT JOIN	ItemDiscussionUserProperties idup ON idup.ItemDiscussionId = id.ItemDiscussionId
			AND idup.UserId = u.UserId
WHERE		u.RoleId < 3
AND			u.IsApproved = 1

Daze.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:39:41

It is strange.
The last HASH MATCH produces 14 records. Then SQL Server apply a Compute Scalar and then the number of records are up to 1600!

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 08:47:07

quote:
Originally posted by Peso

It is strange.
The last HASH MATCH produces 14 records. Then SQL Server apply a Compute Scalar and then the number of records are up to 1600!

E 12°55'05.63"
N 56°04'39.26"

Similarly, after the nested loop it says 14 records. Compute scalar makes that 1600.

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 08:48:10

Sorry - I missed your earlier post:

SELECT COUNT(*) FROM User WHERE RoleID < 3 AND IsApproved = 1

Result = 2

SELECT COUNT(*) FROM UserData

Result = 32

SELECT COUNT(*) FROM Project

Result = 5

SELECT COUNT(*) FROM Client

Result = 4

The total rows returned by the "System Administrators and Administrators" query itself is 4.

Daze.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:48:54

Try something like this too see if there is a change

-- System Administrators and Administrators
SELECT		ISNULL(id.ItemDiscussionId, 0) AS ItemDiscussionId,
		ISNULL(id.ItemId, 0) AS ItemId,
		p.ProjectId,
		c.ClientId,
		u.UserId,
		ud.FirstName + ' ' + ud.Surname AS UserFullName,
		u.RoleId,
		ISNULL(idup.IsSubscribed, ud.AutoSubscribeToDiscussions) AS IsSubscribed,
		idup.DateLastRead
FROM		Client AS c
INNER JOIN	Project AS p ON p.ClientId = c.ClientId
INNER JOIN	[User] AS u ON u.RoleId < 3
			AND u.IsApproved = 1
INNER JOIN	UserData AS ud ON ud.UserId = u.UserId
LEFT JOIN	Item AS i ON i.ProjectId = p.ProjectId
LEFT JOIN	ItemDiscussion AS id ON id.ItemId = i.ItemId
LEFT JOIN	ItemDiscussionUserProperties AS idup ON idup.ItemDiscussionId = id.ItemDiscussionId
			AND idup.UserId = u.UserId

E 12°55'05.63"
N 56°04'39.26"

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 08:54:17

It's still 1600 records (assuming I'm looking in the correct place in the plan). FYI, here's the plan:

http://www.curvenet.co.uk/files/AdminQuery.zip

Daze.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:58:26

I think I posted a new version after you read the first version.
Try the second version above, edited 01/07/2009 08:52:22.

E 12°55'05.63"
N 56°04'39.26"

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 09:04:12

Ah. Yes. Thanks.

Here's the updated plan: http://www.curvenet.co.uk/files/AdminQuery.zip

It definitely *appears* to be quicker, but I still see 1600 mentioned in the plan.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:13:24

The only way to make sure if it is faster, is to use SQL Profiler.
Make a standard trace and run the original query and the second version query to compare reads, cpu and duration for execution.

E 12°55'05.63"
N 56°04'39.26"

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 09:21:08

With my current (limited) test data, it's three reads lighter (new = 84 reads, old = 87 reads). The duration for both is 1.

The complete view makes 319 reads, and had a duration of 4.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:27:29

How many milliseconds do the complete query take?
Are you satisfied with that number?

E 12°55'05.63"
N 56°04'39.26"

Daze
Starting Member

42 Posts

Posted - 2009-01-07 : 09:30:47

I am happy that there's nothing more I can do at this point in time - It's definitely an improved query (dropping the CROSS JOIN was reward enough). I'll need to pump a lot of test data into the system to see how it performs with volume, I guess.

Thanks for your help with this Peso - I really appreciated it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:36:41

The gain was to CROSS JOIN with User table (2 records) instead of Project table (5 records).
There is still a CROSS JOIN in the query (only masked as an INNER JOIN). This is because the User table do not have a JOIN predicate to other table, just itself.

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources