index filter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » index filter

index filter

Rate Topic

Display Mode

Topic Options

Author

Message

varshini

Posted Sunday, December 20, 2009 8:32 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:03 PM
Points: 99, Visits: 107

i have two tables employee & employeeDetails .
Both tables having index filter. How can i specify index filter in
sql joins (like inner join or left outer join ) ?.

Post #836948

GilaMonster

Posted Monday, December 21, 2009 1:10 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

Sorry, I don't understand your question. Can you explain in more detail please? Possibly include the queries and table/index definitions?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #836998

varshini

Posted Monday, December 21, 2009 2:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:03 PM
Points: 99, Visits: 107

For simple query we can use index filter like

select * from employee WITH (INDEX = 2) WHERE colDate > '12/31/2007 23:59:59'

select * from employeeDetails WITH (INDEX = 2) WHERE colDate > '12/31/2007 23:59:59'

how can i use the index filter in inner join ?

select * from emplyee
inner join employeeDetails as empd on empd.id=emplyee.id

Post #837011

GilaMonster

Posted Monday, December 21, 2009 2:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

You mean an index hint "WITH (INDEX = ...)"?
If so, you do it in an inner join the same way as your example. The hint immediately follows the table.

That said, why are you using index hints? Are you really, 100%, absolutely certain that you know better than the query optimiser how to run this query. Have you tested extensively that this hint really is useful for the query? Are you testing regularly to ensure that the hint is still the optimal way of running the query? Have you looked for alternate ways to optimise the query (if it's necessary to optimise it)

These may be of use:
http://www.sqlservercentral.com/articles/Indexing/68439/ (series of 3 articles)
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #837030

varshini

Posted Monday, December 21, 2009 3:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 23, 2012 3:03 PM
Points: 99, Visits: 107

i am using the index hint like

select * from employeeDetails
inner join employee with (index =18) on
employee.id=employeeDetails.id

but i got the following error message :
" Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."

how can i use the index hint ?

Post #837042

GilaMonster

Posted Monday, December 21, 2009 3:37 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

The error's pretty clear.

Resubmit the query without specifying any hints and without using SET FORCEPLAN.

You're telling the optimiser to use a particular index that it absolutely cannot use to run this query. The solution is to not use the hints.

Read what I said about hints earlier. You should not be using them at all unless you know exactly what you're doing and why you want to use a particular hint.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #837050

Grant Fritchey

Posted Monday, December 21, 2009 7:04 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:44 PM
Points: 13,381, Visits: 25,173

Just to follow on to what Gail said, and hopefully reinforce it, index hints, any query hint, should be avoided. More often than not, fixing either the query or the index itself will result in better performance than attempting to force SQL Server to do what you want through the use of hints.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #837147

Nik Desai

Posted Wednesday, December 23, 2009 4:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:34 PM
Points: 7, Visits: 1,166

I tried on AdventureWorks and found no issue.

SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,d.[Name] AS [Department]
,d.[GroupName]
,edh.[StartDate]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c with (index = 1)
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON e.[EmployeeID] = edh.[EmployeeID]
INNER JOIN [HumanResources].[Department] d
ON edh.[DepartmentID] = d.[DepartmentID]

WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());

Post #838801

freeman.e.l

Posted Wednesday, December 23, 2009 5:51 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 6:37 PM
Points: 65, Visits: 44

Try the following:

select * from employee e
inner join employeeDetails ed on e.[id]=ed.[id]

Post #838817

« Prev Topic | Next Topic »

Permissions