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

Développement SQL Server Discussion :

Lenteur étrange dans un insert fom select [2016]


Sujet :

Développement SQL Server

  1. #1
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 698
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    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 : 3 698
    Points : 6 332
    Points
    6 332
    Billets dans le blog
    1
    Par défaut Lenteur étrange dans un insert fom select
    Bonjour,

    J'ai une procédure stockée qui exécute cette requête :
    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
     
    insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, fi_extsys, fi_extkey, rep, rep_extsys, rep_extkey, [date], SB_StaNo, SB_SerNo)
    	select
    			'DEL',
    			0,
    			0,
    			'',
    			'',
    			0,
    			'',
    			'',
    			@dte,
    			dbo.CU_ID_To_StaNo(sb.ID),
    			dbo.CU_ID_To_SerNo(sb.ID)
    	from dbo.te_isi_sb sb
    	where sb.LosKZ = 0 and sb.FreiL1 = 0 and sb.F7007 = 1
    	and not exists (
    		select 1 / 0
    		from ##tmp tmp
    		left outer join dbo.te_isi_id id on id.id = sb.BearbeiterId
    		where sb.F4018 = concat(isnull(dbo.CU_ID_To_StaNo(tmp.id_fi), ''), '_', isnull(dbo.CU_ID_To_SerNo(tmp.id_fi), ''), '_', isnull(id.ExtKey, ''))
    	);

    La procédure tourne depuis plusieurs heures, mais on va dire que c'est normal.

    En revanche, dans le même temps, j'exécute à intervalle régulier cette requête :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    select allocated, mode, count(*)
    from cu_customerallocation with (readuncommitted)
    group by Allocated, mode;

    Et là, je vois qu'une ligne est créée dans la table toutes les 3 ou 4 minutes…

    Il n'y a aucun autre traitement concurrent sur la table.

    Comment est-ce possible que le insert soit aussi lent ?
    J'ai toujours pensé que c'était le SELECT qui était lent, mais une fois évalué, l'insert du jeu de résultat était quasi instantané, non ?
    Comment ça se fait que là ça rame autant ?
    On ne jouit bien que de ce qu’on partage.

  2. #2
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    juillet 2016
    Messages
    2 567
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : juillet 2016
    Messages : 2 567
    Points : 10 374
    Points
    10 374
    Billets dans le blog
    21
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    J'ai toujours pensé que c'était le SELECT qui était lent, mais une fois évalué, l'insert du jeu de résultat était quasi instantané, non ?
    Comment ça se fait que là ça rame autant ?
    J'avoue que je ne me suis jamais posé la question, mais naïvement, j'ai effectivement envie de dire que le SELECT est évalué, puis ensuite le INSERT, car les bons SGBD sont avant tout ensemblistes et non séquentiels (bien entendu, le tout de manière transparente et atomique pour l'utilisateur).

    Difficile pour l'instant de répondre à ta question, mais je vois quelques pistes : y a-t-il des triggers qui sont affectés à cette table ? Et comment sont définies tes fonctions dbo.CU_ID_To_StaNo et dbo.CU_ID_To_SerNo ?

    As-tu également affiché le plan d'exécution de la requête, pour voir s'il n'y aurait pas des surprises ? Y a-t-il du RLS d'activé ?
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  3. #3
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 698
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    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 : 3 698
    Points : 6 332
    Points
    6 332
    Billets dans le blog
    1
    Par défaut
    Bonjour François,

    Non, aucun trigger sur les tables utilisées, et accessoirement, aucune autre activité sur la base (je suis sur une base de DEV et seul à bosser dessus).

    Les deux fonctions sont très simples :
    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
     
    ALTER FUNCTION [dbo].[CU_ID_To_StaNo] 
    (
    	-- Parameter ID
    	@ID bigint
    )
    RETURNS bigint
    with schemabinding
    AS
    BEGIN
     
    	-- Return the result of the function
    	RETURN @ID / 4294967296;
     
    END
     
    ALTER FUNCTION [dbo].[CU_ID_To_SerNo] 
    (
    	-- Parameter ID
    	@ID bigint
    )
    RETURNS bigint
    with schemabinding
    AS
    BEGIN
     
    	-- Return the result of the function
    	RETURN @ID & cast(4294967295 as bigint);
     
    END

    J'ai modifié ma procédure pour avoir directement le résultat de la concaténation sous forme d'une colonne calculée persisted, ainsi qu'un index dessus (ça me fait bizzare de faire des données persisted et un index dans une table temporaire) :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    create table #tmp (id_fi bigint, id_id bigint, id_extkey varchar(80), extkey as (concat(cast(id_fi / 4294967296 as bigint), '_', id_fi & cast(4294967295 as bigint), '_', id_extkey)) persisted, primary key (id_fi, id_id));
    create unique index uix_tmp on #tmp (extkey);

    Maintenant la requête est instantanée (et plus lisible )
    J'en ai aussi profité pour nettoyer un peu le code en simplifiant un truc ou deux (notamment disparition des isnull() puisque les colonnes ne sont jamais nulles)
    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
     
    	insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, fi_extsys, fi_extkey, rep, rep_extsys, rep_extkey, [date], SB_StaNo, SB_SerNo)
    	select
    			'DEL',
    			0,
    			0,
    			'',
    			'',
    			0,
    			'',
    			'',
    			@dte,
    			dbo.CU_ID_To_StaNo(sb.ID),
    			dbo.CU_ID_To_SerNo(sb.ID)
    	from dbo.te_isi_sb sb
    	where sb.LosKZ = 0 and sb.FreiL1 = 0 and sb.F7007 = 1
    	and not exists (
    		select 1 / 0
    		from #tmp tmp
    		where sb.F4018 = tmp.extkey
    	);

    Pas trop compris pourquoi ça a autant d'impact sur les performances par contre...
    On ne jouit bien que de ce qu’on partage.

  4. #4
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    juillet 2016
    Messages
    2 567
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : juillet 2016
    Messages : 2 567
    Points : 10 374
    Points
    10 374
    Billets dans le blog
    21
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    Pas trop compris pourquoi ça a autant d'impact sur les performances par contre...
    Les index, ont a presque l'impression que c'est magique parfois ! Clairement, ici, leur impact est plus que positif, et rien d'étonnant à cela. Sans, la sous-requête dans le NOT EXISTS doit scanner l'ensemble des enregistrements de ta table temporaire. Avec, la réponse est quasi immédiate car une simple recherche d'index permet de trouver la réponse.

    Mais cela ne répond toujours pas à la question initiale du pourquoi les insertions se font ligne par ligne avec un intervalle de plusieurs minutes entre chaque... On peut avoir une idée du nombre d'enregistrements de la table temporaire ? Et la config de la machine de test (notamment la RAM allouée à SQL Server) ?
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  5. #5
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 698
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    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 : 3 698
    Points : 6 332
    Points
    6 332
    Billets dans le blog
    1
    Par défaut
    Visiblement la table temporaire était très grosse (environ 1,7 million de lignes, ça commence à faire pour une table temporaire).

    Quant à la config, ça commence à devenir étriqué :
    4 cœurs à 2,19 GHz (j'ai réussi à faire benner les Goupil l'an dernier)
    8 Go de RAM (4096 fois plus que la NASA n'en affait pour la mission Apollo 13 !)
    Les différentes bases et leurs logs sont découpés en plusieurs fichiers répartis sur 6 disques logiques (le tout sur le même NAS par contre… on n'a plus les bandes magnétiques depuis la semaine dernière)

    Le tout pour :
    Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) Jun 15 2019 19:20:12 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)
    4 bases de données de 90 Go
    Et 12 bases en tout (hors bases systèmes)

    Là j'ai un traitement (basé sur le résultat de la procédure) qui tourne depuis 12 heures et qui a traité 40 000 lignes (sur 1,7 millions grmpf) et un CPU est à 100% d'occupation.
    Reste plus qu'à demander à l'infra un nouveau serveur pour Noël, j'aurai peut-être un CPC 6128+ !
    On ne jouit bien que de ce qu’on partage.

  6. #6
    Expert confirmé Avatar de 7gyY9w1ZY6ySRgPeaefZ
    Homme Profil pro
    dba
    Inscrit en
    juillet 2007
    Messages
    4 746
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : juillet 2007
    Messages : 4 746
    Points : 5 712
    Points
    5 712
    Par défaut
    Est-ce que tes disques sont plein ? En forme ? As-tu vérifié ta vitesse d'écriture / lecture ?

  7. #7
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    juillet 2016
    Messages
    2 567
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : juillet 2016
    Messages : 2 567
    Points : 10 374
    Points
    10 374
    Billets dans le blog
    21
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    Visiblement la table temporaire était très grosse (environ 1,7 million de lignes, ça commence à faire pour une table temporaire).
    Donc le gain de performance lié à l'ajout d'un index s'explique très bien !

    Citation Envoyé par StringBuilder Voir le message
    4 cœurs à 2,19 GHz (j'ai réussi à faire benner les Goupil l'an dernier)
    8 Go de RAM (4096 fois plus que la NASA n'en affait pour la mission Apollo 13 !)
    Les différentes bases et leurs logs sont découpés en plusieurs fichiers répartis sur 6 disques logiques (le tout sur le même NAS par contre… on n'a plus les bandes magnétiques depuis la semaine dernière)
    Pour une machine de dev, ça passe
    Après, les disques sont sur un NAS (en iSCSI je suppose). Donc il faut tenir compte du réseau et de son potentiel encombrement.

    Et comme le dit 7gyY9w1ZY6ySRgPeaefZ, vérifie aussi les disques, ainsi que le taux d'utilisation du NAS (pas l'espace disque du NAS, mais bien l'occupation du NAS). Est-il dédié, ou fait-il autre chose qui pourrait expliquer des lenteurs d'accès ?.
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  8. #8
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 698
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    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 : 3 698
    Points : 6 332
    Points
    6 332
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par 7gyY9w1ZY6ySRgPeaefZ Voir le message
    Est-ce que tes disques sont plein ? En forme ? As-tu vérifié ta vitesse d'écriture / lecture ?
    Bah le souci vient de là (mais pas que).

    On a deux NAS :
    - Un "vrai" NAS à base de disques magnétiques qui ont plus de 10 ans.
    - Un SAN à base de disques SSD flambant neufs (enfin... ils doivent bien avoir 3 ou 4 ans maintenant)

    Et là dessus il y a des centaines de VM qui tournent.
    Donc, même si quand je parle de PC Carrefour à l'infra ça les fait sauter au plafond, même avec une connectique de malade, je vois pas comment, même avec du SSD, on va pouvoir avoir des débits de fou lorsqu'il y a de la concurrence de plusieurs milliers de process sur la même baie de disques...

    Il y a quelques années, on avait des soucis de temps de réponse disque et réseau, car l'hyperviseur n'utilisait qu'un seul CPU pour gérer les IO (disques et réseau) et qu'il était en constante saturation.

    C'est soit-disant corrigé depuis, mais à chaque fois que je tente de faire des "bench" pour essayer de comprendre où ça coince, j'ai des résultats incompréhensibles...
    Par moment je vais pouvoir copier un fichier d'une partition à l'autre à plus de 1 Go/s.
    Et d'autres fois, le même fichier va se transférer à quelques Ko/s avec des pauses toutes les 10 secondes...

    Alors forcément, SQL Server il subit la même chose.

    Mais indépendamment de ça, ce qui me surprenais c'était que la partie "insert" soit si lente, alors que les lignes étaient visiblement déjà trouvées par le SELECT.

    Après, j'ai vidé la table (par un DELETE et non un TRUNCATE)... et j'ai fini par comprendre... il a mis près d'une heure à supprimer les lignes... par lot de quelques dizaines et une pause à chaque paquet.

    Je vais me chercher une corde.
    On ne jouit bien que de ce qu’on partage.

  9. #9
    Modérateur

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

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 278
    Points : 12 008
    Points
    12 008
    Par défaut
    bonjour,

    Non, un SELECT peut "renvoyer" des données avant que la requête ne soient complément terminée dans certains cas. Cela dépend du plan d'exécution et de la présence ou non d'opérateurs bloquants.

    ici, la seule restriction est un NOT EXISTS, probablement traduit par une boucle imbriquée, qui n'est pas un opérateur bloquant, il parait donc logique que les lignes soient insérées au fur et à mesure qu'elles sont traitées par l'opération de boucle imbriquée.

    En revanche, il est probable aussi que la table temporaire ne tienne pas en mémoire (surtout si sa taille est sous évaluée, avec par exemple une estimation d'une seule ligne pour la table, ce qui est loin d'être le cas... que donne la requête initiale avec un option recompile ?).
    En l'absence d'index, le NOT EXISTS provoque un scan de table dans une boucle imbriquée, et si la table temporaire ne tient pas en mémoire en totalité, c'est alors le disque qui va prendre le relais, et comme le système disque ne semble pas performant... c'est le drame

    Enfin ce n'est qu'une théorie, et le plan d’exécution réel pourrait nous en dire un peu plus...

  10. #10
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 141
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 141
    Points : 45 203
    Points
    45 203
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    Bonjour François,

    Non, aucun trigger sur les tables utilisées, et accessoirement, aucune autre activité sur la base (je suis sur une base de DEV et seul à bosser dessus).

    Les deux fonctions sont très simples :
    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
     
    ALTER FUNCTION [dbo].[CU_ID_To_StaNo] 
    (
    	-- Parameter ID
    	@ID bigint
    )
    RETURNS bigint
    with schemabinding
    AS
    BEGIN
     
    	-- Return the result of the function
    	RETURN @ID / 4294967296;
     
    END
     
    ALTER FUNCTION [dbo].[CU_ID_To_SerNo] 
    (
    	-- Parameter ID
    	@ID bigint
    )
    RETURNS bigint
    with schemabinding
    AS
    BEGIN
     
    	-- Return the result of the function
    	RETURN @ID & cast(4294967295 as bigint);
     
    END

    ...

    Pas trop compris pourquoi ça a autant d'impact sur les performances par contre...
    En fait dès que tu met une UDF, le parcours des données devient itératif (scan de table). Si tu les éradiques, il est capable de faire un parcours optimisé en tenant compte des index et en utilisant le parallélisme.

    Pour info, dans la version 2019, ce problème sera partiellement éliminé, par tranformation de l'UDF en expression en ligne dans la requête... Cela s’appelle le "Scalar UDF Inlining"...

    A lire :
    https://blogs.msdn.microsoft.com/sql...-udf-inlining/


    A +
    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  11. #11
    Modérateur

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

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 278
    Points : 12 008
    Points
    12 008
    Par défaut
    est-ce que tu es sûr de toi ?

    ok pour le parallélisme, mais dans la mesure où les fonctions scalaire ne sont utilisées que dans le SELECT, il ne me semble pas que cela empêche l'utilisation d'indexes...

  12. #12
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 141
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 141
    Points : 45 203
    Points
    45 203
    Par défaut
    Au passage SCHEMABINDING n'a aucun intérêt puisque la fonction n'encapsule par d'appel à d'autres objets...

    A +
    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  13. #13
    Modérateur

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

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 278
    Points : 12 008
    Points
    12 008
    Par défaut
    si, il y a un intérêt : lier la fonction au schéma est une condition pour qu'elle soit considérée comme déterministe, ce qui peut influer pas mal sur les performances dans certains cas....

  14. #14
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 141
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 141
    Points : 45 203
    Points
    45 203
    Par défaut
    Citation Envoyé par aieeeuuuuu Voir le message
    si, il y a un intérêt : lier la fonction au schéma est une condition pour qu'elle soit considérée comme déterministe, ce qui peut influer pas mal sur les performances dans certains cas....
    Cela n'a d'intérêt que pour les fonctions écrites en SQL CLR... SQL Server sait par défaut pour une UDF si elle est déterministe ou non...

    Petite démo...

    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
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    CREATE FUNCTION F_DATE ()
    RETURNS DATE
    AS
    BEGIN
       RETURN CAST(GETDATE() AS DATE)
    END
    GO
     
    CREATE FUNCTION F_DATE_AN2000 ()
    RETURNS DATE
    AS
    BEGIN
       RETURN CAST('2000-01-01' AS DATE)
    END
    GO
     
    CREATE FUNCTION F_DATE_AN3000 ()
    RETURNS DATE
    WITH SCHEMABINDING
    AS
    BEGIN
       RETURN CAST('3000-01-01' AS DATE)
    END
    GO
     
    SELECT OBJECTPROPERTYEX(object_id, 'isDeterministic'), name 
    FROM   sys.objects WHERE name LIKE 'F?_DATE%' ESCAPE '?'
    A +
    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  15. #15
    Modérateur

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

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 278
    Points : 12 008
    Points
    12 008
    Par défaut
    Je n'ai pas compris le sens de la démo... les trois fonctions sont non deterministes par nature. Les lier au schéma ne change donc rien en effet.

    en revanche, si on reprend la première fonction de StringBuilder, et que l'on en crée une lié au schéma, l'autre non :

    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
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
     
    USE tempdb;
    GO
     
    CREATE FUNCTION [dbo].[CU_ID_To_StaNo] 
    (
    	-- Parameter ID
    	@ID bigint
    )
    RETURNS bigint
    AS
    BEGIN
     
    	-- Return the result of the function
    	RETURN @ID / 4294967296;
     
    END
     
    GO
     
    CREATE FUNCTION [dbo].[CU_ID_To_StaNo_SCHEMABOUND] 
    (
    	-- Parameter ID
    	@ID bigint
    )
    RETURNS bigint
    with schemabinding
    AS
    BEGIN
     
    	-- Return the result of the function
    	RETURN @ID / 4294967296;
     
    END

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    SELECT R.ROUTINE_NAME, R.IS_DETERMINISTIC
    FROM INFORMATION_SCHEMA.ROUTINES AS R
    WHERE R.ROUTINE_NAME LIKE 'CU_ID_To_StaNo%' ESCAPE'/'
    -->
    ROUTINE_NAME                                                                                                                     IS_DETERMINISTIC
    -------------------------------------------------------------------------------------------------------------------------------- ----------------
    CU_ID_To_StaNo                                                                                                                   NO
    CU_ID_To_StaNo_SCHEMABOUND                                                                                                       YES
    
    si on crée ensuite deux tables temporaires identiques, utilisant l'une puis l'autre des fonctions :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
     
    CREATE TABLE #T_SCHEMABOUND(val BIGINT, result AS  [dbo].[CU_ID_To_StaNo_SCHEMABOUND](val) )
    CREATE TABLE #T(val BIGINT, result AS  [dbo].[CU_ID_To_StaNo](val) )
    Créons maintenant un index sur la colonne calculée :
    Table avec la fonction liée au schéma
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE INDEX IX_T_SCHEMABOUND_Result ON #T_SCHEMABOUND(result);
    -->
    Commande(s) réussie(s).
    Sur la table avec la fonction non liée au schéma :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE INDEX IX_T_Result ON #T(result);
    -->
    Msg*2729, Niveau*16, État*1, Ligne*15
    Impossible d'utiliser la colonne 'result' de table '#T' dans un index, des statistiques ou en tant que clé de partition car elle n'est pas déterministe.

    D'ailleurs, au passage, @StringBuilder : il n'est pas nécessaire ici que la colonne calculée soit persistante pour pouvoir créer un index, car le type est précis (c'est en revanche nécessaire avec des types imprécis comme FLOAT)

  16. #16
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 698
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    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 : 3 698
    Points : 6 332
    Points
    6 332
    Billets dans le blog
    1
    Par défaut
    Merci pour toutes ces informations

    J'ai obtenu de passer de 8 à 16 Go sur le serveur.
    Même si c'est pas folichon, ça a drastiquement amélioré les performances : au lieu de mettre plus de 48 heures à tourner, l'ensemble du traitement ne dure plus que 4 heures...

    En revanche je constate toujours ces pauses à interval régulier. Je pense qu'on a clairement un souci d'IO quelque part, mais augmenter la mémoire a permis de réduire ces dernières... Vu que c'est un traitement exceptionnel (en temps normal il ne traite quelques dizaines de milliers de lignes et non des millions) on va dire que c'est satisfaisant.
    On ne jouit bien que de ce qu’on partage.

  17. #17
    Membre éprouvé
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    septembre 2016
    Messages
    580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2016
    Messages : 580
    Points : 1 097
    Points
    1 097
    Par défaut
    Bonsoir,

    Il me semble acquit que le problème de performance vient de la saturation mémoire.

    Je n'ai pas compris, il me semble avoir lu que la requête est instantanée après indexation ; pourquoi l'ajout de RAM ? pourquoi 4 heures ?

    Bon, disons que ce n'est pas le sujet. En absence du plan d’exécution on est réduit à faire des supputations.
    Est-ce que le SELECT est sensible au nombre de lignes issues de la table dbo.te_isi_sb ?
    Est-ce qu'on peut imaginer une approche par paquets de 100, 1000 ou 10000 lignes ?
    Le savoir est une nourriture qui exige des efforts.

  18. #18
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 698
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    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 : 3 698
    Points : 6 332
    Points
    6 332
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par Michel.Priori Voir le message
    Il me semble acquit que le problème de performance vient de la saturation mémoire.
    En fait, le problème me semble venir de plusieurs facteurs, qui se marchent sur les pieds :
    - CPU clairement trop faibles. Il y a 4 cœurs sur la VM. Je pourrais en demander plus, mais à ça ne sert à rien : il y a peu de gros traitements concurrents, donc autant lors de certains traitements, je peux avoir 1 ou 2 cœurs à 100% d'occupation pendant des dizaines de minutes, autant les autres cœurs se tournent les pouces pendant ce temps. Là, le seul moyen d'améliorer ça serait de changer de machine hôte, mais il paraît qu'il faut amortir les investissements d'y il y 10 ans avant d'en refaire de nouveaux…
    - IO mal maîtrisées. On a le choix entre un NAS très anciens avec des disques magnétiques en fin de vie (un disque à changer par jour, avec plusieurs heures de reconstruction du RAID à chaque fois… heureusement à chaud) ou un SAN flambant neuf (enfin… il doit bien avoir 3 ou 4 ans maintenant) à base de RAID 10 sur des SSD. Bref, le choix entre la peste et le choléra, puisque dans l'idéal il faudrait un bon NAS… A ça on ajoute que le système de virtualisation dédie un seule cœur aux IO… et que les CPU sont à la ramasse, et donc on se retrouve avec des IO ralenties.
    - Par conséquent, les gens (client comme développeurs) s'énervent, relancent les traitements qui mettent trop longtemps, et on arrive à une surconsommation des ressources qui empire lorsqu'il y a un pic.
    - Visiblement, le fait d'ajouter de la mémoire permet de réduire grandement le nombre d'IO, puisqu'on a moins besoin de travailler dans tempdb ni charger et décharger les données entre chaque requête. Donc moins d'IO, moins de charge sur le CPU qui gère les IO, et on retrouve des performances "normales" sur les baies de disque.

    Citation Envoyé par Michel.Priori Voir le message
    Je n'ai pas compris, il me semble avoir lu que la requête est instantanée après indexation ; pourquoi l'ajout de RAM ? pourquoi 4 heures ?
    Bon, disons que ce n'est pas le sujet. En absence du plan d’exécution on est réduit à faire des supputations.
    Le souci, c'est que ma procédure stockée est un peu longue, et n'est qu'une étape dans un traitement plus long. La création d'un index a donc permis de grandement améliorer les performances d'une partie du traitement, mais je suis ensuite tombé sur d'autres points problématiques…

    Citation Envoyé par Michel.Priori Voir le message
    Est-ce que le SELECT est sensible au nombre de lignes issues de la table dbo.te_isi_sb ?
    Oui, absolument, dans la mesure où une partie du traitement consiste à identifier les lignes qui sont en trop ou en manque dans cette table par rapport à une liste théorique. A partir du moment où je fois faire des not exists d'une liste de plusieurs millions de lignes sur une table de plusieurs millions de lignes… bah ça n'arrange pas que la table soit grosse

    Citation Envoyé par Michel.Priori Voir le message
    Est-ce qu'on peut imaginer une approche par paquets de 100, 1000 ou 10000 lignes ?
    Pour résumer, on a une table qui défini des règles du type : "tel commercial s'occupe des clients de telle catégorie sur telle tranche de code postaux". J'ai plusieurs milliers de lignes là dedans, avec un total de 6 règles différentes, utilisant diverses données dans plusieurs tables différentes, donc certaines peuvent se chevaucher.
    A partir de là, je génère dans une table temporaire toutes les relations commercial <-> client théoriques, sans doublons. Du coup, là je ne peux pas trop découper en paquets, sinon je vais générer des doublons.
    Ensuite, je compare cette liste théorique au contenu de la véritable table de relations, et vu que ce serait trop simple de directement bosser en SQL, je dois passer par un programme d'import (qui recalcule entre autre des colonnes binaires cryptés que je ne sais pas décoder ni générer… mais qui sont vitales pour l'application). Du coup, je stocke dans une table persistante cette fois le résultat de ma comparaison. Des lignes avec une colonne "mode" initialisée à "NEW" pour celles qui doivent être ajoutées, et "DEL" pour celles qui au contraire doivent être supprimées.
    A nouveau, à moins de lancer X fois le programme d'import ce qui, vu son fonctionnement, ne me semble pas du tout permettre de gagner en performances, je suis obligé de tout traiter d'un coup.
    Enfin, je lance l'import (une fois en mode création et une fois en mode suppression) pour mettre à jour ma table te_isi_sb. Et là c'est le drame, car le programme d'import gère les lignes 1 à 1, avec une transaction à chaque ligne… mais tout un tas de traitements complémentaires qui durent un temps pas possible… bref, en suppression, ça supprime un peu plus de 1000 lignes par minute, et en création, ça va encore un peu plus lentement…

    A ça, on ajoute que suite à un changement de nature d'une colonne, il faut regénérer l'ensemble des 1,7 millions de lignes de la table te_isi_sb et on arrive à un traitement qui tourne depuis 3 jours, et je ne suis pas certain que demain matin il ait fini…

    Pour la gloire, voici le code complet de la PS...
    J'avoue que je ne sais plus trop par où la prendre pour améliorer ses performances... même si maintenant le fait qu'elle tourne 15 ou 20 minutes n'est plus vraiment un problème comparé au temps pharaonique de l'import qui s'en suit...
    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
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		Un collèque
    -- Create date: 2015-07-31
    -- Description:	Build customer allocation (SB) from matrix C024
    --				SB is updated through the use of a temporary table and an import
    -- History:
    --			2015-09-11 Un collèque : Create allocation for Customers only
    --			2015-10-29 Un collèque : Species criteria is now linked to the species of the productions (C022) of the customer
    --			2015-11-27 Un collèque : Do not allocate and delete allocation of inactive users
    --			2016-05-04 Moi : Optimisation
    --			2016-05-19 Moi : Réécriture complète
    --			2016-06-17 Moi : Ajout de SB_StaNo et SB_SerNo pour la suppression
    --			2018-06-19 Moi : Ajout d'une règle pour pouvoir recopier les SB d'un rep vers un autre
    --			2018-10-24 Moi : Ajout d'une règle pour pouvoir affecter une plage de code postaux
    --			2019-01-25 Moi : Ajout de critères d'exclusions d'une règle à l'autre pour éviter qu'une règle plus large ne prenne le pas sur un paramétrage plus fin)
    --			2019-06-06 Moi : Ajout d'un test sur SB.FreiL1 (Disabled) en plus de LosKZ
    --			2019-09-16 Moi : Modification du test pour suppression des SB avec mauvaise clé externe + petites optimisations
    -- =============================================
    ALTER PROCEDURE [dbo].[CU_Customer_Allocation]
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	declare @dte datetime2;
    
    	-- Création d'une table temporaire qui servira à générer l'allocation complète
    	print 'Création de la table temporaire';
    	create table #tmp (id_fi bigint, id_id bigint, extkey as (concat(cast(id_fi / 4294967296 as bigint), '_', id_fi & cast(4294967295 as bigint), '_', id_id)) persisted, primary key (id_fi, id_id));
    	create unique index uix_tmp on #tmp (extkey);
    
    	set @dte = SYSDATETIME();
    
    	-- Suppression des allocations archivées
    	print 'Suppression des allocations déjà traitées';
    	DELETE CU_CustomerAllocation WHERE Allocated = 1;
    
    	-- Archivage de la précédente allocation
    	print 'Archivage des allocations en cours';
    	UPDATE CU_CustomerAllocation SET Allocated = 1 WHERE Allocated = 0;
    	
    	print 'Génération de la liste des allocations théoriques';
    	insert into #tmp (id_fi, id_id)
    
    	-- Règle 1
    	-- On affecte à un utilisateur les clients d'un tenant qui font une espèce donnée dans une plage de codes INSEE donnée
    	SELECT fi.id, id.id
    	FROM dbo.te_isi_C024 C024
    	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
    	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
    	INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
    	WHERE C024.del = 0
    	and C024.f7000 > 0
    	and C024.f7004 > 0
    	and C024.f7006 > 0
    	and C024.f7007 = 0
    	and C024.f7008 = 0
    	and isnull(C024.F7011, '') <> ''
    	and isnull(C024.F7012, '') <> ''
    	and C024.F7015 = 0
    	and isnull(C024.F7019, '') = ''
    	and isnull(C024.F7020, '') = ''
    	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
    	and exists (
    		select *
    		from te_isi_C022 SP
    		where SP.F7003 = c024.f7006 AND SP.ID_FI = FI_GLO.ID AND SP.DEL = 0
    	)
    	union
    	-- Règle 2
    	-- On affecte à un utilisateur les clients d'un tenant sans une espèce dans une plage de codes INSEE donnée
    	SELECT fi.id, id.id
    	FROM dbo.te_isi_C024 C024
    	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
    	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
    	INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
    	WHERE C024.del = 0
    	and C024.f7000 > 0
    	and C024.f7004 > 0
    	and C024.f7006 = 0
    	and C024.f7007 = 0
    	and C024.f7008 = 0
    	and isnull(C024.F7011, '') <> ''
    	and isnull(C024.F7012, '') <> ''
    	and C024.F7015 = 0
    	and isnull(C024.F7019, '') = ''
    	and isnull(C024.F7020, '') = ''
    	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
    	union
    	-- Règle 3
    	-- On affecte à un utilisateur les clients d'un tenant qui ont un réseau donné sans sous-réseau dans une plage de codes INSEE donnée
    	SELECT fi.id, id.id
    	FROM dbo.te_isi_C024 C024
    	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
    	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and FI.F7020 = C024.F7007 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
    	WHERE C024.del = 0
    	and C024.f7000 > 0
    	and C024.f7004 > 0
    	and C024.f7006 = 0
    	and C024.f7007 > 0
    	and C024.f7008 = 0
    	and isnull(C024.F7011, '') <> ''
    	and isnull(C024.F7012, '') <> ''
    	and C024.F7015 = 0
    	and isnull(C024.F7019, '') = ''
    	and isnull(C024.F7020, '') = ''
    	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
    	union
    	-- Règle 4
    	-- On affecte à un utilisateur les clients d'un tenant qui ont un réseau et un sous-réseau donné dans une plage de codes INSEE donnée
    	SELECT fi.id, id.id
    	FROM dbo.te_isi_C024 C024
    	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
    	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and FI.F7020 = C024.F7007 and FI.F7021 = C024.F7008 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
    	WHERE C024.del = 0
    	and C024.f7000 > 0
    	and C024.f7004 > 0
    	and C024.f7006 = 0
    	and C024.f7007 > 0
    	and C024.f7008 > 0
    	and isnull(C024.F7011, '') <> ''
    	and isnull(C024.F7012, '') <> ''
    	and C024.F7015 = 0
    	and isnull(C024.F7019, '') = ''
    	and isnull(C024.F7020, '') = ''
    	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
    	union
    	-- Règle 5
    	-- On affecte à un utilisateur tous les SB d'un autre utilisateur
    	SELECT SB.ID_FI, C024.F7000
    	FROM dbo.TE_ISI_C024 C024
    	INNER JOIN dbo.TE_ISI_SB SB on SB.BearbeiterId = C024.F7015 and SB.LosKZ = 0 and SB.FreiL1 = 0
    	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
    	WHERE C024.DEL = 0
    	and C024.F7000 > 0
    	--and C024.f7004 = 0
    	and C024.f7006 = 0
    	and C024.f7007 = 0
    	and C024.f7008 = 0
    	and isnull(C024.F7011, '') = ''
    	and isnull(C024.F7012, '') = ''
    	and C024.F7015 > 0
    	and isnull(C024.F7019, '') = ''
    	and isnull(C024.F7020, '') = ''
    	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
    	union
    	-- Règle 6
    	-- On affecte à un utilisateur les clients d'un tenant sans une espèce dans une plage de codes postaux donnée
    	SELECT fi.id, id.id
    	FROM dbo.te_isi_C024 C024
    	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
    	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.Plz between c024.f7019 and c024.f7020
    	INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
    	WHERE C024.del = 0
    	and C024.f7000 > 0
    	and C024.f7004 > 0
    	and C024.f7006 = 0
    	and C024.f7007 = 0
    	and C024.f7008 = 0
    	and isnull(C024.F7011, '') = ''
    	and isnull(C024.F7012, '') = ''
    	and C024.F7015 = 0
    	and isnull(C024.F7019, '') <> ''
    	and isnull(C024.F7020, '') <> ''
    	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014;
    
    	-- On enregristre la liste des SB à désallouer
    	print 'Identification des allocations existantes à supprimer';
    	insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, rep, [date], SB_StaNo, SB_SerNo)
    	select
    			'DEL',
    			null,
    			null,
    			null,
    			@dte,
    			dbo.CU_ID_To_StaNo(sb.ID),
    			dbo.CU_ID_To_SerNo(sb.ID)
    	from dbo.te_isi_sb sb
    	where sb.LosKZ = 0 and sb.FreiL1 = 0 and sb.F7007 = 1
    	and not exists (
    		select 1 / 0
    		from #tmp tmp
    		where sb.F4018 = tmp.extkey
    	);
    	
    	-- On exclu les allocations déjà existantes
    	print 'Exclusion des allocations déjà correctes';
    	delete #tmp
    	where exists
    	(
    		select *
    		from dbo.te_isi_sb sb
    		where sb.BearbeiterId = #tmp.id_id
    		and sb.ID_FI = #tmp.id_fi
    		and sb.LosKZ = 0
    		and sb.FreiL1 = 0
    		and sb.F4018 = #tmp.extkey
    	);
    
    	-- On enregistre les allocations à créer
    	print 'Ajout des allocations à créer';
    	insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, rep, [date], SB_StaNo, SB_SerNo)
    	select 'NEW', 
    			dbo.CU_ID_To_StaNo(FI.ID) CompanyStaNo,
    			dbo.CU_ID_To_SerNo(FI.ID) CompanySerNo,
    			id.id,
    			@dte,
    			dbo.CU_ID_To_StaNo(SB.ID) SB_StaNo,
    			dbo.CU_ID_To_SerNo(SB.ID) SB_SerNo
    	from #tmp tmp
    	inner join dbo.te_isi_fi fi on fi.id = tmp.ID_fi
    	inner join dbo.te_isi_id id on id.id = tmp.id_id
    	left outer join dbo.te_isi_sb sb on sb.LosKZ = 0 and sb.F7007 = 1 and sb.FreiL1 = 1 and sb.F4018 = tmp.extkey;
    
    	-- On détruit la table temporaire
    	print 'Destruction de la table temporaire';
    	drop table #tmp;
    END;
    On ne jouit bien que de ce qu’on partage.

  19. #19
    Modérateur

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

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 278
    Points : 12 008
    Points
    12 008
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    J'avoue que je ne sais plus trop par où la prendre pour améliorer ses performances...
    En premier lieu, je mettrai la création de l'index sur la table temporaire après le peuplement de celle-ci, voire créer la clef primaire après également.

    ensuite, quelques pistes à tester éventuellement.
    Concernant la requête avec les UNION : voir s'il n'est pas possible de remplacer par une seule requête avec des CASE en remplacement. pas sûr du résultat toutefois, et cela risque de nuire à la lisibilité/maintenance en "mélangeant" les différentes règles de gestion...

    Concernant les différentes opération sur la table dbo.CU_CustomerAllocation : remplacer par un MERGE pour effectuer en une fois le DELETE et l'UPDATE du début, mais également peut-être l'INSERT final, si la table temporaire est peuplée avant. Là aussi, la transformation risque d'être compliquée, et le résultat risque de nuire à la lisibilité du code. Par sûr non plus que cela améliore vraiment les perf, ce n'est qu'une piste à tester.

    En revanche, vérifier aussi que la fonction CU_Date_To_CRMDate est bien liée au schéma. Au delà de l’aspect déterministe déjà évoqué plus haut, il y a un autre point également important, car la fonction est utilisée dans le cadre d'un INSERT. Si elle n'est pas liée au schéma, le moteur ignore si la fonction accède ou non aux données de la table visée par l'INSERT. Il mettra donc en place une protection contre l'effet Halloween, ce qui se traduira par un spool de table relativement couteux, surtout dans ton cas puisque la RAM semble un peu juste...

  20. #20
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 698
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    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 : 3 698
    Points : 6 332
    Points
    6 332
    Billets dans le blog
    1
    Par défaut
    Merci d'avoir pris le temps de relire la procédure

    Citation Envoyé par aieeeuuuuu Voir le message
    En premier lieu, je mettrai la création de l'index sur la table temporaire après le peuplement de celle-ci, voire créer la clef primaire après également.
    C'est fait en effet.
    A la base, j'aime pas trop désactiver les index/clés mais c'est vrai que sur une table temporaire, je ne risque pas grand chose.

    Citation Envoyé par aieeeuuuuu Voir le message
    ensuite, quelques pistes à tester éventuellement.
    Concernant la requête avec les UNION : voir s'il n'est pas possible de remplacer par une seule requête avec des CASE en remplacement. pas sûr du résultat toutefois, et cela risque de nuire à la lisibilité/maintenance en "mélangeant" les différentes règles de gestion...
    Si tu regardes dans l'historique de la procédure, tu verras "2016-05-19 Moi : Réécriture complète".
    C'est à ce moment que j'ai mis en place les UNION.
    En effet, avant il y avait toute une tripotée de CASE, mais aussi et surtout un grand nombre de OR. Du coup aucun index n'était utilisé, et le select seul mettait à l'époque plus de 12 heures à tourner (alors qu'il y avait à peine le tiers des données actuelles, et la moitié des règles).
    Les UNION ont donc permis de m'affranchir des OR, et, j'ose espérer, permettre à SQL Server de paralléliser les différents SELECT.

    Citation Envoyé par aieeeuuuuu Voir le message
    Concernant les différentes opération sur la table dbo.CU_CustomerAllocation : remplacer par un MERGE pour effectuer en une fois le DELETE et l'UPDATE du début, mais également peut-être l'INSERT final, si la table temporaire est peuplée avant. Là aussi, la transformation risque d'être compliquée, et le résultat risque de nuire à la lisibilité du code. Par sûr non plus que cela améliore vraiment les perf, ce n'est qu'une piste à tester.
    Je pense surtout que je vais virer ce flag "allocated" qui ne sert au final à rien du tout... et remplacer tout ça par un truncate de la table en début de traitement.

    Citation Envoyé par aieeeuuuuu Voir le message
    En revanche, vérifier aussi que la fonction CU_Date_To_CRMDate est bien liée au schéma. Au delà de l’aspect déterministe déjà évoqué plus haut, il y a un autre point également important, car la fonction est utilisée dans le cadre d'un INSERT. Si elle n'est pas liée au schéma, le moteur ignore si la fonction accède ou non aux données de la table visée par l'INSERT. Il mettra donc en place une protection contre l'effet Halloween, ce qui se traduira par un spool de table relativement couteux, surtout dans ton cas puisque la RAM semble un peu juste...
    Elle est déjà liée au schéma : (attention, ne pas vomir)

    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
     
    ALTER FUNCTION [dbo].[CU_Date_To_CRMDate]
    (
    	-- Date value
    	@Date DATETIME
    )
    RETURNS bigint
    with schemabinding
    AS
    BEGIN
     
    	-- Return the result of the function
    	RETURN CONVERT(CHAR, @Date, 112);
     
    END
    On ne jouit bien que de ce qu’on partage.

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

Discussions similaires

  1. [SQL] Problème de guillemets dans un INSERT INTO SELECT
    Par Lenezir dans le forum PHP & Base de données
    Réponses: 1
    Dernier message: 25/02/2008, 14h21
  2. lenteur dans l'insertion de donnees en 10Gr2
    Par JUSTIN Loïc dans le forum SQL
    Réponses: 55
    Dernier message: 22/09/2006, 19h55
  3. probleme dans un INSERT INTO...........SELECT
    Par briiice dans le forum Requêtes
    Réponses: 5
    Dernier message: 26/01/2006, 15h13
  4. [SQL] SELECT dans un INSERT
    Par macben dans le forum Oracle
    Réponses: 3
    Dernier message: 28/12/2005, 09h40
  5. [ASE]probleme memoire: select dans une insert
    Par SegmentationFault dans le forum Sybase
    Réponses: 2
    Dernier message: 16/08/2005, 12h20

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