Need help

Question

Indexes fragmentation level is very high after rebuild. Need help what is do for best database performance? Database queries still running slow....

Jignesh Trivedi · Answer

hi SQL server execute these all queries individual and union to all queries and gave result. just run and check all query which take more time all take more time.... for example first run select count(OuterboxNo)as TQty0 as SQty from wms_bulk_transaction where status='PUT' and concept='FL' and slot_code in (select slot_code from wms_slot_master sltwms_aielse_master ail where slt.aielse_code=ail.aielse_code and ail.aielse_type='HR' and cyclecountnumber='CNT1300005') after other...

Ravi Shekhar · Answer

all are in one query..

Jignesh Trivedi · Answer

hi here you four query and you union to these query first of all check which query takes more time to execute?

Ravi Shekhar · Answer

Sir below query takes more nd more time.... select sum(T.TQty)TQtysum(T.SQty)SQty from ( select count(OuterboxNo)as TQty0 as SQty from wms_bulk_transaction where status='PUT' and concept='FL' and slot_code in (select slot_code from wms_slot_master sltwms_aielse_master ail where slt.aielse_code=ail.aielse_code and ail.aielse_type='HR' and cyclecountnumber='CNT1300005') union select count(MonoboxNo)as TQty0 as SQty from wms_loose_transaction where status='PUT' and concept='FL' and slot_code in (select slot_code from wms_slot_master sltwms_aielse_master ail where slt.aielse_code=ail.aielse_code and (ail.aielse_type='SA' OR ail.aielse_type='MA') and cyclecountnumber='CNT1300005') union select 0 as TQtycount(OuterboxNo) as SQty from wms_bulk_transaction where status='PUT' and concept='FL' and slot_code in (select slot_code from wms_slot_master sltwms_aielse_master ail where slt.aielse_code=ail.aielse_code and ail.aielse_type='HR' and cyclecountnumber='CNT1300005') and (cyclecountdone='CNT1300005' or cyclecountdone is not null) union select 0 as TQtycount(MonoboxNo) as SQty from wms_loose_transaction where status='PUT' and concept='FL' and slot_code in (select slot_code from wms_slot_master sltwms_aielse_master ail where slt.aielse_code=ail.aielse_code and (ail.aielse_type='SA' OR ail.aielse_type='MA') and cyclecountnumber='CNT1300005') and (cyclecountdone='CNT1300005' or cyclecountdone is not null) )T

Jignesh Trivedi · Answer

hi Data base query performance is very critical area. Creating Index is not just upgrade database performance. you have to study query(table) static base on this you have to change your query and join... also study execution plan of your query...