La procédure stockée SQL de la table temporaire problème de mémoire

Nous avons le simple suivant une Procédure Stockée qui s'exécute comme une nuit de travail SQL server agent. Habituellement, il s'exécute en 20 minutes, mais récemment, le MatchEvent et le résultat du match, les tableaux ont augmenté de plus de 9 millions de lignes chacune. Cela a abouti à la boutique de la procédure de la prise de plus de 2 heures de course, avec tous les 8GO de mémoire sur notre SQL zone utilisée. Ce qui rend la base de données est indisponible pour l'ordinaire des requêtes qui tentent d'y accéder.

Je suppose que le problème est que la table temporaire est trop grand et est à l'origine de la mémoire et de la base de données unavailablity questions.

Comment puis-je réécrire la procédure stockée pour le rendre plus efficace et moins gourmande en mémoire?

Note: j'ai édité le SQL pour indiquer qu'il est venu condition affectant la première instruction SELECT. J'avais déjà quitté ce pour des raisons de simplicité. Aussi, lors de l'exécution de la requête d'utilisation de l'UC est à 1-2%, mais memoery, comme indiqué précédemment, est maxed


CREATE TABLE #tempMatchResult
(
    matchId VARCHAR(50)
)

INSERT INTO #tempMatchResult SELECT MatchId FROM MatchResult WHERE SOME_CONDITION

