从数据库中检索数据的最快方法

I am working on a ASP.NET project with C# and Sql Server 2008.

我正在做一个ASP。NET项目与c#和Sql Server 2008。

I have three tables:

我有三个表:

Each user has a specific value for each data field, and this value is stored in the DataFieldsValues.

每个用户对每个数据字段都有一个特定的值，这个值存储在DataFieldsValues中。

Now I want to display a report that looks like this:

现在我想显示一个像这样的报告:

I have created the objects User, and DataField. In the DataField object, there is the Method string GetValue(User user), in which I get the value of a field for a certain user.

我已经创建了对象用户和数据。在datfierce对象中，有一个方法字符串GetValue(User User User)，在该方法中，我获得了某个用户的字段值。

Then I have the list of Users List<User> users and the list of DataFields List<DataField> fields and I do the following:

然后我列出了用户列表 <用户> 用户和DataFields列表的列表< datfield>字段，我做如下操作:

string html = string.Empty;
html += "<table>";
html += "<tr><th>Username</th>";
foreach (DataField f in fields)
{
   html += "<th>" + f.Name + "</th>";
}
html += "</tr>"

foreach (User u in users)
{
   html += "<tr><td>" + u.Username + "</td>"
   foreach (DataField f in fields)
   {
      html += "<td>" + f.GetValue(u) + "</td>";
   }
   html += "</tr>"
}
Response.Write(html);

This works fine, but it is extremely slow, and I am talking about 20 users and 10 data fields. Is there any better way in terms of performance to achieve this?

这工作得很好，但是非常慢，我指的是20个用户和10个数据字段。有没有更好的方法来实现这一点?

EDIT: For each parameter inside the classes, I retrieve the value using the following method:

编辑:对于类内的每个参数，我使用以下方法检索值:

public static string GetDataFromDB(string query)
{
    string return_value = string.Empty;
    SqlConnection sql_conn;
    sql_conn = new SqlConnection(ConfigurationManager.ConnectionStrings["XXXX"].ToString());
    sql_conn.Open();
    SqlCommand com = new SqlCommand(query, sql_conn);
    //if (com.ExecuteScalar() != null)
    try
    {
        return_value = com.ExecuteScalar().ToString();
    }
    catch (Exception x)
    {
    }
    sql_conn.Close();
    return return_value;
} 

For instance:

例如:

public User(int _Id)
{
this.Id = _Id
this.Username = DBAccess.GetDataFromDB("select Username from Users where Id=" + this.Id)
 //...
}

10 个解决方案

#1

Here are 2 suggestions that will help. The first suggestion is what will improve your performance significantly. The second suggestion will help also, though probably not make your app faster in your case.

这里有两个建议可以帮助你。第一个建议是什么能显著提高你的表现。第二个建议也会有帮助，虽然可能不会让你的应用程序运行得更快。

Suggestion 1

You call the method GetDataFromDB(string query) very often. This is bad because you create a new SqlConnection and SqlCommand each time. This takes time and resources. Also, if there is any network delay, that is multiplied by the number of calls you are making. So it's just a bad idea.

您经常调用GetDataFromDB(字符串查询)方法。这很糟糕，因为每次都创建一个新的SqlConnection和SqlCommand。这需要时间和资源。此外，如果存在任何网络延迟，则将其乘以正在进行的调用数量。这是个坏主意。

I suggest that you call that method once and have it populate a collection like a Dictionary<int, string> so that you can quickly look up your Username value from the user id key.

我建议您调用该方法一次，让它填充一个集合，比如Dictionary ，以便您可以从用户id键快速查找用户名值。 ,>

Like this:

是这样的:

// In the DataField class, have this code.
// This method will query the database for all usernames and user ids and
// return a Dictionary<int, string> where the key is the Id and the value is the 
// username. Make this a global variable within the DataField class.
Dictionary<int, string> usernameDict = GetDataFromDB("select id, username from Users");

