I had the very same issue issue on the payroll system I used to support. Despite numerous attempts to get the queries re-written, nothing was done. "It works fine on our system," claimed the supplier. Of course it does, you don't have much data in it. The main failing of SQL developers is that they have their heads in the sand when it comes to scaling up data. Every database we had issues with was the same, written and tested against a small data set. "Duh!" Nice presentation.
Good explanation! I very happy you stated the fact the most of the people think that CTEs “cache” their result sets. I really do not understand why they think that, because it is not written anywhere… Please, do you have additional explanations?
@@DataWithBert Materializing a CTE is some sort of "parameter sniffing" technique... that is why caching the result set is implemented at application level and not at RDBMS level... until SQL Server vNext....
Hey Birt... I saw you in one of Pinal Dave's videos. Then saw your channel and subscribed it today. I like the way you have made the video series and it helps understand one concept at a time. Keep them coming. All the best
I have a set of OLAP reporting tables that come from staging tables after being processed where you can run reports and analytics on the OLAP tables to gather insights from the data. “I can’t run my reports for yesterday. How you go about investigating this?. What you prevent this for later?
Nice video! As usual! Question: Why when you saved the table to a #temp table did the (12) logical reads not come from "Worktable" which I thought was TempDB. Is it because the "#January..." records were somehow in the Buffer Pool already, and therefore not in Worktable?? Tx in advance!
Hi, well we can use temp tables to store data and can reference multiple times in Procs/Queries.. What if we are dealing with views, what sort of approach we go with to write in an optimized way.. Pls suggest, thanks
Nice video again, Bert! To make the Statistics IO outputs even prettier, you could use statisticsparser.com/. Might be overkill in most situations, but really helpful for more complex queries with lots of tables!
I had the very same issue issue on the payroll system I used to support. Despite numerous attempts to get the queries re-written, nothing was done. "It works fine on our system," claimed the supplier. Of course it does, you don't have much data in it.
The main failing of SQL developers is that they have their heads in the sand when it comes to scaling up data. Every database we had issues with was the same, written and tested against a small data set. "Duh!"
Nice presentation.
Good explanation! I very happy you stated the fact the most of the people think that CTEs “cache” their result sets. I really do not understand why they think that, because it is not written anywhere… Please, do you have additional explanations?
@@DataWithBert Materializing a CTE is some sort of "parameter sniffing" technique... that is why caching the result set is implemented at application level and not at RDBMS level... until SQL Server vNext....
It is easy to forget you can create indexes on temp tables. Thanks for the reminder.
Bert, Another great subject simply explained with a good example. Thank you again.
Hey Birt... I saw you in one of Pinal Dave's videos. Then saw your channel and subscribed it today. I like the way you have made the video series and it helps understand one concept at a time.
Keep them coming. All the best
Thanks Bert. Please keep on bringing such kind of info on performance tuning stuff.
Great Video Bert! I have really enjoyed this series!
Great video Bert! Keep up the GREAT work!
Well done Bert. Keep Posting
I've learned new stuff here man. Thanks a lot.
Nice video Bert,
I have a set of OLAP reporting tables that come from staging tables after being processed where you can run reports and analytics on the OLAP tables to gather insights from the data.
“I can’t run my reports for yesterday. How you go about investigating this?. What you prevent this for later?
Great Video Keeping posting more video
Great job! Thanks a lot for knowledge sharing!
Nice video! As usual! Question: Why when you saved the table to a #temp table did the (12) logical reads not come from "Worktable" which I thought was TempDB. Is it because the "#January..." records were somehow in the Buffer Pool already, and therefore not in Worktable?? Tx in advance!
Hi, well we can use temp tables to store data and can reference multiple times in Procs/Queries.. What if we are dealing with views, what sort of approach we go with to write in an optimized way.. Pls suggest, thanks
Well done! I wasn't aware of that fact on CTEs.
The low volume background music at the end was slighly distracting/disturbing.
Nice video again, Bert!
To make the Statistics IO outputs even prettier, you could use statisticsparser.com/.
Might be overkill in most situations, but really helpful for more complex queries with lots of tables!