Simplifying while loop query

Question

Post reply

Simplifying while loop query

rs80

SSCrazy

Points: 2312
More actions
October 14, 2015 at 8:24 am

#305507

In order to improve readability and performance, can I simplify the query below to a set based query?
DECLARE @ProductCodes TABLE(ProductCode nvarchar(3), IsProcessed bit DEFAULT 0)
INSERT INTO @ProductCodes(ProductCode) VALUES('ABC')
INSERT INTO @ProductCodes(ProductCode) VALUES('DEF')
INSERT INTO @ProductCodes(ProductCode) VALUES('GHI')
DECLARE @OrderData TABLE(OrderID nvarchar(3), OrderDescription nvarchar(1000))
INSERT INTO @OrderData VALUES('F12', 'Description1')
INSERT INTO @OrderData VALUES('F22', 'Description2')
DECLARE @FinalData TABLE(ProductCode nvarchar(3), OrderID nvarchar(3), OrderDescription nvarchar(1000))
DECLARE @ProductCode nvarchar(3)
WHILE ((SELECT COUNT(*) FROM @ProductCodes WHERE IsProcessed = 0) > 0)
BEGIN
SELECT TOP 1 @ProductCode = ProductCode
FROM @ProductCodes
WHERE IsProcessed = 0
INSERT @FinalData(ProductCode, OrderID, OrderDescription)
SELECT @ProductCode, OrderID, OrderDescription
FROM @OrderData
UPDATE @ProductCodes
SET IsProcessed = 1
WHERE ProductCode = @ProductCode
END
The jist of the query is to repeat the Order Information for each Product.
Thanks for the help!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

rs80 SSCrazy Points: 2312 More actions · Answer 1

Can I just use CROSS APPLY like below?

SELECT ProductCode

,OrderCode

,OrderDescription

FROM @ProductCodes PC

CROSS APPLY @OrderData OD

ORDER BY ProductCode

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

rs80 (10/14/2015)
In order to improve readability and performance, can I simplify the query below to a set based query?
DECLARE @ProductCodes TABLE(ProductCode nvarchar(3), IsProcessed bit DEFAULT 0)
INSERT INTO @ProductCodes(ProductCode) VALUES('ABC')
INSERT INTO @ProductCodes(ProductCode) VALUES('DEF')
INSERT INTO @ProductCodes(ProductCode) VALUES('GHI')
DECLARE @OrderData TABLE(OrderID nvarchar(3), OrderDescription nvarchar(1000))
INSERT INTO @OrderData VALUES('F12', 'Description1')
INSERT INTO @OrderData VALUES('F22', 'Description2')
DECLARE @FinalData TABLE(ProductCode nvarchar(3), OrderID nvarchar(3), OrderDescription nvarchar(1000))
DECLARE @ProductCode nvarchar(3)
WHILE ((SELECT COUNT(*) FROM @ProductCodes WHERE IsProcessed = 0) > 0)
BEGIN
SELECT TOP 1 @ProductCode = ProductCode
FROM @ProductCodes
WHERE IsProcessed = 0
INSERT @FinalData(ProductCode, OrderID, OrderDescription)
SELECT @ProductCode, OrderID, OrderDescription
FROM @OrderData
UPDATE @ProductCodes
SET IsProcessed = 1
WHERE ProductCode = @ProductCode
END
The jist of the query is to repeat the Order Information for each Product.
Thanks for the help!

You mean like this?

INSERT INTO #FinalData(ProductCode, OrderID, OrderDescription)

SELECT p.ProductCode, o.OrderID, o.OrderDescription

FROM #ProductCodes p

CROSS JOIN #OrderData o

WHERE p.IsProcessed = 0

UPDATE #ProductCodes SET IsProcessed = 1 WHERE IsProcessed = 0

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rs80 SSCrazy Points: 2312 More actions · Answer 3

Thanks for your reply Chris.

Based on what I'm trying to achieve, cross JOIN seems to be the correct way instead of cross APPLY. I'm referencing this explaination: http://www.sqlservercentral.com/Forums/Topic843768-8-1.aspx

Please let me know if that's correct.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

rs80 (10/14/2015)
Thanks for your reply Chris.
Based on what I'm trying to achieve, cross JOIN seems to be the correct way instead of cross APPLY. I'm referencing this explaination: http://www.sqlservercentral.com/Forums/Topic843768-8-1.aspx
Please let me know if that's correct.

These statements

INSERT INTO #FinalData(ProductCode, OrderID, OrderDescription)

SELECT p.ProductCode, o.OrderID, o.OrderDescription

FROM #ProductCodes p

CROSS JOIN #OrderData o

WHERE p.IsProcessed = 0

INSERT INTO #FinalData(ProductCode, OrderID, OrderDescription)

SELECT p.ProductCode, o.OrderID, o.OrderDescription

FROM #ProductCodes p

CROSS APPLY #OrderData o

WHERE p.IsProcessed = 0

INSERT INTO #FinalData(ProductCode, OrderID, OrderDescription)

SELECT p.ProductCode, o.OrderID, o.OrderDescription

FROM #ProductCodes p

INNER JOIN #OrderData o ON 1 = 1

WHERE p.IsProcessed = 0

all have the same execution plan.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rs80 SSCrazy Points: 2312 More actions · Answer 5

But doesn't CROSS APPLY have a different purpose than CROSS JOIN?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

rs80 (10/14/2015)
But doesn't CROSS APPLY have a different purpose than CROSS JOIN?

CROSS APPLY dbo.InlineTableValuedFunction (parameters)

I guess might be the standard usage of APPLY. So replace the tablesource dbo.InlineTableValuedFunction with an actual table, reduce the number of parameters to none, and there you have it. It's just a subset (or a special set) of the standard usage.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

rs80 (10/14/2015)
But doesn't CROSS APPLY have a different purpose than CROSS JOIN?

Yes, it allows you to reference columns from the previous tables to use in correlated subqueries or functions.

In this case, you logically need a CROSS JOIN which can be structured in any of the 3 options posted by Chris.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rs80 SSCrazy Points: 2312 More actions · Answer 8

rs80

SSCrazy

Points: 2312

October 14, 2015 at 12:37 pm

#1833318

Thanks Chris and Luis.

rs80 SSCrazy Points: 2312 More actions · Answer 9

Does the original code that I posted with the while loop and the CROSS JOIN/APPLY have the same performance? I would think that the CROSS JOIN has better performance cause it's preserving the set based algorithm whereas the while loop is performing iteration.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

rs80 (10/16/2015)
Does the original code that I posted with the while loop and the CROSS JOIN/APPLY have the same performance? I would think that the CROSS JOIN has better performance cause it's preserving the set based algorithm whereas the while loop is performing iteration.

Using the code I posted above and substituting your table names, compare the execution plans and look for any differences, ensuring that you look at the property sheets of the operators.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden