October 15, 2003 at 6:04 am
Hello,
I was trying to optimise a large query I have which returns appointment data and ran into a problem with Bookmarks and covering indexes.
The query joins several tables and only takes a second to pull back appointments ranging over 4 months but it uses a Bookmark in the execution plan, which I understand to be a bad thing.
So I created a covering index on the appointments table to cover all the values selected along with values in the where clause.
However, the execution plan has not changed and the bookmark is still used (using 84%)
The covering index covers all the values but I read on the net that if you have a clustered index (one exists on the primary key (app_id) then the covering index is ignored.
Can anyone help ?
the stored procedure is:
CREATE procedure ecal_getteamapps @did int, @team int, @start varchar(20), @end varchar(20)
as
set dateformat dmy
select a.app_id, a.cdb, a.c_id,
convert(varchar(10),a.start_date,103)+' '+convert(varchar(5),a.start_date,108) as vstart_date,
convert(varchar(10),a.end_date,103)+' '+convert(varchar(5),a.end_date,108) as vend_date,
v.description, v.text, v.back, v.visit_group, c.post_code, u.forename+' '+u.surname as name,
v.visit_id, ch.app_id as charged
FROM db_ecdn.dbo.appointments a
INNER JOIN users u ON (a.c_id =u.user_id)
INNER JOIN db_ecdn.dbo.visit_type v ON (a.visit_id = v.visit_id )
INNER JOIN db_ecdn.dbo.customer c ON (a.cdb = c.cdb)
LEFT OUTER JOIN db_ecdn.dbo.foChargedApps ch ON (a.app_id=ch.app_id)
WHERE (u.team_id = @team) AND (u.dep_id = @did)
AND (a.start_date between cast(@start as datetime) and cast(@end as datetime))
UNION
select 0,0,u.user_id,null,null,
--'','',
'','','',0,'',u.forename+' '+u.surname,0,null
from users u where (u.team_id = @team) AND (u.dep_id = @did)
order by a.c_id, a.vstart_date
GO
users and appointments are in different databases but a.c_id matches u.user_id
the table def of appointments is below, with a C next to the fields used in the covering index ...
CREATE TABLE [dbo].[appointments] (
C[app_id] [int] IDENTITY (1, 1) NOT NULL ,
C[cdb] [int] NOT NULL ,
C[start_date] [datetime] NOT NULL ,
C[c_id] [int] NOT NULL ,
C[visit_id] [int] NOT NULL ,
C[end_date] [datetime] NOT NULL ,
[notes] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
any help would be great
thanks
Steve
October 15, 2003 at 7:02 am
Hi Steve,
maybe this will help you
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=16048
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply