|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
View performance when using UNIONcreate view vwWidgetPermissionEmployee as select WidgetId, PermissionId, EmployeeId from Widget join WidgetGroupPermission on WidgetId = Widget.Id join GroupEmployee on GroupId = Group.Id If I query this as follows: select WidgetId, PermissionId , EmployeeId from Widget join vwWidgetPermissionEmployee on WidgetId = Widget.Id where Widget.Size = 10 then the query comes back straight away. If however I change my view as follows, to use a union create view vwWidgetPermissionEmployee as select WidgetId, PermissionId, EmployeeId from Widget join WidgetGroupPermission on WidgetId = Widget.Id join GroupEmployee on GroupId = Group.Id union select WidgetId, PermissionId, EmployeeId from Widget join WidgetEmployeePermission on WidgetId = Widget.Id then the same query is now slow The reason can be seen in the query plan: in the first case (without the union), the query is seen to use an index lookup on the Widget table, and then effectively substitute that in to the view. In effect the query executed is select WidgetId, PermissionId, EmployeeId from Widget join WidgetGroupPermission on WidgetId = Widget.Id join GroupEmployee on GroupId = Group.Id where Widget.Size = 10 However, when the union is added, the index is not used. Instead, SQL server resolves select WidgetId, PermissionId, EmployeeId from Widget join WidgetGroupPermission on WidgetId = Widget.Id join GroupEmployee on GroupId = Group.Id and conctatenates that with select WidgetId, PermissionId, EmployeeId from Widget join WidgetEmployeePermission on WidgetId = Widget.Id And only then *that* is joined to the rows found by the bookmark lookup on Widget.Size The problem is that even though all the queries in the view use the Widget table, the query optimizer isn't intelligent enough that when I ask for select * from Widget join vwWidgetPermissionEmployee on Widget.Id = WidgetId where WIdget.Size = 10, to effectively substitute 'Where Size = 10' into each of the queries inside the view. If you did select * from vwWidgetPermissionEmployee where EmployeeId = 10 and WIdgetId = 20 then it does use the index on Size, and the result is returned quickly, as the sql server query plan shows that in effect where Widget.Id = 20 and EmployeeId = 10 is being substituted into both queries in the union. However, as soon as you add a join, it's not able to first work out how many rows are found in Widget, and then use this result set inside the view. It insists on resolving the whole view (*for the given employeeid*, since that employeeid is a constant, it seems to cope) - which is very slow, as there are many thousands of widgets in the table - and then doing a join to the very few rows returned from (select Id from Widget where Size = 10). Does anyone know how I can get the view to use the correct query plan when I query a unioned view in this manner? It is not clear to me that the Widget table serves any purpose in the
view as shown in the example. Unless there are WidgetId values in either WidgetEmployeePermission or WidgetGroupPermission that do not appear as Widget.Id, the Widget table isn't really making any difference. Which probably won't make much difference, but seemed worth pointing out. Roy On 27 Jul 2006 09:15:59 -0700, "Matthew Brealey" <thelaw***@gmail.com> wrote: Show quote >I have a view, which loosely looks like this > >create view >vwWidgetPermissionEmployee >as >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id > > > >If I query this as follows: > >select >WidgetId, PermissionId , EmployeeId >from Widget >join vwWidgetPermissionEmployee on WidgetId = Widget.Id >where Widget.Size = 10 > >then the query comes back straight away. > >If however I change my view as follows, to use a union > >create view >vwWidgetPermissionEmployee >as >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id >union >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetEmployeePermission on WidgetId = Widget.Id > >then the same query is now slow > >The reason can be seen in the query plan: in the first case (without >the union), the query is seen to use an index lookup on the Widget >table, and then effectively substitute that in to the view. In effect >the query executed is > >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id >where Widget.Size = 10 > >However, when the union is added, the index is not used. > >Instead, SQL server resolves >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id > >and conctatenates that with > >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetEmployeePermission on WidgetId = Widget.Id > > > >And only then *that* is joined to the rows found by the bookmark lookup >on Widget.Size > >The problem is that even though all the queries in the view use the >Widget table, the query optimizer isn't intelligent enough that when I >ask for select * from Widget join vwWidgetPermissionEmployee on >Widget.Id = WidgetId where WIdget.Size = 10, to effectively substitute >'Where Size = 10' into each of the queries inside the view. > >If you did > >select * from vwWidgetPermissionEmployee where EmployeeId = 10 and >WIdgetId = 20 > >then it does use the index on Size, and the result is returned quickly, >as the sql server query plan shows that in effect where Widget.Id = 20 >and EmployeeId = 10 is being substituted into both queries in the >union. > >However, as soon as you add a join, it's not able to first work out how >many rows are found in Widget, and then use this result set inside the >view. It insists on resolving the whole view (*for the given >employeeid*, since that employeeid is a constant, it seems to cope) - >which is very slow, as there are many thousands of widgets in the table >- and then doing a join to the very few rows returned from (select Id >from Widget where Size = 10). > >Does anyone know how I can get the view to use the correct query plan >when I query a unioned view in this manner? Matthew,
This is a well known weakness of SQL Server. Have you tried pushing down the highly selective predicate yourself, as follows: select WidgetId, PermissionId, EmployeeId from Widget join WidgetGroupPermission on WidgetId = Widget.Id join GroupEmployee on GroupId = Group.Id where Widget.Size = 10 union select WidgetId, PermissionId, EmployeeId from Widget join WidgetEmployeePermission on WidgetId = Widget.Id where Widget.Size = 10 Can you abandon the view altogether? Try changing UNION to UNION ALL, so there is no effort wasted on
eliminating duplicate rows. Steve Kass Drew University www.stevekass.com Matthew Brealey wrote: Show quote >I have a view, which loosely looks like this > >create view >vwWidgetPermissionEmployee >as >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id > > > >If I query this as follows: > >select >WidgetId, PermissionId , EmployeeId >from Widget >join vwWidgetPermissionEmployee on WidgetId = Widget.Id >where Widget.Size = 10 > >then the query comes back straight away. > >If however I change my view as follows, to use a union > >create view >vwWidgetPermissionEmployee >as >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id >union >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetEmployeePermission on WidgetId = Widget.Id > >then the same query is now slow > >The reason can be seen in the query plan: in the first case (without >the union), the query is seen to use an index lookup on the Widget >table, and then effectively substitute that in to the view. In effect >the query executed is > >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id >where Widget.Size = 10 > >However, when the union is added, the index is not used. > >Instead, SQL server resolves >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetGroupPermission on WidgetId = Widget.Id >join GroupEmployee on GroupId = Group.Id > >and conctatenates that with > >select >WidgetId, PermissionId, EmployeeId >from >Widget >join WidgetEmployeePermission on WidgetId = Widget.Id > > > >And only then *that* is joined to the rows found by the bookmark lookup >on Widget.Size > >The problem is that even though all the queries in the view use the >Widget table, the query optimizer isn't intelligent enough that when I >ask for select * from Widget join vwWidgetPermissionEmployee on >Widget.Id = WidgetId where WIdget.Size = 10, to effectively substitute >'Where Size = 10' into each of the queries inside the view. > >If you did > >select * from vwWidgetPermissionEmployee where EmployeeId = 10 and >WIdgetId = 20 > >then it does use the index on Size, and the result is returned quickly, >as the sql server query plan shows that in effect where Widget.Id = 20 >and EmployeeId = 10 is being substituted into both queries in the >union. > >However, as soon as you add a join, it's not able to first work out how >many rows are found in Widget, and then use this result set inside the >view. It insists on resolving the whole view (*for the given >employeeid*, since that employeeid is a constant, it seems to cope) - >which is very slow, as there are many thousands of widgets in the table >- and then doing a join to the very few rows returned from (select Id >from Widget where Size = 10). > >Does anyone know how I can get the view to use the correct query plan >when I query a unioned view in this manner? > > >  |
|||||||||||||||||||||||
Other interesting topics