This repository was archived by the owner on Dec 23, 2017. It is now read-only.
Repository navigation
This repository was archived by the owner on Dec 23, 2017. It is now read-only.
Provide a way of detecting or specifying correct datatype for strings. #52
Description
Implicit casts from VARCHAR to NVARCHAR in the execution plan occur. Updates to the parameter definition from NVARCHAR to VARCHAR in the query, sees the index hit without the implicit cast, and the query reads drop significantly. Tested this on a few different queries and found SIGNIFICANT performance improvements by changing the parameter type.
In one test logical reads dropped from 10k+ to 22. In another (included below) logical reads dropped from 5.6 million to 3965, CPU dropped from 10k to 547, and duration dropped by a factor of 6
Activity
aidapsibr commentedon Aug 15, 2016
Susanoo doesn't scan the tables or anything like that and I have avoided introducing attributes as they violate separation of concerns. Specifying a default is likely the best thing to do and allow overrides when needed.
aidapsibr commentedon Aug 15, 2016
From a current parameter named String in Susanoo tests:
aidapsibr commentedon Aug 15, 2016
SqlServer ADO.NET defaults all DbType.String instances to NVARCHAR by default.
porterchris commentedon Aug 15, 2016
.NET strings are unicode compatible so the conversion to NVARCHAR is a natural path. In a DB where NVARCHAR provides little or no value, it could be assumed that VARCHAR would be optimal. A global configuration value during initialization would allow the developer to define the format that they are using the DB (for the most part) without being too structured or rigid.