How expensive is sql data type conversion?

user_10365244 0
Viewed 405

I am currently working on a transaction engine, and we are having some issues with our locking mechanism and the wait time. So in order to fix the issue I am trying to simulate the process in a separate application. So I started the SQL profiler and processed a transaction. While looking at the queries, I noticed the following:

in the database my columns are defined as follows:

EVENTNBR (int), 
DEPFILENBR (int),
DEPFILESEQ (int), 
USERID (varchar(20)),
STATUS (varchar(1)),
CREATION_DT (datetime),
MOD_DT (datetime),
SOURCE_TYPE (varchar(100)),
SOURCE_GROUP (varchar(100)),
SOURCE_REFID (varchar(100)),
SOURCE_DATE (datetime),
CHECKSUM (varchar(50)),

however when I look at the query, say an insert statement in the SQL profiler, somehow, somewhere, the code is sending the following;

INSERT INTO TABLE_DATA WITH (ROWLOCK) (EVENTNBR, DEPFILENBR, DEPFILESEQ,       USERID, STATUS, CREATION_DT, MOD_DT, SOURCE_TYPE, SOURCE_GROUP, SOURCE_REFID, SOURCE_DATE, CHECKSUM)

@mod_dt **varchar(19),**
@source_date **varchar(8000),**
@depfilenbr **varchar(7),**
@eventnbr **varchar(1),**
@source_refid **varchar(8000),**
@creation_dt **varchar(19)**,
@source_group varchar(8000),
@source_type **varchar(7),**
@userid **varchar(7)**,
@checksum **varchar(44)**,
@status **varchar(1)**,
@depfileseq **varchar(1)**',

Now the query is successful and works fine. I am wondering how expensive is this type conversion that is happening on the SQL side? If there is a million of these inserts happening, does correcting this makes a difference?

Thanks

sql-server sql
2 Answers

Data type conversion can be super expensive under some circumstances. This is particularly true in on clauses and where clauses where the conversion impedes the use of an index.

In your case, you are simply converting between scalar types, mostly different lengths of strings and an occasional date. There is some overhead in the conversion. I am guessing, though, that the overhead for the type conversion is rather smaller than what already needs to be done for the insert -- in terms of allocating data pages, structuring the data to fit on the page, maintaining indexes, and any additional checks or triggers.

user_11440350

Just to add to that, Microsoft includes a list of data types and their precedence in "implicit conversion" (e.g. you use an int variable in a where clause against a bigint column, and SQL "implicity converts" the int to a bigint. It's primarily in cases where your joining or filtering on these fields that you can run into performance issues (for example, you might have something like where [IntAsString] = @Int or vice versa. If SQL changes the type of the variable, you're good to go. If it has to change the type of the column (again, see the data type precedence link), it will have to do a LOT of work.

Don't quote me on this, but I would say as far as inserts go, it's probably not something to worry about. But when in doubt, look at the execution plan and look for costly operators and implicit casts.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

user_13067840
Related