Life Comes At You Fast
The summer intern at Microsoft was hard at work between CTPs. Last time around, there were some serious performance issues with our dear new friend GENERATE_SERIES.
- SQL Server 2022 GENERATE_SERIES: Some Notes At Release Time
- SQL Server 2022: GENERATE_SERIES Causes Parallel Deadlocks In A Transaction
With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.
There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.
You just put in whatever numbers you’re into.
That’s probably a good thing.
Caveat #1: Parallelism Restrictions
Loading data into tables that have row store indexes on them can’t go parallel.
DROP TABLE IF EXISTS dbo.art_aux; CREATE TABLE dbo.art_aux ( id int NOT NULL PRIMARY KEY CLUSTERED ); INSERT INTO dbo.art_aux WITH(TABLOCK) ( id ) SELECT gs.value FROM GENERATE_SERIES ( 1, 10000000 ) AS gs OPTION ( MAXDOP 8, QUERYTRACEON 8649 );
But performance is still pretty good, here. Much better than prior.
I think most folks out there would be okay waiting a few seconds for 10 million rows to show up in a table for them.
So sequential. Much unique.
But, what if you want to load those rows in faster?
Caveat #2: Use A Heap Or SELECT INTO Instead
If you create a heap, or just SELECT INTO on instead, you’ll get a parallel load into the table.
For 10 million rows, there’s an improvement of about 1.7 seconds on my sort of crappy travel VM with 4 available CPUs.
DROP TABLE IF EXISTS dbo.art_aux; CREATE TABLE dbo.art_aux ( id int NOT NULL ); INSERT INTO dbo.art_aux WITH(TABLOCK) ( id ) SELECT gs.value FROM GENERATE_SERIES ( 1, 10000000 ) AS gs;
Here’s the plan:
Yes, dear reader, I even blog on vacation. That’s how much I love and care about you.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.