SELECT T.OuterboxNo,T.MonoboxNo,T.GateInNo AS [Gate In No],T.CONCEPT,T.Category,T.ArtNo,T.[Aielse Code],T.[Slot Code],T.[Box Status] AS Status,
(
CASE WHEN T.[Box Status]='N' THEN 'Steging progress for this box'
WHEN T.[Box Status]='REC' THEN 'Steging done for this box'
WHEN T.[Box Status]='GRN' THEN 'GRN done for this box'
WHEN T.[Box Status]='WIP' THEN 'Putaway progress for this box'
WHEN T.[Box Status]='PUT' THEN 'Putaway done for this box'
WHEN T.[Box Status]='LDW' THEN 'Box is suggested for Letdown'
WHEN T.[Box Status]='LDP' THEN 'Letdown progress for this box'
WHEN T.[Box Status]='LTD' THEN 'Letdown done for this box'
WHEN T.[Box Status]='PUT' THEN 'Putaway done for this box'
WHEN T.[Box Status]='PIK' THEN 'Box already picked'
WHEN T.[Box Status]='RPK' THEN 'Repacking done for this box'
WHEN T.[Box Status]='LOD' THEN 'Loading done for this box'
WHEN T.[Box Status]='DSP' THEN 'Box dispatched'
ELSE T.[Box Status] END
) [Box Status]
,
T.[Picklist No.],T.InvoiceNo as [Invoice No.],T.[IS Box OK],
(CASE WHEN T.StatusTemp1 ='H' THEN '' WHEN T.StatusTemp1 IS NULL THEN '' ELSE T.StatusTemp1 END)AS [Stock Transfer No.],T.CYCLECOUNTDONE,
T.Area
FROM
(
SELECT BLK.OuterboxNo,MAP.MonoboxNo,BLK.GateInNo,BLK.CONCEPT,BLK.Category,BLK.ArtNo,BLK.aielse_code as [Aielse Code],BLK.slot_code as [Slot Code],
BLK.status as [Box Status],BLK.StatusTemp as [Picklist No.],BLK.InvoiceNo,BLK.OKStatus as [IS Box OK],
BLK.StatusTemp1,BLK.CYCLECOUNTDONE,'High Rack'as Area FROM WMS_BULK_TRANSACTION BLK LEFT JOIN WMS_BULK_MAPPING MAP
ON BLK.OuterboxNo=MAP.OuterboxNo WHERE (BLK.OuterboxNo='2122Z4200000000000027808,01420,101274,2122Z,FA$8000220-060,20#' OR MAP.MONOBOXNO='2122Z4200000000000027808,01420,101274,2122Z,FA$8000220-060,20#')
UNION all
SELECT '' AS OuterboxNo, MonoboxNo,GateInNo,CONCEPT,Category,Left(MonoboxNo,7)ArtNo,aielse_code as [Aielse Code],slot_code as [Slot Code],
status as [Box Status],StatusTemp as [Picklist No.],InvoiceNo,OKStatus as [IS Box OK],
StatusTemp1,CYCLECOUNTDONE,'Sloted Area'as Area FROM WMS_LOOSE_TRANSACTION WHERE (MonoboxNo='2122Z4200000000000027808,01420,101274,2122Z,FA$8000220-060,20#' OR OuterboxNo='2122Z4200000000000027808,01420,101274,2122Z,FA$8000220-060,20#')
)T
I am sharing my sql query. I have total data around 40lacs(Total count) in wms_loose_tran ,wms_bulk_tran,wms_bulk_mapping. I checked in execution plan. it gives no suggesstion to make any indexes. My query takes around 35-50 seconds to execute. Now pls suggest me it always occur timeout.
Please give me suggession what i do.