Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-06-07
9
Medium Priority
?
454 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?
0
Comment
Question by:efamilant
9 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 39230396
If you use a join statement does it still use the other index?
select * from table1 t1
INNER JOIN table2  t2
ON t2.table1id = t1.id
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39230957
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?
0
 

Author Comment

by:efamilant
ID: 39231015
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_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 icdtable,code_table,doc_table where doc_table.acct= '2958404_017'
and doc_table.id=code_table.did and code_table.cid=icdtable.cid and icdtable.status <> 'X'

And the version of the query using inner joins:

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 doc_table INNER JOIN code_table ON doc_table.id = code_table.did
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39231030
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}
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39231037
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'

Open in new window

0
 

Author Comment

by:efamilant
ID: 39231573
As requested.
executionplan.txt
0
 

Author Comment

by:efamilant
ID: 39231578
If I use this version of the query, with the hint as part of it, the query executes instantly.

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 icdtable WITH (INDEX=IX_icdtable_cid),code_table,doc_table where doc_table.acct= '999999999'
and doc_table.id=code_table.did and code_table.cid=icdtable.cid and icdtable.status <> 'X'

But my question is, is there a way to do this without using hints.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1500 total points
ID: 39232395
how current are your statistics? perhaps these should be updated?

the most costly operation in that execution plan is a clustered index seek in the doc_table searching for the '2058404_017' (node 4), and index IX_icdtable_cid is already being used (node 5).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39233603
As indicated previously and without looking at your Execution Plan I would be surprised if it was not as simple as some stale statistics.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question