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
and in the
I am wondering why the
Why the
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:


read-ahead reads
count:


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:

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
Last activity: Nov 15, 2016, 06:00 AM