Increase sql performance

Navigation
0

I have this function below that executes this SQL, but the execution time of this SQL is too high would I have any way to decrease the execution time? At the time of loading the page takes about 5 seconds to execute the query below. Would you like to reduce this time somehow? 

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 owns some cart, if yes it brings all information of that cart if it does not have nor a cart it returns in white. Each else is a select in each column to bring the information if there is a cart. That's rough talk.

    
asked by anonymous 02.06.2016 / 19:08

1 answer

1

Kevin, whenever I saw a query with a subquery as the select field, it had performance issues. In your case, you have two subqueries per field.

Then try to move them to LEFT JOIN and use ISNULL / COALESCE instead of 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)
    
02.06.2016 / 20:11
Asynchronous jQuery-Prototype conflict Error: You have an error in your SQL syntax [closed]