November 12, 2008 at 11:37 am
Hi
I have a master-detail join and when I query for master_id, all well, but when I query for detail_id, it performs table scan on master view:
create table dbo.master(id integer primary key, title varchar(1024),... other fields)
go
create view dbo.vw_master as select *, Afunction(fields from master) as UserRights from dbo.master.
go
create table dbo.detail(detail_id integer primary key,master_id integer references dbo.master(id),...other fields)
go
create index ix_detail_master_id on dbo.detail(master_id)
go
create view dbo.vw_detail
as select D.*, M.title, M.UserRights from dbo.detail D join dbo.vw_master M on M.id=D.master_id
go
--this query performs well, returns ~100 records, 11ms
select * from dbo.vw_detail where master_id=constant and (userRights & 1=1)
go
--this query performs badly, returns 1 record
select * from dbo.vw_detail where detail_id=constant
go
The plan for first query shows that it uses detail.ix_detail_master_id index and dbo.master primary key, it takes 11ms.
The plan for second query shows that it uses primary key for detail and table scan for master and it takes 12sec to execute, because it also executes the function for each record. It's one record from detail located with primary key, one record from master and one function call.
If I join tables (expand vw_master in vw_detail), both queries perform well, so it seems that use of function in view confuses the optimizer. This solution is out of question as there are many detail tables and even details of details.
I have a workaround with a table function and "cross apply", which is a lot less flexible than view.
Any ideas?
November 12, 2008 at 11:56 am
Can you post the execution plan please? Saved as a .sqlplan file, zipped and attached to your post. My guess is that it's because the NC index on the detail table isn't covering.
Any function in the select clause that takes a column as a parameter will always be run once for every row in the outer query. They tend not to perform very well for that exact reason.
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 12, 2008 at 6:34 pm
I could post it, but it's huge and wouldn't do much good without the whole model.
The problem is reduced to this:
1. foreign key field in where and it properly uses primary key from master and executes the function for one row
2. primary key of detail in where clause, there's a foreign key joined to master's primary key, so it should select one row from master, instead it scans the whole master table executing function for each row.
Query returns one row in 12 sec instead in 12 msec, if I add "include actual execution plan"... after 20 minutes it's still running.
Damn, after 45 minutes it's still running, in a new window I reexecute the query without plan and it takes 29 sec, which is normal as it's busy with first query.
November 13, 2008 at 12:14 am
Robert (11/12/2008)
I could post it, but it's huge and wouldn't do much good without the whole model.
Thing is, without seeing the exec plan I can't give you more than vague hints as to why the index isn't been used. I'm guessing it's because the index isn't covering, but that's just a guess. It may have to do with the functions, it may have to do with bad stats, etc.
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 17, 2008 at 4:21 am
Just in case it helps.
The query select * from vw_detail where detail_id=const does not use foreign key index, but if i add master_id=const, id does select * from vw_detail where detail_id=const and master_id=const
so this expands to:
select .... from detail D join master M on M.id=D.master_id
where D.detail_id=const and D.master_id=const
With the original query, the execution plan says "missing index for detail.master_id, but the index is there and used, if I add master_id to where clause.
I still don't know why this happens.
November 18, 2008 at 1:56 am
Have you tried forcing an index by using a "WITH INDEX" clause in the view?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply