[Solved] Existing query optimization
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Existing query optimization

We have 5 tables and we are trying to create a view to get the results.

Below is the view which is working fine.

I need suggestions. Is it a good practice to write this query in this way or it can be optimized in a better way.

SELECT p.Pid,  hc.hcid, hc.Accomodation, ghc.ghcid, ghc.ProductFeatures,   wp.existing, wp.acute, mc.cardiaccover, mc.cardiaclimitationperiod
FROM   TableA p 
      LEFT JOIN TableB hc 
             ON p.pid = hc.pid 
      LEFT JOIN TableC ghc 
             ON p.pid = ghc.pid 
      LEFT JOIN (SELECT * 
                 FROM   (SELECT hcid, 
                                title, 
                                wperiodvalue + '-' + CASE WHEN 
                                wperiodvalue > 1 THEN 
                                unit + 
                                        's' ELSE 
                                unit END wperiod 
                         FROM   TableD) d 
                        PIVOT ( Max(wperiod) 
                              FOR title IN (acute, 
                                            existing 
                                           ) ) piv1) wp 
             ON hc.hcid = wp.hcid 
      LEFT JOIN (SELECT * 
                 FROM   (SELECT hcid, 
                                title + col new_col, 
                                value 
                         FROM   TableE 
                                CROSS apply ( VALUES (cover, 
                                            'Cover'), 
                                                     (Cast(limitationperiod AS 
                                                           VARCHAR 
                                                           (10)), 
                                            'LimitationPeriod') ) x (value, col 
                                            )) d 
                        PIVOT ( Max(value) 
                              FOR new_col IN (cardiaccover, 
                                              cardiaclimitationperiod, 
                                              cataracteyelenscover, 
                                              cataracteyelenslimitationperiod
                                             ) ) piv2) mc 
             ON hc.hcid = mc.hcid 

Any suggestions would be appreciated.

Thanks

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries In From Clause (modified query below): The database cannot properly optimize subqueries in the FROM clause. Therefore, we recommend to extract the subqueries to temporary tables, index them and join to them in the outer query.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `TableB` ADD INDEX `tableb_idx_pid` (`pid`);
ALTER TABLE `TableC` ADD INDEX `tablec_idx_pid` (`pid`);
ALTER TABLE `es_temp1` ADD INDEX `es_temp1_idx_hcid` (`hcid`);
ALTER TABLE `es_temp2` ADD INDEX `es_temp2_idx_hcid` (`hcid`);
The optimized query:
SELECT
        p.Pid,
        hc.hcid,
        hc.Accomodation,
        ghc.ghcid,
        ghc.ProductFeatures,
        wp.existing,
        wp.acute,
        mc.cardiaccover,
        mc.cardiaclimitationperiod 
    FROM
        TableA p 
    LEFT JOIN
        TableB hc 
            ON p.pid = hc.pid 
    LEFT JOIN
        TableC ghc 
            ON p.pid = ghc.pid 
    LEFT JOIN
        es_temp1 wp 
            ON hc.hcid = wp.hcid 
    LEFT JOIN
        es_temp2 mc 
            ON hc.hcid = mc.hcid

Related Articles



* original question posted on StackOverflow here.