Q:

Select Query With hint for add Performance

my select Query is:
Select A.DocumentID,
       A.CodingID,
       A.DebitPrice,
       A.CreditPrice,
       A.DocumentDetailDescriptionID,
       A.HKSID,
       A.OldInc,
       A.EffectiveDate,
       A.DetailedAccount1ID,
       A.DetailedAccount2ID,
       A.DetailedAccount3ID,
       C.FinancialPeriodID,
       C.SalesCompanyID,
       B.[Date]
 From  acc.DocumentDetail A
 INNER JOIN ACC.Document B ON B.DocumentID = A.DocumentID
 INNER JOIN ORG.SalesCompanyFinancialPeriod C ON C.SalesCompanyFinancialPeriodID = B.SalesCompanyFinancialPeriodID

acc.DocumentDetail have about 12917388 record
acc.documet have about 131767 record
org.SalesCompanyFinancialPeriod have about 93 record
my query is slow and I want to make it faster. what hint I must to use ?

A:

There are two important questions regarding this situation:

Are you going to the overall best performance or are you wanting to return the first rows faster (better suited for UI applications)?
Does the table org.SalesCompanyFinancialPeriod acts like a filter or a dictionary?

If you are looking for better overall time and SalesCompanyFinancialPeriod acts like a dictionary, the best performance can be achieved by using a MERGE join between Document and DocumentDetail and, then, using a HASH join to SalesCompanyFinancialPeriod.

SELECT (...)
FROM acc.Document doc
INNER MERGE JOIN acc.DocumentDetail det ON (...)
INNER HASH JOIN org.SalesCompanyFinancialPeriod fnp ON (...)
OPTION(FORCE ORDER);

If SalesCompanyFinancialPeriod acts like a filter or this query is to be used into a UI application, you can return the first rows faster by beginning with this table and then using LOOP/HASH join with Document and then, again, another LOOP/HASH join with DocumentDetail. The choice between LOOP or HASH join depends on many environment variables, so I'd rather specify the order of the JOIN and let the engine choose the join:

SELECT (...)
FROM org.SalesCompanyFinancialPeriod fnp
INNER JOIN acc.Document doc ON (...)
INNER JOIN acc.DocumentDetail det ON (...)
OPTION(FORCE ORDER);

More important than the hints for the query performance, though, is the index structure of these tables. For this query matters, I'd suggest the following indexes (valid for both usage cases):
CREATE UNIQUE CLUSTERED INDEX idxc_det
  ON acc.DocumentDetail(DocumentID, CodingID);

CREATE UNIQUE CLUSTERED INDEX idxc_doc
  ON acc.Document(DocumentID);

CREATE INDEX idx_fnper
  ON acc.Document(SalesCompanyFinancialPeriodID);

CREATE UNIQUE CLUSTERED INDEX idxc_doc
  ON org.SalesCompanyFinancialPeriod(SalesCompanyFinancialPeriodID);