// Then in the GetValue(int userId) method, do this:
public string GetValue(int userId)
{
    // Add some error handling and whatnot. 
    // And a better name for this method is GetUsername(int userId)
    return this.usernameDict[userId];
}

Suggestion 2

Here is another way that you can improve things, though slightly in this case—use the StringBuilder class. There are significant performance gains (here is an overview: http://support.microsoft.com/kb/306822).

这里是另一种可以改进的方法，尽管在本例中略有改进——使用StringBuilder类。有显著的性能提升(这里有一个概述:http://support.microsoft.com/kb/306822)。

SringBuilder sb = new StringBuilder();
sb.Append("<table><tr><th>Username</th>");
foreach (DataField f in fields)
{
    sb.Append("<th>" + f.Name + "</th>");
}

// Then, when you need the string
string html = sb.ToString();

Let me know if you need some more clarification, but what you are asking for is very do-able. We can work this out!

如果你需要更多的说明，请告诉我，但你所要求的是非常可行的。我们可以算出来!

If you make these 2 simple changes, you will have great performance. I guarantee it.

如果你做这两个简单的改变，你会有很好的表现。我保证它。

#2

The database design you choose is named Entity-Attribute-Value, a design that is well known for its performance problems. SQL Server team has release a whitepaper for guidance around EAV design, see Best Practices for Semantic Data Modeling for Performance and Scalability.

您选择的数据库设计名为Entity-Attribute-Value，这是一种以性能问题而闻名的设计。SQL Server团队发布了一份有关EAV设计指导的白皮书，请参阅关于性能和可扩展性的语义数据建模的最佳实践。

Alas, you already have the design in place and what can you do about it now? The important thing is to reduce the miriad calls to the dB to one single call, and execute one single set oriented statement to retrieve the data. The name of the game is Table Valued Parameters:

唉，你已经有了设计，你现在能做些什么呢?重要的是将对dB的miriad调用减少为一个调用，并执行一个面向集合的语句来检索数据。游戏名称为表值参数:

declare @users as UsersType;

insert into @users (UserId) values (7), (42), (89);

select ut.Id, 
  ut.Username, 
  df.Name as DataFieldName, 
  dfv.Value
from Users ut
join @users up on ut.Id = up.UserId
join DataFieldValues dfv on ut.Id = dfv.UserId
join DataFields df on dfv.DataFieldId = df.Id
order by ut.Id;

For a full example, see this SqlFiddle.

有关完整示例，请参见这个SqlFiddle。

While, strictly speaking, it is possible to retrieve a result on the shape you desire (data field names transposed as column names) using the PIVOT operator, I would very strongly advise against doing so. PIVOT on its own is a performance quagmire, but when you add the dynamic nature of the desired result set is basically impossible to pull it off. The traditional result set consisting of one-row-per attribute is trivial to parse into a table, because the required order by user Id guarantees a clean break between sets of correlated attributes.

虽然严格地说，可以使用PIVOT操作符检索您希望的形状(数据字段名转换为列名)的结果，但我强烈建议不要这样做。主自己是一个性能泥潭,但当你添加所需的结果集的动态特性基本上是不可能做到的。传统的结果集one-row-per属性组成的简单解析成一个表,由于用户Id担保所需的顺序设置相关属性之间的彻底决裂。

#3

This is slow because under the hood you are making 20 x 10 = 200 queries to the database. Correct way would be to load everything in one turn.

这是很慢的，因为在底层，您正在对数据库进行20×10 = 200个查询。正确的方法是一次装载所有东西。

You should post some details about the way you load data. If you are using Entity Framework, you should use something called Eager Loading using Include command.

您应该发布一些关于加载数据的方式的细节。如果您使用的是实体框架，那么您应该使用Include命令来进行“即时加载”。

// Load all blogs and related posts
var blogs1 = context.Blogs
                      .Include(b => b.Posts)
                      .ToList();

Some samples can be found here: http://msdn.microsoft.com/en-us/data/jj574232.aspx

可以在这里找到一些示例:http://msdn.microsoft.com/en-us/data/jj574232.aspx

EDIT:

编辑:

It seems that you are not using the tools .NET Framework gives you. These days you don't have to do your own database access for simple scenarious like yours. Also, you should avoid concatenating string HTML like you do.

看来你并没有使用。net框架提供的工具。现在，您不必为简单的场景(如您的)进行自己的数据库访问。此外，您应该避免像这样连接字符串HTML。

I would suggest you to redesign your application using existing ASP.NET controls and Entity Framework.

我建议您使用现有的ASP重新设计您的应用程序。NET控件和实体框架。

Here is a sample with step by step instructions for you: http://www.codeproject.com/Articles/363040/An-Introduction-to-Entity-Framework-for-Absolute-B

下面是一个逐步指导您的示例:http://www.codeproject.com/articles/363040 / an - introduction to- entityframeworkfor absolute - b

#4

As Remus Rusanu said, you can get the data you want in the format you require by using the PIVOT relational operator, as far as performance of PIVOT is concerned, I've found that it will depend on the indexing of your tables and the variability and size of the data set. I would be greatly interested in hearing more from him about his opinion of PIVOTs as we are all here to learn. There is a great discussion on PIVOT vs JOINS here.

Remus Rusanu说过,你可以得到你想要的数据格式需要通过使用主关系算子,主的表现而言,我发现这将取决于你的表和索引的可变性和规模数据集。我将极大地兴趣支点的从他听到更多关于他的意见我们都在这里学习。这里有一个关于PIVOT vs join的很好的讨论。

If the DataFields table is a static set then you may not need to worry about generating the SQL dynamically and you can build yourself a stored procedure; if it does vary you may need to take the performance hit of dynamic SQL(here is an excellent article on this) or use a different approach.

如果DataFields表是一个静态集，那么您可能不需要担心动态生成SQL，您可以自己构建一个存储过程;如果它确实不同，您可能需要采用动态SQL的性能冲击(这里有一篇关于这方面的优秀文章)，或者使用不同的方法。

Unless you have further need for the data try to keep the returned set to the minimum you need for display it's a good way to reduce overhead as everything will need to go over the network unless your db is on the same physical server as the web server.

除非您对数据有进一步的需求，否则您需要将返回的集合保持到最小值，以便显示它是一种减少开销的好方法，因为除非您的db位于与web服务器相同的物理服务器上，否则将需要遍历网络。

Make sure that you perform as few separate data calls as possible will reduce that time you spend raising and dropping connections.

确保您尽可能少地执行单独的数据调用，这将减少您增加和删除连接的时间。

You should always double-check of data calls within a loop when the control for the loop is based on a (probably related?) data set as this screams JOIN.

当循环的控件基于(可能是相关的)数据集时，您应该始终在循环中检查数据调用。

When you are experimenting with your SQL try to become familiar with execution plans these will help you figure out why you have slow running queries check out these resources for more info.

当您尝试使用SQL时，请尝试熟悉执行计划，这些将帮助您了解为什么您的查询运行缓慢，请查看这些资源以获得更多信息。

Whatever you approach you decide you need to figure out where the bottlenecks are in your code, something as basic as stepping through the execution can help with this as it will allow you to see for yourself where problems lie, this will also allow you to identify for yourself possible problems with your approach and build good design choice habits.

不管你的方法你决定,你需要在代码中找出瓶颈所在,通过执行基本的步进可以帮助这个,因为它将让你看到自己的问题所在,这也将允许你为自己确定可能出现的问题与你的方法和构建良好的设计选择习惯。

Marc Gravel has some interesting points to make about c# data reading here the article is a bit old but worth a read.

Marc砾石有一些关于c#数据阅读的有趣观点，这篇文章有点旧，但值得一读。

PIVOTing your data.(Sorry Remus ;-) ) Bases on the data example you have provided, the following code will get what you need with no in-query recursion:

转动你的数据。(对不起，Remus;-))根据您提供的数据示例，以下代码将在没有查询递归的情况下得到您需要的:

