| Author |
Topic |
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
 Posted - 2007-02-22 : 02:00:30
|
| can anybody tell me, why to use 'LOOP' in a query? i m not getting much info about the keyword used in query. I have one proc, my prev developer has used LOOP in that query. I have to re-write it as that proc is taking much time ti execute(i.e. 58, 57 seconds). when i removed that LOOP keyword, it is executing in 2, 3 seconds.thanks in advance,Mahesh |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-22 : 02:08:10
|
| LOOP is type of join (generally called as NESTED LOOP join). You are seeing it because previous developer has added LOOP JOIN hint in the queries to force query optimizer to use NESTED LOOP joins. Generally, it is not good idea to add hints as it may not result in the best plan always.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-22 : 02:13:50
|
| Are you talking about a WHILE loop?That you decided to replace wih a SET-BASED query?Peter LarssonHelsingborg, Sweden |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-22 : 02:17:55
|
quote:
LOOP is type of join
Type of join ? BOL said it is a JOIN HINT
KH |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-22 : 02:28:34
|
quote:
Originally posted by khtan
quote:
LOOP is type of join
Type of join ? BOL said it is a JOIN HINT
KH
What I meant was LOOP keyword is type of join hint but NESTED LOOP is type of join.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-02-22 : 03:45:41
|
quote:
Originally posted by harsh_athalye
LOOP is type of join (generally called as NESTED LOOP join). You are seeing it because previous developer has added LOOP JOIN hint in the queries to force query optimizer to use NESTED LOOP joins. Generally, it is not good idea to add hints as it may not result in the best plan always.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
do it means to force the query optimizer to use NESTED LOOPS joins. if u ll not mention, ll query optimizer not ally join or something else?Mahesh |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-22 : 08:22:03
|
| If you don't specify join hint, query optimizer will choose whatever join type (Loop/Merge/Hash) it feels best depending on table and index statistics.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-02-22 : 08:50:54
|
| thanks Harsh.to i get something beyong JOIN. i was not aware with these things. thanks again.by the way, Harsh do u know, how these hints actually works? e.g. if u applied LOOP / HASH / MERGE hints, how the individually works or what query optimizer exactly do? i m just curious to know.Mahesh |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-22 : 10:58:34
|
| Mahesh,Basically googling out on HASH/MERGE/LOOP join will provide you enough links to get a feel of it. Still, if you want to go deeper in it, check this out:[url]http://blogs.msdn.com/craigfr/archive/tags/Joins/default.aspx[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-02-22 : 11:22:35
|
| thanks a lot HarshMahesh |
|
|
|
|
'LOOP' in query