December 20, 2009 at 8:32 pm
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 ) ?.
December 21, 2009 at 1:10 am
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
December 21, 2009 at 2:00 am
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
December 21, 2009 at 2:52 am
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
December 21, 2009 at 3:25 am
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 ?
December 21, 2009 at 3:37 am
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
December 21, 2009 at 7:04 am
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
December 23, 2009 at 4:13 pm
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());
December 23, 2009 at 5:51 pm
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