Query on char with selector with N prefix is very slow

Question

I have a table with a CHAR(36) "id" column, which is a clustered primary key. It has multiple other columns, including an "imdbId" column, which is an INT.

When I update using

UPDATE Movies SET imdbId=12 WHERE id = N'012e1892-872e-4bb0-804b-f70ff59689ae';

the query takes forever. And I don't really understand why. Can't SQL Server convert the Unicode string (as it does not contain special characters) and then use the index?

If I remove the N prefix, the query returns instantly.

The problem is, of course, that an existing application does all of its queries with the N prefix. Is converting my "id" column to NCHAR my best option?

Answer 1

It's bad enough that they are using a GUID as a Clustered PK, but they had to make it worse by making it a string and then doing a case-insensitive, locale-aware comparison? Amazing!

Well, if you see that huge of a difference between passing in a string without the N and with the N, then that indicates that the Collation of the silly CHAR(36) column is a SQL Server Collation (one starting with SQL_), most likely SQL_Latin1_General_CP1_CI_AS (the default for US installs).

But you are in luck. There are three options that should help greatly, given that you cannot change the queries themselves:

1. Ideally: Change the datatype of the id column to be UNIQUEIDENTIFIER: this will be faster not only because it will convert the N'xxx-xx-xx... value into a GUID first and then do a binary comparison (due to Data Type Precedence), but each row will be something like 22 bytes smaller (36 bytes for VARCHAR(36) down to 16 bytes for UNIQUEIDENTIFIER, minus another 2 or so for getting rid of a variable-length column).

2. If you can't do #1: Change the Collation of the id column to a Windows Collation, and preferably a binary one (ending in _BIN2), such as Latin1_General_100_BIN2. This won't save any space, but won't be nearly as expensive to convert the incoming NVARCHAR literal to CHAR(36) (or vice-versa), AND will be a binary comparison (much faster than case-insensitive, locale-aware) since it is a GUID and not subject to linguistic rules.

3. If you can't even do #2: Change the Collation of the id column to a Windows Collation that isn't a binary one, most likely Latin1_General_100_CI_AS. This doesn't save space or even do a binary comparison, but does allow for sloppy coding / ad-hoc queries with mixed-case values AND is still much faster than the conversion used by the SQL Server Collation.

You can see this behavior yourself via the following test:

SET NOCOUNT ON;

CREATE TABLE dbo.GuidPkAsUI (
  ID UNIQUEIDENTIFIER NOT NULL CONSTRAINT [PK_GuidPkAsUI] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsUI_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCci (
  ID CHAR(36) COLLATE Latin1_General_100_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARci] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARci_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCbin (
  ID CHAR(36) COLLATE Latin1_General_100_BIN2 NOT NULL CONSTRAINT [PK_GuidPkAsCHARbin] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARbin_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCsql (
  ID CHAR(36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARsql] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARsql_InsertTime] DEFAULT (GETDATE())
);

INSERT INTO dbo.GuidPkAsUI ([ID])
    SELECT NEWID()
    FROM   master.sys.objects
    CROSS APPLY master.sys.all_columns;

INSERT INTO dbo.GuidPkAsVCci ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCbin ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCsql ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;


SELECT * FROM dbo.GuidPkAsUI;
-- Pick a value from half-way down and paste into
-- the 4 queries below


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsUI
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCci
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCbin
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCsql
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 7157 (yikes!)

Answer 2

Maybe you should try to cast as varchar the parameter to avoid the implicit conversion of the column:

UPDATE Movies SET imdbId=12 WHERE id = cast(N'012e1892-872e-4bb0-804b-f70ff59689ae' as varchar);

Answer 3

I have not tested this solution, but it appears you may be able to solve your problem by adding a computed column to the table and adding a non-clustered index to the computed column. See this link for more information.

http://www.johnsansom.com/implicit-conversions-computed-columns/

Answer 4

Can't SQL server convert the unicode string (as it does not contain special characters) and then use the index?

To answer this part of the question specifically, no SQL server will not implicitly convert NCHAR to CHAR. SQL server only looks at the data types of the two fields and then chooses the conversion that will not result in any data loss or errors. In this case CHAR to NCHAR, even if that results in the expensive conversion of every row in the table from CHAR to NCHAR to then compare with the NCHAR parameter passed.

It also does not look at the actual characters of the parameter to see if the conversion of NCHAR to CHAR would be successful in this particular case.