How Fixed Length and Variable Length Data types affect performance in Sql server Explain with example
Sql Sever Performance tuning Interview Question: How Fixed Length and Variable Length Data types affect performance in Sql server Explain with example?
Let's look at the following example and create two tables and insert records. The first table, dbo.LargeRows, uses a char(2000) fixed-length data type to store the data. As a result, you can fit only four rows per data page, regardless of the size of Col data. The second table, dbo.SmallRows, uses a varchar(2000) variable-length data type. Let's populate both of the tables with the same data.
Let's look at the following example and create two tables and insert records. The first table, dbo.LargeRows, uses a char(2000) fixed-length data type to store the data. As a result, you can fit only four rows per data page, regardless of the size of Col data. The second table, dbo.SmallRows, uses a varchar(2000) variable-length data type. Let's populate both of the tables with the same data.
create table dbo.LargeRows
(
ID int not null,
CompDesc char(2000) null
);
create table dbo.SmallRows
(
ID int not null,
CompDesc varchar(2000) null
);
--Insert records
;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0) -- 2 rows
,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2) -- 4 rows
,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 rows
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 rows
,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2) -- 65,536 rows
,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
insert into dbo.LargeRows(ID, CompDesc)
select ID, 'Placeholder'
from Ids;
;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0) -- 2 rows
,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2) -- 4 rows
,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 rows
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 rows
,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2) -- 65,536 rows
,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
insert into dbo.SmallRows(ID, CompDesc)
select ID, 'Placeholder'
from Ids;
There are many factors that affect the performance of queries, and the number of I/O operations involved is at the top of the list; that is, the more I/O operations a query needs to perform, the more data pages it needs to read and slower it gets.
The size of a data row affects how many rows will fit in a data page. Large data rows require more pages to store the data and, as a result, increase the number of I/O operations during scans. Moreover, objects will use more memory in the buffer pool.
Now see result(performance ): Result saying that fist query doing 21846 logical reads and second query is only 342. that why first query taking 142 milliseconds and second query taking only 2 milliseconds
Very useful. Thank you
ReplyDelete