I have the following query:

select  field1, field2 from table1 t1 with (index(IndSearch))
inner join table2 t2 ON t2.Id = t1.Id 
where t1.date between '04/01/2019' AND '05/01/2019' 
AND t2.field3 IN(....) AND t2.field4 IN(...)
  • Table1 has clustered index in t1.Id
  • IndSearch is a nonclustered index with (t1.Id, t1.date and other fields)
  • Table2 has a nonclustered index with (t2.id, t2.field3, field4)

  • Table1 contains more than 5M rows

  • Table2 contains more than 11M rows

The problem is the query analyzer always chooses the index in Table2. That option is like 7 seconds slower because there are more rows in that table.

I would like to hint the query analyzer to use the index IndSearch, searching with the date field. I have done some tests and that search is faster.

I did try using with (index(IndSearch)), however that did not work.

I did try using left join instead inner join. That changed the execution plan. In fact, that modification improved the response time, now the query analyzer first do an index scan over IndSearch.

I think that did solve my problem, however I am still curious if someone has any comments or suggestions. I will appreciate if someone can explain how to achieve a similar result using query or table hints.

You can take a look in the execution plans:

Inner join (7-8 seconds duration): https://www.brentozar.com/pastetheplan/?id=BJl-Lg5jE

Left Join (almost instantaneous): https://www.brentozar.com/pastetheplan/?id=ryya8gqoN

  • 1
    The goto solution to improve queries should not be query or table hints, that should be one of the last on the list (if all else fails). Having said that, could you add the execution plan to pastethepan? Why did the index hint not work, did you get an error? – Randi Vertongen May 3 '19 at 15:52
  • 2
    How many elements are in the IN filters? – Tony Hinkle May 3 '19 at 15:53
  • 5
    You are not posting the full query, therefore we can't help you. There's a mention of a t3 for example that is not defined. Post the full query, and the execution plan (without the hint) so that we can analyze it correctly – Luis Alberto Barandiaran May 3 '19 at 15:56
  • 1
    The problem is that your search is not based on the first value in the index. By default, and in most situations, the Query Optimizer will not use an index unless the first element is explicitly in the WHERE clause, and is not just part of a JOIN. An index built with t1.date, <other fields> without even mentioning t1.id would be efficient in this case. NOTE: t1.id does not need to be included, since the clustered key is always included in any non-clustered key. – Laughing Vergil May 3 '19 at 16:25
  • 1
    @RandiVertongen I did not know Pastethepan exists. I did add two execution plans – Ayorus May 3 '19 at 17:12

The goto solution to improve queries should not be query or table hints.

You should try other solutions like changing the query, adding or changing indexes first.

If the optimizer is still making mistakes after trying other workarounds then you might try adding a query / table hint.

Table variable estimates

The table variables have low estimates (one , two rows) while 850 rows or even 16.284 rows can be returned. This is impacting the rest of the query.

Estimated plan on the inner join query:

enter image description here

Actual query plan on the inner join query: enter image description here

Consider changing the table variables to temporary tables:

CREATE TABLE #lTemas(TemaId int);
INSERT INTO #lTemas (TemaId)
SELECT t.TemaId FROM Temas t
INNER JOIN @lClientes lc ON lc.n = t.ClienteId
AND nEstado > 0;

or adding OPTION(RECOMPILE) to the main query.


Next to these awful estimates, the query gets filtered from 127.689 rows to 4.071 rows at this point:

enter image description here

This could be due to the predicate on dNota

enter image description here

** possible fix**

Consider adding an index on dNota first

ON dbo.NotasHist(dNota,ArtId)

and removing the with (index(Buscador)) hint to see if the optimizer will use the index with the better estimates.

The main difference with the left join

The filtering is happening much sooner on the left join:

enter image description here

The estimates are higher

enter image description here

And due to the higher estimates the query is going parallel

enter image description here

Whereas on the inner join you are not using parallelism.

This and other considerations make the difference in execution time happen, fixing the estimates by adding option recompile or changing the table variables to temp tables would be the first thing to look into.

| improve this answer | |

If SQL Server isn't using an index the way you would expect it to, it usually means one of a few different things:

  1. That index isn't actually the most efficient way to carry out the query.
  2. Your statistics are badly in need of updating.
  3. The index doesn't contain the correct key columns to perform the search/lookup in the query.
  4. You've got a massive query with dozens of joins, requiring various intermediate sorts (and you might be better off breaking it into multiple queries and using indexed temp tables for intermediate results).

Your situation very likely falls under option 3. If the nonclustered index key starts with Id and date, then asking SQL Server to use that index to do a range search on date is like asking someone to open up the phone book and find everyone named Michael. You'll need an index with date as the first key column, and you may very well need to add enough included columns so that the RID/cluster key lookups don't cause that index to actually perform worse than what SQL Server is already doing (and then you're dealing with option 1).

| improve this answer | |
  • Thanks for your answer. However, you are missing the point that the query has already an aceptable performance. The main question is about the difference between the inner and the left join in relation with the usage of the indexes in the tables – Ayorus May 3 '19 at 19:49

The Query Plan that you have posted for INNER JOIN states that there are 2 missing index recommendations.

 Missing Index (Impact 99.7001):     

  [<Name of Missing Index, sysname,>]. 
ON [dbo].[NotaClientesH] ([ClienteId])  
 INCLUDE ([ArtId],[TemaId],[SintId])

Missing Index (Impact 99.9047):  

 [<Name of Missing Index, sysname,>]  
ON [dbo].[NotaClientesH] ([ClienteId],[TemaId]). 
INCLUDE ([ArtId],[SintId])

You have huge tables, but you say that they are properly indexed.
Therefore, you should make sure that your query is writen to use the indexes that you have on your tables, or try those recomendations.

| improve this answer | |
  • In fact there is a nonclustered index which contains those columns: ArtId (ASC), ClienteId (ASC), TemaId (ASC), SintId (ASC). However I did try the new index and that solve the problem. I think I need to review the difference between add and include columns to an index – Ayorus May 3 '19 at 20:03

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