efamilant
asked on
Use of index hints in MS SQL. Required? How does this work with hibernate?
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?
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?
you definitely should be using ANSI join syntax (like that shown by UnifiedIS above)
and/or reversing the table order might influence results
how did you determine that mssql was using the index on table2.id and attempting to match through this?
and/or reversing the table order might influence results
how did you determine that mssql was using the index on table2.id and attempting to match through this?
ASKER
Using inner join syntax makes no difference in execution time.
I provided simplified example of the query in my original post. Here is the real query:
select doc_table.doctype,doc_tabl
code_table.pos_end, icdtable.icdid, icdtable.cid, icdtable.status,icdtable.k
and doc_table.id=code_table.di
And the version of the query using inner joins:
select doc_table.doctype,doc_tabl
code_table.pos_end, icdtable.icdid, icdtable.cid, icdtable.status,icdtable.k
INNER JOIN icdtable ON code_table.cid =icdtable.cid where doc_table.acct= '2958404_017' and icdtable.status <> 'X'
The execution time is identical in each case. So whether I explicitly use inner joins in my statement or implicitly use them, as I did in the original query, the execution time is the same.
I provided simplified example of the query in my original post. Here is the real query:
select doc_table.doctype,doc_tabl
code_table.pos_end, icdtable.icdid, icdtable.cid, icdtable.status,icdtable.k
and doc_table.id=code_table.di
And the version of the query using inner joins:
select doc_table.doctype,doc_tabl
code_table.pos_end, icdtable.icdid, icdtable.cid, icdtable.status,icdtable.k
INNER JOIN icdtable ON code_table.cid =icdtable.cid where doc_table.acct= '2958404_017' and icdtable.status <> 'X'
The execution time is identical in each case. So whether I explicitly use inner joins in my statement or implicitly use them, as I did in the original query, the execution time is the same.
can you post the execution plan xml? (as a file)
regardless of execution timings - I'd advise use of ANSI syntax for maintainability if nothing else
{+edit sorry fixed spelling}
regardless of execution timings - I'd advise use of ANSI syntax for maintainability if nothing else
{+edit sorry fixed spelling}
perhaps try mixing it up a little too? e.g.
SELECT
doc_table.doctype
, doc_table.create_date
, icdtable.icdcode
, code_table.token
, code_table.did
, code_table.pos_start
, code_table.pos_end
, icdtable.icdid
, icdtable.cid
, icdtable.STATUS
, icdtable.kstatus
FROM code_table
INNER JOIN doc_table ON code_table.did = doc_table.id
AND doc_table.acct = '2958404_017'
INNER JOIN icdtable ON code_table.cid = icdtable.cid
AND icdtable.STATUS <> 'X'ASKER
As requested.
executionplan.txt
executionplan.txt
ASKER
If I use this version of the query, with the hint as part of it, the query executes instantly.
select doc_table.doctype,doc_tabl
code_table.pos_end, icdtable.icdid, icdtable.cid, icdtable.status,icdtable.k
and doc_table.id=code_table.di
But my question is, is there a way to do this without using hints.
select doc_table.doctype,doc_tabl
code_table.pos_end, icdtable.icdid, icdtable.cid, icdtable.status,icdtable.k
and doc_table.id=code_table.di
But my question is, is there a way to do this without using hints.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As indicated previously and without looking at your Execution Plan I would be surprised if it was not as simple as some stale statistics.
select * from table1 t1
INNER JOIN table2 t2
ON t2.table1id = t1.id