DELETE FROM MatchEvent WHERE
MatchId IN (SELECT MatchId FROM #tempMatchResult)

DELETE FROM MatchResult WHERE MatchId In (SELECT MatchId FROM #tempMatchResult)

DROP TABLE #tempMatchResult

OriginalL'auteur Robin Weston | 2008-10-23

  1. 3

    Il y a probablement beaucoup de choses qui se passent ici, et il n'est pas tout de votre requête.

    Tout d'abord, je suis d'accord avec les autres affiches. Essayez de réécrire ce sans une table temp si possible.

    Mais en supposant que vous avez besoin d'une table temp ici, vous avez un GROS problème que vous n'avez pas PK définie sur elle. C'est très va augmenter la quantité de temps vos requêtes nécessaire pour exécuter. Créez votre table comme si au lieu:

    CREATE TABLE #tempMatchResult (
    matchId VARCHAR(50) NOT NULL PRIMARY KEY /* NOT NULL if at all possible */
);

INSERT INTO #tempMatchResult
SELECT DISTINCT MatchId FROM MatchResult;

    Aussi, assurez-vous que votre base de données TempDB est correctement dimensionné. Votre serveur SQL server peut très bien se faire élargir le fichier de base de données dynamique sur vous, l'origine de votre requête pour sucer le PROCESSEUR et le disque de temps. Aussi, assurez-vous que votre journal des transactions est correctement dimensionné, et qu'il n'est pas auto-croissant sur vous. Bonne chance.

    Il serait légèrement plus rapide pour ajouter un index à la table temp APRÈS qu'il a été rempli.
    Aussi, essayez de jointure interne de la table temporaire de de la "vraie" table via la crête pour les supprimer, au lieu d'utiliser le WHERE [..] IN ([..]) si vous avez DELETE me FROM MatchEvent me JOIN #tempMatchResult tmpmr ON tmpmr.MatchId = me.MatchId.

    OriginalL'auteur Dave Markle

  2. 0

    En regardant le code ci-dessus, pourquoi avez-vous besoin d'une table temporaire?

    
DELETE FROM MatchEvent WHERE
MatchId IN (SELECT MatchId FROM MatchResult)


DELETE FROM MatchResult
-- OR Truncate can help here, if all the records are to be deleted anyways.
    Désolé, j'ai oublié d'inclure que nous ne sommes que de la suppression d'un sous-ensemble de MatchIds, pas tous d'entre eux

    OriginalL'auteur shahkalpesh

  3. 0

    Vous voudrez probablement le processus de cette par morceaux d'une certaine façon. (Je suppose que les requêtes sont beaucoup plus compliqué que vous avez montré?) Dans ce cas, que vous aimeriez essayer un de ces:

    • Écrire votre procédure stockée pour itérer sur les résultats. (Peut toujours verrouiller lors du traitement.)
    • À plusieurs reprises sélectionnez la N de la première frappe, par exemple, LIMIT 100 et le processus de ceux.
    • Diviser le travail en analysant les régions de la table séparément, en utilisant quelque chose comme OÙ M <= x ET x < N.
    • Exécuter le "minuit" du job le plus souvent. Sérieusement, la course des trucs comme ça toutes les 5 minutes au lieu de cela, peut faire des merveilles, surtout si le travail augmente de façon non linéaire. (Si non, vous pouvez toujours obtenir tout le travail répartis sur les heures de la journée).

    Dans Postgres, j'ai eu un certain succès à l'aide conditionnelle indices. Ils ont de la magie par l'application d'un indice si certaines conditions sont remplies. Cela signifie que vous pouvez garder les nombreux "résolue" et le peu de suspens rangées dans le même tableau, mais toujours obtenir que l'indice spécial sur le suspens. Ymmv.

    Convient de rappeler que c'est là que l'utilisation de bases de données obtient intéressant. Vous devez payer une attention particulière à vos indices et utiliser EXPLAIN sur vos requêtes beaucoup.

    (Oh, et n'oubliez pas, intéressant est une bonne chose dans votre passe-temps, mais pas au travail.)

    C'est pour SQL Serveur, MySQL...
    Cela peut être utile si les autres options ne permettent pas de résoudre les problèmes de verrouillage. Si votre verrou exclusif de l'instruction DELETE est encore trop long en cours d'exécution, essayez de l'exécution d'assez gros morceaux (qui est, si elle répond aux exigences de l'entreprise, qui ne sont pas mentionnés dans votre question). J'ai eu à faire au ecommerce magasins où tous les soirs de la maintenance a été effectuée sans perturber le fonctionnement de l'avant-face à l'expérience utilisateur. Sorte de hiérarchisation des serrures par la stratégie de requête.

    OriginalL'auteur Anders Eurenius

  4. 0

    Tout d'abord, les indices sont un must ici voir Dave M de la réponse.

    Une autre approche que je vais parfois utiliser lors de la suppression de très grands ensembles de données, est la création d'une ombre tableau avec toutes les données, de recréer les index et ensuite à l'aide de sp_rename pour passer en. Vous devez être prudent avec les transactions ici, mais en fonction de la quantité de données qui doit être supprimé, cela peut être plus rapide.

    Note Si il ya une pression sur la base de données tempdb envisager l'utilisation de jointures et de ne pas copier toutes les données dans la table temporaire.

    Ainsi, par exemple 

    CREATE TABLE #tempMatchResult (
    matchId VARCHAR(50) NOT NULL PRIMARY KEY /* NOT NULL if at all possible */
);

INSERT INTO #tempMatchResult
SELECT DISTINCT MatchId FROM MatchResult;

set transaction isolation level serializable
begin transaction 

create table MatchEventT(columns... here)

insert into MatchEventT
select * from MatchEvent m
left join #tempMatchResult t on t.MatchId  = m.MatchId 
where t.MatchId is null 

-- create all the indexes for MatchEvent

drop table MatchEvent
exec sp_rename 'MatchEventT', 'MatchEvent'

-- similar code for MatchResult

commit transaction 


DROP TABLE #tempMatchResult

    OriginalL'auteur Sam Saffron

  5. 0

    Éviter le temp de table si possible

    C'est seulement à l'aide de la mémoire.

    Vous pouvez essayer ceci: 

    DELETE MatchEvent
FROM MatchEvent  e , 
     MatchResult r
WHERE e.MatchId = r.MatchId

    Si vous ne pouvez pas éviter une table temp

    Je vais m'en tenir à mon cou ici et dire: vous n'avez pas besoin d'un index sur votre table temporaire parce que vous voulez que la table temporaire pour être la plus petite table dans l'équation et vous souhaitez analyse de la table (parce que toutes les lignes sont pertinents). Un indice ne sera pas vous aider ici.

    Faire de petits morceaux de travail

    Travail sur quelques lignes à la fois.

    Ce sera probablement ralentir l'exécution, mais il devrait permettre de libérer des ressources.

    - Une ligne à la fois 

    SELECT @MatchId = min(MatchId) FROM MatchResult

WHILE @MatchId IS NOT NULL
BEGIN
    DELETE MatchEvent 
    WHERE  Match_Id = @MatchId 

    SELECT @MatchId = min(MatchId) FROM MatchResult WHERE MatchId > @MatchId 
END

    - Quelques lignes à la fois 

    CREATE TABLE #tmp ( MatchId Varchar(50) ) 

/* get list of lowest 1000 MatchIds: */ 
INSERT #tmp 
SELECT TOP (1000) MatchId 
FROM MatchResult 
ORDER BY MatchId 

SELECT @MatchId = min(MatchId) FROM MatchResult

WHILE @MatchId IS NOT NULL
BEGIN
    DELETE MatchEvent
    FROM MatchEvent e , 
         #tmp       t
    WHERE e.MatchId = t.MatchId 

    /* get highest MatchId we've procesed: */  
    SELECT @MinMatchId = MAX( MatchId ) FROM #tmp  

    /* get next 1000 MatchIds: */  
    INSERT #tmp 
    SELECT TOP (1000) MatchId 
    FROM MatchResult 
    WHERE MatchId > @MinMatchId
    ORDER BY MatchId 

END

    Ce on supprime jusqu'à 1000 lignes à la fois.

    Plus le nombre de lignes que vous supprimer à la fois, le plus de ressources que vous utiliserez, mais plus il aura tendance à fonctionner (jusqu'à épuisement de ressources!). Vous pouvez essayer de trouver une valeur optimale de 1000.

    OriginalL'auteur AJ.

  6. 0
    DELETE FROM MatchResult WHERE
MatchId In (SELECT MatchId FROM #tempMatchResult)

    peut être remplacé par 

    DELETE FROM MatchResult WHERE SOME_CONDITION
    Mais cela a à voir la SOME_CONDITION partie deux fois. Si c'est une grosse requête, il sera moins performant.

    OriginalL'auteur Philip Wade

  7. 0

    Pouvez-vous il suffit de tourner les suppressions en cascade entre le résultat du match et matchevent? Ensuite, vous avez seulement besoin de s'inquiéter sur l'identification d'un ensemble de données à supprimer et laisser SQL prendre soin de l'autre.

    L'alternative serait de faire usage de la clause de SORTIE, mais c'est certainement plus fragiles.

    Ces deux vous permettrait de supprimer à partir de deux tables, mais seulement à l'état (et exécuter) vos prédicat de filtre une fois. Cela peut encore pas être aussi performante que le dosage approche comme suggéré par d'autres affiches, mais vaut la peine d'examiner. YMMV

    OriginalL'auteur piers7