We help IT Professionals succeed at work.
Get Started

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

598 Views
Last Modified: 2013-10-07
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?
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE