Does anybody know how to improve performance of this query?
I'm using doctrine DQL model, here's the code
(it takes 5-6 sec without pagination bundle)
Controller:
$data = $this->getDoctrine()
->getEntityManager('sparcs')
->getRepository('TruckingMainBundle:BCT_CNTR_EVENTS')
->findOperationReport(Convert::serializeToArray($request->getContent()));
Repository method:
public function findOperationReport($condition = array()) {
$data = $condition['record'];
$move_types = array();
$result = $this->createQueryBuilder("sp")
->addSelect("sp");
// move types
if(isset($data['vessel_discharge'])) {
//$move_types[] = $this->container->getParameter('MOVE_TYPE.VESSEL_DIS');
$move_types[] = 'VY';
}
if(isset($data['vessel_loading'])) {
//$move_types[] = $this->container->getParameter('MOVE_TYPE.VESSEL_LOAD');
$move_types[] = 'YV';
}
if(isset($data['truck_out'])) {
$move_types[] = 'TC';
}
if(isset($data['truck_in'])) {
$move_types[] = 'CT';
}
if(isset($data['stuffing'])) {
//$move_types[] = 'CT';
}
if(isset($data['unstuffing'])) {
//$move_types[] = 'CT';
}
if(isset($data['rail_in'])) {
$move_types[] = 'YR';
}
if(isset($data['rail_out'])) {
$move_types[] = 'RY';
}
if(count($move_types) > 0) {
$result->andWhere('sp.move_type IN (:move_type)')
->setParameter('move_type',$move_types);
} else {
$result->andWhere("1 = 2");
}
//container types
if(isset($data['empty']) && isset($data['full'])) {
//skipping
}
elseif (isset($data['empty'])) {
$result->andWhere('sp.ctnr_status = :ctnr_status')
->setParameter('ctnr_status',self::CTNR_EMPTY);
}
elseif (isset($data['full'])) {
$result->andWhere('sp.ctnr_status = :ctnr_status')
->setParameter('ctnr_status',self::CTNR_FULL);
if(isset($data['weight_from'])) {
$result->andWhere("cast(replace([weight],',','.') as float) :weight_from")
->setParameter('weight_from',$data['weight_from']);
echo 'weight from';
}
if(isset($data['weight_to'])) {
$result->andWhere('sp.weight <= :weight_to')
->setParameter('weight_to',(string)$data['weight_to']);
}
}
/*
//excpetion
$result->andWhere('sp.move_type NOT IN (:move_type_not)')
->setParameter('move_type_not',array('TY','YT'));
*/
if(isset($data['today']) || isset($data['yesterday'])) {
//yesterday
if(isset($data['yesterday'])) {
$yesterday = new \DateTime(date("Ymd"));
$interval = new \DateInterval("P1D");
$interval->invert = 1;
$yesterday->add($interval);
}
//yesterday + today
if(isset($data['today']) && isset($data['yesterday'])) {
$result->andWhere('sp.move_time >= :yesterday')
->setParameter('yesterday',$yesterday->format("Ymd000000"));
}
elseif(isset($data['yesterday'])) {
$result->andWhere('sp.move_time >= :yesterday_from AND sp.move_time <= :yesterday_to')
->setParameter('yesterday_from',$yesterday->format("Ymd000000"))
->setParameter('yesterday_to',$yesterday->format("Ymd235959"));
}
elseif(isset($data['today'])) {
$result->andWhere("sp.move_time = :today")
->setParameter('today',date("Ymd000000"));
}
}
else {
//date conditions
$date_from = new \DateTime(strtotime($data['date_from']));
$date_to = new \DateTime(strtotime($data['date_to']));
$result->andWhere("sp.move_time >= :date_from")
->setParameter('date_from',$date_from->format("Ymd000000"));
$result->andWhere("sp.move_time <= :date_to")
->setParameter('date_to',$date_to->format("Ymd235959"));
}
//booking
if(isset($data['booking']) && !empty($data['booking'])) {
$result->andWhere("sp.booking = :booking")
->setParameter('booking',$data['booking']);
}
//is reffer
if(isset($data['reefer'])) {
$result->andWhere("sp.reefer_flag = :reefer")
->setParameter('reefer','Y');
}
//is damage
if(isset($data['damage'])) {
$result->andWhere("sp.hazards <> ''"); //$result->expr()->neq("sp.hazards","")
}
//specific_type
if(isset($data['specific_type'])) {
/*
$result->andWhere("sp.equip_type <> :specific_type")
->setParameter('specific_type',$data['specific_type']);
*
*/
}
//specific_type
if(isset($data['container_type_20']) && isset($data['container_type_40'])) {
//$result->andWhere("sp.equip_type <> :specific_type")
// ->setParameter('specific_type',$data['specific_type']);
}
elseif(isset($data['container_type_20'])) {
$result->andWhere($result->expr()->substring('sp.equip_type',1,1)." = :equip_type")
->setParameter('equip_type',2);
}
elseif(isset($data['container_type_40'])) {
$result->andWhere($result->expr()->substring('sp.equip_type',1,1)." = :equip_type")
->setParameter('equip_type',4);
}
return $result->setMaxResults(30)->getQuery();
}
If I use KnpPaginationBundle ,then it takes more than 27 sec )
$paginator = $this->get('knp_paginator');
$pagination = $paginator->paginate(
$data,
2/*page number*/,
10/*limit per page*/
);
I can execute a native sql query without KnpPaginatorBundle (and it takes 0.333 ms)
//START
$query = $this->getDoctrine()
->getEntityManager('sparcs')->getConnection()->executeQuery (
'SELECT *
FROM (SELECT Row_number()
OVER (
ORDER BY (SELECT 0)) AS "doctrine_rownum",
b0_.id AS id0,
b0_.container AS container1,
b0_.container_use_key AS container_use_key2,
b0_.line AS line3,
b0_.billable_line AS billable_line4,
b0_.move_time AS move_time5,
b0_.move_type AS move_type6,
b0_.pos_from AS pos_from7,
b0_.pos_to AS pos_to8,
b0_.rotation_nbr AS rotation_nbr9,
b0_.from_che AS from_che10,
b0_.to_che AS to_che11,
b0_.from_che_kind AS from_che_kind12,
b0_.to_che_kind AS to_che_kind13,
b0_.from_che_op AS from_che_op14,
b0_.to_che_op AS to_che_op15,
b0_.pow AS pow16,
b0_.internal_truck AS internal_truck17,
b0_.lifter AS lifter18,
b0_.quay_crane AS quay_crane19,
b0_.license_plate AS license_plate20,
b0_.trucker_id AS trucker_id21,
b0_.trucker_name AS trucker_name22,
b0_.arrv_qual AS arrv_qual23,
b0_.arrv_carrier AS arrv_carrier24,
b0_.dept_qual AS dept_qual25,
b0_.dept_carrier AS dept_carrier26,
b0_.invoyage AS invoyage27,
b0_.outvoyage AS outvoyage28,
b0_.lloyds_code AS lloyds_code29,
b0_.load_port AS load_port30,
b0_.disch_port AS disch_port31,
b0_.destination AS destination32,
b0_.equip_type AS equip_type33,
b0_.bundle AS bundle34,
b0_.ctnr_category AS ctnr_category35,
b0_.ctnr_status AS ctnr_status36,
b0_.ctnr_stopped AS ctnr_stopped37,
b0_.commodity AS commodity38,
b0_.weight AS weight39,
b0_.damage AS damage40,
b0_.reefer_temp AS reefer_temp41,
b0_.reefer_flag AS reefer_flag42,
b0_.damage_details AS damage_details43,
b0_.seal_1 AS seal_144,
b0_.railcar_id AS railcar_id45,
b0_.dwell_time AS dwell_time46,
b0_.special_stow AS special_stow47,
b0_.service AS service48,
b0_.booking AS booking49,
b0_.release_note AS release_note50,
b0_.cmr_number AS cmr_number51,
b0_.forwarding_agent AS forwarding_agent52,
b0_.cargo_agent AS cargo_agent53,
b0_.invoice_number AS invoice_number54,
b0_.invoice_status AS invoice_status55,
b0_.last_flag AS last_flag56,
b0_.sparcs_user AS sparcs_user57,
b0_.program AS program58,
b0_.id AS id59,
b0_.container AS container60,
b0_.container_use_key AS container_use_key61,
b0_.line AS line62,
b0_.billable_line AS billable_line63,
b0_.move_time AS move_time64,
b0_.move_type AS move_type65,
b0_.pos_from AS pos_from66,
b0_.pos_to AS pos_to67,
b0_.rotation_nbr AS rotation_nbr68,
b0_.from_che AS from_che69,
b0_.to_che AS to_che70,
b0_.from_che_kind AS from_che_kind71,
b0_.to_che_kind AS to_che_kind72,
b0_.from_che_op AS from_che_op73,
b0_.to_che_op AS to_che_op74,
b0_.pow AS pow75,
b0_.internal_truck AS internal_truck76,
b0_.lifter AS lifter77,
b0_.quay_crane AS quay_crane78,
b0_.license_plate AS license_plate79,
b0_.trucker_id AS trucker_id80,
b0_.trucker_name AS trucker_name81,
b0_.arrv_qual AS arrv_qual82,
b0_.arrv_carrier AS arrv_carrier83,
b0_.dept_qual AS dept_qual84,
b0_.dept_carrier AS dept_carrier85,
b0_.invoyage AS invoyage86,
b0_.outvoyage AS outvoyage87,
b0_.lloyds_code AS lloyds_code88,
b0_.load_port AS load_port89,
b0_.disch_port AS disch_port90,
b0_.destination AS destination91,
b0_.equip_type AS equip_type92,
b0_.bundle AS bundle93,
b0_.ctnr_category AS ctnr_category94,
b0_.ctnr_status AS ctnr_status95,
b0_.ctnr_stopped AS ctnr_stopped96,
b0_.commodity AS commodity97,
b0_.weight AS weight98,
b0_.damage AS damage99,
b0_.reefer_temp AS reefer_temp100,
b0_.reefer_flag AS reefer_flag101,
b0_.damage_details AS damage_details102,
b0_.seal_1 AS seal_1103,
b0_.railcar_id AS railcar_id104,
b0_.dwell_time AS dwell_time105,
b0_.special_stow AS special_stow106,
b0_.service AS service107,
b0_.booking AS booking108,
b0_.release_note AS release_note109,
b0_.cmr_number AS cmr_number110,
b0_.forwarding_agent AS forwarding_agent111,
b0_.cargo_agent AS cargo_agent112,
b0_.invoice_number AS invoice_number113,
b0_.invoice_status AS invoice_status114,
b0_.last_flag AS last_flag115,
b0_.sparcs_user AS sparcs_user116,
b0_.program AS program117
FROM bct_cntr_events b0_
WHERE b0_.move_type IN ( \'YY\',\'YV\',\'VY\',\'TY\' )
AND b0_.move_time >= \'20120910000000\'
AND b0_.move_time <= \'20120912300000\') AS doctrine_tbl
WHERE "doctrine_rownum" BETWEEN 11 AND 20 '
)->fetchAll();
