PARSE или CAST и CONVERT
Пересказ статьи Max Vernon. PARSE vs CAST and CONVERT
T-SQL обычно предоставляет много способов "снять шкурку с кота", как говорится. Здесь мы рассмотрим два интересных способа преобразования дат и времени из символьных столбцов в столбец, использующий тип данных datetime.
PARSE предоставляет механизм для преобразования разнообразного символьного представления даты в тип данных datetime. Из документации:
Возвращает результат выражения, транслированный в требуемый тип данных в SQL Server.
Используйте PARSE только для преобразования из строки в числовой тип и типы даты/времени. Для преобразований обычных типов продолжайте использовать CAST и CONVERT. Имейте в виду, что имеется определенная потеря производительности при парсинге строковых значений.
Вы можете использовать PARSE для преобразования в дату следующей строки:
Для этого может быть использован следующий код:
Результат выглядит так:
Отлично. Теперь, когда получен работающий код, вы заходите преобразовать таблицу с большим числом строк, которые содержат символьные даты, в столбец datetime. Можно написать так:
Это будет работать, но займет массу времени при большом числе строк. PARSE ооочень медленная. Давайте сравним синтаксис этого оператора с CAST или CONVERT. Лично я предпочитаю CONVERT, хотя он, да, на 3 символа длиннее.
Это ненамного труднее. Вывод аналогичен выводу функции PARSE.
Теперь давайте сравним время, потребляемое каждым из вариантов. Сначала создадим пару таблиц; одну хранящую исходные данные с датами в столбце varchar, а другую с результатами PARSE и CONVERT. Исходная таблица будет содержать 10000 строк. Это немного, но достаточно для нашего теста.
Последний SELECT гарантирует, что данные находятся в буферном кэше, что даст нам адекватное сравнение времени, затрачиваемого на конвертацию.
Далее мы включим статистику I/O и времени, после чего преобразуем данные, используя PARSE и CONVERT:
Вывод выглядит следующим образом:
Как вы можете увидеть, первый тест с использованием PARSE занял 2 секунды на преобразование 10000 строк. Второй тест с CONVERT - только 71 миллисекунду, или в 28.5 раз быстрее.
Какие выводы? Если вы преобразуете правильно сформированные даты и время в текстовом представлении в столбец типа datetime, и вас заботит скорость преобразования, используйте CONVERT (или CAST, что то же самое).
Возвращает результат выражения, транслированный в требуемый тип данных в SQL Server.
Используйте PARSE только для преобразования из строки в числовой тип и типы даты/времени. Для преобразований обычных типов продолжайте использовать CAST и CONVERT. Имейте в виду, что имеется определенная потеря производительности при парсинге строковых значений.
Вы можете использовать PARSE для преобразования в дату следующей строки:
December 21, 1989 6:00 PM
Для этого может быть использован следующий код:
SELECT PARSE(N'December 21, 1989 6:00 PM' AS datetime USING 'en-GB');
Результат выглядит так:
1989-12-21 18:00:00.000
Отлично. Теперь, когда получен работающий код, вы заходите преобразовать таблицу с большим числом строк, которые содержат символьные даты, в столбец datetime. Можно написать так:
UPDATE t
SET t.DateTimeColumn = PARSE(t.CharDateTime AS datetime USING 'en-GB')
FROM dbo.MyTable t;
Это будет работать, но займет массу времени при большом числе строк. PARSE ооочень медленная. Давайте сравним синтаксис этого оператора с CAST или CONVERT. Лично я предпочитаю CONVERT, хотя он, да, на 3 символа длиннее.
SET LANGUAGE 'British';
SELECT CONVERT(datetime, N'December 21, 1989 6:00 PM');
Это ненамного труднее. Вывод аналогичен выводу функции PARSE.
Теперь давайте сравним время, потребляемое каждым из вариантов. Сначала создадим пару таблиц; одну хранящую исходные данные с датами в столбце varchar, а другую с результатами PARSE и CONVERT. Исходная таблица будет содержать 10000 строк. Это немного, но достаточно для нашего теста.
DROP TABLE IF EXISTS dbo.ParseTest;
DROP TABLE IF EXISTS dbo.ParseTestOutput1;
DROP TABLE IF EXISTS dbo.ParseTestOutput2;
GO
CREATE TABLE dbo.ParseTest
(
d varchar(23) NOT NULL
);
INSERT INTO dbo.ParseTest(d)
SELECT TOP(10000) DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2019-08-20 00:00:00')
FROM sys.syscolumns sc1
CROSS JOIN sys.syscolumns sc2;
CREATE TABLE dbo.ParseTestOutput1
(
d datetime NOT NULL
);
CREATE TABLE dbo.ParseTestOutput2
(
d datetime NOT NULL
);
SELECT *
FROM dbo.ParseTest;
GO
Последний SELECT гарантирует, что данные находятся в буферном кэше, что даст нам адекватное сравнение времени, затрачиваемого на конвертацию.
Далее мы включим статистику I/O и времени, после чего преобразуем данные, используя PARSE и CONVERT:
SET STATISTICS IO, TIME ON;
GO
INSERT INTO dbo.ParseTestOutput1 (d)
SELECT PARSE(pt.d AS datetime USING 'en-US')
FROM dbo.ParseTest pt;
GO
INSERT INTO dbo.ParseTestOutput2 (d)
SELECT CONVERT(datetime, pt.d)
FROM dbo.ParseTest pt;
Вывод выглядит следующим образом:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'ParseTestOutput1'. Scan count 0, logical reads 10021, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParseTest'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2000 ms, elapsed time = 2027 ms.
(10000 rows affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'ParseTestOutput1'. Scan count 0, logical reads 10021, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParseTest'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 71 ms.
(10000 rows affected)
Как вы можете увидеть, первый тест с использованием PARSE занял 2 секунды на преобразование 10000 строк. Второй тест с CONVERT - только 71 миллисекунду, или в 28.5 раз быстрее.
Какие выводы? Если вы преобразуете правильно сформированные даты и время в текстовом представлении в столбец типа datetime, и вас заботит скорость преобразования, используйте CONVERT (или CAST, что то же самое).
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой