SQL Query is taking long time when using WHILE inside another WHILE loop. Please help

The performance issue you're experiencing is likely due to the nested WHILE loops and the repeated creation and dropping of the temporary table ##TempLinePatternSequence. Each iteration of the outer loop creates a new temporary table, inserts data into it, and then processes it in the inner loop. This can be very inefficient, especially with large datasets.

Here are some strategies to optimize your SQL query:

1. Avoid Repeated Table Creation

• Instead of creating and dropping the temporary table ##TempLinePatternSequence in each iteration of the outer loop, create it once outside the loop and truncate it at the beginning of each iteration.

2. Use Set-Based Operations

• SQL Server is optimized for set-based operations rather than row-by-row processing. Try to replace the nested loops with set-based operations where possible.

3. Indexes

• Ensure that the tables involved have appropriate indexes. For example, indexes on LineDirId, PatternId, and SignId can significantly improve performance.

4. Batch Processing

• If the dataset is too large to process in one go, consider processing it in smaller batches.

Optimized Query Example

Here’s a revised version of your query that incorporates these suggestions:

sql

Copy

-- Create the temporary table once outside the loop CREATE TABLE ##TempLinePatternSequence ( [Id] int identity(1,1) not null, [SignId] uniqueidentifier NULL, [LineDirId] uniqueidentifier NULL, [PatternId] uniqueidentifier NULL, [StopNum] uniqueidentifier NULL, [Sequence] int null, [DistanceFromPrevious] float null ); -- Indexes on the temporary table CREATE INDEX idx_TempLinePatternSequence ON ##TempLinePatternSequence (LineDirId, PatternId, SignId); -- Outer loop WHILE @LineDircurrent <= @LineDirCount BEGIN -- Truncate the temporary table at the beginning of each iteration TRUNCATE TABLE ##TempLinePatternSequence; -- Get the current LineDirId, PatternId, and SignId SELECT @CurrentLineDirId = LineDirId, @CurrentPatternId = PatternId, @CurrentSignID = SignId FROM ##TARGETLineDir WHERE id = @LineDircurrent; -- Insert data into the temporary table INSERT INTO ##TempLinePatternSequence SELECT DISTINCT [SignId], [LineDirId], [PatternId], [StopNum], [Sequence], [DistanceFromPrevious] FROM ##LinePatternSequence LPS WHERE LPS.LineDirId = @CurrentLineDirId AND LPS.PatternId = @CurrentPatternId AND LPS.SignId = @CurrentSignID ORDER BY lps.Sequence; -- Get the count of rows in the temporary table SELECT @RTcount = COUNT(*) FROM ##TempLinePatternSequence; -- Set-based insert into ##TargetRunTimes INSERT INTO ##TargetRunTimes SELECT DISTINCT MRT.RuntimesBandId, LPS.SignId, LPS.LineDirId, LPS.PatternId, LPS.StopNum, NULL, ROW_NUMBER() OVER (ORDER BY LPS.Id), NULL, NULL, NULL, NULL, MRT.ExtId FROM ##TempLinePatternSequence LPS INNER JOIN ##RunTimes MRT ON LPS.LineDirId = MRT.LineDirId AND LPS.PatternId = MRT.PatternId AND LPS.SignId = MRT.SignId WHERE LPS.LineDirId = @CurrentLineDirId AND LPS.PatternId = @CurrentPatternId AND LPS.SignId = @CurrentSignID; -- Increment the outer loop counter SET @LineDircurrent = @LineDircurrent + 1; END; -- Drop the temporary table after the loop DROP TABLE ##TempLinePatternSequence;

Explanation

1. Temporary Table Creation:
   • The temporary table ##TempLinePatternSequence is created once outside the loop and truncated at the beginning of each iteration.
2. Set-Based Insert:
   • The inner loop is replaced with a set-based insert operation using ROW_NUMBER() to generate the sequence.
3. Indexes:
   • An index is created on the temporary table to speed up the join operations.
4. Batch Processing:
   • If the dataset is very large, consider processing it in smaller batches by adding a TOP clause to the INSERT INTO ##TempLinePatternSequence query and adjusting the loop logic accordingly.

By following these optimizations, you should see a significant improvement in the performance of your SQL query.

SQL Query is taking long time when using WHILE inside another WHILE loop. Please help

Help on what? You haven't posted any usefull information?

The only suggestion I can give is: Stop working with iterations = WHILE loop.

Do what SQL Server can do best: Work set-based = real SQL solution.