May 4, 2018 at 12:22 pm
Hi all, I have just started a new gig as a contractor, and the DB architect here insists that we should not pass table variables between procedures as parameters as there is an “enormous” performance overhead, when I asked why this was, I got an answer that did not make any sense to me, in fact it sounded total BS. Been the new boy, calling BS on my boss in week one did not seem a great move, plus, I have been wrong before…
So, I have done some googling/ research and have found nothing to indicate there is a problem simply passing a TVP between procs. With some basic testing I can see nothing in the query plans or CPU/IO stats showing significant differences related to the fact the TVP was declared in another proc……..
If there is something definitive someone could point me to, or any sights as to what my boss is taking about, that would be great.
Thanks
Pat
pef
May 4, 2018 at 4:05 pm
pef - Friday, May 4, 2018 12:22 PMHi all, I have just started a new gig as a contractor, and the DB architect here insists that we should not pass table variables between procedures as parameters as there is an “enormous†performance overhead, when I asked why this was, I got an answer that did not make any sense to me, in fact it sounded total BS. Been the new boy, calling BS on my boss in week one did not seem a great move, plus, I have been wrong before…
So, I have done some googling/ research and have found nothing to indicate there is a problem simply passing a TVP between procs. With some basic testing I can see nothing in the query plans or CPU/IO stats showing significant differences related to the fact the TVP was declared in another proc……..
If there is something definitive someone could point me to, or any sights as to what my boss is taking about, that would be great.Thanks
Pat
So what was his answer? Can't answer your question unless we understand what he is saying.
May 4, 2018 at 4:07 pm
pef - Friday, May 4, 2018 12:22 PMHi all, I have just started a new gig as a contractor, and the DB architect here insists that we should not pass table variables between procedures as parameters as there is an “enormous†performance overhead, when I asked why this was, I got an answer that did not make any sense to me, in fact it sounded total BS. Been the new boy, calling BS on my boss in week one did not seem a great move, plus, I have been wrong before…
So, I have done some googling/ research and have found nothing to indicate there is a problem simply passing a TVP between procs. With some basic testing I can see nothing in the query plans or CPU/IO stats showing significant differences related to the fact the TVP was declared in another proc……..
If there is something definitive someone could point me to, or any sights as to what my boss is taking about, that would be great.Thanks
Pat
Write a test to prove it one way or another. It's important for two reasons....
1. The Internet is full of sometimes horribly incorrect information.
2. You need to prove it in the environment you're working on.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2018 at 4:53 pm
Lynn Pettis - Friday, May 4, 2018 4:05 PMSo what was his answer? Can't answer your question unless we understand what he is saying.
"It generates horrible query plans because it makes copies of the temp tables passed between the procedures"
pef
May 4, 2018 at 5:05 pm
Jeff Moden - Friday, May 4, 2018 4:07 PMWrite a test to prove it one way or another. It's important for two reasons....
1. The Internet is full of sometimes horribly incorrect information.
2. You need to prove it in the environment you're working on.
As I mentioned in the op, I have done some testing and can find nothing to indicate there is a problem. But I am painfully aware I don't know all the gotcha's in SQL, and this was related to me as something I should know.
pef
May 4, 2018 at 6:50 pm
pef - Friday, May 4, 2018 5:05 PMJeff Moden - Friday, May 4, 2018 4:07 PMWrite a test to prove it one way or another. It's important for two reasons....
1. The Internet is full of sometimes horribly incorrect information.
2. You need to prove it in the environment you're working on.As I mentioned in the op, I have done some testing and can find nothing to indicate there is a problem. But I am painfully aware I don't know all the gotcha's in SQL, and this was related to me as something I should know.
If you were to post your test code, perhaps we could help you prove things one way or the other. I don't know offhand which will win because I simply don't use such things.
Also, execution plans are a great thing to look at for troubleshooting but they mean nothing to me for which is actually the best code. The best code will, of course, be required to first produce the correct answer(s) but then duration followed closely by resource usage are normally the KPIs that make or break the day. I can show you where the execution plan has been 100% backwards where the best looking execution plan is actually the worst performer and vice versa. Execution plans should never be used to actually identify which code is the best. Even actual execution plans are riddled with estimates.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2018 at 6:02 am
Why do you need a table parameter?
Can you describe your scenario which validates it's use?
_____________
Code for TallyGenerator
May 7, 2018 at 10:49 am
If there's going to be a reason to not pass data via a TVP, it's likely because they don't have statistics like an actual table would. Any such table with more than a few records in it could really mess up the estimates the optimizer uses, and that could potentially lead to performance problems, especially when the data quantity in such a table is small now, but eventually grows, and then long after the developer is gone, the problem "magically appears out of nowhere", when the truth is that the problem was there all along, and just being masked by a lack of volume.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 7, 2018 at 11:28 am
pef - Friday, May 4, 2018 4:53 PMLynn Pettis - Friday, May 4, 2018 4:05 PMSo what was his answer? Can't answer your question unless we understand what he is saying.
"It generates horrible query plans because it makes copies of the temp tables passed between the procedures"
Even if it would make sense that it makes copies of the table variables, it's not the case. It's not that hard to prove it. If what your boss says had been true, this code would return 2 rows but only returns one. You could add a select in the caller procedure to demonstrate that the table is the same.
CREATE TYPE dbo.TestTVP AS TABLE( id INT, weirdcolumn VARCHAR(10))
GO
CREATE PROC dbo.TestingTVP_Redundance_Receiver( @TVPP dbo.TestTVP READONLY)
AS
SELECT *
FROM tempdb.sys.tables AS t
JOIN tempdb.sys.columns AS c ON t.object_id = c.object_id
WHERE c.name = 'weirdcolumn'
ORDER BY t.name, c.name;
GO
CREATE PROC dbo.TestingTVP_Redundance_Caller
AS
DECLARE @TVP dbo.TestTVP;
INSERT INTO @TVP
(
id,
weirdcolumn
)
VALUES
( 1, -- id - int
'ASFD' -- weirdcolumn - varchar(10)
)
EXEC TestingTVP_Redundance_Receiver @TVPP = @TVP;
GO
EXEC dbo.TestingTVP_Redundance_Caller
GO
DROP PROC dbo.TestingTVP_Redundance_Caller;
DROP PROC dbo.TestingTVP_Redundance_Receiver;
DROP TYPE dbo.TestTVP;
That said, what Steve said is true. It generates bad query plans because it always estimates one row. You can replace the table variables with temp tables that don't have to be passed as parameters because they already exist in the scope when calling a stored procedure from another stored procedure.
Now, you can let your boss know that he is wrong in the reason to avoid table variables, but correct in the fact that they could cause performance problems. You can also prevent this problems by minor changes on the code. Table-Valued Parameters are a great tool, but mostly when used to send values from the application, not within the database.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply