For The Last Time, It Doesn’t Matter How Many Rows You’re Putting In A Table Variable
Thanks for watching!
Going Further
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. You can also get a quick, low cost health check with no phone time required.
Hi Eric.
I tested this on my (Microsoft SQL Server 2022 (RTM) – 16.0.1000.6) and the StackOverFlow2010 Db.
I dont get that same queryplan when using a table variable.
It uses “Parallelism” and is pretty fast even if estimates are off.
I changed compatibility_level to 150
I dislike table variables as much as you but I need to convince my devs to not use them by showing them when it´s bad using our servers and Db´s.
In order to do that I have to try your stuff and see the same results as you.
Could you send me the queries for Table variable and Temptable, so I am sure that the queries are correct or hint me to what could be going on?
What you’re describing is impossible. Table variables do not under any circumstances allow for parallel inserts, unless the query is executed in a different context, like from dynamic SQL.
Are you sure you’re using a @table_variable and not a #temp_table? If so, please show me what you’re doing. I’ve already shown what I’m doing 😃
I was sure as hell I would get the same results as you so I guess i´m doing it wrong 🙂
The part in queryplan that shows parallelism(Gather Streams):
Query:
DECLARE @tblvar TABLE
(
Id INT
, INDEX ix CLUSTERED (Id)
);
INSERT INTO @tblvar
(
Id
)
SELECT dta.Id
FROM
(
SELECT Id
FROM
Users
WHERE
Id = 22656
UNION ALL
SELECT TOP (99) Id
FROM
Users
WHERE
Reputation = 1
ORDER BY CreationDate
) dta (Id);
SELECT COUNT_BIG(1)
FROM
@tblvar t
INNER JOIN
Posts p
ON p.OwnerUserId = t.Id
INNER JOIN
Comments c
ON c.UserId = t.Id;
Try it with these indexes:
CREATE INDEXdog
ON dbo.Posts
(OwnerUserId)
WITH
(MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
CREATE INDEX
cat
ON dbo.Comments
(UserId)
WITH
(MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
Gahh, xml wasn´t the best to send in a Reply obviously.
Lets try it without the xml stuff.
RelOp AvgRowSize=”15″ EstimateCPU=”0.00181815″ EstimateIO=”0″ EstimateRebinds=”0″ EstimateRewinds=”0″ EstimatedExecutionMode=”Batch” EstimateRows=”171.014″ LogicalOp=”Inner Join” NodeId=”2″ Parallel=”true” PhysicalOp=”Hash Match” EstimatedTotalSubtreeCost=”125.696″
Post reply bombardment:
Screen shot:
https://www.dropbox.com/scl/fi/u73fzvrz3safw92vo2ewt/TblVarParallelism.png?rlkey=xtebvskpdw3z376vqyrlw42fd&st=nj6l7ajz&dl=0
Yep, just like I said in the video, table variable modifications can’t use parallel plans. At no point did I say that select queries can’t.
Of course. I was assuming that my plan would be more or less exactly like yours and didn´t listen very well to what you didn´t say 🙂
I´ll pay better attention going forward.
Thanks for your time and have a great weekend.