CODEWITHSUNDEEP
sqlsql-serverperformance
eggwhites
eggwhites

Reputation: 85

Temp table to speed up this query

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

Answers (1)

user3572680
user3572680

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

Related Questions