Reputation: 85
This basic query is throwing a System.OutOfMemoryException error after I join job_price_line to job_price_hdr
Would creating a temp table speed up this query? Im not understanding other explanations I have read on this topic. Thanks!
select
oe_line.qty_invoiced,
invoice_hdr.invoice_no,
invoice_hdr.invoice_date,
invoice_line.unit_price,
invoice_line.item_desc,
invoice_line.customer_part_number,
invoice_line.pricing_unit,
invoice_hdr.ship_to_id,
invoice_hdr.po_no,
invoice_hdr.ship_to_id,
invoice_line.item_id,
invoice_hdr.customer_id,
job_price_hdr.contract_no,
job_price_hdr.cancelled,
job_price_line.line_no,
invoice_hdr.sales_location_id
from invoice_hdr
join invoice_line on invoice_line.invoice_no = invoice_hdr.invoice_no
join oe_line on oe_line.order_no = invoice_hdr.order_no
join job_price_hdr on job_price_hdr.corp_address_id = invoice_hdr.corp_address_id
join job_price_line on job_price_line.job_price_hdr_uid = job_price_hdr.job_price_hdr_uid
where invoice_hdr.invoice_date between ('2016-05-02') and ('2016-05-03')
and job_price_hdr.cancelled = 'N'
and invoice_hdr.sales_location_id = '200'
Upvotes: 0
Views: 187
Reputation:
No matter of speed is going to solve an out of memory exception. It looks like your last join has multiplied up heavily the number of records your returning. Try replacing your fields list with count(*) to see how many records your getting back first.
Upvotes: 1