Weak performance of query

I need help in improving very poor query performance.

Query

SELECT DISTINCT
        ROW_NUMBER () over  (order by  WR.WPIS_ID)  AS ANA_PROJEKT_GRAFIK_ID
        ,
    --WR.WPIS_ID  as ANA_PROJEKT_GRAFIK_ID,
    PGR.OSOBA_ID AS PGR_ORG_ZASOB_ID,
    M.OSOBA_ID AS PGR_ORG_ZASOB_MANAGER_ID,
    GPZ.ZADANIE_ID AS   PGR_ZADANIE_ID,
    w.WPIS_ID as PGR_ZADANIE_PRZYDZIAL_ID,
    wr.WPIS_ID as PGR_ZADANIE_REALIZACJA_ID,
    kgp.KONF_GRAFIKPRACY_NAZWA as "PGR Konfiguracja grafika" , 
    OSOBA.OSOBA_NAZWIM AS "PGR Nazwisko i imiê" ,
    OSOBA.OSOBA_SKROT AS "PGR Skrót",
    M.OSOBA_NAZWIM AS "PGR Manager grafika" ,
    M.OSOBA_SKROT AS "PGR Mgr skr.",
    'Grafik od: '+  CONVERT(nvarchar, PGR.GRAFIKPRACY_DATAOD, 102)  +' do: '+ CONVERT(nvarchar, PGR.GRAFIKPRACY_DATADO, 102) AS   "PGR Nazwa grafika",
    PGR.GRAFIKPRACY_DATAOD AS  "PGR Data od",
    PGR.GRAFIKPRACY_DATADO AS   "PGR Data do",
    CASE PGR.GRAFIKPRACY_STATUS 
        WHEN 1 THEN 'Otwarty'
        when 2 then 'Do zatwierdzenia'
        when 3 then 'Analizowany'
        when 4 then 'Zatwierdzony'
        when 5 then 'Zwrócony'
    END AS  "PGR Status",
    case pgr.GRAFIKPRACY_STATUSLICZONY 
        when 1 then 'Otwarty'
        when 2 then 'Zamkniêty'
    END As "PGR Status adm.",

    cAST(Z.zadanie_nr AS nvarchar ) + '/' + Z.zadanie_numer  AS          "PGR Nr zadania",
    Z.ZADANIE_DOTYCZY AS  "PGR Nazwa zadania",
    z.zadanie_drozpplan as "PGR Data zadania od", 
    z.ZADANIE_DZAKPLAN  as "PGR Data zadania do",
    W.WPIS_TEMAT AS "PGR Nazwa przydzia³u", 

    wr.WPIS_TEMAT as "PGR Nazwa realizacji",
    Wr.WPIS_CZPCZAS  /60.0 as "PGR Czas pracy", 
    WR.WPIS_CZPCZAS_UDZ  /60.0 as "PGR Czas UDZ", 
    WR.WPIS_DATAWPISU as "PGR Data realizacji", 

    --STATUSOG.STATUSOG_NAZWA AS   "PGR Status ogólny",
    STATUSZAD.STATUSZAD_NAZWA AS "PGR Status zadania",
    pgr.GRAFIKPRACY_UWAGIPRACOWNIKA as "PGR Uwagi pracownika",
    pgr.GRAFIKPRACY_UWAGIMANAGERA as "PGR Uwagi managera",
    kgp.KONF_GRAFIKPRACY_OPIS as "PGR Szczegó³owy opis grafika",
    kgp.KONF_GRAFIKPRACY_OKRESNOTOWANIA as "PGR Okres notowania pracy",
    kgp.KONF_GRAFIKPRACY_DZIEN_START as "PGR Start grafika w dzien tyg",
    --kgp.KONF_GRAFIKPRACY_ZEZW_CZASY_PROJEKT_ZAMKN as "PGR WprowadŸ czas w proj zamk",
    kgp.KONF_GRAFIKPRACY_ZEZW_CZASY_PRZYSZLE_DATY as "PGR WprowadŸ czas na przysz³",
    --kgp.KONF_GRAFIKPRACY_PRZENIES_NIEZAKONCZONE as "PGR Przenieœ zakoñczone",
    --kgp.KONF_GRAFIKPRACY_DODAJ_ZADANIA_Z_OKRESU as "PGR Dodawanie zadañ z okresu",
    kgp.KONF_GRAFIKPRACY_DODAJ_ZADANIA_Z_OKRESU_NAST as "PGR Dodanie zad z okresu nast",
    kgp.KONF_GRAFIKPRACY_BLOKUJ_PO_N_DNIACH as "PGR Blokuj po dniach",
    kgp.KONF_GRAFIKPRACY_BLOKUJ_W_N_DNIU_MIESIACA as "PGR Blokuj w dniu miesi¹ca",
    kgp.KONF_GRAFIKPRACY_LICZBA_OKRESOW as "PGR Liczba okresów",
    kgp.KONF_GRAFIKPRACY_DATA_OD as "PGR Pocz¹tek konfiguracji",
    kgp.KONF_GRAFIKPRACY_AKTYWNA as "PGR Konfiguracja aktywna",
    --kgp.KONF_GRAFIKPRACY_BLOK_DOD_SPOZA_OKR as "PGR Blokuj dod spoza okresu",
    kgp.KONF_GRAFIKPRACY_POKAZ_BIEZ_TYDZ as "PGR Pokazuj bie¿¹cy tydzieñ",
    pgr.KMODINS as "PGR Utworzony przez",
    pgr.DMODINS as "PGR Data utworzenia",
    pgr.KMOD as "PGR Zmodyfikowany przez",
    pgr.DMOD as "PGR Data modyfikacji",
    z.PAKIETZAD_ID as PGR_PROJEKT_ID,
    pz.PAKIETZAD_NAZWA as "PGR Nazwa projektu",
    pz.PAKIETZAD_WBS as "PGR Kod projektu"
