The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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;

asked 27 Apr, 04:49

Ben8sens's gravatar image

Ben8sens
4118
accept rate: 25%

1

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...

(27 Apr, 08:54) Volker Barth

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...

permanent link

answered 07 May, 09:55

Volker%20Barth's gravatar image

Volker Barth
32.4k328476692
accept rate: 32%

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 :

alt text

And the graphical plan whith the quick view :

alt text

permanent link

answered 07 May, 09:22

Ben8sens's gravatar image

Ben8sens
4118
accept rate: 25%

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.

(07 May, 14:41) Chris Keating

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.

permanent link

answered 11 May, 11:31

Ben8sens's gravatar image

Ben8sens
4118
accept rate: 25%

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?

(14 May, 03:04) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×27

question asked: 27 Apr, 04:49

question was seen: 292 times

last updated: 14 May, 03:05