改进存储过程中查询

Question

我需要在存储过程中改进这个查询，大量数据的性能破坏了应用程序。有没有办法让它更快？

我需要从几个表中收集某些列，以便在我的应用程序web中构建一个仪表板，其他列我在其他查询中收集并通过类连接到我的控制器中。

编辑:此查询是从动态事务的一部分，他们可以改变数据库。

SELECT 
    a.fact_num as cotizacion, a.comentario, m.co_cli, k.cli_des, 
    m.co_ven, l.ven_des, m.fec_emis, m.fec_venc, m.campo8, 
    a.reng_num, a.co_art, g.art_des, a.co_alma, b.fact_num as pedido, 
    c.fact_num as factura, d.fact_num as despacho, e.cob_num as cobro, 
    f.fec_venc as fecha_venc, f.fec_emis as fecha_pedido,
    h.odp_num as ord_produccion, h.co_ced as cedula, i.req_num as requisicion, 
    j.ent_num as cierre 
FROM 
    reng_cac a
LEFT JOIN  
    cotiz_c m ON a.fact_num = m.fact_num
LEFT JOIN 
    reng_ped b ON a.co_art = b.co_art AND a.fact_num = b.num_doc AND b.tipo_doc = 'T'
LEFT JOIN 
    pedidos f ON b.fact_num = f.fact_num 
LEFT JOIN 
    reng_fac c ON b.fact_num = c.num_doc AND a.co_art = c.co_art AND c.tipo_doc = 'P'
LEFT JOIN 
    reng_ndd d ON c.fact_num = d.num_doc AND a.co_art = d.co_art AND d.tipo_doc = 'F'
LEFT JOIN 
    reng_cob e ON c.fact_num = e.doc_num AND e.tp_doc_cob = 'FACT'
LEFT JOIN 
    art g ON a.co_art = g.co_art
LEFT JOIN 
    spodp h ON b.fact_num = h.doc_ori AND b.co_art = h.co_art 
LEFT JOIN 
    spreqalm i ON h.odp_num = i.odp_num
LEFT JOIN 
    spcierre j ON h.odp_num = j.odp_num 
LEFT JOIN 
    clientes k ON m.co_cli = k.co_cli
LEFT JOIN 
    vendedor l ON m.co_ven = l.co_ven
WHERE 
    a.fact_num BETWEEN '0' AND '999999999' 
    AND m.fec_emis BETWEEN '01/01/2012' AND '30/06/2012'
    AND m.co_cli BETWEEN '' AND 'þþþþþþþþþþþþþþþþþþþþþþþþþþþþþþ' 
ORDER BY 
    a.fact_num, a.reng_num ASC 

Answer 1

正如Nick评论的那样，您总是需要在执行计划中运行查询。有几点你需要考虑。例如，拥有过多的LEFT JOIN会降低性能。尝试在可能的情况下使用INNER JOIN%s。检查是否有正确的索引。如果你能把你的执行计划附加到你的问题上，你会得到更有帮助的答案。