avoid bookmark lookup - SQL Server Forums

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 bookmark lookup

Author

Topic

shiloh
Starting Member

48 Posts

Posted - 2007-05-04 : 14:25:30

Hello all

I have a query like this:


     Select e.EmployeeId, abe.FirstName, abe.LastName  
       From Employee e (NoLock)  
       Join EmployeeFunctionXref efx (NoLock) on efx.EmployeeId = e.EmployeeId   
       Join AddrBookEntry abe (NoLock) on abe.AddrBookEntryId = e.AddrBookEntryId  
      Where e.HomeOfficeId = @BusinessUnitId  
        and e.StatusCd = 1          
        and efx.FunctionTypeCdId = @FunctionTypeCdId  
      Order by abe.LastName ,abe.FirstName

Table Employee has

clustered index on employeeId

Non clustered index on HomeOfficeID

Non clustered index on AddrBookEntryID

Table EmployeeFunctionXref Has

clustered index on EmployeeID, FunctionTypeCdID

Table AddrBookEntry has

clustered index on AddrBookEntryId

Non clustered index on LastName, FirstName

The main issue is when I look at the query plan, I see a bookmarklookup for e.statuscd = 1. This column is not covered in any index. This column is like a status flag (50% of rows have value of 1 and 50% of rows have value 0). So adding an index only on this column is useless. I tried adding this as a covering index column on the Non clustered index on employee table along with addrbookentryid which is almost unique. but the covering column does not make any difference.

anyone has any suggestions on how i can get rid of this bookmark for statuscd column. What is the general methodology people follow for status columns like this which are like gender columns (with hardly 2-3 values).
The table has 80k rows currently.

Also, we are using SQL 2005. Please let me know if any more information is needed.

Thanks for your help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 14:32:21

try using included columns which is a new feature of SS2k5.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shiloh
Starting Member

48 Posts

Posted - 2007-05-04 : 14:36:01

thanks spirit for answering. thats how I Added the column when I said I added as covering index. vis INCLUDE in create index.
It says "key lookup". I am guessing its joining by the clustered index and doing lookup for this column.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 14:40:17

yup.
i don't think there's much you can do here, since it isn't selective.

if anyone has some super-duper solution to this i'd love to hear it too...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shiloh
Starting Member

48 Posts

Posted - 2007-05-04 : 15:24:52

yes I'd likt to hear how the other folks here tackle the status columns like that...

thanks spirit again..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-04 : 15:35:13

Did you try adding the StatusCd column to your HomeOfficeID index? So it would become HomeOfficeID, StatusCd?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-04 : 16:12:50

Slightlt off topic, but it might help speed things up. Sometimes moving parts on the WHERE Clause up into the join Condition will help over all query performance if SQL reduces the set size before applying the WHERE clause, potentially reducing the number bookmark lookups:

     Select e.EmployeeId, abe.FirstName, abe.LastName  
       From Employee e (NoLock)  
       Join EmployeeFunctionXref efx (NoLock) on efx.EmployeeId = e.EmployeeId   
        and efx.FunctionTypeCdId = @FunctionTypeCdId  
        and e.HomeOfficeId = @BusinessUnitId  
       Join AddrBookEntry abe (NoLock) on abe.AddrBookEntryId = e.AddrBookEntryId  
      Where e.StatusCd = 1          
      Order by abe.LastName ,abe.FirstName

But, with only 80k rows it probably wouldn't do much if anything.

shiloh
Starting Member

48 Posts

Posted - 2007-05-04 : 16:37:05

I tried moving the join up but it didnt help much. I will try adding the index to HomeOfficeId to see if it makes any difference. thansk everyone. will post back in a few..

shiloh
Starting Member

48 Posts

Posted - 2007-05-04 : 16:44:24

When I added the statuscd to HomeOfficeId, the key lookup only took 14%. Earlier the cost was 74%. thats a good improvement. Now the SORT is taking a good 45%. There is an index on the Lastname, FirstName but its still costing more.. How do we find out if the index is being used in the sort? thanks everyone..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-04 : 16:47:17

The execution plan would show that information.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

shiloh
Starting Member

48 Posts

Posted - 2007-05-04 : 16:54:42

hmm..do you know where I Can look Tara? I am looking at the plan, on the Sort it has all the Physical operation, logical operation...output list (dbo.[Employee].Employeeid, dbo.[AddrBookEntry ].lastname, dbo.[AddrBookEntry ].firstname), order by (dbo.[AddrBookEntry ].lastname, dbo.[AddrBookEntry ].firstname)

doesnt have the index name anywhere..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources