December 1, 2015 at 6:10 pm
Hello,
Just wondering if anyone has any performance measures with regard to declaring table variables.
Method 1 - Declare table variable with required columns, ie:
DECLARE @ap_tbl TABLE ( col1 UNIQUEIDENTIFIER, col2 INTEGER, col3 DATETIME );
Method 2 - Create a user defined table type and then use that type to declare the table variable, ie:
/* Create user defined table type; done once ... */
CREATE TYPE [dbo].[udt_ap_tbl] AS TABLE(
[col1] [uniqueidentifier],
[col2] [int],
[col3] [datetime] );
And then, when you need to declare the table variable ...
/* Create table variable using type ... */
DECLARE @ap_tbl AS dbo.udt_ap_tbl;
I am assuming method 2 would be quicker as the table structure is already defined, but I have not found any articles that prove this.
Thanks in advance.
December 1, 2015 at 8:20 pm
Likely to be bound by SGAM/GAM page contention in tempdb, but could be CPU bound. Should be easy to benchmark on your CPUs, with your tempdb files (mine differ). Duration can/should be measured in microseconds, but with millisecond accuracy. Best to iterate at least a thousand. Also test up to a hundred concurrent sessions.
But the above benchmark worries me less than a proposed use of a table variable, when the topic is performance. I am hoping you are well-versed in performance, execution plans, cardinality estimates, and table variables ;-).
December 2, 2015 at 2:04 am
You'll probably find the time difference to be utterly meaningless and under the error range for measuring duration of statements. Worrying about a couple microseconds here or there is likely to be a waste of time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply