December 29, 2018 at 6:23 am
Thanks Eirikur;
I saw that thanks for your help.
In my thought some type of implicit conversions does not significantly affect performance.
This insert statement is similar to this type.
December 29, 2018 at 7:27 am
ESAT ERKEC - Saturday, December 29, 2018 6:23 AMThanks Eirikur;
I saw that thanks for your help.
In my thought some type of implicit conversions does not significantly affect performance.
This insert statement is similar to this type.
It depends on the accumulated cost of the type conversions, when the datatype difference is only the length of the data type definition, this difference is minimal. In other cases, the difference can be quite significant.
😎
Here is a result set from my i5 laptop
T_TXT DURATION
-------------- -----------
NO CONVERSION 785611
IMPLICIT 832875
DRY RUN 1766440
December 29, 2018 at 7:46 am
Here is an example of a more costly implicit conversion
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- /*
IF OBJECT_ID(N'dbo.TBL_TEST_IMPLICIT') IS NOT NULL DROP TABLE dbo.TBL_TEST_IMPLICIT;
DECLARE @SAMPLE_SIZE INT = 1000000;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6)
SELECT
CONVERT(VARCHAR(8000),NEWID(),0) AS VAL_1
,CONVERT(VARCHAR(8000),NEWID(),0) AS VAL_2
,CONVERT(VARCHAR(50),NEWID(),0) AS VAL_3
,CONVERT(VARCHAR(50),NEWID(),0) AS VAL_4
,CONVERT(NVARCHAR(4000),NEWID(),0) AS VAL_5
,CONVERT(NVARCHAR(4000),NEWID(),0) AS VAL_6
,LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),NEWID(),0),CHAR(45),''),CHAR(65),''),CHAR(66),''),CHAR(67),''),CHAR(68),''),CHAR(69),''),CHAR(70),''),9) AS BINT_STR
,CONVERT(INT,ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE,0) AS INT_1
,CONVERT(BIGINT,ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE,0) AS BINT_2
INTO dbo.TBL_TEST_IMPLICIT
FROM NUMS NM;
-- */
IF OBJECT_ID(N'dbo.TBL_TEST_IMPLICIT_INSERT') IS NOT NULL DROP TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
CREATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT
(
VAL_1 VARCHAR(50) NOT NULL
,VAL_2 VARCHAR(50) NOT NULL
,BINT_VAL BIGINT NOT NULL
);
DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @CHAR_BUCKET VARCHAR(50) = '';
DECLARE @NCHAR_BUCKET VARCHAR(50) = N'';
DECLARE @BIGINT_BUCKET BIGINT = 0;
TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@CHAR_BUCKET = TI.VAL_1
,@CHAR_BUCKET = TI.VAL_2
,@BIGINT_BUCKET = TI.INT_1
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
INSERT INTO @timer(T_TXT) VALUES('IMPLICIT');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_1
,TI.VAL_2
,TI.INT_1
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('IMPLICIT');
TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@CHAR_BUCKET = TI.VAL_3
,@CHAR_BUCKET = TI.VAL_4
,@BIGINT_BUCKET = TI.BINT_2
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
INSERT INTO @timer(T_TXT) VALUES('NO CONVERSION');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_3
,TI.VAL_4
,TI.BINT_2
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('NO CONVERSION');
TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@NCHAR_BUCKET = TI.VAL_5
,@NCHAR_BUCKET = TI.VAL_6
,@CHAR_BUCKET = TI.BINT_STR
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
INSERT INTO @timer(T_TXT) VALUES('STR TO BIGINT CONVERSION');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_5
,TI.VAL_6
,TI.BINT_STR
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('STR TO BIGINT CONVERSION');
SELECT
T.T_TXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION ASC;
And the results on my i5 laptop
T_TXT DURATION
------------------------- ---------
NO CONVERSION 737767
IMPLICIT 769955
STR TO BIGINT CONVERSION 1255381
DRY RUN 3068261
December 29, 2018 at 9:52 am
Erikur thanks for your interest about this issue.
You are exactly right because STR TO BIGINT CONVERSION more expensive than other conversions.
If you allow me I want to use your script in my post with little changes.
December 29, 2018 at 9:59 am
ESAT ERKEC - Saturday, December 29, 2018 9:52 AMErikur thanks for your interest about this issue.
You are exactly right because STR TO BIGINT CONVERSION more expensive than other conversions.
If you allow me I want to use your script in my post with little changes.
Of course you can use it Esat and if you want me to review it, just ping it over.
😎
December 29, 2018 at 10:02 am
Eirikur Eiriksson - Saturday, December 29, 2018 7:46 AMHere is an example of a more costly implicit conversion
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- /*
IF OBJECT_ID(N'dbo.TBL_TEST_IMPLICIT') IS NOT NULL DROP TABLE dbo.TBL_TEST_IMPLICIT;
DECLARE @SAMPLE_SIZE INT = 1000000;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6)
SELECT
CONVERT(VARCHAR(8000),NEWID(),0) AS VAL_1
,CONVERT(VARCHAR(8000),NEWID(),0) AS VAL_2
,CONVERT(VARCHAR(50),NEWID(),0) AS VAL_3
,CONVERT(VARCHAR(50),NEWID(),0) AS VAL_4
,CONVERT(NVARCHAR(4000),NEWID(),0) AS VAL_5
,CONVERT(NVARCHAR(4000),NEWID(),0) AS VAL_6
,LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),NEWID(),0),CHAR(45),''),CHAR(65),''),CHAR(66),''),CHAR(67),''),CHAR(68),''),CHAR(69),''),CHAR(70),''),9) AS BINT_STR
,CONVERT(INT,ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE,0) AS INT_1
,CONVERT(BIGINT,ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE,0) AS BINT_2
INTO dbo.TBL_TEST_IMPLICIT
FROM NUMS NM;
-- */IF OBJECT_ID(N'dbo.TBL_TEST_IMPLICIT_INSERT') IS NOT NULL DROP TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
CREATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT
(
VAL_1 VARCHAR(50) NOT NULL
,VAL_2 VARCHAR(50) NOT NULL
,BINT_VAL BIGINT NOT NULL
);DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @CHAR_BUCKET VARCHAR(50) = '';
DECLARE @NCHAR_BUCKET VARCHAR(50) = N'';
DECLARE @BIGINT_BUCKET BIGINT = 0;TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@CHAR_BUCKET = TI.VAL_1
,@CHAR_BUCKET = TI.VAL_2
,@BIGINT_BUCKET = TI.INT_1
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');INSERT INTO @timer(T_TXT) VALUES('IMPLICIT');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_1
,TI.VAL_2
,TI.INT_1
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('IMPLICIT');TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@CHAR_BUCKET = TI.VAL_3
,@CHAR_BUCKET = TI.VAL_4
,@BIGINT_BUCKET = TI.BINT_2
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');INSERT INTO @timer(T_TXT) VALUES('NO CONVERSION');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_3
,TI.VAL_4
,TI.BINT_2
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('NO CONVERSION');TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@NCHAR_BUCKET = TI.VAL_5
,@NCHAR_BUCKET = TI.VAL_6
,@CHAR_BUCKET = TI.BINT_STR
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');INSERT INTO @timer(T_TXT) VALUES('STR TO BIGINT CONVERSION');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_5
,TI.VAL_6
,TI.BINT_STR
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('STR TO BIGINT CONVERSION');SELECT
T.T_TXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION ASC;And the results on my i5 laptop
T_TXT DURATION
------------------------- ---------
NO CONVERSION 737767
IMPLICIT 769955
STR TO BIGINT CONVERSION 1255381
DRY RUN 3068261
Just curious - but would it be 'safe' to generalize that like to like implicit conversions on an insert/update probably have minimal impact when the conversion is from smaller to larger? And therefore - when the conversion is from larger to smaller (if it doesn't outright fail - of course) or from different data types (when possible) would have a higher impact?
Of course - you also have to consider the impact of page splits...but that is separate I would think.
With that said - I would not be so worried about implicit conversions in this situation. I would be more concerned about implicit conversions when that is done in a filter - as that can bypass utilizing any indexing available and force table/index scans.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 29, 2018 at 10:39 am
Jeffrey Williams 3188 - Saturday, December 29, 2018 10:02 AMEirikur Eiriksson - Saturday, December 29, 2018 7:46 AMHere is an example of a more costly implicit conversion
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- /*
IF OBJECT_ID(N'dbo.TBL_TEST_IMPLICIT') IS NOT NULL DROP TABLE dbo.TBL_TEST_IMPLICIT;
DECLARE @SAMPLE_SIZE INT = 1000000;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6)
SELECT
CONVERT(VARCHAR(8000),NEWID(),0) AS VAL_1
,CONVERT(VARCHAR(8000),NEWID(),0) AS VAL_2
,CONVERT(VARCHAR(50),NEWID(),0) AS VAL_3
,CONVERT(VARCHAR(50),NEWID(),0) AS VAL_4
,CONVERT(NVARCHAR(4000),NEWID(),0) AS VAL_5
,CONVERT(NVARCHAR(4000),NEWID(),0) AS VAL_6
,LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),NEWID(),0),CHAR(45),''),CHAR(65),''),CHAR(66),''),CHAR(67),''),CHAR(68),''),CHAR(69),''),CHAR(70),''),9) AS BINT_STR
,CONVERT(INT,ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE,0) AS INT_1
,CONVERT(BIGINT,ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE,0) AS BINT_2
INTO dbo.TBL_TEST_IMPLICIT
FROM NUMS NM;
-- */IF OBJECT_ID(N'dbo.TBL_TEST_IMPLICIT_INSERT') IS NOT NULL DROP TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
CREATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT
(
VAL_1 VARCHAR(50) NOT NULL
,VAL_2 VARCHAR(50) NOT NULL
,BINT_VAL BIGINT NOT NULL
);DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @CHAR_BUCKET VARCHAR(50) = '';
DECLARE @NCHAR_BUCKET VARCHAR(50) = N'';
DECLARE @BIGINT_BUCKET BIGINT = 0;TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@CHAR_BUCKET = TI.VAL_1
,@CHAR_BUCKET = TI.VAL_2
,@BIGINT_BUCKET = TI.INT_1
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');INSERT INTO @timer(T_TXT) VALUES('IMPLICIT');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_1
,TI.VAL_2
,TI.INT_1
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('IMPLICIT');TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@CHAR_BUCKET = TI.VAL_3
,@CHAR_BUCKET = TI.VAL_4
,@BIGINT_BUCKET = TI.BINT_2
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');INSERT INTO @timer(T_TXT) VALUES('NO CONVERSION');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_3
,TI.VAL_4
,TI.BINT_2
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('NO CONVERSION');TRUNCATE TABLE dbo.TBL_TEST_IMPLICIT_INSERT;
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');
SELECT
@NCHAR_BUCKET = TI.VAL_5
,@NCHAR_BUCKET = TI.VAL_6
,@CHAR_BUCKET = TI.BINT_STR
FROM dbo.TBL_TEST_IMPLICIT TI
INSERT INTO @timer(T_TXT) VALUES('DRY RUN');INSERT INTO @timer(T_TXT) VALUES('STR TO BIGINT CONVERSION');
INSERT INTO dbo.TBL_TEST_IMPLICIT_INSERT WITH (TABLOCKX) (VAL_1,VAL_2,BINT_VAL)
SELECT
TI.VAL_5
,TI.VAL_6
,TI.BINT_STR
FROM dbo.TBL_TEST_IMPLICIT TI
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES('STR TO BIGINT CONVERSION');SELECT
T.T_TXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION ASC;And the results on my i5 laptop
T_TXT DURATION
------------------------- ---------
NO CONVERSION 737767
IMPLICIT 769955
STR TO BIGINT CONVERSION 1255381
DRY RUN 3068261Just curious - but would it be 'safe' to generalize that like to like implicit conversions on an insert/update probably have minimal impact when the conversion is from smaller to larger? And therefore - when the conversion is from larger to smaller (if it doesn't outright fail - of course) or from different data types (when possible) would have a higher impact?
Of course - you also have to consider the impact of page splits...but that is separate I would think.
With that said - I would not be so worried about implicit conversions in this situation. I would be more concerned about implicit conversions when that is done in a filter - as that can bypass utilizing any indexing available and force table/index scans.
The short answer is no, generalization is another word for an assumption😉
😎
As I said before, sequential inserts only are very different from updates and non-sequential inserts, one byte average update may easily make the most optimal insert pattern the most fragmented update situation. Have you had the opportunity to look at Jeff Moden's presentation from SQL Saturday in Pittsburgh?
December 29, 2018 at 12:36 pm
Eirikur Eiriksson - Saturday, December 29, 2018 10:39 AMThe short answer is no, generalization is another word for an assumption😉
😎
As I said before, sequential inserts only are very different from updates and non-sequential inserts, one byte average update may easily make the most optimal insert pattern the most fragmented update situation. Have you had the opportunity to look at Jeff Moden's presentation from SQL Saturday in Pittsburgh?
I guess I wasn't clear enough on my thoughts...
The implicit conversion on a column being inserted would have a minimal impact on the performance when that implicit conversion is for the same data type. From a varchar(10) to a varchar(30) is nothing more than an increase in the length and requires minimal processing and validation. On the other hand - and implicit conversion from string to bigint or date to string or string to date will have a much higher impact as there is additional type checking that needs to be performed.
The implicit conversion from a larger size to smaller size of the same data type - varchar(30) to varchar(10) - would also have minimal impact because it is a simple truncation of the data and does not require anything more than a truncation with no other validation required.
This holds true for UPDATES also - the implicit conversion of the data will not have a significant impact on performance. The only way you can mitigate the impact of the conversion is when dealing with static values where you can cast/convert the static value to the same data type. If the insert or update is coming from another table where the source column is a STR and the destination column is a BIGINT - then the values will either be implicitly converted or the code will explicitly convert the values - resulting in the same impact to the process.
All of this is to show that worrying about converting the data to an appropriate data type is less of a concern than focusing on conversions in the filter clause whether those are implicit or explicit. Any conversions that need to be performed in the filter will most likely cause a table scan and have a much higher impact on the performance.
None of this has anything to do with the affect on indexes...an implicit conversion isn't by itself going to cause any difference in how the insert is affected by page splits. If the implicit conversion is from varchar(10) to varchar(30) - the same amount of storage will be utilized on the page, and if the page fullness requires a page split the conversion to varchar(30) has no impact on that requirement. Even if there are no implicit/explicit conversions of data - the insert will cause a page split if the index key(s) requires inserting onto a page that does not have enough room for the row.
As for updates - an implicit/explicit conversion of data does not impact what happens either. The determining factor is whether or not that update can be done in place or not which is dependent on whether or not the varchar value changes in size enough to force a page split. The conversion of the data doesn't change that...and an update without the conversion will have the exact same impact on the indexes as an update with a conversion.
Implicit (or explicit) conversions of like data types have minimal impact on performance and implicit (or explicit) conversions from non-like data types can have a significant impact on performance. This is why your test shows a significant impact when converting from STR to BIGINT - and would also have a significant impact converting from STR to DATE or DATE to STR. Conversion from INT to BIGINT will have minimal impact and even BIGINT to INT would probably have a minimal impact as it is a simple range check...
In summary - any conversion whether implicit or explicit could have an impact on performance if the conversion requires additional checking and validation. If the conversion does not require additional checking and validation the impact will be minimal/negligible.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2018 at 6:54 am
Jeffrey Williams 3188 - Saturday, December 29, 2018 12:36 PMEirikur Eiriksson - Saturday, December 29, 2018 10:39 AMThe short answer is no, generalization is another word for an assumption😉
😎
As I said before, sequential inserts only are very different from updates and non-sequential inserts, one byte average update may easily make the most optimal insert pattern the most fragmented update situation. Have you had the opportunity to look at Jeff Moden's presentation from SQL Saturday in Pittsburgh?I guess I wasn't clear enough on my thoughts...
The implicit conversion on a column being inserted would have a minimal impact on the performance when that implicit conversion is for the same data type. From a varchar(10) to a varchar(30) is nothing more than an increase in the length and requires minimal processing and validation. On the other hand - and implicit conversion from string to bigint or date to string or string to date will have a much higher impact as there is additional type checking that needs to be performed.
The implicit conversion from a larger size to smaller size of the same data type - varchar(30) to varchar(10) - would also have minimal impact because it is a simple truncation of the data and does not require anything more than a truncation with no other validation required.
This holds true for UPDATES also - the implicit conversion of the data will not have a significant impact on performance. The only way you can mitigate the impact of the conversion is when dealing with static values where you can cast/convert the static value to the same data type. If the insert or update is coming from another table where the source column is a STR and the destination column is a BIGINT - then the values will either be implicitly converted or the code will explicitly convert the values - resulting in the same impact to the process.
All of this is to show that worrying about converting the data to an appropriate data type is less of a concern than focusing on conversions in the filter clause whether those are implicit or explicit. Any conversions that need to be performed in the filter will most likely cause a table scan and have a much higher impact on the performance.
None of this has anything to do with the affect on indexes...an implicit conversion isn't by itself going to cause any difference in how the insert is affected by page splits. If the implicit conversion is from varchar(10) to varchar(30) - the same amount of storage will be utilized on the page, and if the page fullness requires a page split the conversion to varchar(30) has no impact on that requirement. Even if there are no implicit/explicit conversions of data - the insert will cause a page split if the index key(s) requires inserting onto a page that does not have enough room for the row.
As for updates - an implicit/explicit conversion of data does not impact what happens either. The determining factor is whether or not that update can be done in place or not which is dependent on whether or not the varchar value changes in size enough to force a page split. The conversion of the data doesn't change that...and an update without the conversion will have the exact same impact on the indexes as an update with a conversion.
Implicit (or explicit) conversions of like data types have minimal impact on performance and implicit (or explicit) conversions from non-like data types can have a significant impact on performance. This is why your test shows a significant impact when converting from STR to BIGINT - and would also have a significant impact converting from STR to DATE or DATE to STR. Conversion from INT to BIGINT will have minimal impact and even BIGINT to INT would probably have a minimal impact as it is a simple range check...
In summary - any conversion whether implicit or explicit could have an impact on performance if the conversion requires additional checking and validation. If the conversion does not require additional checking and validation the impact will be minimal/negligible.
I now get what you are saying and the implicit data type conversion between different length definitions of the same data type impose minimal impact as the storage definition is exactly the same, stored int the VarOffset in the FixedVar, hence the overhead is simply the check whether the value exceeds the length limits.
😎
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply