C＃ADO：优化查询及其性能 [英] C# ADO: Optimize query and its performance

问题描述

我有一个C＃RestApi，它需要通过执行如下查询来使用ADO从数据库中提取记录信息：

I have a C# RestApi which need to extract information of records from database using ADO by executing queries like this:

declare @id1 int 
set @id1 =  (select id from myTable where col1= x1, col2 = y1, col3 = z1 from mappingTable)

declare @id2 int 
set @id2 =  (select id from myTable where col1= x2, col2 = y2, col3 = z2 from mappingTable)

declare @id3 int 
set @id3 =  (select id from myTable where col1= x3, col2 = y3, col3 = z3 from mappingTable)
.
.
.
declare @idN int 
set @idN =  (select id from myTable where col1= xN, col2 = yN, col3 = zN from mappingTable)

select @id1,@id2,@id3, ..., @idN

我在上面运行的查询中使用以下命令运行N个查询ADO.NET SqlCommand 并读取结果。我有两个问题：

I run above query which runs N queries inside it using ADO.NET SqlCommand and read the results. I have two questions:

1. 是否使用单独的 SqlCommand 运行每个查询会导致性能降级与否？通常，在I / O任务中，执行许多小的I / O任务的性能要比在一批I / O任务中运行所有I / O任务的性能低，但是我不知道在Databases和ADO中是否存在相同的情况。


2. 是否有更好的方法通过更好的SQL查询提取相同的结果？换句话说，我可以用其他方式编写此查询以使其具有更好的性能吗？

1. Does running each of queries using separate SqlCommand lead to performance downgrade or not? Usually in I/O tasks, executing many small I/O tasks have lower performance than running all of them in one batch I/O tasks but I have no idea about the same scenario in Databases and ADO.
2. Are there any better ways to extract the same result with a better SQL query? In other words can I write this query other way to run it with better performance?

注意：在上面的查询中，列和表是在所有查询中都相同，仅修改where子句中的值。

Note: In above queries columns and tables are the same in all queries only values in where clause are modified.

推荐答案

使用表值参数存储<$ c的对应值$ c> x ， y 和 z 。
使用单个查询，在其中将 mappingTable 与该表值参数进行内部联接。

Use a table valued parameter to store corresponding values of x, y and z. Use a single query where you inner join your mappingTable with that table valued parameter.

在SQL中：

CREATE TYPE XYZ As Table -- Do not call it XYZ!!!
(
    x int,
    y int,
    z int -- I'm using ints because it's simple - you need the data types of your columns here
)

在c＃中，创建一个与此类型相对应的数据表：

In c#, create a data table that corresponds to this type:

var dt = new DataTable();
dt.Columns.Add("x", typeof(int));
dt.Columns.Add("y", typeof(int));
dt.Columns.Add("z", typeof(int));

用正确的值填充该数据表，然后将数据表作为类型为 SqlDbType.Structured 的参数：

populate that data table with the correct values, and then you send the data table to SQL Server as a parameter of type SqlDbType.Structured:

 cmd.Parameters.Add("@tvp", SqlDbType.Structured).Value = dt;

要在查询中使用它：

SELECT id 
FROM mappingTable
JOIN @tvp
    ON col1= x
   AND col2 = y
   AND col3 = z

这将返回一个包含单个列的记录集，其中包含您需要的所有ID 。

This will return a recordset containing a single column with all the ids you need.

注意：代码是直接在此处编写的-可能会有一些错字。

Note: Code was written directly here - there might be some typos.

这篇关于C＃ADO：优化查询及其性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！