Slow view SQLA17

[former_SQLA_member1694891]

Hi,

I recently upgrade my sqlanywhere server to 17 from 12.

Since then I have some views that became slow.

For exemple I have this view :

   "DBA"."RBEveView"
as select "E1"."EveID","E1"."EveGPriID","E1"."EveGCamID","E1"."EveGAkiID","E1"."EveGTxtID","E1"."EveGSocID","E1"."EveGEveID","E1"."EveGRapID",
    "E1"."EveGPcaID","E1"."EveGPcvID","E1"."EveACopID","E1"."EveGMelID","E1"."EveGAdpID","E1"."EveGAffID","AKI"."MereID",
    "E1"."EveNuf","E1"."EveCode","E1"."EveSujet","E1"."EveDebDate","E1"."EveFinDate","E1"."EveCategEnu","E1"."EveIsRef",
    "DATE"("E1"."EveDebDate") as "EveDDate","DATE"("E1"."EveFinDate") as "EveFDate","DateFormat"("E1"."EveDebDate",'HH:NN:SS') as "EveDHeure","DateFormat"("E1"."EveFinDate",'HH:NN:SS') as "EveFHeure","E1"."EveIsAllDay","E1"."EveDuree",
    "RESP"."PriCode" as "EveGPriCode","RESP"."PriLib" as "EveGPriLib",
    "Cop"."CopMereCode","Cop"."CopMereLib","PR2"."PrsName" as "EveAPrsName","PR2"."PrsPrenom" as "EveAPrsPrenom",
    "PRIU"."PriCode" as "EveCPriCode","PRIU"."PriLib" as "EveCPriLib",
    "AKI"."AkiIsTache" as "EveGAkiIsTache","AKI"."AkiAlaDate" as "EveGAkiAlaDate","AKI"."AkiAvanct" as "EveGAkiAvanct","AKI"."AkiRepDate" as "EveGAkiRepDate",
    "TXT"."TxtText" as "EveGTxtText",
    "MOC"."MocTel" as "EveAMocTel","MOC"."MocFax" as "EveAMocFax","MOC"."MocPort" as "EveAMocPort","MOC"."MocMail" as "EveAMocMail",
    "E1"."EvePriorNuf","E1"."EveSens","E1"."EveEtatNuf","E1"."EveNiv","E1"."EveSeqID","E1"."EveIsPrive",
    "Cam"."CamCode" as "EveGCamCode","Cam"."CamLib" as "EveGCamLib",
    "Rap"."RapCode" as "EveGRapCode","Rap"."RapLib" as "EveGRapLib",
    "PCA"."PcaCode" as "EvePcaCode",case when "PCA"."PcaDevGCours" <> 0 then "PCA"."PcaMtHT"/"PCA"."PcaDevGCours" else 0 end as "EvePcaMtHTGDev",
    "PCV"."PcvCode" as "EvePcvCode",case when "PCV"."PcvDevGCours" <> 0 then "PCV"."PcvMtHT"/"PCV"."PcvDevGCours" else 0 end as "EvePcvMtHTGDev",
    "Aff"."AffCode" as "EveGAffCode","Aff"."AffLib" as "EveGAffLib",
    "E1"."EveCout","E1"."EveDureePrev",
    "SOC"."SocCode" as "EveGSocCode","SOC"."SocLib" as "EveGSocLib",
    "E2"."EveCode" as "EveGEveCode","E2"."EveSujet" as "EveGEveSujet",
    "E1"."EveIsSupp",
    "E1"."EveFree0","E1"."EveFree1","E1"."EveFree2","E1"."EveFree3","E1"."EveFree4","E1"."EveFree5","E1"."EveFree6","E1"."EveFree7","E1"."EveFree8","E1"."EveFree9",
    "E1"."EveCreaDate","E1"."EveModDate"
    from "DBA"."EVE" as "E1","DBA"."PRI" as "RESP","DBA"."PRI" as "PRIU","DBA"."SOC","DBA"."EVE" as "E2","DBA"."PCA","DBA"."PCV","DBA"."CAM","DBA"."RAP","DBA"."COP","DBA"."PRS" as "PR2","DBA"."AKI","DBA"."TXT","DBA"."MOC","DBA"."AFF"
    where "E1"."EveGAkiID" = "AKI"."AkiID" and "AKI"."MereID" = "PRIU"."PriID"
    and "E1"."EveGEveID" = "E2"."EveID" and "E1"."EveGSocID" = "SOC"."SocID"
    and "E1"."EveGCamID" = "CAM"."CamID" and "E1"."EveGRapID" = "RAP"."RapID"
    and "E1"."EveGPcaID" = "PCA"."PcaID" and "E1"."EveGPcvID" = "PCV"."PcvID"
    and "E1"."EveGPriID" = "RESP"."PriID" and "E1"."EveGAffID" = "AFF"."AffID"
    and "E1"."EveACopID" = "COP"."CopID" and "COP"."CopGPrsID" = "PR2"."PrsID" and "COP"."CopGMocID" = "MOC"."MocID"
    and "E1"."EveGTxtID" = "TXT"."TxtID" and "E1"."EveID" > 0;

