Avoid table scan using multi union view

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)

Avoid table scan using multi union view

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-26 : 17:07:57

Anyone see a way to trick the optimizer into not scanning all tables involved in the view?

-- create two test tables
create table dbo.test1
(testID int, TestName varchar(10))

create table dbo.test2
(testID int, TestName varchar(10))

-- populate
declare @i int
set @i = 1000
while @i > 0
begin
insert into dbo.test1
select @i, '1.' + cast(@i as varchar(5))
set @i = @i - 1
end

insert into dbo.test2
select 1, '2.1' union all
select 2, '2.2'
go

-- create view
create view dbo.vw_Test
as
select 1 as QueryID,
TestName
from dbo.Test1
union all
select 2 as QueryID,
TestName
from dbo.Test2;
go

-- this works as i want, only scans table dbo.Test2
select *
from dbo.vw_Test
where QueryId = 2

-- joining to a table triggers scan of both tables in view:
declare @table table (QueryID int)
insert into @table
select 2;

select vt.TestName
from dbo.vw_Test vt
join @table t on
vt.QueryID = t.QueryID

Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.

Nathan Skerl

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-26 : 17:08:53

only if you add a where part to your join.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-26 : 17:09:50

Also, Ive tried using TVF but still get both scans:


create function dbo.fn_Test(@QueryID tinyint)
returns table
as
return (	select	QueryID,
			TestName
		from	dbo.vw_Test
		where	QueryID = @QueryID
	);
go

declare @table table (QueryID int)
insert into @table
	select 2;

select	tvf.*
from	@table t
cross
apply	dbo.fn_Test(t.QueryID) tvf

Any ideas?

Nathan Skerl

edit: pasted wrong function

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-26 : 17:26:38

Yea, but looks like any parameterization of the query results in all scans:


declare @QueryID int
set @QueryID = 2

select	vt.QueryID,
	vt.TestName
from	dbo.vw_Test vt
where	QueryId = @QueryID


select	tvf.*
from	dbo.fn_Test(@QueryID) tvf

So, even putting the parameterized select in a loop wont help. I guess thats what I did essentially with the cross apply attempt.

Is this something an ~~partitioned~~ indexed view might help? The view in question unions many different queries, some of which are much more costly than others. So, I thought it would be prudent to at least investigate if its possible to avoid the full scans.

Thanks

Nathan Skerl

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-26 : 17:32:51

the optimizer will disregard scaning tables only if it can figure out they don't have to be used based on a where condition.

i don't see how an indexed view would help here. for it to be useful you still need a filtering where condition.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-26 : 17:34:55

Thanks Mladen, I hoped you had some magic optimizer hint i could throw at it :)

Ill see what I can do to get these out of the view and in some usable fashion.

Nathan Skerl

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-26 : 17:47:47

hmm... speaking of which... maybe you could try playing with the USE PLAN option...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources