Avoid VIEWs in queries returning large data sets

20 July 2012 Amjad Hussain Optimization of Query/SP/DB, 0

In queries which returns large data set, avoid using complicated views in main query join part. It contribute in increasing of query execution time. The solution is to make the temporary table from the complicated views and created the index on joining fields with main query. This improve the significant time of main query execution. This was experienced while optimizing MBAFC procedure SP_contract_rep_detail. See below about changes in query.

======
Before
======

In main query join with view

LEFT JOIN contract_overdues_view COV

ON COV.contract_id = C.contract_id

======
After
======

Create temp table

select contract_id,contract_delinquent_interest into #contract_od_view

from contract_overdues_view

create index tmp_idx_contract_od_view on #contract_od_view(contract_id)

In main query join with Temp table

LEFT JOIN #contract_od_view COV

ON COV.contract_id = C.contract_id

This post was viewed (41) times.

VUGen in LoadRunner Fasting incentive for Muneeb Kayani