0

I'm trying to performance tune some lengthy queries and I'm slicing bits out and running them in isolation.

The query below features three tables D500M, D550M and D580M, all joined on person_ref. All of these tables have clustered indexes on the person_ref fields. They also have non clustered indexes which include the fields in the select list.

These tables are not large - just under 10k rows in each.

select p.PERSON_REF,p.surname,p.first_forname,e.employee_number,ph.MAIN_FLAG
from D500M p
INNER join D550M e on e.PERSON_REF=p.PERSON_REF
INNER join D580M ph on ph.PERSON_REF=p.PERSON_REF

I'm studying the Actual Execution Plan and seeing Index scans on the three tables (nonclustered), and then two Hash Matches on the joins, with costs of 39% and 41%.

The question is this; Is having these hash matches good, bad or irrelevant?

| improve this question | | | | |
  • If you are trying to understand then you should provide what Veron has asked for. – KumarHarsh Nov 9 '17 at 9:43
1

SQL Server is very good at deciding the best, most efficient execution plan for any given query. Since you have no predicates, such as a WHERE D500M.PERSON_REF = 123456, SQL Server is most likely assuming an index scan is the quickest way to gather all the relevant details from each table. If you add a predicate to the query, you will most likely see an index seek instead. This is to be expected.

"Good, bad or irrelevant" is only something you can decide. Is the performance acceptable for your needs?

You could try forcing the join type to see what happens if a loop join or a merge join were used instead. Something like:

SELECT p.PERSON_REF, p.surname, p.first_forname, e.employee_number, ph.MAIN_FLAG
FROM D500M p
INNER LOOP JOIN D550M e ON e.PERSON_REF=p.PERSON_REF
INNER LOOP JOIN D580M ph ON ph.PERSON_REF=p.PERSON_REF

If you want a more conclusive answer, you should provide code for a minimally complete verifiable environment that matches your environment. i.e. provide us table definitions, the actual queries involved, and the actual query plans you're seeing.

| improve this answer | | | | |
  • Thanks for your input. I think that the thing which confuses me is that everywhere I've read about Hash Match says that having full and covering indexes (and smaller tables) should encourage the query optimiser to avoid hash matching, yet that's not happening in this situation. I'm just trying to understand, not necessarily fix. – Nick Nov 9 '17 at 7:51
0

I think when you have under 10K rows on each table and all join columns are clustered index then Index Scan do matter.

select p.PERSON_REF,p.surname,p.first_forname,e.employee_number,ph.MAIN_FLAG
from D500M p
INNER join D550M e on e.PERSON_REF=p.PERSON_REF
INNER join D580M ph on ph.PERSON_REF=p.PERSON_REF

Here e.PERSON_REF ,p.PERSON_REF ,ph.PERSON_REF all are Clustered index

Why you have mention non clustered index when NON CI is not visible in this query and also NON CI is not require from this query point of view.

Since CI actually store the row data.

you are getting HASH Match because index is not being utilized.Index is not being utilize because of poor cardianility estimate.

So hope you have correct choice of CI and data type is correct.

Sometime changing the order of join correct the cardianility estimate.

your query or table relation seem fishy.

On D500M for example, I have a CI on person_ref (the join field), and a Non CI on person_ref that INCLUDES forename and surname

you do not need to have CI and NON CI on same column.NON CI is of no use and covering index definitely not.

Because by definition of CI ,"CI actually store the row data" so it will directly locate all rows.So you should remove NON CI and it will definitely not improve your query.

| improve this answer | | | | |
  • Thank you. I think I'm getting a clearer picture now. You seem to be suggesting that I might have poor indexes that are causing a problem. On D500M for example, I have a CI on person_ref (the join field), and a Non CI on person_ref that INCLUDES forename and surname (the selected columns). Damn its complicated. – Nick Nov 9 '17 at 10:38
  • @Nick,see my edited last 3 para. – KumarHarsh Nov 9 '17 at 10:56
  • Thanks again. I have now removed the non clustered indexes, amended the query to have a where clause and created a new Non CI on the predicate field and INCLUDED all other fields selected from that table. That seems to have improved matters. I'm getting there ....slowly. A hearty plus 1 on your answer! (although its not displayed as I'm not 'reputable' enough. – Nick Nov 9 '17 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.