--Test Data
DECLARE @Users AS TABLE ( Id int
                        , Username VARCHAR(50)
                        , Name VARCHAR(50)
                        , Email VARCHAR(50)
                        , [Role] INT --Avoid reserved words for column names.
                        , Active INT --If this is only ever going to be 0 or 1 it should be a bit.
                        );

DECLARE @DataFields AS TABLE ( Id int
                        , Name VARCHAR(50)
                        , [Type] INT --Avoid reserved words for column names.
                        );

DECLARE @DataFieldsValues AS TABLE ( Id int
                        , UserId int
                        , DataFieldId int
                        , Value VARCHAR(50)
                        );

INSERT INTO @users  ( Id
                    , Username
                    , Name
                    , Email
                    , [Role]
                    , Active) 
VALUES (1,'enb081','enb081','[e-mail]',2,1),
       (2,'Mack','Mack','[e-mail]',1,1),
       (3,'Bob','Bobby','[e-mail]',1,0)


INSERT INTO @DataFields  
                    ( Id
                    , Name
                    , [Type]) 
VALUES (1,'DataField1',3),
       (2,'DataField2',1),
       (3,'DataField3',2),
       (4,'DataField4',0)

INSERT INTO @DataFieldsValues  
                    ( Id
                    , UserId
                    , DataFieldId
                    , Value) 
