#sql-server #xml #xpath #sql-server-2012 #xquery
#sql-server #xml #xpath #sql-server-2012 #xquery
Вопрос:
У меня есть переменная 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)
Ответ №1:
Я не думаю, что ваш подход является лучшим или быстрым…
Вот несколько подходов для их сравнения:
Используйте это для тестов
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;