Code Optimization in sql

Question

I have written some sql (below) however it is running so slowly and was wondering if there was any advice / examples / suggestions how I could speed this up. It may be glaringly obvious to some but not me. Also the lines are duplicated.

Any help would be great

SELECT 
      a.account,
      a.amount,
      a.apar_id, 
      h.apar_name, 
      g.description as 'supplier description',
      d.description as 'supplier catagory',
      a.description as 'expense description',
      a.dim_1 as 'cost centre',
      d1.description,a.dim_7 as 'legal ent',
      a.period,a.trans_date,a.voucher_no 
FROM agltransact a
      LEFT JOIN asuheader h ON a.apar_id = h.apar_id AND a.client=h.client
      LEFT JOIN acrtrees t ON t.cat_1=a.apar_id AND t.att_agrid='61' AND t.client=a.client 
      LEFT JOIN acrtrees t1 ON t1.dim_b=a.dim_7 AND t1.att_agrid='47' AND t1.client=a.client 
      LEFT JOIN agldescription d1 ON d1.dim_value=t1.dim_c AND d1.attribute_id='71' AND d1.client=a.client
      LEFT JOIN agldescription d ON d.dim_value=t.dim_a AND d.attribute_id='2661' AND d.client=a.client
      LEFT JOIN asugroup g ON g.apar_gr_id=h.apar_gr_id AND g.client=a.client
WHERE 
      a.client='CL'
      AND period >='201307' and period <='201406'
      AND (t.dim_a='1030' OR a.account IN ('43050','43100'))
      AND a.account!='A5200'

Maybe the parser has as much trouble reading it as I do. ;) Which table is `period` in? — GolezTrol, Nov 13 '14 at 11:37
you can replace AND (t.dim_a='1030' OR a.account IN ('43050','43100')) this line with Cross Apply that will help you — Hardik Parmar, Nov 13 '14 at 11:40
agltransact a is where period is. Thanks Hardik, any suggestions what to replace with? — Jim, Nov 13 '14 at 11:41
Do you have an index on every column in the WHERE clause? Check the [execution plan](http://stackoverflow.com/a/7359705/562459). — Mike Sherrill 'Cat Recall', Nov 13 '14 at 11:42
What is the datatype of your columns ending with id? If they are Int then please replace all the chars with appropriate Int as SQL server CPU cycles are used when doing implicit conversion and also indexes might not get used. Similarly with Date columns — Rajesh, Nov 13 '14 at 15:12
I would start by figuring out where the duplicates come from first. Most likely one (or more) JOIN ON clauses is 'incomplete'. In theory that part of the code should 'cover' the entire primary key of the joined table and return only 1 row per 'match'. (PS: instead of PK it can off course also be a UNIQUE constraint (or index) or even neither of those but rather a functionally correct 'unique-combination'. In the latter situation, for (query) speed it would be advisable to put at least a unique index on that combination then!). PS: PLEASE DO NOT "SOLVE" IT BY ADDING A `DISTINCT` !! — deroby, Nov 14 '14 at 10:07

score 0 · Answer 1 · answered Nov 13 '14 at 12:44

Without seeing your tables and structures / indexes, I would at least have these specific indexes available for each respective table.

table          index
agltransact    (client, period
asuheader      (client, apar_id, apar_gr_id )
asugroup       (client, apar_gr_id )
acrtrees       (client, att_agrid, cat_1, dim_a, dim_b )
agldescription (client, attribute_id, dim_value )

And primarily for readability and table associations to clearly see where things are coming from and joining to

SELECT 
      a.account,
      a.amount,
      a.apar_id, 
      h.apar_name, 
      g.description as 'supplier description',
      d.description as 'supplier catagory',
      a.description as 'expense description',
      a.dim_1 as 'cost centre',
      d1.description,a.dim_7 as 'legal ent',
      a.period,
      a.trans_date,
      a.voucher_no 
   FROM
      agltransact a
         LEFT JOIN asuheader h 
            ON  a.client = h.client
            AND a.apar_id = h.apar_id 
            LEFT JOIN asugroup g 
               ON  h.client = g.client
               AND h.apar_gr_id = g.apar_gr_id

         LEFT JOIN acrtrees t 
            ON  a.client = t.client
            AND t.att_agrid = '61' 
            AND a.apar_id = t.cat_1 
            LEFT JOIN agldescription d 
               ON t.client = d.client
               AND d.attribute_id = '2661' 
               AND t.dim_a = d.dim_value 

         LEFT JOIN acrtrees t1 
            ON a.client = t1.client
            AND t1.att_agrid = '47' 
            AND a.dim_7 = t1.dim_b 
            LEFT JOIN agldescription d1 
               ON t1.client = d1.client
               AND d1.attribute_id = '71' 
               AND t1.dim_c = d1.dim_value
   WHERE 
          a.client='CL'
      AND a.period >='201307' and a.period <='201406'
      AND (t.dim_a='1030' OR a.account IN ('43050','43100'))
      AND a.account != 'A5200'

I changed some of the joins to join to their secondary level alias via the client ID. since ex: A.client = H.client and a.Client = T.Client... H joined to T could use its H.Client = T.Client so th engine might make better association of just the H table to the T table instead of A through H to T.

Thanks for this, it is still a slow query and bringing back duplicate rows, but is a help. — Jim, Nov 13 '14 at 13:39
@Jim, were you getting duplicates before and just not seeing them now? If not previously, the only real change I did was the association of "Client" as described and could be changed back to the "a." alias instead. Other than that, it was all just visual alignments for readability. — DRapp, Nov 13 '14 at 13:51
I was getting duplicates before and I am also getting them still. My gut feeling is it is something to do with the joins. Thanks for your help so far — Jim, Nov 13 '14 at 14:16
The duplicates are result of Cartesian data... meaning that for any given record in table "X", there are more than one record in table "Y" and I don't know which table(s) have the duplicated entries. — DRapp, Nov 13 '14 at 14:47
Thanks for your help @DRapp I'll try to figure out how I can remove the duplicated ones (somehow!) — Jim, Nov 13 '14 at 14:55

Code Optimization in sql

1 Answers1