VALUES (1,1,1,'value11'),
       (2,1,2,'value12'),
       (3,1,3,'value13'),
       (4,1,4,'value14'),
       (5,2,1,'value21'),
       (6,2,2,'value22'),
       (7,2,3,'value23'),
       (8,2,4,'value24')

--Query
SELECT *
FROM
(   SELECT  ut.Username, 
            df.Name as DataFieldName, 
            dfv.Value
    FROM @Users ut
    INNER JOIN @DataFieldsValues dfv 
        ON ut.Id = dfv.UserId
    INNER JOIN @DataFields df 
        ON dfv.DataFieldId = df.Id) src
PIVOT
(   MIN(Value) FOR DataFieldName IN (DataField1, DataField2, DataField3, DataField4)) pvt

--Results
Username    DataField1  DataField2  DataField3  DataField4
enb081      value11     value12     value13     value14
Mack        value21     value22     value23     value24

The most important thing to remember is to try things out for yourself as whatever we suggest might be altered by factors at your site that we aren't aware of.

需要记住的最重要的事情是亲自尝试，因为我们的建议可能会被我们不知道的网站上的因素所改变。

#5

Make sure that you are not making a connection to the database for each loop.

As I can see, the f.GetValue(u) part is a method that returns a string value that was fetched from the database.

Put the data in an object once and for all and do the same thing as f.GetValue(u) is doing here.

确保没有为每个循环建立到数据库的连接。如我所见，f.GetValue(u)部分是一个方法，它返回从数据库中获取的字符串值。将数据一次性地放入对象中，并执行与这里的f.GetValue(u)相同的操作。

#6

How are you accessing the database? Check the generated SQL from those queries with the Profiler, if you are using EF, for example. Don't make connection every time in the foreach loop.

如何访问数据库?例如，如果您使用EF，则使用Profiler检查生成的SQL。不要每次在foreach循环中建立连接。

I would not build the html on the server side as well. Just return the object for a page datasource control.

我也不会在服务器端构建html。只需返回页面数据源控件的对象。

#7

Use Indexed for the primary key field of the table and in the code behind use string builder.

将索引用于表的主键字段和后面的代码中，使用string builder。

#8

Worst problem there: tons of round trips to the database. Each time you get a value a request goes over the network and it waits for the result.

最糟糕的问题是:大量的往返数据库。每次你得到一个值，一个请求就会经过网络，等待结果。

