Блог

У меня есть переменная xml, которая содержит набор идентификаторов, которые я хочу найти в таблице. При запросе я попробовал несколько версий, но следующая (из моего тестирования) оказалась самой быстрой:

 declare @idsxml as xml (IdSchemaColelction) = '<root><Id>505766</Id><Id>458073</Id><Id>460689</Id><Id>464050</Id></root>'

SELECT * FROM entity
WHERE @idsXml.exist('/root/Id[data(.)=sql:column("id")]') = 1
  

Проблема в том, что план запроса содержит следующее предупреждение «Преобразование типа в выражении (CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(числовой(38,10),[XmlTest].[dbo].[entity].[id],0),0)) может повлиять на «оценку мощности» при выборе плана запроса»

Я создал XML-схему, которая определяет текст Id как целое число, поэтому я ожидал бы, что data(.)=sql:column("id") это сравнение между целыми числами, но это предупреждение предполагает иное.

Каков правильный способ удалить это предупреждение в этом случае? Влияет ли это на производительность в будущем?

Определение таблицы и схемы:

 CREATE XML SCHEMA COLLECTION IdSchemaColelction AS  '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" >
  <xs:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" 
             schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Id" type="sqltypes:int" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
'
go

create table entity ( id int not null  primary key)
  

Я не думаю, что ваш подход является лучшим или быстрым…

Вот несколько подходов для их сравнения:

Используйте это для тестов

 create table test ( id int not null  primary key);
insert into test VALUES(100),(200),(505766),(300),(400),(500),(458073),(600),(700),(464050),(800),(900),(1000)
GO
  

Вот ваш список идентификаторов

 declare @idsxml as xml = '<root><Id>505766</Id><Id>458073</Id><Id>460689</Id><Id>464050</Id></root>'
  

— Это ваш подход. Он будет анализировать XML снова и снова
— .data() — это, конечно, не лучший способ считывания типобезопасных данных для сравнения…

 SELECT test.id
FROM test
WHERE @idsXml.exist('/root/Id[data(.)=sql:column("id")]') = 1;
  

— Это точно такой же подход, но с более быстрым XQuery

 SELECT test.id
FROM test
WHERE @idsXml.exist('/root/Id[text()=sql:column("id")]') = 1;
  

— Это немного медленнее… Вероятно, потому, что существует неявное преобразование типов…

 SELECT test.id
FROM test
WHERE @idsXml.exist('/root[Id=sql:column("id")]') = 1;
  

—При большем списке может быть лучше использовать производную таблицу в INNER JOIN

 WITH DerivedTable AS
(
    SELECT i.value('.','int') AS id
    FROM @idsxml.nodes('root/Id') AS A(i)
)
SELECT test.id
FROM test 
INNER JOIN DerivedTable AS dt ON test.id=dt.id;
  

— И с большим списком вы могли бы даже подумать об индексированной объявленной таблице (читайте о in memory последней квантовой скорости)

 DECLARE @tbl TABLE(id INT NOT NULL PRIMARY KEY) --PK only, if your XML never contains a value twice!
INSERT INTO @tbl
SELECT i.value('.','int') AS id
FROM @idsxml.nodes('root/Id') AS A(i);

SELECT test.id
FROM test 
INNER JOIN @tbl AS tbl ON test.id=tbl.id;

GO

DROP TABLE test;