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
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
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
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
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