12/8/2022 0 Comments Sql server cteIf you hit a performance wall, try ripping out a CTE and writing it to a temp table, then joining to the temp table. I’d suggest starting with CTEs because they’re easy to write and to read. ![]() When you need to break a query up into phases to isolate unpredictable components that dramatically affect the behavior of the rest of the query.When you need to pass data between stored procedures, or.You have to refer to the output multiple times, or.When you’re not sure what portions of the CTE’s data will actually be necessary for the rest of the query (because SQL Server can figure out what parts to execute, and what parts to simply ignore).When what comes out of the CTE doesn’t really influence the behavior of the rest of the query, or.SQL Server can do a good job of estimating how many rows will come out of it, and the contents of what those rows will be, or.Great: it chose to do a table scan, leading to less logical reads (just 49,900 total for both operations).Great: it guessed much more accurately about how many Users lived in those locations (est: 24,657 rows, actual: 50,073 rows). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |