如何优化此SQL查询（来自C＃）

I am newbie to db programming and need help with optimizing this query:

我是db编程的新手,需要帮助优化这个查询:

Given tables A, B and C and I am interested in one column from each of them, how to write a query such that I can get one column from each table into 3 different arrays/lists in my C# code?

给定表A,B和C,我感兴趣的是每个列中的一列,如何编写查询,以便我可以从每个表中将一列写入我的C#代码中的3个不同的数组/列表?

I am currently running three different queries to the DB but want to accomplish the same in one query (to save 2 trips to the DB).

我目前正在向DB运行三个不同的查询,但希望在一个查询中完成相同的操作(以保存两次到DB的访问)。

5 个解决方案

#1

@patmortech Use UNION ALL instead of UNION if you don't care about duplicate values or if you can only get unique values (because you are querying via primary or unique keys). Much faster performance with UNION ALL.

@patmortech如果您不关心重复值或者只能获取唯一值(因为您通过主键或唯一键查询),请使用UNION ALL而不是UNION。 UNION ALL的性能要快得多。

There is no sense of "arrays" in SQL. There are tables, rows, and columns. Resultsets return a SET of rows and columns. Can you provide an example of what you are looking for? (DDL of source tables and sample data would be helpful.)

SQL中没有“数组”的意义。有表,行和列。 Resultsets返回一组行和列。你能提供一个你想要的例子吗? (源表和样本数据的DDL会有所帮助。)

As others have said, you can send up multiple queries to the server within a single execute statement and return multiple resultsets via ADO.NET. You use the DataReader .NextResult() command to return the next resultset.

正如其他人所说,您可以在单个执行语句中向服务器发送多个查询,并通过ADO.NET返回多个结果集。您使用DataReader .NextResult()命令返回下一个结果集。

See here for more information: MSDN

有关更多信息,请参见此处:MSDN

Section: Retrieving Multiple Result Sets using NextResult

部分:使用NextResult检索多个结果集

Here is some sample code:

以下是一些示例代码:

static void RetrieveMultipleResults(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM dbo.Categories;" +
          "SELECT EmployeeID, LastName FROM dbo.Employees",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.HasRows)
        {
            Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
                reader.GetName(1));

            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
            reader.NextResult();
        }
    }
}

#2

With a stored procedure you can return more than one result set from the database and have a dataset filled with more than one table, you can then access these tables and fill your arrays/lists.

使用存储过程,您可以从数据库返回多个结果集,并且数据集中包含多个表,然后您可以访问这些表并填充数组/列表。

#3

You can do 3 different SELECT statements and execute in 1 call. You will get 3 results sets back. How you leverage those results depends on what data technology you are using. LINQ? Datasets? Data Adapter? Data Reader? If you can provide that information (perhaps even sample code) I can tell you exactly how to get what you need.

您可以执行3个不同的SELECT语句并在1个调用中执行。您将获得3个结果集。如何利用这些结果取决于您使用的数据技术。 LINQ?数据集?数据适配器?数据读卡器?如果您可以提供该信息(甚至可能是示例代码),我可以告诉您如何获得所需信息。

#4

Not sure if this is exactly what you had in mind, but you could do something like this (as long as all three columns are the same data type):

不确定这是否与您的想法完全相同,但您可以这样做(只要所有三列都是相同的数据类型):

select field1, 'TableA' as TableName from tableA
UNION
select field2, 'TableB' from tableB
UNION
select field3, 'TableC' from tableC

This would give you one big resultset with all the records. Then you could use a data reader to read the results, keep track of what the previous record's TableName value was, and whenever it changes you could start putting the column values into another array.

这将为您提供一个包含所有记录的大结果集。然后,您可以使用数据读取器读取结果,跟踪上一个记录的TableName值,并且每当它发生更改时,您可以开始将列值放入另一个数组中。

#5

Take the three trips. The answers so far suggest how far you would need to advance from "new to db programming" to do what you want. Master the simplest ways first.

三次旅行。到目前为止,答案表明你需要从“新编程到数据库编程”前进到你想要的程度。先掌握最简单的方法。

If they are three huge results, then I suspect you're trying to do something in C# that would better be done in SQL on the database without bringing back the data. Without more detail, this sounds suspiciously like an antipattern.

如果它们是三个巨大的结果,那么我怀疑你正在尝试用C#做一些最好在数据库上用SQL做的事情而不带回数据。没有更多的细节,这听起来像一个反模式。

#1