If you must have the user list in code first, then make sure that:

如果您必须首先在代码中拥有用户列表，那么请确保:

1. You retrieve all the info in the user list in a single db call. If you have a set of user IDs, send you can send it with a table valued parameter.
2. 在一个db调用中检索用户列表中的所有信息。如果您有一组用户id，可以发送一个表值参数。
3. If the above didn't include the field values, send the list of user IDs and the list of field IDs in 2 table valued parameters to retrieve it all in one go.
4. 如果上面没有包含字段值，则将用户id列表和字段id列表分别发送到两个表值参数中，以一次性检索所有参数。

That should make a huge difference. With those 2 specific queries you have taken the network noise out of the way and can focus of improving the indexes if necessary.

这将产生巨大的影响。通过这两个特定的查询，您已经消除了网络噪声，并可以在必要时重点改进索引。

Another gain you will get is on the whole concatenating strings. First step is to replace with a StringBuilder. The next step is to write to the output stream directly, so you don't need to hold all that data in memory ... but it is unlikely you will need that; and if you do due to too much data you will have trouble with browsers handling that anyway.

您将获得的另一个好处是在整个连接字符串上。第一步是用StringBuilder替换。下一步是直接写入到输出流，因此不需要将所有数据保存在内存中……但你不太可能需要它;如果你因为太多的数据而这么做，你将会在浏览器处理这些数据时遇到麻烦。

ps. not the OP scenario, but for those needing speed in bulk, you want bulk export instead: http://technet.microsoft.com/en-us/library/ms175937.aspx

不是OP场景，但是对于需要批量速度的人，您需要批量导出:http://technet.microsoft.com/en-us/library/ms175937.aspx

#9

FAST... USE

快……使用

1. stored procedures
2. 存储过程

3. use reader

   使用读者

   SqlDataReader dbReader = mySqlCommand.ExecuteReader();
   
   //if reader has row values
   if (dbReader.HasRows) // while(xxx) for more rows return
   {
        //READ DATA
   }
   

4. DO PROPER INDEXES if need go for partitions...

   如果需要分区，请执行适当的索引……

5. Use and HINTs for SELECT NOLOCK work for me

   使用和提示为我选择NOLOCK工作

Query Hints (Transact-SQL) http://technet.microsoft.com/en-us/library/ms181714.aspx

查询提示(transact - sql)http://technet.microsoft.com/en-us/library/ms181714.aspx

Locking Hints http://technet.microsoft.com/en-us/library/aa213026(v=sql.80).aspx

锁定提示http://technet.microsoft.com/en-us/library/aa213026(v = sql.80). aspx

Yeah the only time I will use LINQ will be if I call a stored procedure.

是的，我唯一使用LINQ的时间是如果我调用一个存储过程。

Search LINQ to SQL

搜索LINQ to SQL

BUT I AM OLD SCHOOL....

但我老了学校....

This Entity Framework I get of rid of them since Entity Framework 1.0 is good when you do school project...

我去掉了这个实体框架，因为实体框架1.0在做学校项目的时候很好……

But is very expensive as compute instance...

但是作为计算实例是非常昂贵的……

READ ALL IN THE MEMORY DO SOMETHING???? WHY I AM PAYING FOR SQL? USE some JSON file structure then....

读遍所有的记忆做什么???? ?为什么我要为SQL付费?使用一些JSON文件结构然后....

#10

Instead of DataReader use DataAdapter and Dataset. Execute all queries one time as show below:

而不是DataReader使用DataAdapter和Dataset。执行所有查询一次，如下所示:

  string SqlQuery ="Select * from Users;Select * From DataFields;Select * From DataFieldsValues;";

This will open sqlconnection one time only fire all these three queries and return three different data table in dataset then use your method for rendering.

这将一次打开sqlconnection，只触发这三个查询并在dataset中返回三个不同的数据表，然后使用您的方法进行呈现。