index filter

  • 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 ) ?.

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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 ?

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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());

  • Try the following:

    select * from employee e

    inner join employeeDetails ed on e.[id]=ed.[id]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply