Create Table vs Declare

Salut à tous

Le contexte :
J'ai un script qui tourne depuis plusieurs années sans problème. Dans le cas particulier d'un client, ce script est amené à traiter un très gros volume de données, et le problème est qu'il sort en timeout.

A force de tests, j'ai réussi à isoler le problème au niveau de la création d'une table temporaire et de 2 requêtes Insert successives pour la remplir avant de continuer les traitements. Sur un très gros volume de données la seconde requête se met à tourner dans le vide jusqu'au timeout quelque soit la valeur de celui-ci (je suis monté à plus de 16h lors d'un test nocturne)

Lors de tests aujourd'hui, j'ai modifié la création de la table et j'aurais à priori résolu le problème.

A l'heure actuelle, la table temporaire est crée via DECLARE @MaTableTemp AS TABLE (...). J'ai modifié la création par CREATE TABLE #MaTableTemp (....).
J'aimerais comprendre la différence entre ces 2 façons alors que l'on est toujours sur de la table temporaire.
Et surtout est-ce que la résolution du problème est bien réelle, ou est-ce que j'ai affaire à un effet de bord trompeur ?

De mémoire, il y a notamment un souci de cardinalité / calcul de statistiques.

Les variables table, tout comme les fonctions tables, ont une cardinalité de 1 pour l'optimiseur.
En revanche, les tables temporaires ont des cardinalités calculées correctement.

Du coup ça induit en erreur l'optimiseur lors de l'utilisation de ces tables, surtout sur de gros volumes.

Aussi, visiblement l'accès aux variables table n'est pas parallélisable : c'est aussi pénalisant lorsqu'on traite de gros volumes dans une requête complexe.

Voici deux liens que j'ai trouvé qui détaillent mieux :
http://www.concatskills.com/2017/12/...ariable-table/
https://stackoverflow.com/questions/...-in-sql-server

Dans les grandes lignes, il y a très peu d'avantages à utiliser les variables tables.

Une variable table n'est pas une table temporaire. Elle ne dispose pas de cardinalité et donc l'optimiseur considère qu'il n'y a jamais qu'une seule ligne dans la variable table… Si ce n'est pas le cas, le plan de requête peut donc devenir catastrophique car le choix des algorithmes (jointure, groupage, déduplication…) dépend des cardinalités !

En revanche une table temporaire dispose de statistiques. Le plan est donc ré évalué après alimentation de la table temporaire pour cadrer avec les statistiques.

D'où le comportement que vous avez expérimenté à votre détriment !

A +

Ok, merci pour les infos.

Je n'ai donc pas de scrupule pour modifier mon script et remplacer ma variable table par une table temporaire locale pour résoudre mon problème.