如何提高这个功能的性能？

This function takes around 1.2 seconds to execute. I am unable to understand why? Is it because of the inner joins? If yes, then how can i improve the execution speed? I am using Microsoft Enterprise Library.

此功能大约需要1.2秒才能执行。我无法理解为什么？是因为内部连接？如果是，那我怎样才能提高执行速度？我正在使用Microsoft企业库。

  Public Shared Function GetDataByInterests(ByVal accountId As Integer) As Object


            Dim details As New List(Of GetIdBasedOnInterest)()
            Dim getIDs As New GetIdBasedOnInterest

            Dim interests As String = ""

            Dim db As SqlDatabase = Connection.Connection


            Using cmdGeneric As DbCommand = db.GetSqlStringCommand("SELECT Interests.InterestName FROM UserInterests INNER JOIN Interests ON UserInterests.InterestID = Interests.InterestID WHERE UserInterests.AccountID=@AccountID")
                db.AddInParameter(cmdGeneric, "AccountID", SqlDbType.Int, accountId)
                Dim dsInterests As DataSet = db.ExecuteDataSet(cmdGeneric)
                For i = 0 To dsInterests.Tables(0).Rows.Count - 1
                    If i = dsInterests.Tables(0).Rows.Count - 1 Then
                        interests = interests & dsInterests.Tables(0).Rows(i).Item(0).ToString
                    Else
                        interests = interests & dsInterests.Tables(0).Rows(i).Item(0).ToString & ","
                    End If
                Next
            End Using
    getIDs.InterestName = interests
            details.Add(getIDs)

            Return details
        End Function

2 个解决方案

#1

Without knowing anything of the underlying tables and their indexes (and this is a check you should do immediately) there is an obvious problem in your loop.
You cancatenate strings, this, potentially could pose a strong pressure on the memory used by your program.
A string concatenation results in a new string allocated on the memory and thus, if your table contains many rows, the effect could be noticeable.

在不知道底层表及其索引的任何内容的情况下（这是一个应该立即执行的检查），循环中存在明显的问题。你可以使用字符串，这可能会对程序使用的内存造成很大的压力。字符串连接导致在内存上分配新的字符串，因此，如果您的表包含许多行，则效果可能会很明显。

You could try to use a StringBuilder

您可以尝试使用StringBuilder

Dim interests As new StringBuilder(1024) ' suppose an internal buffer of 1K'
...

If i = dsInterests.Tables(0).Rows.Count - 1 Then
    interests.Append(dsInterests.Tables(0).Rows(i).Item(0).ToString)
Else
    interests.Append(dsInterests.Tables(0).Rows(i).Item(0).ToString & ",")
End If

....

getIDs.InterestName = interests.ToString

Of course this optimization could be absolutely not important if your tables (UserInterests and Interests) are not correctly indexed on the fields InterestID and AccountID

当然，如果您的表（UserInterests和Interests）没有在InterestID和AccountID字段上正确编入索引，那么这种优化可能绝对不重要

EDIT: Another micro-optimization is to remove the internal IF test and truncate the resulting output only after the loop ends

编辑：另一个微优化是删除内部IF测试并在循环结束后截断结果输出

For ....
    interests.Append(dsInterests.Tables(0).Rows(i).Item(0).ToString & ",")
Next
if(interest.Length > 0) interest.Length -= 1;

EDIT As for your request, this is an example to create an unique index. The syntax could be more complex and varying depending on the Sql Server version, but basically you do this in Sql Management Studio

编辑对于您的请求，这是创建唯一索引的示例。语法可能更复杂，并且根据Sql Server版本而变化，但基本上您在Sql Management Studio中执行此操作

CREATE UNIQUE INDEX <indexname> ON <tablename>
(
   <columntobeindexed>
)

Check the CREATE INDEX statement examples on MSDN

检查MSDN上的CREATE INDEX语句示例

#2

1) Time your query in SQL Server Management studio. It will be much easier to tune it there in isolation from your VB code. Also you can run the display the query plan, and it ight even suggest new indexes.

1）在SQL Server Management Studio中查询您的查询。将它与VB代码隔离在一起更加容易。您还可以运行显示查询计划，甚至可以建议新索引。

2) Check you have the relevent primary keys and indexes defined.

2）检查是否已定义相关的主键和索引。

3) Pull common expressions out of your for loop, to avoid recomputing the same thing over and over:

3）从for循环中拉出常用表达式，以避免反复重复计算同一个东西：

4) Like Steve says, use a StringBuilder

4）像Steve说的那样，使用StringBuilder

Combining those points:

结合这些要点：

            Dim theTable as ...
            Dim rowCount as Integer
            Dim interests As new StringBuilder(1024)
            Set theTable = dsInterests.Tables(0)
            rowCount = theTable.Rows.Count 
            For i = 0 To rowCount  - 1
                interests.Append(theTable.Rows(i).Item(0).ToString)
                If i <> rowCount - 1 Then
                    interests.Append(",")
                End If
            Next

#1