Better sql query for performace

Question

I have a query that looks like :

    select *
      from Franvaro f 
inner join Anvandare a on a.id = f.anvandare_id
     where (
                f.friskskriven = 'Y'
            and not exists (
                    select * 
                      from sscpost
                     where franvaro_id = f.id
                       and friskskrivenpost = 'Y'
                )
           )
        or (
                f.startdatum <= '2020-04-05' 
            and not exists ( 
                    select *
                      from sscpost
                     where franvaro_id = f.id
                       and friskskrivenpost = 'Y'
                )
           )
       and a.anstallningtyp = 'A'
         ;

I wonder if this query could be written in another way with the same output/result in order to have a better performance or better structure.I have tested also with this query(see below), but the result is not the same compared to the other query.

    select * 
      from Franvaro f 
inner join Anvandare a on a.id = f.anvandare_id
     where f.friskskriven = 'Y' 
        or f.startdatum <= '2020-04-05'
       and not exists ( 
               select * 
                 from sscpost 
                where franvaro_id = f.id
                  and friskskrivenpost = 'Y'
           )
       and a.anstallningtyp = 'A'
         ;

Sample data, desired results, an explanation of the logic, and an appropriate database tag would all help. — Gordon Linoff, Apr 13 '20 at 23:59
In the first query, records selected due matching the `f.friskskriven = 'Y'` condition and not matching the `startdatum` condition are further constrained by the `not exists` clause. This restriction is missing in the second clause. Note that in evaluating the where clause conditions, `and` binds stronger than `or`. This holds across most sql dialects, see [this SO answer](https://stackoverflow.com/a/1241158). — collapsar, Apr 14 '20 at 00:14

score 0 · Answer 1 · answered Apr 14 '20 at 00:18

This might not matter with modern optimizers anymore, but in your EXISTS clause, you don't actually need anything returned. Therefore you might return 1 or NULL or something. I'm showing my age.

At the end of the day, the best way to optimize queries is to learn how to read their EXPLAIN PLAN's. Every SQL system works differently, and there sometimes isn't 1 best way. It will also depend on the size of these tables and what the data looks like.

But there is nothing inherently wrong with your code, it looks fine.

    select *
      from Franvaro f 
inner join Anvandare a on a.id = f.anvandare_id
     where (
                f.friskskriven = 'Y'
            and not exists (
                    select 1 
                      from sscpost
                     where franvaro_id = f.id
                       and friskskrivenpost = 'Y'
                )
           )
        or (
                f.startdatum <= '2020-04-05' 
            and not exists ( 
                    select 1
                      from sscpost
                     where franvaro_id = f.id
                       and friskskrivenpost = 'Y'
                )
           )
       and a.anstallningtyp = 'A'
         ;

score 0 · Answer 2 · answered Apr 14 '20 at 01:28

Try:

SELECT *
FROM Franvaro f 
INNER JOIN Anvandare a ON a.id = f.anvandare_id
WHERE a.anstallningtyp = 'A'
 AND ( f.friskskriven = 'Y' OR f.startdatum <= '2020-04-05' )
 AND NOT EXISTS
   (
     SELECT null
     FROM sscpost
     WHERE franvaro_id = f.id
        AND friskskrivenpost = 'Y'
     )

Mainly we combine the NOT EXISTS statement into one call

Better sql query for performace

2 Answers2