Optimize slow SQL query

Question

There is a application with a very slow query (listed below), and I have to optimize to run faster. The truth is that I don't know where to start. Any help?

SELECT DISTINCT v.order_ANC,
                v.order_ANC2,
                f.codi_arxiu,
                f.nom_arxiu,
                f.codi_grup,
                f.codi_fons_refe,
                f.codi_fons,
                f.nom_fons,
                f.any_ini,
                f.any_fi,
                f.cronologia,
                SUM (num_uni_imatges) AS num_uni_imatges,
                SUM (unitats_text) AS unitats_text,
                SUM (unitats_notext) AS unitats_notext
  FROM (  SELECT f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia,
                 SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
                 COUNT (u.codi_unitat) AS unitats_text,
                 0 AS unitats_notext
            FROM ianc_fons f
            JOIN ianc_unicat u ON ( f.codi_arxiu = u.codi_arxiu AND f.codi_fons = u.codi_fons )
           WHERE     1 = 1
                 AND u.anc_unicat_id IN
                        (SELECT DISTINCT u.anc_unicat_id
                           FROM ianc_unicat u
                          WHERE     1 = 1
                                AND u.codi_arxiu = '1'
                                AND u.codi_grup = 'ANC'
                                AND u.codi_fons = 1)
        GROUP BY f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia
        UNION
          SELECT f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia,
                 SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
                 0 AS unitats_text,
                 COUNT (u.nt1_unitat) AS unitats_notext
            FROM ianc_fons f
            JOIN ianci_unicats u ON (     f.codi_arxiu = u.nt1_codi_arxiu
                                      AND f.codi_fons = u.nt1_codi_fons )
           WHERE     1 = 1
                 AND u.nt1_id IN
                        (SELECT DISTINCT u.nt1_id
                           FROM ianci_unicats u
                          WHERE     1 = 1
                                AND u.nt1_codi_arxiu = '1'
                                AND u.codi_grup = 'ANC'
                                AND u.nt1_codi_fons = 1)
        GROUP BY f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia
       ) AS f
LEFT JOIN v_ianc_arxius_order v ON ( f.codi_arxiu=v.codi_arxiu )
GROUP BY v.order_ANC,v.order_ANC2,f.codi_arxiu, f.nom_arxiu, f.codi_grup, f.codi_fons_refe,
         f.codi_fons, f.nom_fons, f.any_ini,  f.any_fi, f.cronologia
ORDER BY v.order_ANC,v.order_ANC2,f.nom_arxiu, f.nom_fons

Answer 1

I suspect one bottleneck is the grouping in the subqueries. For each row in the ianc_fons table the rows are being duplicated - one for each join the chile table ianc_unicat. And then they are being grouped again.

I would try a different approach and move the summing into a joined subquery :

SELECT f.codi_arxiu,
       f.nom_arxiu,
       f.codi_grup,
       f.codi_fons_refe,
       f.codi_fons,
       f.nom_fons,
       f.any_ini,
       f.any_fi,
       f.cronologia,
       child.num_uni_imatges,
       child.unitats_text
 FROM ianc_fons f
 LEFT JOIN
      ( SELECT codi_arxiu, codi_fons,
               SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
               COUNT (u.codi_unitat) AS unitats_text
      FROM ianc_unicat u 
      WHERE u.anc_unicat_id IN
                    (SELECT u.anc_unicat_id
                       FROM ianc_unicat u
                      WHERE     1 = 1
                            AND u.codi_arxiu = '1'
                            AND u.codi_grup = 'ANC'
                            AND u.codi_fons = 1)
      GROUP BY codi_arxiu, codi_fons
      ) AS child ON f.codi_arxiu = child.codi_arxiu AND f.codi_fons = child.codi_fons

Note I have removed the DISTINCT from the Select from ianc_unicat. As this is just used in the filtering, the DISTINCT is not necessary. Using DISTINCT is always an overhead as the query engine needs to sort the records and then compare them all one by one to remove duplicates. Avoid as much as possible.

Each side of the UNION is selecting from the same table, and then summing different child records.

