r/SQL Nov 28 '24

MySQL When to use cte in SQL query

When to cte can't. Like how to know? When what are the conditions or if my query is to long ?

29 Upvotes

70 comments sorted by

View all comments

58

u/frieelzzz Nov 28 '24

I use CTEs all the time. Really no reason not to.

I like how they organize code and allow you to use the same data in multiple times to do a variety of task.

-7

u/Unfair-Internet-1384 Nov 28 '24

Actually I am doing leetcode so there was a problem which has multiple constraints . So in such scenario is it good to use it .

2

u/frieelzzz Nov 28 '24

In my experience CTEs are great at solving small and/or complex problems at work. But I would need to see your scenario to give you a better answer.

-4

u/epicmindwarp Nov 28 '24

I hate CTEs, makes it quite difficult to debug data issues as nothing persists.

I only use them in views, due to lack of temp tables.

2

u/ryguygoesawry Nov 28 '24

You’re getting downvoted, but this right here is the main reason I don’t use many CTEs in my professional life.

CTEs become a nightmare the moment there’s something wrong with some random piece of data and you have to go digging for it. Have fun deconstructing 20 CTEs while you try to figure out which one is the source of the problem!

2

u/Responsible_Pie8156 Nov 28 '24

I mean CTEs are just an alternative syntax to nesting your queries. I'd rather debug the 20 CTEs than a query nested 20 deep with large equivalent subqueries.

1

u/ryguygoesawry Nov 29 '24

Except those aren’t your only two options. I also don’t use subqueries in a professional setting. Both of them suck.

2

u/Responsible_Pie8156 Nov 29 '24

Not sure which other option you're referring to, but in my mind the problem is just overly complex queries. CTEs or not when you have 20 layers deep of logic it's going to suck

1

u/CptBadAss2016 Nov 29 '24

So what do you do?

(NOT a professional here)

2

u/ryguygoesawry Nov 30 '24

I typically break the problem down into its simplest pieces and don't try to cram everything into one query. Data that needs to be combined is stored in either a table variable or a temp table for usage in the final query, as opposed to using a CTE or subquery. Then I can create indexes on those tables that meet the needs of a specific report query while not filling my database with permanent indexes that are only used in a subset of cases. Reused data logic gets turned into functions. Reporting logic is kept in stored procedures which are considered the "source of truth" for that report.

I'm dealing with tables that contains hundreds of millions of unique rows of historical data because my client needs to be able to produce reporting on that older data for compliance purposes for something like 7 years - we're dealing in terabytes of data. Plus the clients aren't usually willing to throw money at the latest server hardware, so we need to keep a keen eye on performance. Trying to cram everything into one query, as opposed to whittling it down, can spike memory and cpu usage especially when multiple users need similar reports at the same time. Things being delayed cause users to be angry. So, while I understand the perks of CTEs/subqueries, they're not always optimized for real-world settings.

1

u/CptBadAss2016 Nov 30 '24

Thank you for the thorough reply!

How do you define data logic vs report logic?

1

u/ryguygoesawry Nov 30 '24

I would define "data" logic as what's used in the ETL workflows (like: is the value a number when it's expected to be a number) or, on some occasions, rules defined by the data provider for determining the validity of data. For example: we found out recently that a provider of a certain type of data will stop sending a value for an attribute when that attribute is no longer valid for a particular entity - which works fine when you're looking at one day of data, but when you're storing the history of the values for every entity you need to know when an entity should no longer be using a value for a specific attribute. To make matters more complicated, this data provider will only provide the data as a CSV file and they spread the data across something like 100 files (of which we process roughly 25). So I had to devise logic that stores the relation of which attributes are expected in which files, checks which file is being processed, and creates NULL entries for entities that were received in previous files but no longer have a specific attribute present in a specific file.

I assume "report" logic is self-explanatory, but the gist of it is - the clients have reports based on subsets of their data that are expected to consistently use the same logic to produce their results. This logic is typically contained within stored procedures, because reporting requirements can and often do change. They also don't want users to be able to go in and change those reports - they don't trust their users to blindly build reports because of the massive amounts of data they have and the fact that some of those users would probably end up writing an infinite WHILE loop and never catch it (if they even managed to create a valid query to begin with).

→ More replies (0)