Optimization of SQL query with multiple unions

Question

Does anybody know how to make this query a bit shorter? The problem, and reason why I am asking this is is that it takes a long time to process the query because the query returns over 16K records.

SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem,
         1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
         s.osnov*s.kol_dok AS osnovica, s.stopa, s.premija*s.kol_dok AS premija 
     FROM osiguranje.police_osn p, osiguranje.st_pol s 
     WHERE p.broj=s.broj AND 
        XUvjet+ AND s.vs='SO' AND s.premija<>0 
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem, 
        1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
        s.osnov AS osnovica, s.stopa, s.premija 
     FROM osiguranje.police_osn p, osiguranje.st_pol s 
     WHERE p.broj=s.broj AND 
        XUvjet AND s.vs='DO' AND s.premija<>0 
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem, 
        1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
        s.osnov AS osnovica, s.stopa, -s.premija AS premija 
    FROM osiguranje.police_osn p, osiguranje.st_pol s 
    WHERE p.broj=s.broj AND 
        XUvjet AND s.vs='PO' AND s.premija<>0 
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem, 
         1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
         s.osnov*s.kol_dok AS osnovica, s.stopa, s.premija*s.kol_dok AS premija 
    FROM osiguranje.police_osn p, osiguranje.st_pril s 
    WHERE p.broj=s.broj AND 
        XUvjet AND s.vs='SO' AND s.premija<>0 
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem, 
        1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
        s.osnov AS osnovica, s.stopa, s.premija 
    FROM osiguranje.police_osn p, osiguranje.st_pril s 
    WHERE p.broj=s.broj AND 
        XUvjet AND s.vs='DO' AND s.premija<>0 
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem, 
        1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
        s.osnov AS osnovica, s.stopa, -s.premija AS premija 
    FROM osiguranje.police_osn p, osiguranje.st_pril s 
    WHERE p.broj=s.broj AND 
        XUvjet AND s.vs='PO' AND s.premija<>0 
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, q.nac_prem, 
        0 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
        s.osnov AS osnovica, s.stopa, s.premija 
    FROM osiguranje.police_osn p, osiguranje.skup_os q, osiguranje.st_pol s 
    WHERE p.proizvod=q.sifra AND p.broj=s.broj AND 
        XUvjet AND s.vs='DK' AND s.stopa<>0 
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, q.nac_prem, 
        0 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
        s.osnov AS osnovica, -s.stopa AS stopa, s.premija 
    FROM osiguranje.police_osn p, osiguranje.skup_os q, osiguranje.st_pol s 
    WHERE p.proizvod=q.sifra AND p.broj=s.broj AND 
        XUvjet  AND s.vs='PK' AND s.stopa<>0 
ORDER BY p.broj, gr_red, s.rbr, s.serial

XUvjet is a variable that'll be inserted in a C# code.

Vahid · Accepted Answer · 2018-02-23 07:29:48Z

1

For the s.vs in WHERE clause you can easily use the IN operator:

WHERE s.vs IN ['PK', 'DK', 'PO', ...]

For the gr_red in SELECT clause you can use the CASE-WHEN operator:

SELECT  ' ' AS nac_prem, gr_red =
      CASE
         WHEN s.vs='PK' THEN 0
         WHEN  XUvjet AND s.vs='PO' THEN 1
         ELSE 1
      END,

Obviously you can add more logic to the WHEN clause here to make it fit the logic of your query.

answered Feb 23, 2018 at 7:29

Vahid

1,9791 gold badge24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jwolf · Accepted Answer · 2018-02-23 07:51:21Z

1

You can combine all those Where clauses, like this:

SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem,
         1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
         s.osnov*s.kol_dok AS osnovica, s.stopa, s.premija*s.kol_dok AS premija 
     FROM osiguranje.police_osn p, osiguranje.st_pol s 
     WHERE (p.broj=s.broj AND XUvjet+ AND s.vs='SO' AND s.premija<>0)
         or(p.broj=s.broj AND XUvjet AND s.vs='DO' AND s.premija<>0)
         or (p.broj=s.broj AND XUvjet AND s.vs='PO' AND s.premija<>0)
UNION ALL 
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, ' ' AS nac_prem, 
         1 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
         s.osnov*s.kol_dok AS osnovica, s.stopa, s.premija*s.kol_dok AS premija 
    FROM osiguranje.police_osn p, osiguranje.st_pril s 
    WHERE (p.broj=s.broj AND XUvjet AND s.vs='SO' AND s.premija<>0)
        or (p.broj=s.broj AND XUvjet AND s.vs='DO' AND s.premija<>0)
        or (p.broj=s.broj AND XUvjet AND s.vs='PO' AND s.premija<>0)
UNION ALL    
SELECT p.broj, p.mje_osig, p.id_cjen, p.poc_dat, p.ist_dat, q.nac_prem, 
        0 AS gr_red, s.rbr, s.serial, s.vs, s.stat_id, s.naziv, 
        s.osnov AS osnovica, s.stopa, s.premija 
    FROM osiguranje.police_osn p, osiguranje.skup_os q, osiguranje.st_pol s 
    WHERE (p.proizvod=q.sifra AND p.broj=s.broj AND XUvjet AND s.vs='DK' AND s.stopa<>0)
        or (p.proizvod=q.sifra AND p.broj=s.broj AND XUvjet  AND s.vs='PK' AND s.stopa<>0)

I hope this helps.

edited Feb 23, 2018 at 7:51

answered Feb 23, 2018 at 7:26

jwolf

9388 silver badges13 bronze badges

7 Comments

Levidoom Over a year ago

Thanks for the help, but if you look closely you will see that the 4th SELECT section looks in a different table called st_pril instead of st_pol. But this looks very interesting :)

jwolf Over a year ago

@Levidoom - Ah, yes - and I notice now that there is also a third. You can use this same method but it looks like you will need 3 queries with 2 unions.

jwolf Over a year ago

@Levidoom - I updated this solution to include all three table combinations.

Levidoom Over a year ago

Thanks again, but... (I'm starting to hate the word "but") this method will turn into the original query if you account all the differences in all the SELECT sections. :(

jwolf Over a year ago

@Levidoom - I;m afraid that given the fact each section is a combination of different tables and you're looking to append the results from each section, you will have to use Union this way.

|

Collectives™ on Stack Overflow

Optimization of SQL query with multiple unions

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related