A select on this view in SQLA12 give me a result in a second, but now with SQLA17 it takes 12 second to have the result.

BUT if we add a "join" on another table the same select give me the exact same result than before but in a second. So why ?

The "new" view that work fine :

RBEveView   create view "DBA"."RBEveView" as select "E1"."EveID","E1"."EveGPriID","E1"."EveGCamID","E1"."EveGAkiID","E1"."EveGTxtID","E1"."EveGSocID","E1"."EveGEveID","E1"."EveGRapID",
    "E1"."EveGPcaID","E1"."EveGPcvID","E1"."EveACopID","E1"."EveGMelID","E1"."EveGAdpID","E1"."EveGAffID","AKI"."MereID",
    "E1"."EveNuf","E1"."EveCode","E1"."EveSujet","E1"."EveDebDate","E1"."EveFinDate","E1"."EveCategEnu","E1"."EveIsRef",
    "DATE"("E1"."EveDebDate") as "EveDDate","DATE"("E1"."EveFinDate") as "EveFDate","DateFormat"("E1"."EveDebDate",'HH:NN:SS') as "EveDHeure","DateFormat"("E1"."EveFinDate",'HH:NN:SS') as "EveFHeure","E1"."EveIsAllDay","E1"."EveDuree",
    "RESP"."PriCode" as "EveGPriCode","RESP"."PriLib" as "EveGPriLib",
    "Cop"."CopMereCode","Cop"."CopMereLib","PR2"."PrsName" as "EveAPrsName","PR2"."PrsPrenom" as "EveAPrsPrenom",
    "PRIU"."PriCode" as "EveCPriCode","PRIU"."PriLib" as "EveCPriLib",
    "AKI"."AkiIsTache" as "EveGAkiIsTache","AKI"."AkiAlaDate" as "EveGAkiAlaDate","AKI"."AkiAvanct" as "EveGAkiAvanct","AKI"."AkiRepDate" as "EveGAkiRepDate",
    "TXT"."TxtText" as "EveGTxtText",
    "MOC"."MocTel" as "EveAMocTel","MOC"."MocFax" as "EveAMocFax","MOC"."MocPort" as "EveAMocPort","MOC"."MocMail" as "EveAMocMail",
    "E1"."EvePriorNuf","E1"."EveSens","E1"."EveEtatNuf","E1"."EveNiv","E1"."EveSeqID","E1"."EveIsPrive",
    "Cam"."CamCode" as "EveGCamCode","Cam"."CamLib" as "EveGCamLib",
    "Rap"."RapCode" as "EveGRapCode","Rap"."RapLib" as "EveGRapLib",
    "PCA"."PcaCode" as "EvePcaCode",case when "PCA"."PcaDevGCours" <> 0 then "PCA"."PcaMtHT"/"PCA"."PcaDevGCours" else 0 end as "EvePcaMtHTGDev",
    "PCV"."PcvCode" as "EvePcvCode",case when "PCV"."PcvDevGCours" <> 0 then "PCV"."PcvMtHT"/"PCV"."PcvDevGCours" else 0 end as "EvePcvMtHTGDev",
    "Aff"."AffCode" as "EveGAffCode","Aff"."AffLib" as "EveGAffLib",
    "E1"."EveCout","E1"."EveDureePrev",
    "SOC"."SocCode" as "EveGSocCode","SOC"."SocLib" as "EveGSocLib",
    "E2"."EveCode" as "EveGEveCode","E2"."EveSujet" as "EveGEveSujet",
    "E1"."EveIsSupp",
    "E1"."EveFree0","E1"."EveFree1","E1"."EveFree2","E1"."EveFree3","E1"."EveFree4","E1"."EveFree5","E1"."EveFree6","E1"."EveFree7","E1"."EveFree8","E1"."EveFree9","E1"."EveFree10",
    "E1"."EveCreaDate","E1"."EveModDate"
    from "DBA"."EVE" as "E1","DBA"."PRI" as "RESP","DBA"."PRI" as "PRIU","DBA"."SOC","DBA"."EVE" as "E2","DBA"."PCA","DBA"."PCV","DBA"."CAM","DBA"."RAP","DBA"."COP","DBA"."PRS" as "PR2","DBA"."AKI","DBA"."TXT","DBA"."MOC","DBA"."AFF","DBA"."ADP" as "A1"
    where "E1"."EveGAkiID" = "AKI"."AkiID" and "AKI"."MereID" = "PRIU"."PriID"
    and "E1"."EveGEveID" = "E2"."EveID" and "E1"."EveGSocID" = "SOC"."SocID"
    and "E1"."EveGCamID" = "CAM"."CamID" and "E1"."EveGRapID" = "RAP"."RapID"
    and "E1"."EveGPcaID" = "PCA"."PcaID" and "E1"."EveGPcvID" = "PCV"."PcvID"
    and "E1"."EveGPriID" = "RESP"."PriID" and "E1"."EveGAffID" = "AFF"."AffID" and "E1"."EveGAdpID" = "A1"."AdpID"
    and "E1"."EveACopID" = "COP"."CopID" and "COP"."CopGPrsID" = "PR2"."PrsID" and "COP"."CopGMocID" = "MOC"."MocID"
    and "E1"."EveGTxtID" = "TXT"."TxtID" and "E1"."EveID" > 0;

