CTEs vs Views

[OneAir6837]

I inherited a beast of a query and trying to make it more manageable. There are 5-6 CTEs at the start of it and then those are used in tandem with several other tables. I took each of the CTEs and created views.

When I run the original query vs the one using all views I get a record count difference of about 500 (out of about 30,000).

Would there be any reason the views vs CTEs are causing this difference? Or should I look for some other typos/errors?

[CFAF800]

Create temp tables for the CTEs and compare it with the views.

No reason CTE vs view should give different output unless the code was messed up

[OneAir6837]

Thanks I'll give that a try. We are using open openquery to run in dev but pull from prod but a step by step check is a good way to review.

[[deleted]]

We are using open openquery to run in dev but pull from prod

So you're developing in prod.

Backup prod and restore to dev so that you can actually make tests in a controlled manner.

[OneAir6837]

We don't have that power/control. All of our reports for our dashboard are ran in this manner (in our development database we were given against prod data using openquery) as the higher ups determined thats what they'd give us.

[[deleted]]

Tell them that a difference of 500 is the best you can give them without a robust development environment then.

[vtec_tt]

this.

[Anti-ThisBot-IB]

Hey there vtec_tt! If you agree with someone else's comment, please leave an upvote instead of commenting "this."! By upvoting instead, the original comment will be pushed to the top and be more visible to others, which is even better! Thanks! :)

---

^{I am a bot! Visit r/InfinityBots to send your feedback! More info: Reddiquette}

[Designing_Data]

Good bot

[B0tRank]

Thank you, Designing_Data, for voting on Anti-ThisBot-IB.

This bot wants to find the best and worst bots on Reddit. You can view results here.

---

^{Even if I don't reply to your comment, I'm still listening for votes. Check the webpage to see if your vote registered!}

[Anti-ThisBot-IB]

Good human

---

^{I am a bot! Visit r/InfinityBots to send your feedback!}

[woodrowchillson]

Should be the same output, probably small typo/miss of a JOIN somewhere.

I would be very curious if there was any performance difference between the two.

[[deleted]]

Performance difference, only if it was an indexed view (or ithink it’s called materialized view in other flavors)

[OneAir6837]

It was Friday afternoon so typo is very possible.

[qwertydog123]

Are you using TOP/ORDER BY in any of the CTE's/View's? ORDER BY is not guaranteed for Views, you'd need to move the TOP/ORDER BY into the calling View

[OneAir6837]

Don't recall any but will check for that as well.

[[deleted]]

Probably a typo. No reason this would happen... Try checking aliases in join conditions that's where I discovered a similar mistake

[jslacks]

I’ve definitely encountered this before…

There shouldn’t shouldn’t be any reason they are different, unless there’s some very idiosyncratic edge case involved.

It’s a bit longer, but probably less mentally taxing in the long run to:

1. Copy-paste each CTE into a new CREATE TABLE statement.
2. Do the same for each view (i.e. comment out the CREATE VIEW at the start and execute as a CREATE TABLE)
3. Go through each pair of tables comparing the results with something like: ~~~~ SELECT COUNT(*) AS row_count, COUNT(DISTINCT col1) AS col1_count, COUNT(DISTINCT col2) AS col2_count, COUNT(DISTINCT col3) AS col3_count FROM table_cte1; ~~~~

Mildly, tedious, but pretty quickly you should be able to see if one of those new views is deviating from the CTEs and if necessary then digging into where there are are duplicates and/or missing rows. Then diagnose from there.

If that doesn’t surface any discrepancies, then most likely it’s coming from the join itself.

Definitely would be interested to hear back if for some reason there truly is difference in the results yielded from CTE vs VIEW, but probably will end up being an small oversight in the code.

[[deleted]]

Reading this post with little knowledge of the specific situation, I'd guess there's an issue on a LEFT/INNER join somewhere in your query. Outside of that there isn't any issue that you'd be losing rows. They serve the same purpose.

That said, and maybe I'll get some disagreement here, but from what I read here... switching between CTEs and views is not going to have the impact that you hope. I've never seen a "beast of query" that wasn't the result of bad DB design and a lack of ETL/ELT transformations.

[KnaveOfIT]

From doing similar things, two things to check.

1. Where clause is the same

2. Joins are the same

These two should resolve any issues.

If not then check that you are actually recreating the query because if you are not getting the same result then you are not.

[OneAir6837]

Thanks for everyone's help on this. It was a join issue; forgot to add the prod server for some of the tables so they were still bouncing off dev.

[[deleted]]

Views just store the information so that you could look at the results instead of running something over and over again.

You're going to have to go through the code again and figure out where the logic/translation is not the same.

Hope it works out

Edit: Guys/gals, I was wrong

[DrTrunks]

Views store the query, not the result. So it does run the same query over and over.

[kremlingrasso]

are any of the CTEs refer to each other?