Discussion :

[sdisp+]

Bonjour.

Je suis sous SQL Server 2005 et je souhaiterais faire un update d'une table comme suit:

la table:
id;idDetail;nb
2;3;
2;2;
2;5;
1;1;
1;2;
1;3;
5;5;
5;4;
5;3;

Je souhaiterais faire un update de cette table et affecter le champ "nb" à 1 pour caque couple id+min(idDetail), 0 dans le cas contraire

soit ici:
id;idDetail;nb
2;3;0
2;2;1
2;5;0
1;1;1
1;2;0
1;3;0
5;5;0
5;4;0
5;3;1

Pour n'avoir que les lignes qui m’intéresse sur un select je fais:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select id, min(idDetail)
from maTable
group by id, idDetail

Mais comment faire l'update?

Merci.

[Waldar]

Comme ceci par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
With MT (id, IdDetail_min) as
(
  select id, min(IdDetail)
    from MaTable
group by id
)
update mt1
   set nb = case mt1.IdDetail when MT.IdDetail_min then 1 else 0 end
  from MaTable as mt1
       inner join MT
         on MT.id = mt1.id

[sdisp+]

Merci Waldar pour cette très rapide réponse qui fonctionne très bien 19s pour 2 Millions de ligne et 1M de ligne maj.

Il faut vraiment que je boss sur l'instruction With!

Cordialement.

[Waldar]

J'ai fait une correction du code, la jointure externe n'est pas nécessaire.
Par contre, si votre table fait deux millions de lignes, il aurait du y avoir deux millions de mises à jour, n'ayant posé aucun filtre.

[sdisp+]

C'est moi qui ai ajouté une condition pour adapter à mes besoins.

J'ai remplacer par le inner join, résultat 14s.

Merci.

[sdisp+]

Bonjour.

Pouvez-vous me dire quel est la différence entre l'utilisation du WITH et d'une table temporaire?
ex:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT id, min(IdDetail) as IdDetail_min
INTO #MT
FROM MaTable
GROUP BY id
 
UPDATE mt1
   SET nb = case mt1.IdDetail when #MT.IdDetail_min then 1 else 0 end
  FROM MaTable AS mt1
       INNER JOIN #MT
         ON #MT.id = mt1.id

Merci pour vos lumières.

[Waldar]

La CTE n'est valide que le temps de la requête et fait partie intégrante de celle-ci.
http://sqlpro.developpez.com/cours/s...cursives/#LIII

La table temporaire est un objet dans votre base de données.

[sdisp+]

Merci Waldar, et niveau performance y a-t-il une solution plus intéressante?

[elsuket]

Bonjour,

La table temporaire est un objet dans votre base de données.

Pas tout à fait : c'est une table qui est stockée pour la durée de la session dans la base de données TempDB.
A ce titre, ses statistiques de colonne sont maintenues plus fréquemment que pour une table dans une base de données utilisateur, ce qui peut résulter en une consommation de CPU anormale et des re-compilations.

D'autre part, à chaque fois que la requête la table est créée, et qu'elle est ensuite supprimée, soit automatiquement par SQL Server, soit par un DROP TABLE, un accès aux pages d'allocation est requis.
Donc si la requête est exécutée un très grand nombre de fois, cela peut créer de la contention sur les pages d'allocation, étant donné qu'elles sont bien moins nombreuses que les pages de données.

En terme de performances, la CTE est une très bonne option, sous réserve que l'indexation supporte au moins assez bien votre requête.
Si vous avez besoin d'aller encore plus vite, il vous faut placer l’agrégat dans une vue indexée, mais cela a un prix en ressources.

