Recursive CTE

Question

Recursive CTE

Scott745618

SSC Enthusiast

Points: 197
More actions
May 25, 2021 at 9:57 am

#3887675

Hi everyone,
I am working with a recursive CTE that loops through records. I have lead and lag variables the issue is that my lag variables need to look back the number of steps the loop is in. I have the lag working fine but the lead works inversely to the lag. So when the lag is looking back 4 steps the lead is looking forward 1. When the lag is looking back 3 steps then the lead should be 2 and so on. Is there any online material where someone has dealt with this issue?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

I doubt you'll find a solution that is exactly specific to your needs. If you post some sample data (as DDL and DML statements in a code block), and expected results, along with what you have so far I am sure someone here'll be able to push you in the right direction or provide a solution.

Though, it sounds like, if I am honest, that the LEAD is just the 5 minus the LAG value.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I guess I don't understand the need to "loop through the records". A well written WHILE loop is usually faster and about 8 times less resource intensive. That, notwithstanding, why aren't you just using some nice, straight-forward set based code? What is it that your code is actually doing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott745618 SSC Enthusiast Points: 197 More actions · Answer 3

Sorry I could not include the data it was a work project. I've simplified the CTE so now I don't need the lead so everything is working. I am going to put it into a multi table UDF function so it is reusable.

Thanks

Thom A SSC Guru Points: 99130 More actions · Answer 4

Scott745618 wrote:

Sorry I could not include the data it was a work project. I've simplified the CTE so now I don't need the lead so everything is working. I am going to put it into a multi table UDF function so it is reusable.
Thanks

If you're making a function, don't make a multi-line table value function, make it in an inline table value function; the former can perform very poorly.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Scott745618 SSC Enthusiast Points: 197 More actions · Answer 5

Is there any resources for learning UDFs? I have look online but I could only find basic introductions. I am keen to fully understand how I can best use them

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 6

Alas, SQL Server's performance can suffer from encapsulating in UDF

https://www.brentozar.com/archive/2019/04/finding-froids-limits-testing-inlined-user-defined-functions/

https://www.mssqltips.com/sqlservertip/4689/understand-the-performance-behavior-of-sql-server-scalar-user-defined-functions/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Scott745618 wrote:

Is there any resources for learning UDFs? I have look online but I could only find basic introductions. I am keen to fully understand how I can best use them

Here's an article on one of the more important aspects of "UDFs" in SQL Server. And, no... I wouldn't trust SQL Server 2019 to "do the needy" thing for scalar of mTVFs (Multi-Statement Table Valued Functions).

https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Scott745618 wrote:

Sorry I could not include the data it was a work project. I've simplified the CTE so now I don't need the lead so everything is working. I am going to put it into a multi table UDF function so it is reusable.
Thanks

If you're using a Recursive CTE (rCTE) to "loop" through rows one at a time, you're probably dooming your project to being slow and very resource intensive. The same holds true if you use a Multi-statement Table Valued Function (mTVF).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)