troubleshooting Question

Use of index hints in MS SQL. Required? How does this work with hibernate?

Avatar of efamilant
efamilant asked on
Microsoft SQL Server 2008Microsoft SQL ServerJava
9 Comments1 Solution599 ViewsLast Modified:
I have a query like this:

select * from table1,table2 where table2.table1id = table1.id;

table2.table1id is a foreign key that points to a corresponding entry in table1. table1 and table2 have primary keys, both called id.

table2 is also a secondary index, called myindex. Here is my problem. During tests,

Microsoft SQL Server wants to use the primary key of table2 (id) and is scanning the table1 table for the matching id to internally perform the join, instead of using the “table1id” index directly.

I know I can formulate my query using a hint to tell SQL to use the secondary key directly.

Here are my questions:

Is this necessary or are their ways to set up the database indexes so that hints are unnecessary.
This is currently written in straight SQL embedded in my Java program. If I move to hibernate, does hibernate automatically generate the hints for me?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros