SQL-Ex blog

Однако эти хорошие намерения могут вызвать проблемы при определенных обстоятельствах, когда вы знаете больше о своих данных, чем SQL Server. Когда это происходит, порядок, который выбирает SQL Server для выполнения предикатов, является важным не только с точки зрения производительности запроса, но также и с точки зрения бизнес-логики.

Столбец с данными смешанных типов

Давайте следующий пример таблицы с данными:

USE master;

DROP DATABASE IF EXISTS MixedDataTypes;

CREATE DATABASE MixedDatatypes;

USE MixedDatatypes;

GO

CREATE TABLE dbo.Pages

(

	Id int identity,

	PageName varchar(20),

	DataValue varchar(100),

	DataType varchar(20),

	CONSTRAINT PK_Id PRIMARY KEY (Id)

);

GO

INSERT INTO dbo.Pages VALUES ('StringsOnlyPage 1','abc','string')

GO 2000

INSERT INTO dbo.Pages VALUES ('NumbersOnlyPage 1','1.20','decimal')

GO 2000

INSERT INTO dbo.Pages VALUES ('NumbersOnlyPage 2','1.20','decimal')

GO 2000

INSERT INTO dbo.Pages VALUES ('MixedDataTypesPage 1','abc','string')

GO 1000

INSERT INTO dbo.Pages VALUES ('MixedDataTypesPage 1','1.20','decimal')

GO 1000

Эта таблица содержит данные приложения, которое имеет много страниц различных типов. Каждая страницы содержит различные типы данных, но, вместо создания отдельной таблицы для каждого типа, мы сохраняем все разнообразные данные в столбце DataValue типа varchar, указывая оригинальный тип данных в столбце DataType.

Такая структура уменьшает сложность поддержки нашей базы данных (по сравнению с потенциальным созданием сотен таблиц, по одной на каждую PageName) и делает запросы проще (требуется обращаться только к одной таблице). Однако такой проект может также привести к некоторым неожиданным результатам запросов.

Фильтрация значений смешанных данных

Пусть нам нужно получить все данные из одной таблицы при условии, что значение равно 1.2:

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName = 'NumbersOnlyPage 1' AND DataValue = '1.2'

Запрос работает прекрасно. Проблема в том, что, поскольку исходный тип наших данных был decimal со значением 1.20, сравнение на базе строк не работает. В действительности нам нужно числовое сравнение в нашем предикате:

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName = 'NumbersOnlyPage 1' AND DataValue = 1.2

Хотя неявное преобразование типа на столбце таблицы DataValue не идеально, если число строк, для которых требуется выполнить преобразование, невелико, это не так плохо.

Теперь повеселимся: что если нам нужно проверить все наши страницы, которые содержат числовые данные, на значение 1.2? Мы можем написать этот запрос парой различных способов:

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName like 'NumbersOnlyPage%' AND DataValue = 1.2

--или

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName in ('NumbersOnlyPage 1','NumbersOnlyPage 2') AND DataValue = 1.2

Для обоих запросов мы получим ошибку: "Ошибка преобразования типа данных varchar в numeric".



Почему? В этом случае SQL Server решил выполнить неявное преобразование столбца DataValue до фильтрации на наших столбцах PageName.

Вплоть до последнего запроса SQL Server полагал, что будет более эффективно сначала отфильтровать строки по конкретной странице (Page), а затем выполнить неявные преобразования по столбцу DataValue. Однако теперь, когда мы выбираем более одной таблицы, SQL Server говорит, что, поскольку он все равно должен все отсканировать, будет лучше выполнить сначала все неявные преобразования, а затем фильтровать имена таблиц.

Конечно, проблема в том, что все наши значения DataValue не являются числовыми. В этом случае порядок предикатов имеет значение, не для производительности, а для корректного выполнения бизнес-логики, которая определена как часть нашего запроса.

Нет хороших решений

Одним способом пофиксить это - заставить SQL Server сначала отфильтровать по PageName путем добавления индекса:

CREATE NONCLUSTERED INDEX IX_PageName ON dbo.Pages (PageName) INCLUDE (DataValue);

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName like 'NumbersOnlyPage%' AND DataValue = 1.2

Это работает замечательно. SQL Server считает, что поскольку этот индекс покрывает все поля нашего запроса и поскольку ключом индекса является PageName, он будет сначала фильтровать строки по PageName и выполнять неявную конвертацию оставшихся строк.

Проблема в том, что такое поведение не гарантировано. Временами может случиться так, что SQL Server не будет использовать индекс: наш индекс больше не покрывает наш запрос, вы добавили дополнительную фильтрацию, индекс удаляется, чтобы быть замененным другим индексом, который не будет больше выбираться для этого конкретного запроса, и т.д.

Это просто ненадежный вариант.

Плюс ко всему, это не работает во всех сценариях. Скажем, мы параметризовали PageName и используем функцию STRING_SPLIT() для фильтрации наших страниц только по передаваемым:

DECLARE @PageNames varchar(100) = 'NumbersOnlyPage 1,NumbersOnlyPage 2';

SELECT *

FROM dbo.Pages

WHERE PageName in (SELECT value FROM string_split(@PageNames,',')) AND DataValue = 1.2

Мы вернулись на круги своя, поскольку в этом случае STRING_SPLIT() требуется сначала распарсить данные PageName, а затем соединить их с остальными данными, что приводит к исходному сбойному сценарию (вот предварительный план выполнения):

Другие варианты

Хотя индексирование, видимо, решает проблему, оно не гарантировано будет работать на 100% все время.

Ясно, что мы могли бы не сохранять данные в таком формате, но это добавило бы сложности в базе данных и приложении.

Мы могли бы попробовать добавить фильтр по PageName в производную таблицу и навязать порядок соединения, но это заставит нас читать таблицу несколько раз.

Поскольку мы также имеем информацию о типе данных для каждой строки, мы можем использовать эту информацию:

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName like 'NumbersOnlyPage%' AND DataValue = 1.2 AND DataType = 'decimal'

Но опять таки, если это работает, то благодаря чистой удаче.

Еще один вариант TRY_CONVERT(). Эта функция возвращает NULL, если не может преобразовать к десятичному числу:

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName like 'NumbersOnlyPage%' AND TRY_CONVERT(decimal(2,1),DataValue) = 1.2 

Это действительно довольно хороший вариант, поскольку он гарантирует работу вне зависимости от того, какой столбец SQL Server будет фильтровать первым. Если число DataValues, которое вы должны проверить с помощью TRY_CONVERT, относительно невелико, это может быть вашим лучшим выбором.

Для получения лучшей производительности вы можете создать второй столбец, который содержит данные в формате decimal (или любого другого типа):

ALTER TABLE dbo.Pages

ADD DataValueDecimal AS TRY_CONVERT(decimal(2,1),DataValue) PERSISTED

Вы могли бы проиндексировать оба столбца DataValue* и добиться довольно хорошей производительности. Отрицательным моментом здесь является то, что вам придется изменить запросы в вашем приложении, чтобы они отвечали новой структуре таблиц:

SELECT PageName,DataValue

FROM dbo.Pages

WHERE PageName like 'NumbersOnlyPage%'

 AND CASE WHEN DataType = 'decimal' THEN DataValueDecimal ELSE DataValue END = 1.2 

В итоге сложно сказать, какой вариант лучше всего подходит для такого сценария. Однако важно помнить, что если вы решите использовать такую структуру и писать свои запросы в этом стиле, вам необходимо планировать порядок операций и корректно обрабатывать ошибки.