Slow SafeDataReader循环
问题描述：
我有一个vb.net/SQL Server客户端应用程序，我需要在其中添加左连接到现有查询。 asp.net使用数据层中的相同代码来检索Web版本应用程序的数据。接下来的两个代码块显示了整个原始语句以及包含所有连接的新语句。我建议你跳到较低的代码块，这些代码块要小得多来说明问题。 原始查询：Slow SafeDataReader循环
select q.qasr_id as Id,
q.qasr_identification_number as IdentificationNumber,
q.status as Status,
b.business_unit_name as BusinessUnitName,
b.business_unit_id as BusinessUnitId,
u.login as Originator,
q.date_created as DateCreated,
q.po_number as PoNumber,
q.po_part_number as PoPartNumber,
q.po_part_rev as PoPartRev,
q.lot_quantity as LotQuantity,
q.control_number as ControlNumber,
q.item_number as PoItem,
q.buyer as Buyer,
q.buyercode as BuyerCode,
q.spe as Spe,
p.program as ProgramName,
l.product_line as productline,
s.ent_supplier_nbr as SupplierEsd,
s.sup_name1 as SupplierName,
c.commodity_code as CommodityCode,
a.agency_name as AgencyName,
q.verifyid as AgencyDocId,
q.activity as Activity,
case q.isdeleted when 1 then 'Deleted' else '' end as Deleted
from qasr q
join auth_user u on q.auth_user_id = u.auth_user_id
join functional_area f on q.functional_area_id = f.functional_area_id
join business_unit b on f.business_unit_id = b.business_unit_id
left join suppliers s on q.supplier_id = s.supplier_id
left join programs p on q.program_id = p.program_id
left join product_lines l on p.product_line_id = l.product_line_id
left join commodities c on q.commodity_id = c.commodity_id
left join agency a on q.agency_id = a.agency_id
where b.business_unit_id = @BusinessUnitId
and q.isdeleted = @IsDeleted
新建查询：
select q.qasr_id as Id,
q.qasr_identification_number as IdentificationNumber,
q.status as Status,
b.business_unit_name as BusinessUnitName,
b.business_unit_id as BusinessUnitId,
u.login as Originator,
q.date_created as DateCreated,
q.po_number as PoNumber,
q.po_part_number as PoPartNumber,
q.po_part_rev as PoPartRev,
q.lot_quantity as LotQuantity,
q.control_number as ControlNumber,
q.item_number as PoItem,
q.buyer as Buyer,
q.buyercode as BuyerCode,
q.spe as Spe,
p.program as ProgramName,
l.product_line as productline,
s.ent_supplier_nbr as SupplierEsd,
s.sup_name1 as SupplierName,
c.commodity_code as CommodityCode,
a.agency_name as AgencyName,
q.verifyid as AgencyDocId,
q.activity as Activity,
case q.isdeleted when 1 then 'Deleted' else '' end as Deleted,
case when m.d_count > 0 then 'Yes' else 'No' end as Dispositioned
from qasr q
join auth_user u on q.auth_user_id = u.auth_user_id
join functional_area f on q.functional_area_id = f.functional_area_id
join business_unit b on f.business_unit_id = b.business_unit_id
left join suppliers s on q.supplier_id = s.supplier_id
left join programs p on q.program_id = p.program_id
left join product_lines l on p.product_line_id = l.product_line_id
left join commodities c on q.commodity_id = c.commodity_id
left join agency a on q.agency_id = a.agency_id
left join (select qasr_id as qasr_id, count(1) as d_count
from qasr_metric
where d = 1
group by qasr_id) m on m.qasr_id = q.qasr_id
where b.business_unit_id = @BusinessUnitId
and q.isdeleted = @IsDeleted
新的查询增加了一个左连接到第二个表（qasr_metric），以确定第二个表包含为1的值的任何关联的行在“D”栏中。 qasr_metric表由（quasr_id，d）索引，这就是性能受到最小影响的原因。删除所有其他列，并加入归结之间的区别：
select q.qasr_id as Id from qasr q
和
select q.qasr_id as Id,
case when m.d_count > 0 then 'Yes' else 'No' end as Dispositioned
from qasr q
left join (select qasr_id as qasr_id, count(1) as d_count
from qasr_metric
where d = 1
group by qasr_id) m on m.qasr_id = q.qasr_id
上的应用程序的asp.net侧新的查询的性能大约3秒内原始查询，如果我在SQL Server Mgmt Studio中单独运行查询，情况也是如此。但是，应用程序的客户端在几秒钟内处理原始查询的结果，但对于新查询需要一个多小时，这很荒唐。查看vb代码本身，数据层使用参数化查询调用ExecuteReader，对于原始查询和新查询都几乎立即完成。问题发生在由dr作为由ExecuteReader填充的阅读器调用的业务层中。每行中返回的数据用于填充新对象（InspectionDocumentSummary），并使用这些对象的列表填充显示给用户的DataGridView。结合几个是为了简洁创建新对象的潜艇给：
Private Sub Fetch(ByVal dr As SafeDataReader)
Using dr
While dr.Read()
Add(InspectionDocumentSummary.GetInspectionDocumentSummary(dr))
_id = dr.GetGuid("Id")
_identificationNumber = dr.GetString("IdentificationNumber")
_status = dr.GetString("Status")
_originator = dr.GetString("Originator")
_dateCreated = dr.GetSmartDate("DateCreated")
_poPartNumber = dr.GetString("PoPartNumber")
_poPartRev = dr.GetString("PoPartRev")
_lotQuantity = Convert.ToString(dr.GetInt32("LotQuantity"))
_buyer = dr.GetString("Buyer")
_spe = dr.GetString("Spe")
_agencyDocId = dr.GetString("AgencyDocId")
_agencyName = dr.GetString("AgencyName")
_activity = dr.GetString("Activity")
_controlNumber = dr.GetString("ControlNumber")
_dispositioned = dr.GetString("Dispositioned")
_programName = dr.GetString("ProgramName")
_supplierEsd = dr.GetString("SupplierEsd")
_supplierName = dr.GetString("SupplierName")
_commodityCode = dr.GetString("CommodityCode")
End While
End Using
End Sub
对于原来的查询，这几乎立即完成，但新的查询需要一个多小时。这是我的理解，ExecuteReader检索数据，dr.Read循环只是将返回的数据读入正在填充的DataGridView中（在我的情况下没有其他数据处理）。在上面的代码中，我将“_dispositioned = dr.GetString（”Dispositioned“）”替换为“_dispositioned =”Yes“”，所以数据读取本身不是问题。有什么建议么？
答
像这样的许多问题都是为了在查询中将字符串转换为字符串，例如比较或将varchar one one side与int另一端进行比较或连接。
如果查询没有性能问题，从数据库中直接运行试试你运行你的查询，并让.NET做你的数据类型转换的数据读取器读取该行这样的后：
Private Sub Fetch(ByVal dr As SafeDataReader)
Using dr
While dr.Read()
Add(InspectionDocumentSummary.GetInspectionDocumentSummary(dr))
_id = GUID.Parse(dr("Id"))
_identificationNumber = dr("IdentificationNumber")
_status = dr("Status")
_originator = dr("Originator")
_dateCreated = dr.GetSmartDate("DateCreated")
_poPartNumber = dr("PoPartNumber")
_poPartRev = dr("PoPartRev")
_lotQuantity = Convert.ToString(dr.GetInt32("LotQuantity"))
_buyer = dr("Buyer")
_spe = dr("Spe")
_agencyDocId = dr("AgencyDocId")
_agencyName = dr("AgencyName")
_activity = dr("Activity")
_controlNumber = dr("ControlNumber")
_dispositioned = dr("Dispositioned")
_programName = dr("ProgramName")
_supplierEsd = dr("SupplierEsd")
_supplierName = dr("SupplierName")
_commodityCode = dr("CommodityCode")
End While
End Using
End Sub
如果没有按无需拨打Add来帮助尝试运行它，以确保该方法中的某些内容不会导致问题。
所以有两件事。 1，如果你没有调用'Add'，是否有问题？ 2）使用dr（ColumnName）而不是dr.GetString（ColumnName）尝试查询，因为可能过多的查询时间是由于在查询中将类型转换为varchar。 –