Home Forums SQL Server 2008 SQL Server Newbies I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping . Co RE: I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping

  • Not seeing the code or structures or plans, just guessing at possible solutions, but allow me to point out, there is nothing inherently good, or bad, in nested loop joins. If you have smaller data sets, it's very likely that they will be the most efficient mechanism for retrieving data. But those same loop joins on large data sets are performance killers because, look at the description of a loop join, it's a cursor. Now, it's not a cursor like when you declare a cursor in T-SQL, but internally, it's a cursor. For larger data sets hash & merge joins are usually better performers, again, depending on all sorts of stuff.

    The bigger question for me would be, why would you see the same execution plan for larger result sets? Are your statistics out of date causing you to get a loop join when you should get something else? I'd look into that, not simply focus on a particular type of operator in the plan.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning