嵌套的select语句花费太长时间才能加载到SQL Server上
我有一个在网格上显示报告的页面。 网格使用绑定到返回数据的类的Object数据源。 该类本身使用标准SQL查询来返回记录数并绑定到数据视图。 我们遇到的问题是有时需要大约10分钟才能加载，我知道必须有更好的方法，但不能为我的生活找出什么。 希望从任何人那里得到一些关于如何优化性能的见解。 数据类如下所示：任何反馈将不胜感激。 律师认为，约有650名律师被绑定到2张表格：律师和地点表。 执行计数的观点也被绑定到2个表格：当前案例和以前的案例表格，总共返回大约125,000个案例。 缓存是不可能的，因为最终用户将能够提供任何开始和结束日期来生成报告。
Dim PendingStringBuilder As String = "((dbo.cases.attorney_id = dbo.attorneys.att_id) AND (dbo.cases.date_assigned = @StartDate OR dbo.cases.closing_date IS NULL)) OR ((dbo.casepreviousattorneys.attorney_id =
dbo.attorneys.att_id) AND (dbo.casepreviousattorneys.previous_assignment_date = @StartDate OR dbo.casepreviousattorneys.unassignment_date IS NULL))"
Dim AssignedStringBuilder As String = "((dbo.cases.attorney_id = dbo.attorneys.att_id) AND (dbo.cases.date_assigned >= @StartDate) AND (dbo.cases.date_assigned = @StartDate) AND (dbo.casepreviousattorneys.previous_assignment_date
Dim CountTable As String = " dbo.cases WITH (NOLOCK) INNER JOIN dbo.tlkpcasetype ON dbo.cases.case_type_id = dbo.tlkpcasetype.case_type_id FULL OUTER JOIN dbo.casepreviousattorneys ON dbo.cases.case_no = dbo.casepreviousattorneys.case_no"
Dim dt As New DataTable("ReportTable")
Dim dr As DataRow
dt.Columns.Add("CasesPending", Type.[GetType]("System.Int32"))
dt.Columns.Add("CasesAssigned", Type.[GetType]("System.Int32"))
dt.Columns.Add("ProbationViolation", Type.[GetType]("System.Int32"))
dt.Columns.Add("BailOnly", Type.[GetType]("System.Int32"))
dt.Columns.Add("TotalCases", Type.[GetType]("System.Int32"))
dt.Columns.Add("AttorneyID", Type.[GetType]("System.Int32"))
dt.Columns.Add("AttorneyName", Type.[GetType]("System.String"))
dt.Columns.Add("AttorneyFirstName", Type.[GetType]("System.String"))
dt.Columns.Add("AttorneyLastName", Type.[GetType]("System.String"))
dt.Columns.Add("UnitID", Type.[GetType]("System.Int32"))
dt.Columns.Add("UnitName", Type.[GetType]("System.String"))
dt.Columns.Add("UnitType", Type.[GetType]("System.String"))
dt.Columns.Add("OfficeID", Type.[GetType]("System.Int32"))
dt.Columns.Add("Office", Type.[GetType]("System.String"))
If cn.State = ConnectionState.Closed Then cn.Open()
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
strSQL = "SELECT DISTINCT dbo.attorneys.user_id, dbo.attorneys.att_id AS AttorneyID, dbo.attorneys.first_name +' '+ dbo.attorneys.last_name AS AttorneyName, dbo.attorneys.unit_id AS UnitID, dbo.tlkpunit.unit AS UnitName, dbo.tlkpunit.unit_type AS UnitType,
dbo.tlkpunit.office_id AS OfficeID, dbo.tlkpoffice.office AS Office, "
strSQL += "(SELECT COUNT(DISTINCT dbo.cases.case_no) AS ExprCasesPending FROM " & CountTable & " WHERE (" & PendingStringBuilder & ")) As CasesPending, "
strSQL += "(SELECT COUNT(DISTINCT dbo.cases.case_no) AS ExprCasesAssigned FROM " & CountTable & " WHERE (dbo.tlkpcasetype.case_type 'Probation Violation') AND (dbo.tlkpcasetype.case_type 'Bail Only') AND (" & AssignedStringBuilder & ")) As CasesAssigned,
"
strSQL += "(SELECT COUNT(DISTINCT dbo.cases.case_no) AS ExprProbationViolation FROM " & CountTable & " WHERE (dbo.tlkpcasetype.case_type = 'Probation Violation') AND (" & AssignedStringBuilder & ")) As ProbationViolation, "
strSQL += "(SELECT COUNT(DISTINCT dbo.cases.case_no) AS ExprBailOnly FROM " & CountTable & " WHERE (dbo.tlkpcasetype.case_type = 'Bail Only') AND (" & AssignedStringBuilder & ")) As BailOnly, "
strSQL += "(SELECT COUNT(DISTINCT dbo.cases.case_no) AS ExprTotalCases FROM " & CountTable & " WHERE (" & AssignedStringBuilder & ")) As TotalCases "
strSQL += " FROM dbo.attorneys WITH (NOLOCK) LEFT OUTER JOIN dbo.tlkpunit ON dbo.attorneys.unit_id = dbo.tlkpunit.unit_id LEFT OUTER JOIN dbo.tlkpdivision ON dbo.tlkpunit.division_id = dbo.tlkpdivision.division_id LEFT OUTER JOIN dbo.tlkpoffice ON dbo.tlkpunit.office_id
= dbo.tlkpoffice.office_id WHERE (dbo.tlkpunit.unit 'test-unit') "
cmd = New SqlCommand(strSQL, cn)
cmd.Parameters.AddWithValue("@StartDate", DateAStart)
cmd.Parameters.AddWithValue("@EndDate", DateAEnd)
rdr = cmd.ExecuteReader()
While rdr.Read
If rdr("CasesPending").ToString = 0 And rdr("CasesAssigned") = 0 And rdr("ProbationViolation").ToString = 0 And rdr("BailOnly") = 0 Then
'Do not add record
Else
dr = dt.NewRow()
dr("CasesPending") = CInt(rdr("CasesPending"))
dr("CasesAssigned") = CInt(rdr("CasesAssigned"))
dr("ProbationViolation") = CInt(rdr("ProbationViolation"))
dr("BailOnly") = CInt(rdr("BailOnly"))
dr("TotalCases") = CInt(rdr("TotalCases"))
dr("AttorneyID") = rdr("AttorneyID")
dr("AttorneyName") = rdr("AttorneyName")
dr("UnitID") = rdr("UnitID")
dr("UnitName") = rdr("UnitName")
dr("UnitType") = rdr("UnitType")
dr("OfficeID") = rdr("OfficeID")
dr("Office") = rdr("Office")
dt.Rows.Add(dr)
End If
End While
rdr.Close()
cmd.Dispose()
If cn.State = ConnectionState.Open Then cn.Close()
Dim dv As New DataView(dt)
dv.Sort = "AttorneyName ASC"
Return dv
阅读“SQL执行计划”，你可能想查看你的表索引。 这些事情很可能会产生最大的结果。 有关更多信息，请参阅此SQL Server优化MSDN文章。
我也注意到你的VB代码中你没有参数化你的SQL字符串。 在上述之后，您应该考虑这样做以获得额外的性能优势。
有关使用SQL参数的更多信息，请参阅：
http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html http://technet.microsoft.com/en-us/library/ms186219的.aspx
尝试使用存储过程。 这将会在Sql Server中编译代码，并提前执行计划。 约翰
链接地址: http://www.djcxy.com/p/44027.html上一篇: nested select statements taking too long to load on SQL server