IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

Jointure un peu bizarre et lenteur


Sujet :

Langage SQL

  1. #1
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut Jointure un peu bizarre et lenteur
    Bonjour,

    Je suis sous SQL Server 2014.

    Je simplifie au maximum le modèle des données. Je n'ai pas la main sur la structure des tables.

    J'ai une table T1 avec la structure suivante : (ID, C1, C2, C3, C4)
    J'ai une table T2 avec la structure suivante : (ID, C1, C2, C3, C4)

    La table T1 contient mettons des identifiants de personnes. Et C1, C2, C3, C4 sont des compétences (FK vers des tables de catalogue). Par exemple, C1 = "SQL" avec comme valeurs 0, 1, 2, 3 pour indéterminé, mauvais, moyen, bon

    La table T2 contient mettons des besoins pour un projet spécifique. Par exemple, j'ai besoin d'une personne "bonne en SQL, moyenne en C".
    Mais je peux aussi avoir besoin de tout le monde : à ce moment, C1, C2, C3, C4 sont toutes à 0 (indéterminé).

    Je souhaite donc, pour chaque ligne de T2, obtenir la liste des T1 qui ont les compétences requises (c'est à dire avec une valeur > 0 dans T2).

    Voici donc la requête de prime abord :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    select t2.id, t1.id
    from t2
    inner join t1 on (t2.c1 = 0 or t1.c1 = t2.c1) and (t2.c2 = 0 or t1.c2 = t2.c2) and (t2.c3 = 0 or t1.c3 = t2.c3) and (t2.c4 = 0 or t1.c4 = t2.c4)

    Seul hic : j'ai un assez gros volume de lignes (plus de 10 000 dans T2 et plus de 500 000 dans T1).
    La requête dure alors des heures.

    Ça ne m'étonne guère quand on voit le critère de jointure...

    Comment l'optimiser ?
    J'ai pensé à faire des UNION afin de faire sauter les OR... Mais je vais me retrouver ici avec 16 UNION pour gérer tous les cas (ou 12, j'ai du mal avec les suites numériques).
    Sauf qu'en réalité, c'est bien plus complexe, car j'ai pas que 4 colonnes, je n'ai pas que des égalités, et j'ai des jointures en cascades sur d'autres tables... en bref, si je passe par des UNION, je fais avoir une requête de plusieurs milliers de lignes, qui sera totalement impossible à maintenir (en espérant que SQL Server veuille bien l'exécuter d'ailleurs).

    Une idée ?
    On ne jouit bien que de ce qu’on partage.

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 088
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    bonjour,

    Il me semble que vous devriez sélectionner aussi les personnes dont la compétence est supérieure à la compétence requise pour le projet non ?

    Car si tel est le cas vous pourriez vous simplifier la vie en modifiant la valeur compétence indéterminée, vous la remplacez par "9" et du coup un test de supériorité (supérieur ou égal) suffit, vous vous affranchissez directement des OR

    Ce serait nettement plus simple

  3. #3
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Non, ici c'est principalement des tests d'égalité.

    Par exemple : "spécialité = dentiste / vétérinaire / chirurgien / ..."

    Donc soit je veux juste un médecin (histoire de lui envoyer une publicité généraliste) soit je veux un chirurgien car j'ai besoin d'une transplantation cardiaque.
    On ne jouit bien que de ce qu’on partage.

  4. #4
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 088
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    Oui mais je proposais de prendre les compétences supérieures par rapport à :
    Citation Envoyé par StringBuilder Voir le message
    Par exemple, C1 = "SQL" avec comme valeurs 0, 1, 2, 3 pour indéterminé, mauvais, moyen, bon
    En remplaçant la valeur "0" par "9"

    Du coup la requête devient
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    select t2.id, 
           t1.id
    from t2
    inner join t1 
       on t1.c1 >= t2.c1
      and t1.c2 >= t2.c2
      and t1.c3 >= t2.c3
      and t1.c4 >= t2.c4

    Si C1= compétence chirurgien, C2=compétence anesthésiste
    Si dans T1 j'ai
    ID1; Robert; C1=2;C2=1;
    ID2; Jeanne; C1=1;C2=2;
    ID3; Henri ; C1=2;C2=9
    quand je recherche un chirurgien de niveau 2 qui soit aussi anesthésiste de niveau 2, alors la requete ci-dessus sélectionnera uniquement Henri, ce qui me semble correspondre au besoin

  5. #5
    Membre du Club
    Homme Profil pro
    Consultant en Business Intelligence
    Inscrit en
    Février 2012
    Messages
    28
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Consultant en Business Intelligence

    Informations forums :
    Inscription : Février 2012
    Messages : 28
    Points : 40
    Points
    40
    Par défaut
    D'après ce que je comprends dans le second message, il n'y a pas forcément de rapport entre les valeurs possible d'une colonne (Vétérinaire, Chirurgien, Dentiste...).
    Du coup les tests > ou < ne peuvent pas fonctionner.

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    et comme ça :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select t2.id, t1.id
    from t2
    right outer join t1 
       on  (t1.c1 = t2.c1) 
       and (t1.c2 = t2.c2) 
       and (t1.c3 = t2.c3) 
       and (t1.c4 = t2.c4)
       OR t2.c1 = 0 or 
    	  t2.c2 = 0 or 
    	  t2.c3 = 0 or 
    	  t2.c4 = 0
    ?
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Ça ne donne pas le même résultat.

    En effet, dans ta solution, si par exemple T2.C2 = 0 alors toutes les lignes de T1 sont jointes, quelles que soient les valeurs de C1, C3, C4 et C5.

    Dans mon cas, si T2.C2 = 0, alors je ne doit pas prendre en compte cette colonne dans la jointure, mais je dois bien filtrer sur C1, C3, C4 et C5 (si remplies).

    Pour reprendre mon exemple avec des médecins, que je souhaite inviter à un congrès :

    Une première ligne dans T2 :
    "je veux tous les médecins qui ont obtenu leur diplôme en 1995 (C1), qui ont comme spécialité dentiste (C2), et qui ont n'importe quel âge (C3), habitent n'importe où (C4), et quel que soir leur mode d'activité (C5)".

    Puis j'ai une seconde ligne dans T2 :
    "je veux tous les médecins quelle que soit leur date de diplôme (C1), quelle que soit leur spécialité (C2), qui ont plus de 40 ans (C3), habitent n'importe où (C4), et qui sont séminaristes (C5)".

    Et une troisième ligne dans T3 :
    "je veux tous les médecins quelle que soit leur date de diplôme (C1), quelle que soit leur spécialité (C2), et qui ont n'importe quel âge (C3), habitent à paris (C4), et qui sont en cabinet privé (C5)".

    Et ma requête doit me ramener la liste de tous les médecins à inviter qui répondent à au moins 1 des 3 combinaisons de critères.

    Un exemple en simplifiant au maximum :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
     
    with t1 (id, c1, c2)
    as
    (
    	select 'durant', 1, 2
    	union all
    	select 'dupuis', 1, 1
    	union all
    	select 'dupont', 2, 1
    	union all
    	select 'dubois', 2, 2
    ),
    t2 (id, c1, c2)
    as
    (
    	select '10', 1, 0
    	union all
    	select '22', 2, 2
    )
    select t2.id, t1.id
    from t2
    right outer join t1 
       on  (t1.c1 = t2.c1) 
       and (t1.c2 = t2.c2) 
       OR t2.c1 = 0
       OR t2.c2 = 0

    J'obtiens :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    10	durant
    10	dupuis
    10	dupont
    10	dubois
    22	dubois
    Mois je veux :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    10	durant
    10	dupuis
    22	dubois
    Ce que j'obtiens avec ma requête actuelle :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
     
    with t1 (id, c1, c2)
    as
    (
    	select 'durant', 1, 2
    	union all
    	select 'dupuis', 1, 1
    	union all
    	select 'dupont', 2, 1
    	union all
    	select 'dubois', 2, 2
    ),
    t2 (id, c1, c2)
    as
    (
    	select '10', 1, 0
    	union all
    	select '22', 2, 2
    )
    select t2.id, t1.id
    from t2
    inner join t1 
       on  (t2.c1 = 0 or t1.c1 = t2.c1) 
       and (t2.c2 = 0 or t1.c2 = t2.c2)

    Sauf qu'elle met 21h pour retourner 280 000 lignes... Il faut impérativement que je tombe sous les 15 minutes...
    On ne jouit bien que de ce qu’on partage.

  8. #8
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Actuellement, la seule optimisation qui me vienne à l'esprit, c'est ça :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
     
    with t1 (id, c1, c2)
    as
    (
    	select 'durant', 1, 2
    	union all
    	select 'dupuis', 1, 1
    	union all
    	select 'dupont', 2, 1
    	union all
    	select 'dubois', 2, 2
    ),
    t2 (id, c1, c2)
    as
    (
    	select '10', 1, 0
    	union all
    	select '22', 2, 2
    )
    select t2.id, t1.id
    from t2
    inner join t1 
       on  t1.c1 = t2.c1
       and t1.c2 = t2.c2
    union
    select t2.id, t1.id
    from t2
    inner join t1 
       on  t1.c2 = t2.c2
    where t2.c1 = 0
    union
    select t2.id, t1.id
    from t2
    inner join t1 
       on  t1.c1 = t2.c1
    where t2.c2 = 0
    union
    select t2.id, t1.id
    from t2
    cross join t1 
    where  t2.c1 = 0
       and t2.c2 = 0

    Sauf que là, pour 2 colonnes, j'ai 4 union.

    Alors que moi j'en ai 15, et la requête est autrement plus complexe !

    Et 15² = 225 union... y'a pas moyen de pondre une telle infâmie...
    On ne jouit bien que de ce qu’on partage.

  9. #9
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 088
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    Petite amélioration : utiliser l'opérateur between et des host variables

    si une seule valeur recherchée (par exemple les médecins diplômés en 1995) : where C1 between :HV1 and :HV1 avec HV1=HV2=1995
    si plusieurs valeurs recherchées (exemple les médecins qui ont plus de 40 ans) where C3 between :HV1 and :HV1 avec HV1=41 et HV2=999

    Ca vous évite les OR

  10. #10
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Non, c'est toujours pas ça.

    Ici, dans mon exemple, je ne parle que d'égalités, avec une valeur à la fois. Y'a pas de between.

    Quand je cherche "n'importe quel âge", ça veut dire "t2 cross join t1" et quand je veux dire "40 ans", ça veut dire "t2 inner join t1 on t1.c2 = t2.c2".

    Donc la seule écriture que j'avais trouvé, c'était "t2 inner join t1 on t2.c2 = 0 or t1.c2 = t2.c2" mais c'est lent.


    Par contre, pour toutes mes valeurs "catalogue", je viens d'avoir une idée (peut-être de merde, je suis en train de tester).

    En effet, ce qui est lent, c'est que ne pas savoir comment faire ma jointure entre t1 et t2 en fonction de la présence ou non de valeurs à 0 dans t2.
    Donc j'ai pris le taureau par les cornes pour remplacer les 0 par l'ensemble des valeurs possibles.

    Donc mettons un catalogue 1:dentiste, 2:chirurgien, 3:ORL je transforme la ligne t2.c2 = 0 en 3 lignes avec les valeurs 1, 2, 3

    Ce qui donne ceci :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
     
    with t2 (f7000, f7004, f7006, f7007, f7008, f7009, f7010, f7011, f7012, f7013, f7014)
    as
    (
    	select c024.f7000, cast(k7004.extkey as int), k7006.code, k7007.code, k7008.code, k7009.code, k7010.code, c024.f7011, c024.f7012, c024.f7013, c024.f7014
    	from crm_isi_c024 c024
    	inner join crm_isi_ka k7004 on k7004.katnr = 1085 and k7004.SpracheNr = 0 and (c024.f7004 = 0 or k7004.code = c024.f7005)
    	inner join crm_isi_ka k7006 on k7006.katnr = 1046 and k7006.SpracheNr = 0 and (c024.f7006 = 0 or k7006.code = c024.f7006)
    	inner join crm_isi_ka k7007 on k7007.katnr = 1031 and k7006.SpracheNr = 0 and (c024.f7007 = 0 or k7007.code = c024.f7007)
    	inner join crm_isi_ka k7008 on k7008.katnr = 1032 and k7006.SpracheNr = 0 and (c024.f7008 = 0 or k7008.code = c024.f7008 and k7008.Unterkat1 = k7007.code)
    	inner join crm_isi_ka k7009 on k7009.katnr = 35 and k7006.SpracheNr = 0 and (c024.f7009 = 0 or k7009.code = c024.f7009)
    	inner join crm_isi_ka k7010 on k7010.katnr = 275 and k7006.SpracheNr = 0 and (c024.f7010 = 0 or k7010.code = c024.f7010)
    	where c024.del = 0
    )
    select *
    from t2
    inner join t1 on t1.f7000 = t2.f7000 and ...

    Vu que mes catalogues sont petits, la présence de jointure merdique ne me pose pas de problème de performances.
    Je peux ensuite faire une jointure parfaitement propre avec ma table t1


    Bon, indépendamment des performances qu'il reste à vérifier, il me reste un problème...

    Qu'est-ce que je fais avec les médecins qui n'ont pas de spécialité ?
    En effet, dans mon exemple, mes spécialités sont (1:dentiste, 2:chirurgien, 3:ORL).
    Et j'ai des médecins qui n'ont pas de spécialité. Dans ce cas, vu que c'est un modèle des données merdique et dénormalisé, j'ai pas de NULL, mais 0 dans la table t1.
    Hors avec ma bidouille, j'ai transformé les 0 en 1, 2, 3 dans ma table t2, ce qui fait que j'écarte les médecins sans spécialité quand je demande tous les médecins toutes spécialités confondues (c'est très con quand même...)

    Pffff, ça m'épuise...

    Du coup je me retrouve avec cette bouse...

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
     
    with t2(f7000, f7004, f7006, f7007, f7008, f7009, f7010, f7011, f7012, f7013, f7014)
    as
    (
    	select c024.f7000, k7004.extkey, k7006.code, k7007.code, k7008.code, k7009.code, k7010.code, c024.f7011, c024.f7012, c024.f7013, c024.f7014
    	from crm_isi_c024 c024
    	inner join (select code, cast(extkey as int) extkey from crm_isi_ka where crm_isi_ka.katnr = 1085 and crm_isi_ka.SpracheNr = 0 union select 0, 0) k7004 on (c024.f7004 = 0 or k7004.code = c024.f7005)
    	inner join (select code from crm_isi_ka where crm_isi_ka.katnr = 1046 and crm_isi_ka.SpracheNr = 0 union select 0) k7006 on (c024.f7006 = 0 or k7006.code = c024.f7006)
    	inner join (select code from crm_isi_ka where crm_isi_ka.katnr = 1031 and crm_isi_ka.SpracheNr = 0 union select 0) k7007 on (c024.f7007 = 0 or k7007.code = c024.f7007)
    	inner join (select code, Unterkat1 from crm_isi_ka where crm_isi_ka.katnr = 1032 and crm_isi_ka.SpracheNr = 0 union select 0, code from crm_isi_ka where crm_isi_ka.katnr = 1031 and crm_isi_ka.SpracheNr = 0) k7008 on (c024.f7008 = 0 or k7008.code = c024.f7008 and k7008.Unterkat1 = k7007.code)
    	inner join (select code from crm_isi_ka where crm_isi_ka.katnr = 35 and crm_isi_ka.SpracheNr = 0 union select 0) k7009 on (c024.f7009 = 0 or k7009.code = c024.f7009)
    	inner join (select code from crm_isi_ka where crm_isi_ka.katnr = 275 and crm_isi_ka.SpracheNr = 0 union select 0) k7010 on (c024.f7010 = 0 or k7010.code = c024.f7010)
    	where c024.del = 0
    )
    select *
    from t2
    inner join t1 on t1.f7000 = t2.f7000 and ...
    => Je génère à la volée des entrées à 0 dans mes catalogues lors de la jointure...
    On ne jouit bien que de ce qu’on partage.

  11. #11
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Bon... Ca n'a pas l'air miraculeux comme optimisation...

    Personne n'a une idée ? (même crade ?)

    J'en suis à me demander si je n'ai pas intérêt à remplir une table temporaire avec tous les médecins, puis de supprimer tous ceux qui ne correspondent à aucune règle... Sauf que j'ai de gros doutes quant au gain de performances...

    D'autres pistes ?
    On ne jouit bien que de ce qu’on partage.

  12. #12
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    Effectivement, il faut essayer de sortir les OR.

    Il faut que l'optimiseur tombe que des HASH JOIN de préférence, on doit pouvoir le flouer avec quelque chose du genre :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select t2.id, t1.id
      from t2
      join t1
        on t1.c1 = coalesce(nullif(t2.c1, 0), t1.c1)
       and t1.c2 = coalesce(nullif(t2.c2, 0), t1.c2);

  13. #13
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Très bonne idée, ça me plaît !

    Je vais voir ce que ça donne (lundi).

    En attendant, parmi les 15² combinaisons possibles, j'ai déterminé que pour le moment seulement deux étaient utilisées.
    Du coup j'ai réécrit la requête avec un UNION et des jointures normales prenant en conte les deux combinaisons retenues...

    Je suis passé de 21h à 33 minutes pour le même résultat.

    Avec beaucoup de chance, ta solution permettra la même amélioration tout en permettant de prendre en compte tous les cas !

    Merci en tout cas.


    Sinon, j'ai une question : dans le plan d'exécution, je me fais insulter avec le message suivant sur un SORT :
    L’opérateur a utilisé tempdb pour dépasser les données lors de l'exécution avec le niveau de dépassement 2
    Y me veut quoi au juste ? :o
    On ne jouit bien que de ce qu’on partage.

  14. #14
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    J'ai mis en place la solution de Waldar.

    Pour le moment ça fait 4 heures que ça tourne... Pas certainement que ce soit bien plus rapide (bon, on verra ça au bout de 48 heures ceci dit )

    Toujours est-il que le plan d'exécution est beaucoup plus joli !
    On ne jouit bien que de ce qu’on partage.

  15. #15
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Bonjour,

    Citation Envoyé par StringBuilder Voir le message
    Par contre, pour toutes mes valeurs "catalogue", je viens d'avoir une idée (peut-être de merde, je suis en train de tester).

    En effet, ce qui est lent, c'est que ne pas savoir comment faire ma jointure entre t1 et t2 en fonction de la présence ou non de valeurs à 0 dans t2.
    Donc j'ai pris le taureau par les cornes pour remplacer les 0 par l'ensemble des valeurs possibles.
    ça me semble être une bonne idée mais que j'implémenterai un peu différemment, en passant par de vrais tables.

    Pour l'exemple, je part du principe que chacune des colonnes (C1, C2, C3 ,C4) peuvent prendre les valeurs de 1 a 4 (0 a 4 pour la table t2).
    Je créerai donc une table permettant de générer les lignes avec toutes les valeurs possibles :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    CREATE TABLE EXPLODE( a int , b int, constraint pk_explode primary key(a,b))
    que je peuplerai avec toutes les valeurs spécifique croisées à elles même plus 0 :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    insert into explode VALUES
    (0, 1)
    ,(0,2)
    ,(0,3)
    ,(0,4)
    ,(1,1)
    ,(2,2)
    ,(3,3)
    ,(4,4)
    ;

    La requete pourra alors devenir :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
     
    SELECT  sb2.id asx , sb1.id as y
    FROM sb2
    INNER JOIN explode ec1
    	ON ec1.a = sb2.c1
    INNER JOIN explode ec2
    	ON ec2.a = sb2.c2
    INNER JOIN explode ec3
    	ON ec3.a = sb2.c3
    INNER JOIN explode ec4
    	ON ec4.a = sb2.c4
    INNER JOIN sb1
    	ON sb1.C1 = ec1.b
    	AND sb1.c2 = ec2.b
    	AND sb1.c3 = ec3.b
    	and sb1.C4 = ec4.b
    Pour traiter le cas des médecins sans spécialité (0 dans la table SB1), il suffit alors de rajouter la ligne (0,0) dans la table EXPLODE.

    Dans votre cas précis, il faudra certainement une table EXPLODE par colonne, mais elles pourraient certainement être facilement peuplées si vous disposez de tables de références pour les colonnes Cx.

    Mais on pourra alors raffiner éventuellement, car il n'y aura plus de jointure reflexive, et il deviendra possible de créer une vue indexée
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
    CREATE VIEW sb2_all
    WITH SCHEMABINDING
    AS
    	SELECT	sb2.id, ec1.b As c1, ec2.b as c2, ec3.b as c3, ec4.b as c4
    	FROM	dbo.sb2
    	INNER JOIN dbo.explode1 ec1
    		ON ec1.a = sb2.c1
    	INNER JOIN dbo.explode2 ec2
    		ON ec2.a = sb2.c2
    	INNER JOIN dbo.explode3 ec3
    		ON ec3.a = sb2.c3
    	INNER JOIN dbo.explode4 ec4
    		ON ec4.a = sb2.c4
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE UNIQUE CLUSTERED INDEX CUIX_sb2_all ON sb2_all(id, c1,C2,C3,C4)
    Enfin,si vous êtes sous l’édition express, il faudra faire appel explicitement à la vue, et ajouter l'indicateur NOEXPAND, la requete deviendrait :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    SELECT	sb2.id, sb1.id
    FROM	sb2_all sb2 WITH(NOEXPAND)
    INNER JOIN sb1
    	ON sb1.C1 = sb2.c1
    	AND sb1.C2 = sb2.c2
    	AND sb1.C3 = sb2.C3
    	AND sb1.C4 = sb2.C4

  16. #16
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Merci pour ta réponse.

    Effectivement, j'avais pensé à la table explode, mais ça me semblait assez moyen.

    Je tente avec une table temporaire à la place (comme ça je la crée à la volée juste avant mon traitement).

    Pour le moment, c'est pas super concluant. Mais j'ai pas de chargeur et mon portable est passé en mode économie d'énergie, du coup je suis pas certain que je puisse comparer quoi que ce soit

    Et vu qu'accessoirement le traitement modifie les données, assez difficile de comparer de façon fiable une exécution à l'autre...

    En tout cas, je continue à creuser dans cette direction !

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
     
    ALTER PROCEDURE [dbo].[CU_Customer_Allocation_BIS]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
     
    	-- Delete previously allocated records from the allocation table
    	DELETE CU_CustomerAllocation WHERE Allocated = 1;
     
    	-- Mark records from the previous allocation as Allocated (to keep track of them)
    	UPDATE CU_CustomerAllocation SET Allocated = 1 WHERE Allocated = 0;
     
    	create table #explode
    	(
    		katnr int not null,
    		code int not null,
    		scode int not null,
    		code2 int not null,
    		primary key (katnr, code, scode, code2)
    	);
     
    	insert into #explode
    	select 35, 0, 0, 0
    	union all
    	select katnr, 0, 0, code
    	from te_isi_ka where katnr = 35 and SpracheNr = 0 and LosKZ = 0
    	union all
    	select katnr, code, 0, code
    	from te_isi_ka where katnr = 35 and SpracheNr = 0 and LosKZ = 0	
    	union all
    	select 275, 0, 0, 0
    	union all
    	select katnr, 0, 0, code
    	from te_isi_ka where katnr = 275 and SpracheNr = 0 and LosKZ = 0
    	union all
    	select katnr, code, 0, code
    	from te_isi_ka where katnr = 275 and SpracheNr = 0 and LosKZ = 0	
    	union all
    	select 1031, 0, 0, 0
    	union all
    	select katnr, 0, 0, code
    	from te_isi_ka where katnr = 1031 and SpracheNr = 0 and LosKZ = 0
    	union all
    	select katnr, code, 0, code
    	from te_isi_ka where katnr = 1031 and SpracheNr = 0 and LosKZ = 0	
    	union all
    	select 1032, 0, 0, 0
    	union all
    	select katnr, 0, unterkat1, code
    	from te_isi_ka where katnr = 1032 and SpracheNr = 0 and LosKZ = 0
    	union all
    	select katnr, code, unterkat1, 0
    	from te_isi_ka where katnr = 1032 and SpracheNr = 0 and LosKZ = 0	
    	union all
    	select katnr, code, unterkat1, code
    	from te_isi_ka where katnr = 1032 and SpracheNr = 0 and LosKZ = 0	
    	union all
    	select 1046, 0, 0, 0
    	union all
    	select katnr, 0, 0, code
    	from te_isi_ka where katnr = 1046 and SpracheNr = 0 and LosKZ = 0
    	union all
    	select katnr, code, 0, code
    	from te_isi_ka where katnr = 1046 and SpracheNr = 0 and LosKZ = 0
    	union all
    	select 1085, 0, 0, 0
    	union all
    	select katnr, 0, 0, cast(extkey as int)
    	from te_isi_ka where katnr = 1085 and SpracheNr = 0 and LosKZ = 0
    	union all
    	select katnr, cast(extkey as int), 0, cast(extkey as int)
    	from te_isi_ka where katnr = 1085 and SpracheNr = 0 and LosKZ = 0
    	;
     
     
        -- Insert new records in the allocation table
    	INSERT INTO CU_CustomerAllocation (
    		Mode, FI_StaNo, FI_SerNo, FI_ExtSys, FI_ExtKey,
    		Rep, Rep_ExtSys, Rep_ExtKey, Date)
    	SELECT	distinct 'NEW',
    			DBO.CU_ID_To_StaNo(FI.ID) CompanyStaNo,
    			DBO.CU_ID_To_SerNo(FI.ID) CompanySerNo,
    			FI.ExtSystem,
    			FI.ExtKey,
    			ID.ID RepID,
    			ID.ExtSystem,
    			ID.ExtKey,
    			GETDATE()
    	FROM te_isi_C024 C024
    	INNER JOIN te_isi_ID ID ON -- Rep
    			ID.ID = C024.F7000
    		AND ID.Inaktiv = 0														-- Only active users
    		AND ID.LosKZ = 0														-- Not deleted
    	inner join #explode kt on kt.katnr = 1085 and kt.code = c024.F7004
    	inner join #explode kn1 on kn1.katnr = 1031 and kn1.code = c024.F7007
    	inner join #explode kn2 on kn2.katnr = 1032 and kn2.code = c024.F7008 and kn2.scode = kn1.code
    	inner join #explode kg on kg.katnr = 35 and kg.code = c024.F7009
    	inner join #explode ks on ks.katnr = 275 and ks.code = c024.F7010
    	inner join #explode ke on ke.katnr = 1046 and ke.code = c024.F7006
    	INNER JOIN te_isi_FI FI ON  -- Company
    			FI.MandNr = kt.code2												-- Tenant
    		AND FI.F7020 = kn1.code2												-- Network 1
    		AND FI.F7021 = kn2.code2								 				-- Network 2
    		AND FI.Gebiet = kg.code2												-- Geographical Area
    		AND FI.F7019 = ks.code2													-- Sales Channel
    		AND FI.F7004 between isnull(C024.F7011, FI.F7004)						-- INSEE Code Start
    			and isnull(C024.F7012, FI.F7004)									-- INSEE Code End
    		AND (	FI.Fityp = 2													-- CoType Customer
    			OR	FI.Fityp2 = 2													-- CoType Customer
    			OR	FI.Fityp3 = 2													-- CoType Customer
    			OR	FI.Fityp4 = 2 													-- CoType Customer
    			OR	FI.Fityp5 = 2)													-- CoType Customer
    		AND FI.LosKZ = 0														-- Not deleted
    		AND FI.MandNr <> 0														-- Children FI have a tenant
    		AND FI.ID_FI_250 <> 0													-- Children FI have a parent
    	INNER JOIN te_isi_FI FI_GLO on  -- Global Company
    			FI_GLO.MandNr = 0													-- Parent doesn't have a tenant
    		AND FI_GLO.ID = FI.ID_FI_250
    		AND FI.LosKZ = 0
    	INNER JOIN te_isi_C022 SP on -- Company Production Species
    			SP.F7003 = ke.code2
    		AND SP.ID_FI = FI_GLO.ID
    		AND SP.DEL = 0
    	WHERE C024.DEL = 0
    	AND case when C024.F7013 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7013 end >= C024.F7013	-- Allocation Start
    	AND case when C024.F7014 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7014 end <= C024.F7014	-- Allocation End
    	AND NOT EXISTS ( -- No need to create/modify the SB if it already exists
    		select *
    		from te_isi_SB SB
    		where SB.ID_FI = FI.ID
    		AND SB.BearbeiterId = C024.F7000
    		AND SB.LosKZ = 0
    	);
     
    	-- Insert records in the allocation table to remove SB not anymore applicable
    	INSERT INTO CU_CustomerAllocation (
    		Mode, FI_StaNo, FI_SerNo, FI_ExtSys, FI_ExtKey,
    		Rep, Rep_ExtSys, Rep_ExtKey, Date)
    	SELECT	'DEL',
    			DBO.CU_ID_To_StaNo(SBFI.ID) CompanyStaNo,
    			DBO.CU_ID_To_SerNo(SBFI.ID) CompanySerNo,
    			SBFI.ExtSystem,
    			SBFI.ExtKey,
    			SBID.ID RepID,
    			SBID.ExtSystem,
    			SBID.ExtKey,
    			GETDATE()
    	FROM te_isi_SB SB
    	INNER JOIN te_isi_FI SBFI ON -- Allocated Company
    		SBFI.ID = SB.ID_FI
    	INNER JOIN te_isi_ID SBID ON -- Allocated Rep
    			SBID.ID = SB.BearbeiterId
    	LEFT OUTER JOIN (
    		te_isi_C024 C024
    		inner join #explode kt on kt.katnr = 1085 and kt.code = c024.F7004
    		inner join #explode kn1 on kn1.katnr = 1031 and kn1.code = c024.F7007
    		inner join #explode kn2 on kn2.katnr = 1032 and kn2.code = c024.F7008 and kn2.scode = kn1.code
    		inner join #explode kg on kg.katnr = 35 and kg.code = c024.F7009
    		inner join #explode ks on ks.katnr = 275 and ks.code = c024.F7010
    		inner join #explode ke on ke.katnr = 1046 and ke.code = c024.F7006
    	) ON -- Customer Allocation
    			C024.F7000 = SB.BearbeiterId
    		AND case when C024.F7013 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7013 end >= C024.F7013	-- Allocation Start
    		AND case when C024.F7014 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7014 end <= C024.F7014	-- Allocation End
    		AND C024.DEL = 0
    	LEFT OUTER JOIN te_isi_FI FI ON -- Already allocated Companies
    			FI.MandNr = kt.code2												-- Tenant
    		AND FI.F7020 = kn1.code2												-- Network 1
    		AND FI.F7021 = kn2.code2								 				-- Network 2
    		AND FI.Gebiet = kg.code2												-- Geographical Area
    		AND FI.F7019 = ks.code2													-- Sales Channel
    		AND FI.F7004 between isnull(C024.F7011, FI.F7004)						-- INSEE Code Start
    			and isnull(C024.F7012, FI.F7004)									-- INSEE Code End
    		AND FI.ID = SB.ID_FI													-- Already allocated
    		AND FI.LosKZ = 0														-- Not deleted
    		AND FI.MandNr <> 0														-- Children FI have a tenant
    		AND FI.ID_FI_250 <> 0													-- Children FI have a parent
    	LEFT OUTER JOIN te_isi_FI FI_GLO on -- Global Company (Tenant = 0)
    			FI_GLO.MandNr = 0
    		AND FI_GLO.ID = FI.ID_FI_250
    		AND FI.LosKZ = 0
    	LEFT OUTER JOIN te_isi_C022 SP on -- Company Production Species
    			SP.F7003 = ke.code2
    		AND SP.ID_FI = FI_GLO.ID
    		AND SP.DEL = 0
    	WHERE SB.LosKZ = 0
    	AND SB.F7007 = 1 -- Delete only records created by Automatic Allocation
    	AND (
    			C024.F7006 IS NULL						-- Delete even if Species criteria is not set
    		OR	SP.F7003 IS NOT NULL					-- Species criteria is set: Record deleted in HAVING instruction if needed
    		OR	SBID.Inaktiv = 1						-- Delete allocation of inactive users
    	)
    	GROUP BY SBFI.ID, SBFI.ExtSystem, SBFI.ExtKey, SBID.ID, SBID.ExtSystem, SBID.ExtKey
    	HAVING COUNT(C024.ID) = 0	-- Delete if allocation is no longer relevant
    		OR COUNT(FI.ID) = 0		-- or allocated company does not match allocation criterias anymore
    		OR COUNT(SP.ID) = 0		-- or allocated company does not match species criterias anymore
    	;
     
    	drop table #explode;
    END
    On ne jouit bien que de ce qu’on partage.

  17. #17
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    et par curiosité, que donne ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    WITH T AS(SELECT 0 as x union all select 1)
    SELECT  t2.id as x, t1.id as y
    from t2
    cross join T as xT1
    cross join T as xT2
    cross join T as xT3
    cross join T as xT4
    inner join t1
    	on  (t1.c1 * xT1.x = t2.c1) 
    	and (t1.c2 * xT2.x= t2.c2) 
    	and (t1.c3 * xT3.x= t2.c3) 
    	and (t1.c4 * xT4.x= t2.c4)

  18. #18
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    re,

    La requete est en fait un peu plus compliquée que ça !

    Les autres critères peuvent aussi pas mal entrer en jeu. D'ailleurs je m'interroge sur le critère suivant :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    case when C024.F7013 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7013 end >= C024.F7013
    Que fait la fonction ? est-elle deterministe ?
    et surtout, peut-elle renvoyer des nombre négatifs ? car sinon, il me semble que le critère tout entier peut être supprimé !

    Il serait bon aussi de connaitre l'efficacité du NOT EXISTS. Il pourrait être avantageusement (ou pas !) remplacé par une jointure externe + test de nullité

    De plus, la fonction DBO.CU_ID_To_StaNo(SBFI.ID), que fait-elle ? peut-t-on voir le code ?
    Vu qu'elle est appelée dans le SELECT, il pourrait être utile de remplacer - n'en déplaise à SQLPro - le DISTINCT par un GROUP BY afin de réduire les appels à cette fonction. A tester.

    Enfin que donnent les rapport entre cardinalités estimées et réelles ? vu la requete, il se pourrait qu'il y ait un écart important, et scinder la requete en deux (voire plus) en passant par une table temporaire pourrait aussi être une piste a envisager. Surtout que la table temporaire en question pourrait être utile à la requete suivante qui présente pas mal de similarités.
    (ou pourquoi pas comme je le disais, une vue indexée qui serait utile aux deux requêtes, a voir l'impact toutefois lors des mise à jours de tables sous jacentes).

    Ce n'est que des pistes, difficile d'estimer les effets sans pouvoir tester avec la bd sous la main.

  19. #19
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    La fonction CU_Date_To_CRMDate() converti le type "Datetime" en un INT représentant une date au format YYYYMMDD.
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    ALTER FUNCTION [dbo].[CU_Date_To_CRMDate]
    (
    	@Date DATETIME
    )
    RETURNS bigint
    AS
    BEGIN
    	RETURN CONVERT(CHAR, @Date, 112);
    END
    Je sais, c'est sale.

    Quant aux fonctions CU_ID_To_SerNo et CU_ID_To_StaNo, elle sont le résultat d'une idée brillante qu'a eu l'éditeur (après les dates sous forme de INT, il était très en forme), de stocker dans un ID de type BIGINT en réalité deux clés :
    - Un numéro de station (32 bits de poids fort)
    - Un numéro de séquence (32 bits de poids faible)

    Moi j'ai besoin de récupérer ces clés plutôt que l'ID qui est dénué de sens pour l'application (elles sont sérialisées comme ça dans la base, mais jamais ID est utilisé tel quel).

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    ALTER FUNCTION [dbo].[CU_ID_To_SerNo] 
    (
    	@ID bigint
    )
    RETURNS bigint
    AS
    BEGIN
    	RETURN @ID & cast(4294967295 as bigint);
    END

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    ALTER FUNCTION [dbo].[CU_ID_To_StaNo] 
    (
    	@ID bigint
    )
    RETURNS bigint
    AS
    BEGIN
    	RETURN @ID / 4294967296;
    END

    Reste alors la question du NOT EXISTS en effet. La table en question est une table assez mamouth (en nombre de lignes).
    On ne jouit bien que de ce qu’on partage.

  20. #20
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Commencez par lier les fonctions au schéma, elle deviendront déterministes.
    ça ne mange pas de pain, et ça ne peut avoir que des avantages (hormis le fait d'emmerder parfois les développeurs qui voudront modifier des objets s'appuyant dessus )

    Par ailleurs, il me semble donc que le filtre case when C024.F7013 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7013 end >= C024.F7013 est en effet inutile... je ne vois pas comment il pourrait être faux, mais je ne pense pas que l'optimiseur puisse le savoir. A mon avis, il peut être supprimé.

    enfin effectivement, remplacer le DISTINCT par un GROUP BY pourrait réduire le nombre d'appels aux fonctions CU_ID_To_SerNo et CU_ID_To_StaNo (cependant, vu ce qu'elles font, ça ne va pas aller chercher loin !)
    Au fait, pourquoi ce distinct ? qu'est-ce qui multiplie vos lignes ?

    Enfin, tout ça va pas aller au delà de quelques ms de gain... sur plusieurs heures d’exécution...

    Il faut donc identifier ce qui prend du temps. Que disent les plan d’exécution ?

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. Une question un peu bizarre
    Par krapoulos2006 dans le forum EDI, CMS, Outils, Scripts et API
    Réponses: 3
    Dernier message: 19/12/2006, 00h16
  2. un warning un peu bizarre
    Par salseropom dans le forum C
    Réponses: 2
    Dernier message: 26/10/2006, 11h09
  3. premier pas... un peu bizarre
    Par gaia-harastra dans le forum PostgreSQL
    Réponses: 4
    Dernier message: 10/01/2006, 10h00
  4. [CSS] float un peu bizarre sous FF.
    Par elraton dans le forum Mise en page CSS
    Réponses: 4
    Dernier message: 10/10/2005, 19h59
  5. Requete sql un pti peu bizarre
    Par lamoufle dans le forum Langage SQL
    Réponses: 3
    Dernier message: 26/08/2005, 09h39

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo