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.
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.
Sunny SharmaPosted Aug 14, 2013, 2:55 AM
How about creating Index on these columns, will definitely increase the processing time at DB server end. Coming to your problem, just increase the Timeout property as required (say-60, as you said it takes around 30-35 secs.)
cmd.CommandTimeout=60;
Feedback awaited.
Hope it helps :)
Ravi ShekharPosted Aug 14, 2013, 3:37 AM
and for timeout problem your ans helped me.
thanks for reply
Sunny SharmaPosted Aug 14, 2013, 3:27 AM
This is something different than your actual problem. My reply was on just to remove the timeout error. Now to minimize the response while searching some categorized within 40 Lacs of records will always tend to be expensive in terms of time. Create Index on the columns that are involved in your query and you'll surely get an increase in response time.
Hope it helps :)
Ravi ShekharPosted Aug 14, 2013, 3:19 AM
now query working fine. but sir if user want to search somthing and if it takes 40 sec to search . that means
this is too much slow.
is there any other option to search fast with above query
Sunny SharmaPosted Aug 14, 2013, 3:15 AM
Like you said that it's around 40 lacs of records, I think the response time is OK and your query is all good.
Did you try increasing the CommandTimeout of your SqlCommand object?
Ravi ShekharPosted Aug 14, 2013, 2:59 AM
Ravi ShekharPosted Aug 14, 2013, 2:58 AM
can i change or in below line with
something???