Query parallelism

[former_SQLA_member1694891]

Hi,

I have a big view with aproximatly 30 join. This view can return millions rows. Sometimes when I do a select * from this view the server takes only 1 CPU core (logical) and sometime it takes the 8 CPU cores.

I'm using SQL Anywhere Network Server Version 17.0.10.5745 on macOS 10.14.

So I made some tests and I found that if a table in my join have only one row and all the other table have multiple rows, the serveur use 1 CPU core, but if all the join tables have at least 2 rows then the server takes 8 CPU cores.

The view :

CREATE VIEW RBPlvViewS AS SELECT 
    PlvID,PlvGPcvID,PcvGSocID,PlvGCliID,PlvRPriID,PlvGArtID,PlvDDepID,PlvGCarID,PlvGProID,PlvGPlaID,PcvPAdpID,PcvLAdpID,
    PlvADepID,PlvPAruID,PlvGTarID,PlvGCptID,PlvGAffID,PlvGFraID,PcvSAruID,PcvGPecID,PlvCPriID,PcvGPnaID,
    PlvPnaNuf, PnaCode,
    PerYYYYMM, PerNum, ExeCode,
    PlvDate, PlvSuivDate, PlvCodePcv, PcvEtatNuf, PcvRef,
    PlvNuf, PlvCode, PlvLib,
    PlvQteUS, PlvQteUV, PlvMtGHT, PlvMtCHT,
    PlvPUBrut,PlvRemise, PlvMtTax, PlvMtRemLig, PlvPUNet, PlvMarge, PlvMtNet, CASE WHEN PlvMtGHT<>0 THEN PlvMarge/PlvMtGHT ELSE 0 END PlvMargePC,
    PlvFraisArt, PlvFraisPcf, PlvPA, PlvPMP, PlvCUMP, PlvPR,
    PlvPoids, PDS.AruCode PlvPAruCode, PlvNumColis, PlvNbColis,
    PlvVolume, VOL.AruCode PlvSAruCode, PlvEmpl,
    PlvQteTr, PlvIsSoldee, PlvIsForfait, PlvIsRem, PlvIsDetN,
    A1.ArtCode, A1.ArtLib, A1.ArtNomencNuf, A1.ArtStkNuf, UVE.AruCode PlvVAruCode, UVE.AruLib PlvVAruLib,
    A1.ArtCeeCod, A1.ArtCeeProduit, PlvMasse, A1.ArtIsUnitSup, PlvRegimeEnu, PlvNatureEnu,
    A1.ArtLongueur, A1.ArtLargeur, A1.ArtHauteur,
    A1.ArtGammeEnu, A1.ArtFamilleEnu, A1.ArtCategEnu, A1.ArtNatureEnu, A1.ArtCollectEnu,
    A1.ArtAxe1, A1.ArtAxe2, A1.ArtAxe3, A1.ArtTaux, A1.ArtPoints, A1.ArtIsCaCar,
    A1.ArtFree0, A1.ArtFree1, A1.ArtFree2, A1.ArtFree4, A1.ArtFree5, A1.ArtFree6, A1.ArtFree7, A1.ArtFree8, A1.ArtFree9,
    A2.ArtCode ArtGArtCode, A2.ArtLib ArtGArtLib,
    CarCode, CarLib, CarIsFinite,
    CptCode, CptLib, PlvAxe8, PlvNumLig, PcvLib,
    AffCode, AffLib,
    PRR.PriCode PlvRPriCode, PRR.PriLib PlvRPriLib, PlvComm, PlvComm*PlvMtGHT PlvCommHT, PlvComm*PlvMarge PlvCommMg,
    PRC.PriCode PlvCPriCode, PRC.PriLib PlvCPriLib,
    FraCode, FraLib, FraTaux,
    DEPD.DepCode PlvDDepCode, DEPA.DepCode PlvADepCode,
    TyvCode, TyvLib,
    CliCode, CliLib, CliCategEnu, CliActivEnu, CliGeoEnu, CliBranEnu, CliNAF, CliNIF, CliAxe1, CliAxe2, CliAxe3, PysCode, PysLib,
    CliFree0, CliFree1, CliFree2, CliFree3, CliFree4, CliFree5, CliFree6, CliFree7, CliFree8, CliFree9,
    PRO.ProCode PlvGProCode, PRO.ProLib PlvGProLib, FOU.FouCode PlvGFouCode, FOU.FouLib PlvGFouLib,
    PLA.PlaCode PlvGPlaCode, PLA.PlaLib PlvGPlaLib, PLA.PlaCodePca PlvGPlaCodePca,
    TarCode, TarLib,
    SocCode, SocLib, PcvNiv, PcvMtHT, PcvMtTTC, PcvMtTotal, PcvMtRegle, PcvEtatFNuf,
    PRIC.PriCode PcvGPriCode, PRIC.PriLib PcvGPriLib,
    PlvFree0, PlvFree1, PlvFree2, PlvFree3, PlvFree4, PlvFree5, PlvFree6, PlvFree7, PlvFree8, PlvFree9,
    D1.AdpLib PcvPAdpLib, D1.AdpRue1 PcvPAdpRue1, D1.AdpRue2 PcvPAdpRue2, D1.AdpRue3 PcvPAdpRue3, D1.AdpZip PcvPAdpZip, D1.AdpCity PcvPAdpCity, D1.AdpRgn PcvPAdpRgn, D1.AdpLand PcvPAdpLand, 
    D2.AdpLib PcvLAdpLib, D2.AdpRue1 PcvLAdpRue1, D2.AdpRue2 PcvLAdpRue2, D2.AdpRue3 PcvLAdpRue3, D2.AdpZip PcvLAdpZip, D2.AdpCity PcvLAdpCity, D2.AdpRgn PcvLAdpRgn, D2.AdpLand PcvLAdpLand

