November 14, 2011 at 5:11 am
Hi All,
DB: SQL Server 2008 R2 Enterprise
OS: Windows Server 2008 Enterprise (on VM)
I have a view that is based on three tables, two which have approximately 10 rows each and a second table that has approximately 2 million rows. The view is designed to limit the data that a end-user can view based on the permissions stored in the first two tables I mentioned.
When I do a select * from vMyView the query takes a long time to complete (approximately 5mins) and when I look at the execution plan there is a SORT operation (third operation at the top of the "tree") that's 80% of the total query cost. I have checked and re-checked all the SQL in the view and everywhere else and nowhere do I use a ORDER BY clause so why is there a SORT operation? I have indexed all the columns used in the joins. Here is the SQL for the view, please help!
CREATE VIEW [MyDB].[dbo].[vMyView] AS
SELECT *
FROM [MyDB].[dbo.[BigTable] AS bt
WHERE(EXISTS(SELECT 1
FROM PermissionsDB.dbo.permissions perm
WHERE bt.perm_id = perm.perm_id)
OR EXISTS(SELECT 1
FROM PermissionsDB.dbo.permissions perm
WHERE bt.second_perm_id = perm.perm_id))
AND(EXISTS(SELECT 1
FROM PermissionsDB.dbo.suppliers supp
WHERE bt.supp_id = supp.supp_id)
OR EXISTS(SELECT 1
FROM PermissionsDB.dbo.suppliers supp
WHERE bt.second_supp_id = supp.supp_id))
November 14, 2011 at 5:23 am
Please upload the actual execution plan.
Sorts have way more than 1 use in plans ;-).
November 14, 2011 at 5:23 am
Please post dependent Tables (& Index) DDLs & Execution Plan for analysis.
November 14, 2011 at 5:31 am
Sorry but how do I upload the execution plan? I can save it as a ".sqlplan" file or as an XML file.
November 14, 2011 at 5:35 am
feersum_endjinn (11/14/2011)
Sorry but how do I upload the execution plan? I can save it as a ".sqlplan" file or as an XML file.
When you post, bottom right there's an edit attachement button. If it's not visible search for a + and hit it.
Upload as .sqlplan.
November 14, 2011 at 5:45 am
Thanks Ninja!
While playing around with the SQL for the view I found that if I change the SQL to what's shown below the SORT operation disappears and the query runs in ~20 seconds.
CREATE VIEW [MyDB].[dbo].[vMyView] AS
SELECT *
FROM [MyDB].[dbo.[BigTable] AS bt
WHERE(EXISTS(SELECT 1
FROM PermissionsDB.dbo.permissions perm
WHERE bt.perm_id = perm.perm_id
AND(EXISTS(SELECT 1
FROM PermissionsDB.dbo.suppliers supp
WHERE bt.supp_id = supp.supp_id)
OR EXISTS(SELECT 1
FROM PermissionsDB.dbo.suppliers supp
WHERE bt.second_supp_id = supp.supp_id))
)
OR EXISTS(SELECT 1
FROM PermissionsDB.dbo.permissions perm
WHERE bt.second_perm_id = perm.perm_id)
AND(EXISTS(SELECT 1
FROM PermissionsDB.dbo.suppliers supp
WHERE bt.supp_id = supp.supp_id)
OR EXISTS(SELECT 1
FROM PermissionsDB.dbo.suppliers supp
WHERE bt.second_supp_id = supp.supp_id))
)
November 14, 2011 at 5:48 am
That's an estimated plan, I need the actual plan.
You can play with the query all you want but I can't tell you if they return the same thing...
November 14, 2011 at 6:04 am
Sorry, that was my mistake! I attached the wrong file. Here's the correct one.
Also I changed the view back to it's original format. The ~20 second query time was only if I included a WHERE clause as a end-user. Selecting all rows still takes ~5 mins.
November 14, 2011 at 6:11 am
You have no less than 9 table & CI scans.
I would personally avoid that view like the plegue.
I have no immediate solution for the 80% sort at the end of the plan.
November 14, 2011 at 6:11 am
Is the application ever going to select all rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 7:27 am
Thanks for replies all.
Yes - there are some users who will get all the rows returned.
Unfortunately I can't avoid the view. Our of the three base tables involved two are very small and hold between 10 and 40 records.
November 14, 2011 at 7:30 am
feersum_endjinn (11/14/2011)
Thanks for replies all.Yes - there are some users who will get all the rows returned.
Unfortunately I can't avoid the view. Our of the three base tables involved two are very small and hold between 10 and 40 records.
Doesn't really matter. It increases the plan complexity and makes it harder to find the quickest plan.
I'm not saying this is your problem NOW, but keep piling on more tables or views of views and this is where you're heading.
November 14, 2011 at 7:47 am
Either Suppliers or Permissions are views or functions, or you radically simplified the query when you posted it. There are references to so many tables that are not mentioned in your query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 8:22 am
I didn't actually simplify the CREATE VIEW statement. But you're right, the objects used to apply user permissions within the view are views as well. What I'm trying to understand is why, when those views return (for the test user) just 2 rows does the select * from MyView take so long. I realise that the table to which the permissions are being applied is large (2 million records) however the SORT operation is what seems to be slowing things down and I'm trying to figure out how to setup the view so that the optimizer doesn't need to do a sort.
November 14, 2011 at 8:31 am
rewrite the query to NOT use views and only the minimum amount of objects and the optimiser will most likely pick a better plan (if possible).
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply