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.

asked 19 Jul, 11:38

Ben8sens's gravatar image

Ben8sens
863410
accept rate: 25%

Be the first one to answer this question!
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:

×9

question asked: 19 Jul, 11:38

question was seen: 74 times

last updated: 19 Jul, 11:38