ATOUTFOX
COMMUNAUTÉ FRANCOPHONE DES PROFESSIONNELS FOXPRO
Visual FoxPro : le développement durable

Forum AtoutFox : Re: Optimiser SQL-Select sur plusieurs FKs   

Sujet

rss Flux RSS des derniers messages

Vous devez vous identifier pour pouvoir poser une question ou répondre.

lun. 05 janvier 2015, 21h12

FoxInCloud (Th. Nivelet)
France France

atoutfox.public.association

Re: Optimiser SQL-Select sur plusieurs FKs

Salut Olivier

Le lun. 05 janvier 2015, 18h20 OlivierH a écrit :
> Ici
> join FKs (select FK1, FK2, FK3 ... ) on factures.FK1 = FKs.FK1
> Tu peux nous donner des détails sur cette sous requête sur le JOIN ?

Je peux en donner (annexé) mais je crains que ça complique inutilement l'affaire ...

Le plus important est qu'il faut .15 secondes (en dev.) pour trouver 750 combinaisons des 3 FKs

> Les Fk sont bien sur des int ?

Hélas non, ce sont des c(10) alimentés par sys(2015)

  Select; && divisionIDs, carrierIDs, glcodeIDs of invoices that user can access given his/her rights
      division_.divisionID;
    , carrier_.carrierID;
    , division_.GLcodeID;
    from (;
  ; && ======= DIVISIONS =======
    select; && DIVISIONS: all companies or divisions && non-carrier user, && 2014-10-07 carrier users treated like regular users
        division.divisionID;
      , GLcode.GLcodeID;
      from ip!Company;
        join ip!division on company.companyID = division.companyID;
          join ip!GLcode on division.divisionID = GLcode.divisionID;
      where company.companyID = ?m.companyID;
       and (.F.;
        or .T.;
          and exists(;
             select UR.userID; && all companies...
              from ip!UR;
               where UR.userID = ?m.userID;
                and UR.lAllComp;
           );
           and !exists(; && ... without exception
             select URdiv.URdivID;
               from ip!URdiv;
                 join ip!UR on URdiv.URID = UR.URID;
               where UR.userID = ?m.userID;
                and UR.companyID = ?m.companyID;
           );
         or exists(;
           select UR.userID; && all divisions (of current company)
            from ip!UR;
             where UR.userID = ?m.userID;
             and UR.companyID = ?m.companyID;
              and UR.lAllDiv;
         ));
    union;
    select; && DIVISIONS: rights set explicitely && non-carrier user, && 2014-10-07: carrier users treated like regular users
        URdiv.divisionID;
      , GLcode.GLcodeID;
      from ip!Users;
        join ip!UR on Users.userID = UR.userID;
          join ip!URdiv on UR.URID = URdiv.URID;
            join ip!division on URdiv.divisionID = division.divisionID;
              join ip!GLcode on division.divisionID = GLcode.divisionID;
      where Users.userID = ?m.userID;
       and UR.companyID = ?m.companyID; && and Empty(Users.carrierID); && 2014-10-07 carrier users treated like regular users
    union;
    select; && DIVISIONS: carrier user && deprecated 2014-10-07: carrier users treated like regular users
        division.divisionID;
      , GLcode.GLcodeID;
      from ip!Users;
        join ip!carrier on users.carrierID = carrier.carrierID;
          join ip!comp_carr on carrier.carrierID = comp_carr.carrierID;
            join ip!company on comp_carr.companyID = company.companyID;
              join ip!division on company.companyID = division.companyID;
                join ip!GLcode on division.divisionID = GLcode.divisionID;
      where .F.; && 2014-10-07 carrier users treated like regular users
       and Users.userID = ?m.userID;
       and !Empty(Users.carrierID);
       and company.companyID = ?m.companyID;
    group by 1,2;
  ;
    ) division_;
      join (;
  ; && ======= CARRIERS =======
      select; && CARRIERS: non-carrier user, all carriers
          carrier.carrierID;
        , GLcode.GLcodeID;
        from ip!company;
          join ip!comp_carr on company.companyID = comp_carr.companyID;
            join ip!carrier on comp_carr.carrierID = carrier.carrierID;
              join ip!GLcode on carrier.carrierID = GLcode.carrierID;
        where company.companyID = ?m.companyID;
         and (.F.;
          or .T.;
            and exists(;
               select UR.userID; && all companies...
                from ip!UR;
                 where UR.userID = ?m.userID;
                  and UR.lAllComp;
             );
             and !exists(; && ... without exception
               select URcar.URcarID;
                 from ip!URcar;
                   join ip!UR on URcar.URID = UR.URID;
                 where UR.userID = ?m.userID;
                  and UR.companyID = ?m.companyID;
             );
           or exists(;
             select UR.userID; && all carriers (serving current company)
              from ip!UR;
               where UR.userID = ?m.userID;
               and UR.companyID = ?m.companyID;
                and UR.lAllCar;
           ));
      union;
      select; && CARRIERS: non-carrier user, allowed carriers
          URcar.carrierID;
        , GLcode.GLcodeID;
        from ip!Users;
          join ip!UR on Users.userID = UR.userID;
            join ip!URcar on UR.URID = URcar.URID;
              join ip!carrier on URcar.carrierID = carrier.carrierID;
                join ip!GLcode on carrier.carrierID = GLcode.carrierID;
        where Users.userID = ?m.userID;
         and UR.companyID = ?m.companyID;
      union;
      select; && CARRIERS: carrier user, only his carrier
          carrier.carrierID;
        , GLcode.GLcodeID;
        from ip!Users;
          join ip!carrier on Users.carrierID = carrier.carrierID;
            join ip!GLcode on carrier.carrierID = GLcode.carrierID;
        where Users.userID = ?m.userID;
      group by 1,2;
      ;
      ) carrier_;
      on division_.GLcodeID = carrier_.GLcodeID;
    where .F.;
    or division_.GLcodeID in (;
  ; && ======= GLCODES =======
      select distinct; && GLCODES: all accounts && non-carrier user, && 2014-10-07 carrier users treated like regular users
          GLcode.GLcodeID;
        from ip!company;
          join ip!division on company.companyID = division.companyID;
            join ip!GLcode on division.divisionID = GLcode.divisionID;
        where company.companyID = ?m.companyID;
         and (.F.;
          or .T.;
            and exists(;
               select UR.userID; && all companies...
                from ip!UR;
                 where UR.userID = ?m.userID;
                  and UR.lAllComp;
             );
             and !exists(;
               select URacc.URaccID; && ... without exception
                 from ip!URacc;
                   join ip!UR on URacc.URID = UR.URID;
                 where UR.userID = ?m.userID;
                  and UR.companyID = ?m.companyID;
             );
           or exists(;
             select UR.userID; && all accounts (of current company)
              from ip!UR;
               where UR.userID = ?m.userID;
               and UR.companyID = ?m.companyID;
                and UR.lAllAcc;
           ));
    );
    or division_.GLcodeID in (;
      select distinct; && GLCODES: allowed accounts && non-carrier user, && 2014-10-07 carrier users treated like regular users
          GLcode.GLcodeID;
        from ip!Users;
          join ip!UR on Users.userID = UR.userID;
            join ip!URacc on UR.URID = URacc.URID;
              join ip!GLcode on URacc.AcctNum = Upper(GLcode.AcctNum); && GLcode: index on Upper(AcctNum) tag AcctNum
        where UR.userID = ?m.userID;
         and UR.companyID = ?m.companyID; && and Empty(Users.carrierID); && 2014-10-07 carrier users treated like regular users
    );
    or division_.GLcodeID in (;
      select distinct; && GLCODES: carrier user && deprecated 2014-10-07: carrier users treated like regular users
          GLcode.GLcodeID;
        from ip!GLcode;
          join ip!carrier on GLcode.carrierID = carrier.carrierID;
            join ip!Users on carrier.carrierID = users.carrierID;
          join ip!comp_carr on carrier.carrierID = comp_carr.carrierID;
            join ip!company on comp_carr.companyID = company.companyID;
          join ip!division on GLcode.divisionID = division.divisionID;
            join ip!company company_ on division.companyID = company_.companyID;
        where .F.; && 2014-10-07 carrier users treated like regular users
         and Users.userID = ?m.userID;
         and !Empty(Users.carrierID);
         and company.companyID = ?m.companyID;
         and company_.companyID = ?m.companyID;
    );
  group by 1,2,3;
) div_car_gl;



Permalink : http://www.atoutfox.org/nntp.asp?ID=0000015668
20 286 messages dans le forum • Liste complète des messages

www.atoutfox.org - Site de la Communauté Francophone des Professionnels FoxPro - v3.4.0 - © 2004-2026.
Cette page est générée par un composant COM+ développé en Visual FoxPro 9.0-SP2-HF3