[VolkerBarth]

You could check the graphical plans of both views to find a difference, say a different index scan or the like, or you could provide us with those plans.

A very general suggestion: I would recommend to use JOIN conditions to connect tables (i.e. "FROM table1 inner join table2 on table1.id = table2.id inner join...") instead of using a list of tables in the FROM clause and then using WHERE conditions to specify the join conditions there. It should usually not influence the performance (and so might not help your particular issue) but it certainly helps others to understand your query code, and it helps to distinguish join conditions from other condititions...

[VolkerBarth]

As both views seem to use intra-query parallelism (as presented by the "Exchange" plan item), I would suggest that you prevent intra-query parallelism temporarily, i.e. by setting

set temporary option max_query_tasks = '1';

and re-run both queries and compare results...

Note, I have also noticed several cases where v16/v17 performed significantly worse than previous versions when intra-query parallelism was used, cf. that (not fully unanswered) FAQ, so I disabled that option for some queries...

[former_SQLA_member1694891]

Indeed I need a little help with the plans. I just start using it and there is a lot of informations and I don't find so much information in the online documetation.

I made a plan when it slow and one when it fast, you can download them here.

Thanks for your help.

[VolkerBarth]

Have you tried with the max_query_tasks option?

And do you still have a v12 version running so you can compare the v17 plans with that of v12?

[former_SQLA_member1694891]

Hi,

Sorry for the delay.

Has you recommend I used join for the view but it didn't change anything in term of speed.

Here the graphical plan whith the slow view :

And the graphical plan whith the quick view :

[chris_keating]

These plans show that for the slower query, the estimated number of rows returned is 4.6 million vs 12000 rows for the faster plan. It is not possible to explain that difference with just the screen caps of the plan. For plans to be useful in troubleshooting performance issues, it should be obtained with detailed and node statistics. This would be setting "Niveau des statistiques" to "Statistiques détaillées et statistiques sur le noeud", execute the query/view using "Obtenir le plan". This will provide the actual plan used during the execution as well as information on how the plan was derived. If you need help with the plans, save the plans using "Enregistrer sous" and attach or open a support incident.