Improve query in stored procedure

Question

I need to improve this query inside a stored procedure, the performance with lots of data broke the application. Is there any way to make it faster?

I need to collect certain columns from several tables to build a dashboard in my app web, others columns I collected in other query and joined in my controller through classes.

EDIT: this query is a part from dynamically transaction, they can change the database.

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

If you want to improve performance the first step is to collect the actual/ estiamted plan: https://msdn.microsoft.com/en-us/library/ms189562.aspx — Nick.Mc, Mar 01 '16 at 01:23
have you wondered to use aggregate functions such as sum, max ... with group by clause ? if it fits in your case. — Boody, Mar 01 '16 at 06:26
are you sure all your joins are really `LEFT` and `fact_num` is a string? — Ivan Starostin, Mar 01 '16 at 08:11
@Nick.McDermaid I have the actual / estimated plan but is too large, how copy that in the post? — Der Schwarze König, Mar 01 '16 at 23:27
@Boody let me see is the agregate functions work perfectly in that query — Der Schwarze König, Mar 01 '16 at 23:27
@IvanStarostin well, I saw the joins and yes, all the joins is `LEFT` but if you can see other type of joins, please tell me about it, `fact_num` is `INT` — Der Schwarze König, Mar 01 '16 at 23:30
You do a left join on table `vendedor` but then you use a `WHERE` on it which makes it a inner join. You probably need to move your `AND m. pieces into the left join or do an inner join. Are you doing all those left joins for a reason? — Nick.Mc, Mar 01 '16 at 23:34
Now that you have your execution plan, take a look and find the largest percentage in there and see if it is a table scna or index scan. Also the query plan will recommend indexes if it can. — Nick.Mc, Mar 01 '16 at 23:44
@Nick.McDermaid the `WHERE` works to all query or the last `LEFT` join? I think in my little knowledge is work to the all query, isn't? Sorry for my english is a very poor in this time... The reason for the all `LEFT` joins is bring back all the necesary fields all those tables to build a dashboard — Der Schwarze König, Mar 01 '16 at 23:45
LEFT JOIN as you have it here means bring back all the records from `reng_cac` and only those that match on `fact_num` from `cotiz_c`. If a record _doesn't_ match in `cotiz_c` then all fields in `cotiz_c` will be NULL. So first important question is: can there be a `fact_num` value in `reng_cac` that **doesn't** exist in `cotiz_c`? If the answer is _no_ then you should be using inner joins not left joins. — Nick.Mc, Mar 01 '16 at 23:49
@Nick.McDermaid Yes, `reng_cac` have all the rengs from `cotiz_c` is a child table — Der Schwarze König, Mar 01 '16 at 23:51
@DerSchwarzeKönig, a had no question about what's written in your post, I asked about your real data model. As @NickMcDermaid mentioned you already have logically `inner` joins because of filters applied in `where`. And many other joins don't look like really `LEFT`. Joins described: https://technet.microsoft.com/en-us/library/ms191472.aspx And since you understand and approve that `fact_num` is `int` - why are you attempting to apply string filter to it? what are those quotes there for? — Ivan Starostin, Mar 02 '16 at 08:20
@IvanStarostin I need all the data to the left table with the matched in the right table, those quotes in the INT field is a mistake from my part, I run the query again with the execution plan and the major consumption is in that part of the query `reng_ped b ON a.co_art = b.co_art AND a.fact_num = b.num_doc AND b.tipo_doc = 'T'` and the others similars in quotes @NickMcDermaid — Der Schwarze König, Mar 03 '16 at 13:17
So regarding the link I gave - which join is that? `LEFT` or `INNER`? And do you have any indexes on columns you are joining and filtering by (since you are not showing execution plan)? — Ivan Starostin, Mar 03 '16 at 13:38
@IvanStarostin `LEFT` to me but if you think is wrong, I can change that, The only indexes are in PK values aka `a.fact_num` and `b.co_art` here the execution plan [link](http://imgur.com/kOVrN2A) — Der Schwarze König, Mar 03 '16 at 13:50
Build indexes for "columns you are joining and filtering by". And link is to the picture with a lonely key lookup. It has nothing common with the query in your question. — Ivan Starostin, Mar 03 '16 at 14:05
@DerSchwarzeKönig http://stackoverflow.com/a/20298671/5921826 — Ivan Starostin, Mar 03 '16 at 15:47
@IvanStarostin the execution plan is very large to copy to an image... How to insert that here? Thanks you... — Der Schwarze König, Mar 03 '16 at 20:08
Build indexes for "columns you are joining and filtering by". — Ivan Starostin, Mar 03 '16 at 20:32

score 0 · Answer 1 · answered Mar 01 '16 at 06:05

As Nick commented, you always need to run your query in a execution plan. There are some points you need to consider. For instance, having a lot of LEFT JOINs will reduce the performance. Try to see if you can use INNER JOINs where possible. Check if you have proper indexes. If you can attach your execution plan to your question, you will get more helpful answers.

Improve query in stored procedure

1 Answers1