Stack Overflow
Stack Overflow

Questions tagged [join-hints]

18 questions
41
votes
2 answers

When should I use an INNER -LOOP- JOIN instead of an INNER JOIN

Today I learned about a thing in SQL Server called INNER LOOP JOIN. What does this mean? (Google is not helping .. or should I say ... the blog posts about it are a bit .. technical and are blowing my mind). Also, what are some common scenarios that…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
21
votes
5 answers

advantages in specifying HASH JOIN over just doing a JOIN?

What are the advantages, if any, of explicitly doing a HASH JOIN over a regular JOIN (wherein SQL Server will decide the best JOIN strategy)? Eg: select pd.* from profiledata pd inner hash join profiledatavalue val on val.profiledataid=pd.id In the…
Garrett
  • 1,750
  • 2
  • 16
  • 23
14
votes
1 answer

Loop Join in SQL Server 2008

I'm not clear about working difference between queries mentioned below. Specifically I'm unclear about the concept of OPTION(LOOP JOIN). 1st approach: it's a traditional join used, which is most expensive than all of below. SELECT * FROM [Item…
Vikrant
  • 4,920
  • 17
  • 48
  • 72
12
votes
2 answers

TSQL - What is the proper order to joining tables?

My google-fu and so-fu is failing me here, so I might as well ask. I have many queries with multiple joins in them. Within one query, I am joining header/item/details together, as well as looking up various bits of information for these…
IronicMuffin
  • 4,182
  • 12
  • 47
  • 90
4
votes
1 answer

SQL Server LEFT JOIN fails to match rows without JOIN hint

I have what appears to be a corrupt index? Here is what is happening. I have two table-functions which the first is a set of cases and the second is a set of aware dates. These two sets have a 1 (case) to 0 or 1 (aware date) relationship. …
Dennis Allen
  • 412
  • 5
  • 14
3
votes
3 answers

How do I force MS SQL Server to perform an index join?

I'm working on an assignment where I'm supposed to compare different join methods in SQL Server, namely hash-join, merge-join and index-join. I'm having difficulties getting SQL Server to perform an index-join. Can anyone show me how I can force it…
Thorgeir
  • 3,960
  • 3
  • 24
  • 20
3
votes
2 answers

SQL SERVER 2008 JOIN hints

Recently, I was trying to optimise this query UPDATE Analytics SET UserID = x.UserID FROM Analytics z INNER JOIN UserDetail x ON x.UserGUID = z.UserGUID Estimated execution plan show 57% on the Table Update and 40% on a Hash Match (Aggregate). I…
super9
  • 29,181
  • 39
  • 119
  • 172
2
votes
4 answers

Is this join hint dangerous?

A coworker asked me to look at indexing on some tables because his query was running very long. Over an hour. select count(1) from databaseA.dbo.table1 inner join databaseA.dbo.table2 on (table1.key = table2.key) inner join databaseB.dbo.table3 on…
Bob Probst
  • 9,533
  • 8
  • 32
  • 41
2
votes
1 answer

INNER LOOP JOIN Failing

I need to update a field called FamName in a table called Episode with randomly generated Germanic names from a different table called Surnames which has a single column called Surname. To do this I have first added an ID field and NONCLUSTERED…
MoonKnight
  • 23,214
  • 40
  • 145
  • 277
1
vote
1 answer

SQL Server Hash Joins versus Nested Loops

Quick note So, as I was writing the problem below I found a way to fix it myself. I thought I'd still post the question because: Someone might find it useful. I don't understand too much why it works. Anyway the fixed code (see answers). I…
Ian
  • 57
  • 8
1
vote
1 answer

Transact-SQL: Join-Hints - Differences, Performance, Limitations

I want to know the differences between the join hints (MERGE, HASH, LOOP, REMOTE). So, in which situations I should which one and why? How to know which hint I should use? Are there any pro's or contra's of using the hints? If I use the correct…
Mik4sa
  • 33
  • 1
  • 6
1
vote
2 answers

T-SQL - why INNER MERGE JOIN makes several joins definitons to executed more faster

I am very confused about this. I am not able to provided a example because the final SQL statement is dynamically built and a lot of functions and procedures take role in this... Generally, I have five joins. I have noticed that when I remove one of…
gotqn
  • 42,737
  • 46
  • 157
  • 243
0
votes
0 answers

Hash join hint causes fewer rows to return from query

I have a query that uses a join hint to force a HASH join between a table and a view. I noticed that there was missing data in the query when I use the query hint to force the HASH join. When I run the two queries there 65 NULL rows (out of about…
Dave
  • 61
  • 6
0
votes
1 answer

Explicitly mentioning Hash hint to joins

How do I know where to use Hash hint join explicitly? How does sometimes query optimizer gets tricked and applies a hint which sometimes affects performance?
satyajit
  • 2,540
  • 11
  • 33
  • 44
0
votes
1 answer

Codeigniter join hits

How can I pass in join hints to codeigniter? I want to run the following SQL query: SELECT t1.a, t2.b FROM t1 INNER HASH JOIN t2 on t1.id = t2.id How do I do run the above query with codeigniter? You can specify the join type, but you cannot pass…
Pio
  • 4,044
  • 11
  • 46
  • 81
1
2