Dumb Question - Index. - 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)

Dumb Question - Index.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-06-06 : 11:34:24

If I am running a cross-tab query on a table that has 15000 records in it to check specific records (It basically is running a table-valued function about 10,000 times

Here's the actual query


Select a.EmployeeID,b.*
from #TmpActiveEmployeesWSeverance a
cross apply 
dbo.fn_Severance_AccountItemsTableBULKRUN(a.EmployeeID,a.BenefitTypeID,null,null,null,null,null	) b

Within that function there is a sub-query on a table that is


Select col3 
from T_Mytable a
where col1 = @EmployeeID
and   Col2 = @BenefitTypeID

I can not figure out why there not ANY performance increase in having a non-clusterd index on T_Mytable(EmployeeID,BenefitTypeID)

Shouldn't Sql Reference this index when determining the plan execution, or is it because the record count is only about 10,000 records, so there is no need for sql to use the index?

Thanks for the clarification, I just would like to know why this is.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-06 : 13:46:29

Exactly, It finds best one for you. You can force Query to use your index and see if there is performance gain.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-06 : 13:55:34

Have you tried using a covering index: EmployeeID, BenefitTypeID, and an include column of col3?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-06 : 14:01:14

Col3 is not in the index, so it would have to do bookmark lookup for each row, so maybe it is just faster to scan the table.

In any case, the performance penalty of calling a function for each row in the temp table may completely overwhelm any benefit from an index.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources