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

谁借莪１个温暖的怀抱￠ 2024-02-17 14:30 11阅读 0赞

为了解决在大数据查询时不使用 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);  
\}  
\}