Indexes fragmentation level is very high after rebuild. Need help what is do for best database performance?
Database queries still running slow....
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Jignesh TrivediPosted Nov 26, 2013, 1:37 AM
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 TQty,0 as SQty from wms_bulk_transaction where status='PUT' and concept='FL'
and slot_code in (select slot_code from wms_slot_master slt,wms_aielse_master ail where
slt.aielse_code=ail.aielse_code and ail.aielse_type='HR' and cyclecountnumber='CNT1300005')
after other...
Ravi ShekharPosted Nov 26, 2013, 1:32 AM
Jignesh TrivediPosted Nov 26, 2013, 1:20 AM
hi,
here you four query and you union to these query
first of all, check which query takes more time to execute?
Ravi ShekharPosted Nov 26, 2013, 1:13 AM
select sum(T.TQty)TQty,sum(T.SQty)SQty from
(
select count(OuterboxNo)as TQty,0 as SQty from wms_bulk_transaction where status='PUT' and concept='FL'
and slot_code in (select slot_code from wms_slot_master slt,wms_aielse_master ail where
slt.aielse_code=ail.aielse_code and ail.aielse_type='HR' and cyclecountnumber='CNT1300005')
union
select count(MonoboxNo)as TQty,0 as SQty from wms_loose_transaction where status='PUT' and concept='FL' and
slot_code in (select slot_code from wms_slot_master slt,wms_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 TQty,count(OuterboxNo) as SQty from wms_bulk_transaction where status='PUT' and concept='FL' and
slot_code in (select slot_code from wms_slot_master slt,wms_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 TQty,count(MonoboxNo) as SQty from wms_loose_transaction where status='PUT' and concept='FL' and
slot_code in (select slot_code from wms_slot_master slt,wms_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 TrivediPosted Nov 26, 2013, 1:00 AM
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...