Increase sql performance

Question

I have this function below that runs this SQL, but the running time of this SQL is too high would have some way to decrease the running time ? At the time of loading the page takes around 5 seconds to execute the query below. I would like to decrease this time in some way ?

public function ListaPorTipoP($obj){
    $results = array();
    $stmt = $this->conn->prepare
    ('select 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.NrPlaca from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else null
    end as [DsCavalo], 
    B.ID,
    B.NrPlaca,
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DtProg from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DtProg
    end as [DtProg], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DsMotorista from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DsMotorista
    end as [DsMotorista], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DtSaida from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DtSaida
    end as [DtSaida], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.HrSaida from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.HrSaida
    end as [HrSaida],     
         case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DtChegada from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DtChegada
    end as [DtChegada],      
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.HrChegada from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.HrChegada
    end as [HrChegada], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DsOrigem from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DsOrigem
    end as [DsOrigem], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DsDestino from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DsDestino
    end as [DsDestino], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DsAgendas from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DsAgendas
    end as [DsAgendas], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DsCarga from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DsCarga
    end as [DsCarga], 
        case when exists (select top 1 A.NrPlaca from GTCLogist A
                            where A.DsTpVeiculo = \'Cavalo Truck\'
                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                            and A.DtBase = B.DtBase)  Then (select top 1 A.DtPrevChegDest from GTCLogist A
                                                            where A.DsTpVeiculo = \'Cavalo Truck\'
                                                            AND (A.NrPlacaCarreta=B.NrPlaca OR A.NrPlacaReboque2=B.NrPlaca)
                                                            and A.DtBase = B.DtBase)
        else B.DtPrevChegDest
    end as [DtPrevChegDest], 
        B.DtBase,
        B.DsObservacao,
        B.DsStatus
    from GTCLogist B
    where B.DsTpVeiculo like \'Carreta%\'
    and B.DtBase =  \''.self::convertData($obj->getdata()).'\' 
            and (exists (select top 1 H.NrPlaca from GTCLogist H
                            where H.DsTpVeiculo = \'Cavalo Truck\'
                            AND (H.NrPlacaCarreta=B.NrPlaca OR H.NrPlacaReboque2=B.NrPlaca)
                            and H.DtBase = B.DtBase
                            and H.DsDestino LIKE \''.$obj->getdest().'\')
        OR (NOT exists (select top 1 K.NrPlaca from GTCLogist K
                            where K.DsTpVeiculo = \'Cavalo Truck\'
                            AND (K.NrPlacaCarreta=B.NrPlaca OR K.NrPlacaReboque2=B.NrPlaca)
                            and K.DtBase = B.DtBase)
                            AND B.DsDestino LIKE \''.$obj->getdest().'\'))
    ');
     $stmt->execute();
        if($stmt) {
            while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $prog = new Prog();
                $prog->setid($row->ID);
                $prog->setst($row->DsStatus);
                $prog->setplaca($row->NrPlaca);
                $prog->setcavalo($row->DsCavalo);
                $prog->setmot(stripslashes($row->DsMotorista));
                $prog->setsaida(date('d/m/Y', strtotime($row->DtSaida)));
                $prog->setorig($row->DsOrigem);
                $prog->setdest($row->DsDestino);
                $prog->setprev(date('d/m/Y', strtotime($row->DtPrevChegDest)));
                $prog->setcarga($row->DsCarga);
                $prog->setagen($row->DsAgendas);
                $prog->setobs($row->DsObservacao);
                $results[] = $prog;
            }
        }
    return $results;
}

This SQL checks in the column Horse Truck if the horse has any cart, if yes it brings all information of this cart if it does not have even a cart it returns blank. Each else is a select in each column to bring information if there is trailer. That roughly speaking.

Answer 1

Kevin, every time I saw an appointment with a subquery as a field of select, it presented performance problems. In your case, you have two subqueries per field.

Then try moving them to the LEFT JOIN and use a ISNULL/COALESCE in place of this case:

WITH CTE_Cavalo AS (
    select 
        ROW_NUMBER() OVER (PARTITION BY H.NrPlacaCarreta, H.NrPlacaReboque2 ORDER BY H.DtBase DESC) AS Ordem,
        H.NrPlacaCarreta,
        H.NrPlacaReboque2,
        H.NrPlaca,
        H.DtProg, 
        H.DsMotorista, 
        H.DtSaida, 
        H.HrSaida, 
        H.DtChegada, 
        H.HrChegada, 
        H.DsOrigem,
        H.DsDestino, 
        H.DsAgendas, 
        H.DsCarga, 
        H.DtPrevChegDest
    from GTCLogist H
    where 
        H.DsTpVeiculo = 'Cavalo Truck' AND 
        H.DtBase = @data AND
        H.DsDestino LIKE @dest 
), CTE_Todos AS (
    select 
        ROW_NUMBER() OVER (PARTITION BY H.NrPlacaCarreta, H.NrPlacaReboque2 ORDER BY H.DtBase DESC) AS Ordem,
        H.NrPlacaCarreta,
        H.NrPlacaReboque2,
        H.NrPlaca
    from GTCLogist H
    where 
        H.DsTpVeiculo = 'Cavalo Truck' AND
        H.DtBase = @data
)

select 
    C.NrPlaca AS DsCavalo,
    B.ID,
    B.NrPlaca,
    ISNULL(C.DtProg, B.DtProg) AS DtProg,
    ISNULL(C.DsMotorista, B.DsMotorista) AS DsMotorista,
    ISNULL(C.DtSaida, B.DtPDtSaidarog) AS DtSaida,
    ISNULL(C.HrSaida, B.HrSaida) AS HrSaida,
    ISNULL(C.DtChegada, B.DtChegada) AS DtChegada,
    ISNULL(C.HrChegada, B.HrChegada) AS HrChegada,
    ISNULL(C.DsOrigem, B.DsOrigem) AS DsOrigem,
    ISNULL(C.DtPDsDestinorog, B.DDsDestinotProg) AS DsDestino,   
    ISNULL(C.DtPDsAgendasrog, B.DsAgendas) AS DsAgendas,
    ISNULL(C.DsCarga, B.DsCarga) AS DsCarga,
    ISNULL(C.DtPrevChegDest, B.DtPrevChegDest) AS DtPrevChegDest,
    B.DtBase,
    B.DsObservacao,
    B.DsStatus
from GTCLogist B
left join CTE_Cavalo AS C ON (C.NrPlacaCarreta = B.NrPlaca OR C.NrPlacaReboque2 = B.NrPlaca) and C.Ordem = 1
left join CTE_Todos AS D ON (D.NrPlacaCarreta = B.NrPlaca OR D.NrPlacaReboque2 = B.NrPlaca) and D.Ordem = 1
where 
    B.DsTpVeiculo like 'Carreta%' and 
    B.DtBase = @data and 
    B.DsDestino LIKE @dest and
    (C.NrPlaca IS NOT NULL OR D.NrPlaca IS NULL)