![]() SQL Server will reorder operations – it doesn’t have to execute the first CTE first, then the second CTE second, and so forth.Īs is often the case with your advice, you deftly bait me into wanting to argue specific exceptions (never shrink a db! index frag doesn’t matter! and other greatest hits) when I actually agree with the principal, haha. I am by no means saying that CTEs are always better than temp tables – often it’s the reverse – but if you’re hitting a performance tuning wall on queries that use temp tables, try converting them to CTEs. If you have a multi-step process that involves a lot of filtering and joining, let SQL Server recalculate where the filtering logic should happen. If you’re only going to access the data once, leave it as a heap. Indexes make the most sense when the temp table is going to be reused repeatedly across lots of statements that all do filtering or joining or sorting using the same keys. When loading temp tables – or any objects, really – be ruthless about filtering as early as possible. Only load them with the data you actually need. Now we’re going to have a CPU problem if a bunch of these queries run simultaneously.Īnd it’s still not as fast as our original solution, the heap. Note that CPU time is higher than elapsed time – that’s your clue that the query went parallel across more CPU cores. A big part of the reason why it’s faster is that now the query is going parallel. Įxecution time dropped from 57 seconds down to 18 seconds, but there’s a catch. Here we go:ĬPU time = 42107 ms, elapsed time = 18145 ms. It can’t be a unique index because multiple users share the same DisplayName, but that’s okay. Instead of a heap plus a nonclustered index on DisplayName, we can just define a single structure for the temp table: a clustered index on DisplayName. We can reduce the overhead of the process by only having one structure to store the data. We can’t do them both in parallel at the same time because the nonclustered index has to be able to point back to a specific row in the heap – and to do that, we need its physical location, like I talk about in How to Think Like the Engine. Part of the problem is that our heap (table) has to be loaded first, and then the data has to be sorted by DisplayName, and then an index has to be created on DisplayName. Sure, the second statement drops from 1,017 milliseconds to 75, but…who cares?!?! The additional overhead of building the index is much, much higher, making the query take ten times longer overall. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.CPU time = 56594 ms, elapsed time = 56850 ms. I’m offering a 75% discount to my blog readers if you click from here. If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Next time you’re tuning a query and want to drop some data into a temp table, you should experiment with this technique. If we crank one of the queries that gets a serial insert up to DOP 12, we lose some speed when we hit the table. Though the speed ups above at higher DOPs are largely efficiency boosters while reading from the Posts table, the speed does stay consistent through the insert. If you start experimenting with this trick, and don’t see noticeable improvements at your current DOP, you may need to bump it up to see throughput increases.Īlso remember that if you’re doing this with clustered column store indexes, it can definitely make things worse. At DOP 4, the insert really isn’t any faster than the serial insert. ![]() Note the execution times dropping as DOP increases. ![]() Here are the query plans for 3 fully parallel inserts into an empty, index-less temp #table. Another little thing we may need to tinker with is DOP. It’s the little things we do that often end up making the biggest differences. INSERT #tp WITH(TABLOCK) which is sort of annoying.īut you know. When it comes to parallel inserts, you do need the TABLOCK, or TABLOCKX hint to get it, e.g. If your goal is the fastest possible insert, you may want to create the index later. The first thing to note is that inserting into an indexed temp table, parallel or not, does slow things down. The demo code is available here if you’d like to test it out. If your temp table has indexes, primary keys, or an identity column, you won’t get the parallel insert no matter how hard you try. pattern, you’re probably not, and, well, that could be holding you back. INTO #some_table pattern, you’re probably already getting parallel inserts. If your code is already using the SELECT. Remember that you can’t insert into variables in parallel, unless you’re extra sneaky. If you have a workload that uses #temp tables to stage intermediate results, and you probably do because you’re smart, it might be worth taking advantage of being able to insert into the #temp table in parallel.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |