Sample Header Ad - 728x90

Read-ahead reads and SQL-Variant fields

6 votes
1 answer
181 views
I have two tables containing exactly the same data. Both table have bigint primary key identity column, 60 columns and 300 000 rows. The difference is that all of the columns of the second table have sql-variant type. I am creating temporary table and importing the data from the two tables in it. When data is extracted from the sql-variant columns it is cast to the corresponding SQL type. The data extraction from the first table is performed for 1 sec and from the second table for 6 secs. Basically, the differences in the executions are in the estimations: enter image description here enter image description here and in the read-ahead reads count: enter image description here enter image description here I am wondering why the SQL Server cannot load in advance the data readed from the sql-variant fields (there are almost no read-ahead reads). Also, the storage size of the tables is almost the same: enter image description here Why the SQL Server thinks it should read 67 GB? ---------- The column types are: 16 x BIGINT 8 x DECIMAL(9,2) 36 x NVARCHAR(100) The dbcc dropcleanbuffers command is used each time before data extraction and population. -------- In order to test you can download the sample data files from here . Then, 1. Execute Tech05_01_TableDefinitions.sql 2. Execute 3 times the Tech05_02_TablePupulation.sql 3. Open the Tech05_03_TestingInsertionInTempTable.sql file and excute one time like this: DECLARE @TableStructured SYSNAME = '[dbo].[NormalDataTypes_60Cols]'; DECLARE @TableFromWhichDataIsCoppied SYSNAME = '[dbo].[SQLVariant01_60Cols]'; and one time like this: DECLARE @TableStructured SYSNAME = '[dbo].[NormalDataTypes_60Cols]'; DECLARE @TableFromWhichDataIsCoppied SYSNAME = '[dbo].[NormalDataTypes_60Cols]';
Asked by gotqn (4348 rep)
Nov 14, 2016, 12:13 PM
Last activity: Nov 15, 2016, 06:00 AM