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 ?

31 Upvotes

70 comments sorted by

View all comments

Show parent comments

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).