I have a query in that query i have used subquery as left join which is why the query takes 6 min to bring 6942 records. after little bit optimiztion not it is taking 53 sec i am attaching the code below with comment. is there any way to optimize more
DECLARE @IDWebSite typIDWebSite = 1
DECLARE @IDZone typIDZone = 50
DECLARE @IDSection typIDSection = 17
DECLARE @IDLogin typIDLogin = 331
DECLARE @IDLanguage typIDLanguage = 'ITA'
DECLARE @IDProcessTypeCategory typIDProcessTypeCategory = 'VER'
DECLARE @IDCustomerType typIDCustomerType = NULL
DECLARE @IDCustomerJoin typIDCustomer = NULL
DECLARE @Search typTitle = NULL
DECLARE @IDBusiness typIDBusiness = NULL
DECLARE @IDProcessType typIDProcessType = NULL
DECLARE @IDStatus typIDStatus = NULL
SELECT tbi.IDBlobItem, tb.Blob AS Blob, tb.BlobDate, tb2.Business, tz.[Zone] AS [Zone],
COALESCE(tpsl.[Status], tsl.[Status], tps.[Status], ts.[Status]) AS [Status],
tc.Customer AS Customer, tbi.UrlRandom,
--tbirc.ClientContractReference,
--Invoice.CustomerOrder,Invoice.ExternalReference AS InvoiceNumber,
(SELECT MAX(tc2.CompanyName) FROM tabBlobItemCross AS tbic
INNER JOIN tabBlobItem AS tbi2 ON tbi2.IDBlobItem = tbic.IDBlobItemFather
INNER JOIN tabBlobItemInvoice AS tbii ON tbii.IDBlobItem = tbi2.IDBlobItem
INNER JOIN tabCustomer AS tc2 ON tc2.IDCustomer = tbii.IDCustomerHolder
WHERE tbic.IDBlobItem = tbi.IDBlobItem) AS Supplier
FROM tabBlob AS tb
INNER JOIN tabBlobItem AS tbi ON tbi.IDBlob = tb.IDBlob
INNER JOIN tabBusinessZoneProcess AS tbzp ON tbzp.IDBusinessZoneProcess = tbi.IDBusinessZoneProcess
INNER JOIN tabBusinessZoneProcessWebSiteZoneSection AS tbzpwszs ON tbzpwszs.IDBusinessZoneProcess = tbzp.IDBusinessZoneProcess
AND tbzpwszs.IDWebSite = @IDWebSite
AND tbzpwszs.IDZone = @IDZone
AND tbzpwszs.IDSection = @IDSection
INNER JOIN tabBusinessZone AS tbz ON tbz.IDBusiness = tbzp.IDBusiness AND tbz.IDZone = tbzp.IDZone
INNER JOIN tabBusiness AS tb2 ON tb2.IDBusiness = tbz.IDBusiness
INNER JOIN tabZone tz ON tz.IDZone = tbz.IDZone
INNER JOIN tabCustomer AS tc ON tc.IDCustomer = tb.IDCustomer
INNER JOIN tabProcessStatus AS tps ON tps.IDProcess = tbi.IDProcess AND tps.IDStatus = tbi.IDStatus
INNER JOIN tabStatus AS ts ON ts.IDStatus = tps.IDStatus
INNER JOIN tabProcess AS tp ON tp.IDProcess = tbzp.IDProcess AND tp.SwapRow = 0
INNER JOIN tabProcessType AS tpt ON tpt.IDProcessType = tp.IDProcessType AND tpt.IDProcessTypeCategory = @IDProcessTypeCategory
-- RntContract this part is now ok
LEFT JOIN
( SELECT tbicRntContract.IDBlobItem, tbirc.ClientContractReference
FROM tabBlobItemCross AS tbicRntContract
INNER JOIN tabBlobItem AS tbiRntContract ON tbicRntContract.IDBlobItemFather = tbiRntContract.IDBlobItem
INNER JOIN tabBlobItemRntContract AS tbirc ON tbirc.IDBlobItem = tbiRntContract.IDBlobItem
--INNER JOIN tabBlob AS tbRntContract ON tbRntContract.IDBlob = tbiRntContract.IDBlob --needed only for excel extract
INNER JOIN tabBusinessZoneProcess AS tbzpRntContract ON tbzpRntContract.IDBusinessZoneProcess = tbiRntContract.IDBusinessZoneProcess
INNER JOIN tabProcess AS tpRntContract ON tpRntContract.IDProcess = tbzpRntContract.IDProcess
INNER JOIN tabProcessType AS tptRntContract ON tptRntContract.IDProcessType = tpRntContract.IDProcessType AND tptRntContract.IDProcessTypeCategory = 'RNT'
) AS RntContract ON RntContract.IDBlobitem = tbi.IDBlobitem
--Invoice
LEFT JOIN
(SELECT tbicInvoice.IDBlobItem, tbInvoice.Blob, tbInvoice.ExternalReference,
NULL AS CustomerSupplier, tbiInvoice.IDBlobItem AS IDBlobItemInvoice --tbOrder.ExternalReference AS CustomerOrder
FROM tabBlobItemCross AS tbicInvoice
INNER JOIN tabBlobItem AS tbiInvoice ON tbicInvoice.IDBlobItemFather = tbiInvoice.IDBlobItem
INNER JOIN tabBlobItemInvoice AS tbii ON tbii.IDBlobItem = tbiInvoice.IDBlobItem
INNER JOIN tabBlob AS tbInvoice ON tbInvoice.IDBlob = tbiInvoice.IDBlob
INNER JOIN tabBusinessZoneProcess AS tbzpInvoice ON tbzpInvoice.IDBusinessZoneProcess = tbiInvoice.IDBusinessZoneProcess
INNER JOIN tabProcess AS tpInvoice ON tpInvoice.IDProcess = tbzpInvoice.IDProcess
INNER JOIN tabProcessType AS tptInvoice ON tptInvoice.IDProcessType = tpInvoice.IDProcessType AND tptInvoice.IDProcessTypeCategory = 'INV' AND tpInvoice.IDProcessType = 600
INNER JOIN tabStatus AS ts ON ts.IDStatus = tbiInvoice.IDStatus AND ts.IDStatusType <> 3
--INNER JOIN tabCustomer AS tcHolder ON tcHolder.IDCustomer = tbii.IDCustomerHolder
--RENTNG ORDER after moved here with invoice now taking 53 seconds
LEFT JOIN tabBlobItemCross AS tbicOrder ON tbicOrder.IDBlobItemFather = tbiInvoice.IDBlobItem
INNER JOIN tabBlobItem AS tbiOrder ON tbiOrder.IDBlobItem = tbicOrder.IDBlobItem
INNER JOIN tabBlobItemOrder AS tbio ON tbio.IDBlobItem = tbiOrder.IDBlobItem
INNER JOIN tabBlob AS tbOrder ON tbOrder.IDBlob = tbiOrder.IDBlob
INNER JOIN tabBusinessZoneProcess AS tbzpOrder ON tbzpOrder.IDBusinessZoneProcess = tbiOrder.IDBusinessZoneProcess
INNER JOIN tabProcess AS tpOrder ON tpOrder.IDProcess = tbzpOrder.IDProcess
INNER JOIN tabProcessType AS tptOrder ON tptOrder.IDProcessType = tpOrder.IDProcessType AND tptOrder.IDProcessTypeCategory = 'ORD'
INNER JOIN tabStatus AS tsOrder ON tsOrder.IDStatus = tbiOrder.IDStatus AND ts.IDStatusType <> 3
) AS Invoice ON Invoice.IDBlobItem = tbi.IDBlobItem
--RENTNG ORDER
--LEFT JOIN --bad optimization should be avoided moved to with invoice with uncommented this code took 6 min
--(SELECT tbicOrder.IDBlobItemFather, tbiOrder.IDBlobItem AS IDBlobItemOrder, tbOrder.ExternalReference
-- FROM tabBlobItemCross AS tbicOrder
-- INNER JOIN tabBlobItem AS tbiOrder ON tbiOrder.IDBlobItem = tbicOrder.IDBlobItem
-- INNER JOIN tabBlobItemOrder AS tbio ON tbio.IDBlobItem = tbiOrder.IDBlobItem
-- INNER JOIN tabBlob AS tbOrder ON tbOrder.IDBlob = tbiOrder.IDBlob
-- INNER JOIN tabBusinessZoneProcess AS tbzpOrder ON tbzpOrder.IDBusinessZoneProcess = tbiOrder.IDBusinessZoneProcess
-- INNER JOIN tabProcess AS tpOrder ON tpOrder.IDProcess = tbzpOrder.IDProcess
-- INNER JOIN tabProcessType AS tptOrder ON tptOrder.IDProcessType = tpOrder.IDProcessType AND tptOrder.IDProcessTypeCategory = 'ORD'
-- INNER JOIN tabStatus AS ts ON ts.IDStatus = tbiOrder.IDStatus AND ts.IDStatusType <> 3
--) AS RntOrder ON RntOrder.IDBlobItemFather = Invoice.IDBlobItemInvoice
LEFT JOIN tabProcessStatusLanguage AS tpsl ON tpsl.IDProcess = tps.IDProcess AND tpsl.IDStatus = tps.IDStatus AND tpsl.IDLanguage = @IDLanguage
LEFT JOIN tabStatusLanguage AS tsl ON tsl.IDStatus = ts.IDStatus AND tsl.IDLanguage = @IDLanguage
WHERE tbzp.IDBusiness = ISNULL(@IDBusiness, tbzp.IDBusiness)
AND tbi.IDStatus = ISNULL(@IDStatus, tbi.IDStatus)
