I have a query that perform very slow maybe because it pulled out a large number of records. I come a a solution to breakdown the data into separate temporary table. I create a temp table for finished good history and another one for shipment history. I run the finished history by monthly then the shipment is year to date.
When i perform the query (see below) it takes time. my question. does will affect the performance of server if i will continue to run this query until completely obtain the records. This will create a slow down of server performance?
The records of finished good is 65000 the shipment is 900000 records. for 12 minutes of running i generate only 14612 records out of 65000. thank you.
,fgdate as po_endeddate
,datediff(dd,x.fgdate, v.ship_date ) as 'AGE_FG_toShipment'
,datediff(dd,x.audit_date, v.ship_date ) as 'AGE_ESNreceipt_toShipment'
from #tempjs_details_scans x
( --this portion stored history transaction
select top 1 esn_no , ship_date from #tempjs_shipped_scans m where m.ship_date >=x.fgdate
and m.esn_no = x.esn_no COLLATE SQL_Latin1_General_CP850_CI_AS order by m.ship_date asc
where op_desc = 'Finished Good'
order by esn_no, prodid