As the summing is now in a separate joined subquery, you can get rid of the union, and just join in the other table in a similar fashion. The Select will now need to sum up the totals from both joined subqueries.

SELECT f.codi_arxiu,
       f.nom_arxiu,
       f.codi_grup,
       f.codi_fons_refe,
       f.codi_fons,
       f.nom_fons,
       f.any_ini,
       f.any_fi,
       f.cronologia,
       COALESCE(child.num_uni_imatges, 0) + COALESCE(child2.num_uni_imatges, 0) AS num_uni_imatges,
       child.unitats_text,
       child2.unitats_notext

Note the use of COALESCE. As you are left joining these totals now, there is a chance that the values may be Null. Null is not the same as 0. Null + 4 would not equal 4, the result would be Null. So the COALESCE is necessary to make Null values 0.

Now you no longer need the outer select as this was being used to just sum the rows within the union. You can get rid of this all together. The row joined in to the outer query can just be joined into our main query. We no longer the the extra grouping here either. This gives us a query similar to the following :

SELECT f.codi_arxiu,
       f.nom_arxiu,
       f.codi_grup,
       f.codi_fons_refe,
       f.codi_fons,
       f.nom_fons,
       f.any_ini,
       f.any_fi,
       f.cronologia,
       COALESCE(child.num_uni_imatges, 0) + COALESCE(child2.num_uni_imatges, 0) AS num_uni_imatges,
       child.unitats_text,
       child2.unitats_notext
 FROM ianc_fons f
 LEFT JOIN
      ( SELECT codi_arxiu, codi_fons,
               SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
               COUNT (u.codi_unitat) AS unitats_text
      FROM ianc_unicat u 
      WHERE u.anc_unicat_id IN
                    (SELECT u.anc_unicat_id
                       FROM ianc_unicat u
                      WHERE     1 = 1
                            AND u.codi_arxiu = '1'
                            AND u.codi_grup = 'ANC'
                            AND u.codi_fons = 1)
      GROUP BY codi_arxiu, codi_fons
      ) AS child ON f.codi_arxiu = child.codi_arxiu AND f.codi_fons = child.codi_fons

 LEFT JOIN 
      ( SELECT u.nt1_codi_arxiu, u.codi_fons,
               SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
               COUNT (u.nt1_unitat) AS unitats_notext
        FROM ianci_unicats u 
       WHERE     1 = 1
             AND u.nt1_id IN
                    (SELECT u.nt1_id
                       FROM ianci_unicats u
                        WHERE     1 = 1
                            AND u.nt1_codi_arxiu = '1'
                            AND u.codi_grup = 'ANC'
                            AND u.nt1_codi_fons = 1)
      ) AS child2 ON f.codi_arxiu = child2.nt1_codi_arxiu AND f.codi_fons = child2.nt1_codi_fons

  LEFT JOIN v_ianc_arxius_order v ON ( f.codi_arxiu=v.codi_arxiu )
  ORDER BY v.order_ANC,v.order_ANC2,f.nom_arxiu, f.nom_fons

Answer 2

Optimising an SQL statement generally involves two phases: Understanding how the DBMS has chosen to execute the query in terms of index lookup and join order (called the query plan), and figuring out a way to write a query that gets the same effect, but that the DBMS finds a better query plan for.

Forcing the DBMS to choose a better query plan can be as simple as forcing a different index choice (overriding an automatic but non-optimal choice of index), adding a missing index or overriding the optimiser's choice of table join ordering. Or you might find a completely different way of expressing the same logical query, for example by using a join rather than WHERE column IN (SELECT ...). Overriding the optimiser usually requires special syntax that is specific to your SQL product (SQL Server, in this case) that should be explained in the manuals somewhere.

In your case, your first step should probably be to get a better insight into exactly why this query is slow. You can do this to a limited extent by removing some clauses from the query and seeing whether it gets immediately quicker; perhaps removing the ORDER BY clause or removing JOIN clauses.

If it's available, then a more powerful option is to use whatever analysis tools are provided with your DBMS, or available as third party tools. I don't know SQL server specifically, but I'm sure there's a tool that will show you the query plan for any given query, and ideally give some insight into why the optimiser chose that query plan over other possibilities.