如何加快读取大量数据在SQL Server和C＃ [英] How to speed up reading large amounts of data in SQL Server and C#

问题描述

我在我的数据库20万左右条目的视图。我用下面的代码阅读它：

I have a view in my database with around 200k entries. I'm reading it with the following code:

using (SqlConnection conn = new SqlConnection....)
{
    conn.Open();

    string query = "SELECT * FROM SP3DPROJECT.dbo.XSystemHierarchy";

    using (SqlCommand comm = new SqlCommand())
    {
        comm.CommandText = query;
        comm.Connection = conn;

        using (SqlDataReader reader = comm.ExecuteReader())
        {
            DateTime start = DateTime.Now;

            while (reader.Read())
            {
                // Code goes here, but for performance 
                // test I'm letting this empty
            }

            DateTime end = DateTime.Now;
            TimeSpan elapsed = (end- start).TotalSeconds;
        }
    }
}

该图只有2列的 GUID 键入。在 ExecuteCommand（）是非常快的，但while循环（甚至没有任何代码，只需循环），大约需要150秒。

The view has only 2 columns of GUID type. The ExecuteCommand() is very fast, but the while loop (even with no code, just the looping) takes ~150 seconds.

有没有更好的或者快速的方法来做到这一点？

Is there a better or fast way to do this?

推荐答案

感谢大家。这些意见使我想到阅读视图VS表，我改变了查询，用来做SQL Server中的视图（代替邻读取所有视图直接的内容）完全相同的查询。我不知道为什么这样做了这么多的差异，但现在得到的所有数据所需的时间从150秒下降到不足2秒，这是更够长了我的目的。

Thank you all. The comments led me to think about reading the view vs table and I changed the query to the exact same query used to make the view in SQL Server (instead o reading all the contents of the view directly). I have no idea why this made so much difference, but now the time needed to get all the data dropped from 150s to less than 2s, which is more that enough for my purposes.

这篇关于如何加快读取大量数据在SQL Server和C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！