New question

Question:

Date: 18-08-2021 08:02:26 (In Spanish)

Sentencia sql con exceso de tiempo de ejecucion[Unresolved]

actualmente nos estan migrando a una nueva plataforma operacional, la cual entrega reportes diariamente, y conforme a ido aumentando el numero de registros no muchos por cierto 347,000 la sentencia que genera uno de los resportes tarda 40 minutos, y conforme crecen los registros aumenta el tiempo, de la siguiente sentencia quisiera su apoyo para poder modificar los join que hacen referencia a la misma tabla y que parecieran ser subconsultas, quien diseño esta sentencia, me indica que solo es necesario crear index lo cual no creo sea la solucion, les muestro la sentencia

SELECT
Operations.id
, Operations.company_id
, Operations.merchant_id
, Operations.branch_id
, Operations.terminal_id
, Operations.operation_type
, Operations.internal_response_code
, Operations.reversed
, Operations.returned
, Operations.voided
, Operations.host_input_mode
, Operations.internal_input_mode
, Operations.transaction_datetime
, Operations.transaction_datetime AS TransactionDatetime
, TIMESTAMPDIFF( DAY, DATE(CONCAT(YEAR(Operations.transaction_datetime),'-01','-01')), Operations.transaction_datetime) AS DayOfTheYear
, Operations.transmission_datetime
, Operations.operation_type
, Operations.auth_code
, Operations.amount_to_apply
, Operations.amount AS OriginalAmount
, CASE
WHEN Op.amount_to_apply IS NULL THEN Operations.amount
WHEN Op.amount_to_apply IS NOT NULL THEN Op.amount_to_apply
END AS amount
, CASE
WHEN Operations.rounding_diff_amount IS NULL THEN 0 ELSE Operations.rounding_diff_amount
END AS rounding_diff_amount
, CASE
WHEN Op.rounding_diff_amount IS NULL THEN 0 ELSE Op.rounding_diff_amount
END AS rounding_diff_amount2
, CASE
WHEN Op.operation_type = "AnswerReturn" AND (((Op.amount + IF(Op.rounding_diff_amount IS NULL, 0, Op.rounding_diff_amount)) != Op.request_amount) OR (Op.amount = Op.request_amount AND Operations.amount = (Op.amount*2))) AND Operations.pool_id like "GAS%" AND Operations.financial_cost_perc IS NULL THEN TRUE ELSE FALSE
END AS IsAdjustSale
, CASE
WHEN Op.operation_type = "AnswerReturn" AND (((Op.amount + IF(Op.rounding_diff_amount IS NULL, 0, Op.rounding_diff_amount)) != Op.request_amount) OR (Op.amount = Op.request_amount AND Operations.amount = (Op.amount*2))) AND Operations.pool_id like "GAS%" AND Operations.financial_cost_perc IS NULL THEN Operations.amount ELSE 0
END AS AdjustSaleAmount
, Op2.amount
, CASE
WHEN Op2.amount = Operations.amount*2 AND Operations.pool_id like "GAS%" THEN TRUE ELSE FALSE
END AS HalfAdjustSale
, Operations.voided_amount
, Operations.request_amount
, Operations.card_number_masked
, Operations.plan_id
, Operations.cashback_amount
, CASE
WHEN Operations.facility_payments IS NULL THEN 0
WHEN Operations.facility_payments = 1 THEN 0
WHEN Operations.facility_payments > 1 THEN Operations.facility_payments
END AS facility_payments
, CASE
WHEN Operations.host_facility_type IS NULL THEN 0
ELSE Operations.host_facility_type
END AS host_facility_type
, Operations.notified
, Operations.host_currency_id
, Operations.payment_method_id
, Operations.pos_id
, Operations.batch_merchant_id
, Operations.financial_cost_perc
, IFNULL( Operations.deferral_days, 0 ) AS deferral_days
, IFNULL( ( Operations.deferral_days DIV 30 ), 0 ) AS deferral_months
, Operations.affected_operation_type
, Operations.resolution_mode
, DeviceTypes.typed_input_mode
, DeviceTypes.magnetic_strip_input_mode
, DeviceTypes.emv_input_mode
, DeviceTypes.contactless_input_mode
, IFNULL( Batch.close_state, 0 ) AS close_state
, IFNULL( Batch.close_retries, 0 ) AS close_retries
, 1 AS ElementCounter
, 2 AS InCloseStatus
, 1 AS ClosedStatus
, 0 AS NotClosed
FROM
Multipay.Operations AS Operations
LEFT JOIN Multipay.Operations Op ON Op.affected_transaction_id = Operations.id AND Op.reversed = 0 AND Op.internal_response_code = -1
LEFT JOIN Multipay.Operations Op2 ON Operations.affected_transaction_id = Op2.id AND Operations.reversed = 0 AND Operations.internal_response_code = -1
JOIN Multipay.Companies Companies ON Companies.company_id = Operations.company_id
JOIN Multipay.Branches Branches ON
(
Branches.company_id = Operations.company_id
AND
Branches.branch_id = Operations.branch_id
)
JOIN Multipay.DeviceTypes DeviceTypes ON DeviceTypes.device_type_id = Operations.device_type_used
LEFT JOIN Multipay.Tickets Tickets ON Tickets.answer_key = Operations.id
LEFT JOIN Multipay.Batch Batch ON
(
( (1 = 'ALL' AND Batch.company_id = '*' ) OR Batch.company_id = Operations.company_id )
AND
( ('COSTCO' = 0 AND Batch.host_id = 0 ) OR Batch.host_id = Operations.host_id )
AND
Batch.batch_number = '202108154'
AND
Batch.branch_id = '*'
AND
Batch.pos_id = '*'
AND
Batch.payment_method_id = '*'
AND
Batch.terminal_id = '*'
AND
Batch.batch_merchant_id = '*'
)
WHERE
( 'COSTCO' = 'ALL' OR BINARY Operations.company_id = 'COSTCO' )
AND ( 1 = 0 OR Operations.host_id = 1 )
AND ( Operations.closed = 0 OR Operations.closed = 2 OR Operations.closed = 3)
AND Operations.internal_response_code = -1
AND Operations.create_datetime like '2021-08-15%'
AND Operations.reversed = 0
AND Operations.voided = 0
AND Operations.request_operation_type != "Void"

ORDER BY
Operations.company_id
, Operations.branch_id
, Operations.transaction_datetime;

la intencion es esos join donde aparece Op y Op2 evitarlos y se ejecuten sobre Operations.

Gracias de antemano


Tags: MSSQL - SQL - SQL Server Votes: 0 - Answers: 1 - Views: 8 Share on: Google Facebook Twitter LinkedIn Link
 

Answers:

  • Date: 23-08-2021 06:57:48 Hola Pedro,
    Podría ser una combinación de ambos.
    Respecto a los JOIN estoy de acuerdo contigo y creo que sí pudieras desecharlos totalmente y realizar las operaciones sobre la misma tabla.
    El primer paso sería remover OP2. Este es muy fácil pues es lo mismo que OP.
    Una vez hecho esto podrías pasar las sentencias bajo el ON del JOIN con OP al final de la consulta dentro del WHERE.

    Después de modificar la consulta checa el plan de ejecución de esta para que puedas identificar rápidamente si hay algo relacionado con los índices y puedas optimizarlos adecuadamente.

    Espero que esto te sirva.

    Saludos
      Votes: 0 - Link answer
     
To actively participate in the community first must authenticate, enter the system.Sign In