Mais sans les tables, leurs index et le plan de requête réel (CTRL+M avant d'exécuter la requête), c'est difficile à dire ...

@++

[mikedavem]

D'autre part, à chaque fois que la requête la table est créée, et qu'elle est ensuite supprimée, soit automatiquement par SQL Server, soit par un DROP TABLE, un accès aux pages d'allocation est requis.
Donc si la requête est exécutée un très grand nombre de fois, cela peut créer de la contention sur les pages d'allocation, étant donné qu'elles sont bien moins nombreuses que les pages de données.

Tout dépend de la version de SQL Server. Les versions récentes ne gèrent pas l'allocation et la désallocation de la même manière dans tempdb. Par exemple si une table temporaire est créée puis supprimée puis réutilisée les pages associées à cette table ne sont pas désallouées et seront réutilisés lors de la prochaine utilisation. Cela évite justement les potentiels problèmes de contention qui pourraient exister dans ce cas.

++

[elsuket]

Yup, mais c'est "seulement" pour 32 tables maximum, et pour une seule à la fois, ce qui fait que j'ai observé un problème de contention sur les pages PFS comme décrit dans ce sujet

@++

[mikedavem]

64 objets maximum en 2008 mais je n'ai jamais vu que c'était pour une seule à chaque fois ???? .. pour 2005 j'avoue ne pas savoir ... ca laisse une marge mais dans l'absolu il est certain que l'utilisation des tables temporaires est à proscrire.

++

[elsuket]

Alors, je viens de relire :

- Le blog de Paul Randal :

3) Why is the trace flag not required so much in 2005 and 2008? In SQL Server 2005, my team changed the allocation system for tempdb to reduce the possibility of contention. There is now a cache of temp tables. When a new temp table is created on a cold system (just after startup) it uses the same mechanism as for SQL 2000. When it is dropped though, instead of all the pages being deallocated completely, one IAM page and one data page are left allocated, and the temp table is put into a special cache. Subsequent temp table creations will look in the cache to see if they can just grab a pre-created temp table 'off the shelf'. If so, this avoids accessing the allocation bitmaps completely. The temp table cache isn't huge (I think it's 32 tables), but this can still lead to a *big* drop in latch contention in tempdb.

- SQL Server 2008 Internals, page 167 :

Another optimization specific to tempdb prevents you from having to allocate any new space for some objects. If a work table is dropped, one IAM page and one extent are saved (for a total of nine pages), so there is no need to deallocate and then reallocate the space if the same work table needs to be created again. This dropped work table cache is not very big and has room for only 64 objects. If a work table is truncated internally and the query plan that uses that worktable is still in the plan cache, again the first IAM page and the first extent are saved. For these truncated tables, there is no specific limitation on the number of objects that can be cached; it depends only on the available memory space. User objects in tempdb can also have some of their space cached if they are dropped. For a small table of less than 8 MB, dropping a user object in tempdb causes one IAM page and one extent to be saved. However, if the table has had any additional DDL performed, such as creating indexes or constraints, or if the table was created using dynamic SQL, no caching is done. For a large table, the entire drop is performed as a deferred operation. Deferred drop operations are in fact used in every database as a way to improve overall throughput because a thread does not need to wait for the drop to complete before proceeding with its next task

- on trouve la même chose dans SQL Server 2008 Internals que dans Inside Microsoft SQL Server 2005: The Storage Engine.

Donc désolé pour les 32 tables, c'est bien 64.
En revanche ce que je comprend, c'est que puisque un minimum de pages d'allocation sont conservées, et que le cache cherche la même structure de table, il s'agit donc de 64 tables dont le DDL est différent.

Est-ce que je me trompe en persistant ?

@++

[mikedavem]

Donc désolé pour les 32 tables, c'est bien 64.
En revanche ce que je comprend, c'est que puisque un minimum de pages d'allocation sont conservées, et que le cache cherche la même structure de table, il s'agit donc de 64 tables dont le DDL est différent.

Yep, je n'avais pas compris ce que tu voulais dire au début mais je viens de relire .. on est d'accord donc si je reprends tes termes :

.. et pour une seule (avec la même DDL) à la fois ..

++

[elsuket]

Yep, je n'avais pas compris ce que tu voulais dire au début mais je viens de relire.. on est d'accord donc si je reprends tes termes :

Ben je pense que moi aussi j'ai mal interprété ce que tu as écrit ... faut que j'arrête le Thaï
Donc c'était un quiproquo qui a permis de confirmer que le cache de tables dans TempDB est bien de 64

@++