Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
Добрый день.
На ситуацию натолкнул один запрос, где была неявная конвертация INT в NVARCHAR(100).
Парсинг 4 секунды и выполнение 2. По строковому полю есть индекс. Но оптимизатор посчитал что есть преобразование и задействовал другой, по его мнению лучший.
По некоей случайности сделали
ON FieldNvarchar100 = LTRIM(RTRIM(STR(CAST(FieldInt AS FLOAT))))

И о чудо. CONVERT_IMPLICIT исчез из плана и начал использоваться нужный индекс. Парсинг и выполнение меньше 100 мс.
Собственно вот и наглядный, правда тривиальный, пример.
CREATE TABLE #TempA (Id_A INT NOT NULL, String NVARCHAR(100) NOT NULL)
CREATE TABLE #TempB (Id_B INT NOT NULL, Id_String INT NOT NULL)

INSERT INTO #TempA
  SELECT sv.number + 10000, sv.number + 100 FROM [master].dbo.spt_values AS sv WHERE sv.[type] = 'P'
  UNION ALL
  SELECT sv.number + 20000, sv.number + 100 FROM [master].dbo.spt_values AS sv WHERE sv.[type] = 'P'
  UNION ALL
  SELECT sv.number + 30000, sv.number + 100 FROM [master].dbo.spt_values AS sv WHERE sv.[type] = 'P'
  
INSERT INTO #TempB
  SELECT sv.number + 10, sv.number + 100 FROM [master].dbo.spt_values AS sv WHERE sv.[type] = 'P'

CREATE CLUSTERED INDEX PK_A ON #TempA (Id_A ASC)
CREATE CLUSTERED INDEX PK_B ON #TempB (Id_B ASC)

CREATE NONCLUSTERED INDEX IX_A ON #TempA (String ASC)
CREATE NONCLUSTERED INDEX IX_B ON #TempB (Id_String ASC)

SELECT
  *
FROM
  #TempA AS a
  JOIN #TempB AS b
    ON a.String = b.Id_String
WHERE
  b.Id_B BETWEEN 100 AND 200

SELECT
  *
FROM
  #TempA AS a
  JOIN #TempB AS b
    ON a.String = LTRIM(RTRIM(STR(CAST(b.Id_String AS FLOAT))))
WHERE
  b.Id_B BETWEEN 100 AND 200

DROP TABLE #TempB
DROP TABLE #TempA


План второго запроса меньше использует CPU, хотя и читает больше. НО! При этом нет Warning от оптимизатора.

Есть ли какое-то чудесное объяснение этому чудесному преобразование INT - > FLOAT -> STR
27 июл 17, 10:30    [20678841]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
aleks222
Guest
X-Cite
Есть ли какое-то чудесное объяснение этому чудесному преобразование INT - > FLOAT -> STR

Конечно.
1. Читай про приоритет типов при неявных преобразованиях.
2. Не сравнивай божий дар с яичницей.
3. И будет тебе щастье.
27 июл 17, 10:49    [20678892]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
SELECT
  *
FROM
  #TempA AS a
  JOIN #TempB AS b
    ON a.String = CAST(b.Id_String AS NVARCHAR(100))
WHERE
  b.Id_B BETWEEN 100 AND 200

План идентичен с вариантом STR() но при этом Warning никуда не исчез.

Но фишка в том, что запрос сгенерирован Entity Framework, и он хоть убейся делал
ON a.String = CAST(b.Id_String AS NVARCHAR(MAX))
В данном случае это почти никак не влияет, но в том запросе все было печально.
Пока не сделали через STR( AS FLOAT)
27 июл 17, 11:02    [20678925]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
Ну и тогда вопрос другой
Планы идентичны.
-- Тут Есть Warning
SELECT
  *
FROM
  #TempA AS a
  JOIN #TempB AS b
    ON a.String = CONVERT(NVARCHAR(100), b.Id_String, 0)
WHERE
  b.Id_B BETWEEN 100 AND 200

-- Тут Нет Warning
SELECT
  *
FROM
  #TempA AS a
  JOIN #TempB AS b
    ON a.String = CONVERT(NVARCHAR(10), LTRIM(STR(CONVERT(FLOAT(53), b.Id_String, 0))), 0)
WHERE
  b.Id_B BETWEEN 100 AND 200

Как это можно объяснить?
27 июл 17, 11:13    [20678969]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
aleks222
Guest
X-Cite
-- Тут Есть Warning

Как это можно объяснить?


1. Warning предлагается угадать?
2. Или ты полагаешь, что мне не лень это запускать?
27 июл 17, 13:02    [20679504]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
aleks222
X-Cite
-- Тут Есть Warning

Как это можно объяснить?


1. Warning предлагается угадать?
2. Или ты полагаешь, что мне не лень это запускать?

Ctrl+C Ctrl+V F5 :)
<Warnings>
  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,[a].[String],0)" />
  <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(int,[a].[String],0)=[b].[Id_String]" />
</Warnings>
27 июл 17, 14:06    [20679770]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
aleks222
Guest
X-Cite
aleks222
пропущено...


1. Warning предлагается угадать?
2. Или ты полагаешь, что мне не лень это запускать?

Ctrl+C Ctrl+V F5 :)
<Warnings>
  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,[a].[String],0)" />
  <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(int,[a].[String],0)=[b].[Id_String]" />
</Warnings>


Ну дык, она ругается, что если "CONVERT_IMPLICIT", то индексам и Seek приходит кирдык. И это плохо.
Почему оно не ругается в втором случае... наверное оптимизатор запутался в твоих CONVERT.

Короче, завязывай со сравнением разнотипных данных.
И будет тебе щастье.
27 июл 17, 14:19    [20679806]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
X-Cite
Собственно вот и наглядный
Чтобы он стал наглядным, неплохо бы версию сервера сообщить.
27 июл 17, 14:31    [20679853]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
invm
X-Cite
Собственно вот и наглядный
Чтобы он стал наглядным, неплохо бы версию сервера сообщить.

Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64) Oct 10 2015 17:18:26 Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

автор
Почему оно не ругается в втором случае... наверное оптимизатор запутался в твоих CONVERT.

Ага, запутался и догадался что надо SEEK по правильному индексу. В случае тяжелого большого запроса (по нему я не выкладывал пример) он выбрал SEEK по другому индексу, без всех этих конвертов.
27 июл 17, 15:24    [20680096]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
X-Cite,

покажите план для того где типа нет CONVERT_IMPLICT
27 июл 17, 15:27    [20680111]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
Ну вот два плана из темповых запросов

К сообщению приложен файл (P1.sqlplan - 43Kb) cкачать
27 июл 17, 17:32    [20680628]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
И еще план для этого:
SELECT
  *
FROM
  #TempA AS a
  JOIN #TempB AS b
    ON a.String = CAST(b.Id_String AS NVARCHAR(100))
WHERE
  b.Id_B BETWEEN 100 AND 200


План этого запроса и план 2 запроса выше - одинаковые.. но в одном есть Warning, а в этом нету.

К сообщению приложен файл (P2.sqlplan - 20Kb) cкачать
27 июл 17, 17:34    [20680633]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
X-Cite,
автор
Ну и тогда вопрос другой
Планы идентичны.

да они идентичны, расходимся
27 июл 17, 17:39    [20680647]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное преобразование  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1743
TaPaK
X-Cite,
автор
Ну и тогда вопрос другой
Планы идентичны.

да они идентичны, расходимся

Но в одном есть Warning, а во втором нет. Почему?
27 июл 17, 18:47    [20680820]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить