The optimization process and recommendations:

1. Avoid Correlated Subqueries (query line: 16): A correlated subquery is a subquery that contains a reference (column: KodeBarang) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
2. Avoid Correlated Subqueries (query line: 21): A correlated subquery is a subquery that contains a reference (column: KodeBarang) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
3. Avoid Correlated Subqueries In Select Clause (modified query below): The aggregation function located in a subquery inside the SELECT clause, is executed once for every matched row. Extracting this subquery to a temporary table will improve performance significantly.
4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `MsBarang` ADD INDEX `msbarang_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `PenjualanDTL` ADD INDEX `penjualandtl_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `SPKJahit` ADD INDEX `spkjahit_idx_nospkjahit` (`NoSPKJahit`);
ALTER TABLE `StokBS` ADD INDEX `stokbs_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp1` ADD INDEX `es_temp1_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp2` ADD INDEX `es_temp2_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp3` ADD INDEX `es_temp3_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp4` ADD INDEX `es_temp4_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp5` ADD INDEX `es_temp5_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp6` ADD INDEX `es_temp6_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp7` ADD INDEX `es_temp7_idx_kodebarang` (`KodeBarang`);
ALTER TABLE `es_temp8` ADD INDEX `es_temp8_idx_kodebarang` (`KodeBarang`);

The optimized query:

SELECT
        a.NoSPKJahit AS 'TglSPKJahit',
        c.TglSPKJahit AS 'spkJahit',
        a.SeriBarang AS 'KodeSeri',
        b.NamaBarang AS 'NamaBarang',
        a.JmlTotalPotong - a.JmlTotalRusakSablon AS 'JumlahSPKJahit',
        a.JmlTotalSelesaiJahit AS 'JumlahHasilJahit',
        es_temp1.JumlahPenjualan,
        es_temp2.JumlahRetur,
        es_temp3.JumlahRusak,
        (a.JmlTotalSelesaiJahit - (SELECT
            sum(PenjualanDTL.Qty) 
        FROM
            PenjualanDTL 
        WHERE
            PenjualanDTL.KodeBarang = a.KodeBarang) + ((SELECT
            sum(JumlahRetur) 
        FROM
            StokBS 
        WHERE
            StokBS.KodeBarang = a.KodeBarang) - (SELECT
            sum(JumlahRusak) 
        FROM
            StokBS 
        WHERE
            StokBS.KodeBarang = a.KodeBarang))) AS 'SisaBarang',
        es_temp4.Gudang,
        es_temp5.GudangAtas,
        es_temp6.Mobil,
        es_temp7.MissMode,
        es_temp8.SilverLeafM 
    FROM
        DetilBarang a 
    LEFT JOIN
        MsBarang b 
            ON b.KodeBarang = a.KodeBarang 
    LEFT JOIN
        SPKJahit c 
            ON c.NoSPKJahit = a.NoSPKJahit 
    LEFT JOIN
        es_temp1 
            ON es_temp1.KodeBarang = a.KodeBarang 
    LEFT JOIN
        es_temp2 
            ON es_temp2.KodeBarang = a.KodeBarang 
    LEFT JOIN
        es_temp3 
            ON es_temp3.KodeBarang = a.KodeBarang 
    LEFT JOIN
        es_temp4 
            ON es_temp4.KodeBarang = a.KodeBarang 
    LEFT JOIN
        es_temp5 
            ON es_temp5.KodeBarang = a.KodeBarang 
    LEFT JOIN
        es_temp6 
            ON es_temp6.KodeBarang = a.KodeBarang 
    LEFT JOIN
        es_temp7 
            ON es_temp7.KodeBarang = a.KodeBarang 
    LEFT JOIN
        es_temp8 
            ON es_temp8.KodeBarang = a.KodeBarang