StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server slow stored procedure that deletes
text
Body
copied!<p>I have written a stored procedure. Now I see, that this is very poor performance. I think this is because of the while loop.</p> <pre><code>ALTER PROCEDURE [dbo].[DeleteEmptyCatalogNodes] @CatalogId UNIQUEIDENTIFIER, @CatalogNodeType int = null AS BEGIN SET NOCOUNT ON; DECLARE @CID UNIQUEIDENTIFIER DECLARE @CNT int SET @CID = @CatalogId SET @CNT = @CatalogNodeType DELETE cn FROM CatalogNodes cn LEFT JOIN CatalogNodes as cnj on cn.CatalogNodeId = cnj.ParentId LEFT JOIN CatalogArticles as ca on cn.CatalogNodeId = ca.CatalogNodeId WHERE cn.CatalogId = @CID AND cnj.CatalogNodeId IS NULL AND ca.ArticleId IS NULL AND (cn.CatalogNodeType = @CNT OR @CNT IS NULL) WHILE (@@ROWCOUNT > 0) BEGIN DELETE cn FROM CatalogNodes cn LEFT JOIN CatalogNodes as cnj on cn.CatalogNodeId = cnj.ParentId LEFT JOIN CatalogArticles as ca on cn.CatalogNodeId = ca.CatalogNodeId WHERE cn.CatalogId = @CID AND cnj.CatalogNodeId IS NULL AND ca.ArticleId IS NULL AND (cn.CatalogNodeType = @CNT OR @CNT IS NULL) END END </code></pre> <p>Do anyone of you can give me a hint on how to do it more 'set' like?</p> <p>Thanks a lot!</p> <p><strong>EDIT for comments and answers:</strong></p> <p>The tables are build like this:</p> <p>CatalogNodes:</p> <pre><code>CatalogNodeId|ParentId|Name 1|NULL|Root 2|1|Node1 3|1|Node2 4|2|Node1.1 </code></pre> <p>CatalogArticles:</p> <pre><code>CatalogNodeId|Name 3|Article1 3|Article2 3|Article3 </code></pre> <p>After my SP was called, Node1 and Node1.1 have to be deleted. In the first delete statement, Node1.1 will be deleted. In the While loop, Node1 will be deleted. I hope my problem is now easier to understand, it is a tree structure.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload