SQL-Ex blog

Сначала в публикации 2015 года я сравнивал эти два подхода T-SQL к форматированию даты:

SELECT FORMAT(sysdatetime(), 'yyyy-MM-dd'),

       CONVERT(char(10), sysdatetime(), 120);

Считается, что функция CONVERT является примитивной, и многие из нас генерируют более специализированный вывод, чем простые типы строк ISO. Давайте рассмотрим еще более простое выражение SQL FORMAT, которое создает более читабельный для человека вывод (хотя, замечу, что это на самом деле работа презентационного слоя):

DECLARE @d date = '20220114';

SELECT FORMAT(@d, 'D', 'en-us');

Этот синтаксис даст следующий результат:

Friday, January 14, 2022

Чтобы получить тот же вывод без функции FORMAT, нам потребуется следующий оператор SELECT:

SET LANGUAGE us_english;

DECLARE @d date = '20220114';

SELECT CONCAT_WS(', ', 

                 DATENAME(WEEKDAY, @d), 

                 DATENAME(MONTH, @d) + ' ' 

                   + CONVERT(varchar(2), DATEPART(DAY, @d)), 

                 DATEPART(YEAR, @d)

       );

Ясно, что последнее выражение более трудоемко и выглядит не так красиво. И мы должны начать с оператора SET LANGUAGE, чтобы соответствовать тому же спецификатору локали, который встраивается в FORMAT, поскольку собственные языковые установки пользователя могут отличаться от тех, которые мы используем при тестировании.

Многие коллеги, которым требуются подобные более сложные выражения, положительно оценивают упрощения, которые предоставляет FORMAT, а также согласованность с другими языками типа C#. Они зачастую предполагают, что эти дополнительные вычисления понизят производительность до уровеня FORMAT. Ну, мы можем проверить это! Давайте создадим базу данных с включением хранилища запросов (Query Store):

USE master;

GO

DROP DATABASE IF EXISTS FormatTest;

GO

CREATE DATABASE FormatTest;

GO

ALTER DATABASE FormatTest SET QUERY_STORE = ON (QUERY_CAPTURE_MODE = ALL);

GO

USE FormatTest;

GO

Затем мы можем создать таблицу с нескольким тысячам строк:

SELECT o.*, column_name = c.name 

  INTO dbo.Columns

  FROM sys.all_objects AS o 

  INNER JOIN sys.all_columns AS c 

  ON o.[object_id] = c.[object_id];

CREATE CLUSTERED INDEX cix ON dbo.Columns(name, column_name);

Выполним простые запросы, которые сканируют всю таблицу и преобразуют значение modify_date в каждой строке:

SET NOCOUNT ON;

GO

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

GO

DECLARE @d varchar(50);

SELECT  /* convert1 */ @d = CONCAT_WS(', ', 

    DATENAME(WEEKDAY, modify_date), 

    DATENAME(MONTH, modify_date) + ' ' 

      + CONVERT(varchar(2), DATEPART(DAY, modify_date)), 

    CONVERT(char(4), DATEPART(YEAR, modify_date)))

  FROM dbo.Columns;

GO 50

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

GO

DECLARE @d varchar(50);

SELECT  /* format */ @d = FORMAT(modify_date, 'D', 'en-us') 

  FROM dbo.Columns;

GO 50

Если посмотреть метрики в Query Store, то можно увидеть, что мои прежние наблюдения относительно FORMAT и его накладных расходов CLR по-прежнему верны даже в сравнении с этим более сложным выражением:



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

CREATE FUNCTION dbo.PrettyDate_Scalar

(

    @date date

)

RETURNS varchar(50)

WITH SCHEMABINDING

AS

BEGIN

  RETURN (SELECT /* скалярная */ PrettyDate = CONCAT_WS(', ', 

    DATENAME(WEEKDAY, @date), 

    DATENAME(MONTH, @date) + ' ' 

      + CONVERT(varchar(2), DATEPART(DAY, @date)), 

    CONVERT(char(4), DATEPART(YEAR, @date)))

  );

END

GO

CREATE FUNCTION dbo.PrettyDate_InlineTVF

(

    @date date

)

RETURNS TABLE

WITH SCHEMABINDING

AS

  RETURN (SELECT /* встроенная табличнозначная */ PrettyDate = CONCAT_WS(', ', 

    DATENAME(WEEKDAY, @date), 

    DATENAME(MONTH, @date) + ' ' 

      + CONVERT(varchar(2), DATEPART(DAY, @date)), 

    CONVERT(char(4), DATEPART(YEAR, @date)))

  );

GO

Затем мы можем добавить эти запросы в наш тест:

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

GO

DECLARE /* скалярная */ @d varchar(50);

SELECT  /* скалярная */ @d = dbo.PrettyDate_Scalar(modify_date) 

  FROM dbo.Columns;

GO 50

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

GO

DECLARE /* встроенная табличнозначная */ @d varchar(50);

SELECT  /* встроенная табличнозначная */ @d = f.PrettyDate

  FROM dbo.Columns AS c

  CROSS APPLY dbo.PrettyDate_InlineTVF(c.modify_date) AS f;

GO 50

Результаты:



Это всего лишь микросекунды, и такое отличие может не быть таким очевидным при локальном тестировании, но это станет более заметным при масштабировании.

Заметим, что при использовании встроенного форматирования или функций мы можем легко добавить дополнительное форматирование, которого не может предложить FORMAT; например, мы можем добавить суффиксы для дня, типа 1st, 2nd 3rd, 4th и т.д.

+ CONVERT(varchar(2), DATEPART(DAY, @d))

    + CASE WHEN DATEPART(DAY, @d) BETWEEN 10 AND 19 THEN 'th'

           WHEN DATEPART(DAY, @d) % 10 = 1 THEN 'st'

           WHEN DATEPART(DAY, @d) % 10 = 2 THEN 'nd'

           WHEN DATEPART(DAY, @d) % 10 = 3 THEN 'rd'

           ELSE 'th' END,

Включение этой дополнительной логики форматирования во встроенный запрос преобразования и новую табличнозначную функцию дает следующие результаты:



При достаточно ощутимом скачке мы все же находимся в 10-миллисекундном диапазоне, все еще оставаясь лучше, чем FORMAT и его 200-миллисекундном времени выполнения.

Есть другие способы упрощения запросов при получении этого вывода (другие, чем обработка в слое приложения). В следующий раз мы рассмотрим способы, которые могут вообще вывести форматирование за пределы запроса, а именно, вычисляемый столбец или триггер DML.

Заключение

Хотя мне нравится идея FORMAT, поскольку она делает более удобными выражения, которые согласуются с другими языками, нельзя полностью игнорировать ухудшение производительности. Когда я выступаю против ее использования, то часто слышу в ответ: "Хорошо, но я только использую ее в одном месте" или "Эта таблица никогда не станет большой". Проблема с подобной логикой заключается в том, что очень сложно провести черту, и вашим коллегам легко можно убедиться, что когда все в порядке в ряде случаев, использование ее в других сценариях, будет не столь хорошо.

Еще одно соображение против FORMAT состоит с том, что если вы, как и я, играетесь с Azure SQL Edge в локальном окружении, то там эта функция не поддерживается.