I have a vb.net/SQL Server client app in which I need to add a left join to an existing query. This same code in the data layer is used by asp.net to retrieve the data for the web version of the app. The next two code blocks show the entire original statement and the new statement with all the joins. I suggest you skip down to the lower code blocks, which are much smaller to illustrate the issue. Original query:
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
New query:
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
The new query adds a left join to a second table (qasr_metric) to determine if the second table contains any associated rows with a value of 1 in column "D". The qasr_metric table is indexed by (quasr_id, d), which is why the performance is minimally impacted. Removing all the other columns and joins boils down to the difference between:
select q.qasr_id as Id from qasr q
and
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
The performance of the new query on the asp.net side of the app is within about 3 seconds of the original query and the same is true if I run the queries separately in SQL Server Mgmt Studio. However, the client side of the app processes the results of the original query in seconds but takes over an hour for the new query, which is absurd. Looking at the vb code itself, the data layer calls ExecuteReader with the parameterized query, which completes almost immediately for both the original and new query. The problem occurs back in the business layer that is called with dr as the reader populated by ExecuteReader. The data returned in each row is used to populate a new object (InspectionDocumentSummary) and a list of these objects is used to populate a DataGridView displayed to the user. Combining a few of the subs that create the new objects for brevity gives:
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
For the original query, this completes almost immediately but the new query takes over an hour. It's my understanding the ExecuteReader retrieves the data and the dr.Read loop simply reads the returned data into the DataGridView being populated (there is no other processing of the data in my case). I replaced "_dispositioned = dr.GetString("Dispositioned")" in the above code with "_dispositioned = "Yes"" with no change so the data read itself is not the problem. Any suggestions?