FROM GRAFIKPRACY pgr
    INNER JOIN OSOBA ON (PGR.OSOBA_ID = OSOBA.OSOBA_ID)
    INNER JOIN GRAFIKPRACZADANIE GPZ ON (PGR.GRAFIKPRACY_ID = GPZ.GRAFIKPRACY_ID)
    JOIN ZADANIE z ON (GPZ.ZADANIE_ID = Z.ZADANIE_ID)
    join wpis w on (z.ZADANIE_ID= w.ZADANIE_ID )
    JOIN OSOBAROLA OSR ON (OSR.WPIS_ID  = W.WPIS_ID AND OSOBA.OSOBA_ID = OSR.OSOBA_ID )
    JOIN FUNKCJAROLA FR ON (OSR.FUNKCJAROLA_ID = FR.FUNKCJAROLA_ID 
    AND FR.FUNKCJAROLA_TYP = 1 --after i remove this condition performance is very good (25 seconds vs 30 minutes)
    )
    join wpis wr on (z.ZADANIE_ID = W.ZADANIE_ID AND WR.WPIS_DATAWPISU >=PGR.GRAFIKPRACY_DATAOD  AND  WR.WPIS_DATAWPISU<=PGR.GRAFIKPRACY_DATADO and w.wpis_id = wr.WPIS_IDOJCA )
    JOIN TRODZAJWPISU TW ON (TW.TRODZAJWPISU_ID = WR.TRODZAJWPISU_ID AND TW.TRODZAJWPISU_RODZAJWPISU = 'R' ) -- REALIZACJA
    INNER JOIN STATUSZAD ON (Z.STATUSZAD_ID = STATUSZAD.STATUSZAD_ID)
    --INNER JOIN STATUSOG ON (STATUSZAD.STATUSOG_ID = STATUSOG.STATUSOG_ID)
    join KONF_GRAFIKPRACY kgp on (kgp.KONF_GRAFIKPRACY_ID = pgr.KONF_GRAFIKPRACY_ID )
    join (select  kgm.OSOBA_ID, kgm.KONF_GRAFIKPRACY_ID  
            from KONF_GRAFIK_MEN kgm
            join KONF_GRAFIKPRACY kg on (kg.KONF_GRAFIKPRACY_ID = kgm.KONF_GRAFIKPRACY_ID and kgm.KONF_GRAFIK_MEN_RODZAJ =1)) 
            as Manger on (Manger.KONF_GRAFIKPRACY_ID = pgr.KONF_GRAFIKPRACY_ID )
    join osoba m on (Manger.OSOBA_ID = m.OSOBA_ID)
    left join PAKIETZAD pz on (z.PAKIETZAD_ID=pz.PAKIETZAD_ID)
--where 
--osoba.OSOBA_SKROT ='BZM'   
--ORDER BY PGR_ZADANIE_ID, WR.WPIS_DATAWPISU

Execution plans

• Poor performance
• Good performance

Execution time is about half hour. What is interesting when I remove condition AND FR.FUNKCJAROLA_TYP = 1 execution time is about 25 seconds and execution plan changes at all.

Let’s start with why the poorly performing query is slow. Poor cardinality estimates from joins cause an inefficient ordering of tables. Not only does SQL Server do over 100 million key lookups (which can be slow), it also processes 167 million rows only to have those rows filtered down to 123k rows by a join to WPIS. That’s a lot of unnecessary work which could have been avoided with a different join order. SQL Sentry Plan Explorer makes this easy to see:

The query which had good performance has a completely different join order. Reading from right to left, the joins to WPIS happen at the start of the plan. There’s no longer a step that processes millions of rows:

From the information that you’ve provided it’s not clear why removing the AND FR.FUNKCJAROLA_TYP = 1 filter would lead to a much more efficient query. Given all of the issues with the join cardinality estimates I suspect that it’s partially due to luck.

As a first attempt I would focus on fixing the cardinality estimates. Updating statistics may help. In case it doesn’t you can consider putting part of the query into a temp table. This has the effect of forcing part of the query to be evaluated first. Based on what we’re seeing here you want the joins to WPIS to happen early on in the plan so those tables should definitely be part of the table definition. Another advantage of putting rows into a temp table is that SQL Server will gather statistics on it so you’ll get a very accurate cardinality estimate for that part of the plan.

I would start by putting these tables into a temp table:

GRAFIKPRACZADANIE 
wpis 
KONF_GRAFIKPRACY 
TRODZAJWPISU 
KONF_GRAFIK_MEN 

I picked those five because that’s the point in which the cardinality estimate drops to 1 in the good plan. With a temp table it’s possible that you’ll get better performance than the 25 seconds that you experienced by removing the filter.

I will suggest to rewrite your query. Why?
You are not using columns of this many tables

OSOBAROLA OSR ,FUNKCJAROLA FR, TRODZAJWPISU TW ,Manger

So why use them in join. Use them using EXISTS clause. Also this way there will be many EXISTS clauses so use CTE. You get benefits of both CTE and EXISTS.

Benefits of EXISTS can be like, you can get rid of DISTINCT. You are getting bad performance because of DISTINCT. Also you can rid of poor cardinality problem.

If everything is clear then query is very easy to write.

Also why are you using Row_Number function when you are not using. If you are using some SERIALNO in front end then think of generating SERIALNO in front end.

Try this:

create index IXFK_OSOBAROLA_OSOBA_ID_WPIS_ID on dbo.OSOBAROLA (WPIS_ID, OSOBA_ID)
include (FUNKCJAROLA_ID)
with (DROP_EXISTING = ON);