使用 XML 查询替换 ADO.NET 中的 IN ，提高查询性能

为了解决在大数据查询时不使用 in 操作符，所以使用下面的方案替换 in 的使用

--------------------------------------------------

--1.优化前后代码对比

优化前的代码：

select * from orderinfo where orderid in (1,2,3,4,5,........,n);

优化后的代码：

declare @xmlDoc xml;
set @xmlDoc='
<orders title="订单号">
<id>1111</id>
<id>2222</id>
<id>3333</id>
<id>4444</id>
</orders>';
select * from orderinfo 
where exists 
( 
   select 1 from @xmlDoc.nodes('/orders/id') as T(c)  
  where T.c.value('text()[1]','int')= orderinfo.OrderID 
);

--------------------------------------------------

--2.其它基础的  xquery 使用

declare @xmlDoc xml;


set @xmlDoc='
<orders title="订单号">
<id>862875</id>
<id>862874</id>
<id>862873</id>
<id>862872</id>
</orders>';
--查询所有Id节点
select @xmlDoc.query('/orders/id') c1
--查询第一个Id节点值
,@xmlDoc.value('(/orders/id)[1]', 'nvarchar(max)') AS c2
--查询最后一个Id节点值
,@xmlDoc.value('(/orders/id)[last()]', 'nvarchar(max)') AS c3
--查找属性
,@xmlDoc.value('(/orders/@title)[1]', 'nvarchar(max)') AS  c4


--xml 与表合并查询1（推荐）
select * from orderinfo 
where exists 
( 
select 1 from @xmlDoc.nodes('/orders/id') as T(c) 
where T.c.value('text()[1]','int')= orderinfo.OrderID 
);


--xml 与表合并查询（和in效果一样）
select * from orderinfo 
where OrderID in 
( 
select T.c.value('text()[1]','int') from @xmlDoc.nodes('/orders/id') as T(c) 
);

----------------------------------
--3. ADO.NET 中执行xml查询
DataTable dt = new DataTable(); 
using (SqlConnection conn = new SqlConnection(connectionString)) 
{ 
string xml = @"
<orders title="订单号">
<id>862875</id>
<id>862874</id>
<id>862873</id>
<id>862872</id>
</orders>
"; 
SqlCommand comm = conn.CreateCommand(); 
comm.CommandText = @"
select * from orderinfo 
where exists 
( 
select 1 from @xmlDoc.nodes('/orders/id') as T(c) 
where T.c.value('text()[1]','int')= orderinfo.OrderID 
);"; 


comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml }); 
using (SqlDataAdapter adapter = new SqlDataAdapter(comm)) 
{ 
adapter.SelectCommand = comm; 
adapter.Fill(dt); 
} 
}