FROM PLV, PCV, PNA, ART A1, ART A2, CLI, TYV, DEP DEPD, DEP DEPA, ARU PDS, ARU VOL, ARU UVE, CPT, AFF, PRI PRR, TAR, SOC, PER, FRA, DEV, CAR, EXE, PRO, FOU, PLA, PRI PRIC, PRI PRC, PYS, Adp D1, Adp D2
WHERE PLV.PlvGPcvID = PCV.PcvID AND PCV.PcvGPnaID = PNA.PnaID AND PCV.PcvGExeID = EXE.ExeID 
    AND PCV.PcvGTyvID = TYV.TyvID AND PCV.PcvGSocID = SOC.SocID 
    AND PCV.PcvGPerID = PER.PerID AND PCV.PcvGPriID = PRIC.PriID 
    AND PCV.PcvSAruID = VOL.AruID AND PCV.PcvGDevID = DEV.DevID 
    AND PLV.PlvGArtID = A1.ArtID AND A1.ArtGArtID = A2.ArtID AND A1.ArtVAruID = UVE.AruID 
    AND PLV.PlvGCliID = Cli.CliID AND PLV.PlvGFraID = FRA.FraID  AND CLI.CliGPysID = PYS.PysID
    AND PLV.PlvDDepID = DEPD.DepID AND PLV.PlvADepID = DEPA.DepID 
    AND PLV.PlvPAruID = PDS.AruID AND PLV.PlvGTarID = TAR.TarID 
    AND PLV.PlvGCptID = CPT.CptID AND PLV.PlvGAffID = AFF.AffID 
    AND PLV.PlvRPriID = PRR.PriID AND PLV.PlvCPriID = PRC.PriID 
    AND PLV.PlvGProID = PRO.ProID AND PRO.ProGFouID = FOU.FouID 
    AND PLV.PlvGPlaID = PLA.PlaID AND PLV.PlvGCarID = CAR.CarID 
    AND PCV.PcvPAdpID = D1.AdpID AND PCV.PcvLAdpID = D2.AdpID
    AND PNA.PnaSsStaNuf <> 'NufPnaSsStaNo' AND PlvIsStat = 'X' AND PlvNuf <> 'NufArtTexte' AND PlvID>0;

For exemple, if in all the join tables I have at least 2 rows except in the table CAR where I just have the default row, the server use 1 CPU core, but if I insert a row in the table CAR the server use all the CPU cores.

Did someone experience the same behavior ? This behavior generate a lack of performance on the big database. For exemple with 6 million rows returned by the view, with 1 CPU core it takes over 5min to get the result and with the 8 CPU cores it takes less than a minute to get the result.

[former_SQLA_member1694885]

The query plan and statistics should provide a hint. The way you describe the behavior (> 5 min sequential; < 1 min 8 way parallel) indicates that parallel execution is efficient. My first look would be at the differences between the two execution plans (seq vs par) and specifically where estimated values differ from actual values.

HTH

Volker
DB-TecKy

[former_SQLA_member1694891]

Hi,

Nobody have seen this before ? Or had a clue ? Or an opinion ?

😞

[Breck_Carter]

Nobody is getting paid to answer questions here. Sometimes folks are busy with their regular jobs, or they are on vacation (in July).

I am very sorry that nobody told you to look at the query plan until now. If you are not satisfied with the service, there is a money back guarantee.

[former_SQLA_member1694891]

I think you get it wrong. I know nobody is paid. May be that successive questions are not reading friendly as I was thinking.

I just reply to put the question on top of the list, and if I have a little luck somebody will have something for me.

I tried the query plan but it's a little smoggy for me, but I will retry it and post it here.

[Breck_Carter]

Please accept my apology for the harsh comment, and the delay in this reply... I blame Hurricane Dorian ( but that's a feeble excuse 🙂

[Breck_Carter]

SQL Anywhere performance tuning is mostly unnecessary because Performance Out Of The Box is one of the Hallmarks of SQL Anywhere.

However, when you do have a problem, it can be doubly difficult because SQL Anywhere also lets you code very very very very very complex queries.

So.. the query plan is definitely smoggy (a very good word), and only super intelligent code gods can fully understand it... which excludes me.

But... you don't need to fully understand it, just look for boxes and lines that are highlighted in red and/or drawn with thick black lines... that's where your time is going.

Check this out: [ search blog for Graphical Plan ].

Another way is to [ahem] hire someone ( like me 🙂 to have a look at your query.