J'ai une grande table avec 500 colonnes et des rangées de 100 m. Basé sur un petit échantillon, je ne crois que d'environ 50 des colonnes contenant toutes les valeurs, et les 450 autres ne contiennent que des valeurs null. Je veux énumérer les colonnes qui ne contiennent aucune donnée.
sur mon matériel actuel, il faudrait environ 24 heures pour interroger chaque colonne ( Y a-t-il un moyen moins coûteux de déterminer qu'une colonne est complètement vide / null? SELECT Count (1) de l'onglet dans lequel col_n n'est pas NULL )
8 Réponses :
Essayez celui-ci -
ddl: strong> p> Query 1: strong> p> ID Name IsCitizen Age
----------- ----------- ----------- -----------
0 0 0 1
Sur une seule colonne, cela donne sur la même performance. Vous attendez-vous à une meilleure performance en agrégat? Sélectionnez Compte (1) à partir de dbo.tab où le col n'est pas NULL - 1:07 Sélectionnez Col = Compte (boîtier lorsque ISNULLUD (COULEZ (COL AS NVARCHAR (MAX)), '' Alors 1 extrémité) de DBO .tab - 1:08
@Devart: Je ne suis pas le bownvoter, mais certaines explications de la raison pour laquelle cela vaut mieux serait intéressant. Toujours mieux pour ne pas donner la réponse, mais aider les gens à apprendre. :)
Pas mon bowvote mais il devrait être est null puis 1 non? Il y aurait également de bonnes avec une explication sur ce que vous avez fait ici. J'ai utilisé votre requête sur une table pour voir ce que cela fait en réalité, mais ce serait mieux si vous pouviez expliquer cela et expliquer pourquoi cela améliorerait la performance par rapport à ce qui est déjà dans la question.
@Chris. Vous avez raison :). Dans ces questions, nous recevons la liste des colonnes et faisons une requête dynamique pour une table avec une structure arbitraire. Pour les colonnes qui ne peuvent initialement pas avoir des valeurs nulles c.is_nullable = 0 , nous ne faisons aucune validation - en conséquence, nous augmentons la vitesse d'exécution. Ensuite, nous utilisons la somme de la fonction globale afin de compter le montant des valeurs NULL. Ensuite, nous comparons la valeur résultante avec la quantité globale de chaînes. Dans la deuxième variante, nous avons possible d'utiliser max.
Pourriez-vous vérifier si l'indexation de la colonne vous aidera à atteindre des performances améliorer l'amélioration
CREATE UNIQUE NONCLUSTERED INDEX IndexName ON dbo.TableName(ColumnName) WHERE ColumnName IS NOT NULL; GO
J'ai considéré cela, mais il faut aussi longtemps pour créer un index sur une colonne car il le veut pour la questionner pour NULLS.
Mais il y a encore un avantage. vous indexez une seule fois, mais vous pouvez interroger que autant de fois que vous le souhaitez
Je devrais créer un index sur chaque colonne, non? J'ai laissé un contexte dans ma question, c'est que j'ai besoin que ce processus soit répétable sur une version non indexée de la table.
Exécutez SQL dans la meilleure réponse des questions ci-dessus et générer une nouvelle requête comme ci-dessous.
Sélectionnez ISNULL (Colonne1,1), ISnull (Colonne2,1), ISNULL (Colonne3,1) à partir du tableau
Quoi de ce sujet: ? Vous pouvez facilement créer cette requête si vous utilisez des informations_schema.columns Table. Edit: Une autre idée: Sélectionnez max (colonne_1), max (colonne_2), ..... de la table_name Si le résultat Contient une valeur, colonne est peuplée. Il devrait nécessiter une balayage d'une table.
@ user2161466 Notez que cette réponse ne concerne pas seulement la création de requêtes, il vous permet de traiter toutes les colonnes à la fois au lieu de les traverser une par une. Cela pourrait signifier une énorme performance.
@Serge: Il me semble que Luke répond à la question posée ?? Sa suggestion traite toutes les colonnes à la fois au lieu d'une à la fois ...
@ MR47 Je comptant qu'une seule colonne a pris 24 heures, mais après une deuxième lecture, je vois que la réponse à Lukled était appropriée.
@ user2161466: S'il faudrait 24h pour exécuter 500 requêtes, il faudra probablement 24h / 500 pour exécuter cette requête. Il va toujours scanner une table entière, mais cela le fera dans une itération, pas 500.
@Lukled: le Select Max (Col) ... avait la meilleure performance (500x). La somme (cas lorsque la colonne_1 n'est pas nulle puis 1 autre 0) colonne_1_count était environ (250x).
500 colonnes ?!
OK, la bonne réponse à votre question est la suivante: normaliser votre table.
Voici ce qui se passe pour le moment étant:
Vous n'avez pas d'index sur cette colonne, donc SQL Server doit Faites une analyse complète de votre table humonie.
SQL Server lit certainement complètement toutes les rangées (cela signifie toutes les colonnes, même si vous n'êtes intéressé que par un).
Et depuis que votre rangée est très probable de plus de 8 kb ... http://msdn.microsoft.com/en-us/library/ms186981%28V=SQL.105%29.aspx
Sérieusement, normaliser votre table et si nécessaire Divisez-le horizontalement (mettre des colonnes "Groupes thématiques" à l'intérieur de la table séparée, pour les lire uniquement lorsque vous en avez besoin).
EDIT: Vous pouvez réécrire votre requête comme celle-ci et si vous souhaitez obtenir toutes les colonnes à la fois (mieux):
S'il dispose de 100 m d'enregistrement, cela me semble comme s'il est déjà utilisé en production. Le refactoring n'est pas toujours facile dans ce cas.
Parfois, vous devez apporter des modifications drastiques pour que le système utilisable.
Ceci est une table fournie par un fournisseur de données sur une base périodique. Le code que j'écris est de le nettoyer.
Vous n'auriez pas besoin de «compter» tous les enregistrements de 100 m. Lorsque vous revenez simplement hors de la requête avec un top 1 dès que vous touchez une colonne avec une valeur non nulle, économiseriez beaucoup de temps tout en fournissant les mêmes informations.
Si la plupart des enregistrements ne sont pas nuls, vous pouvez peut-être mélanger une partie de l'approche suggérée (par exemple, cochez uniquement des champs nullables) avec ceci:
if exists (select * from table where field is not null)
Vous pouvez utiliser cette procédure stockée sur le truc que vous devez fournir le nom de la table que vous souhaitez interroger noter que si vous passez à la procédure, le paramètre @Exec = 1, il exécutera la requête SELECT
EXEC [dbo].[SP_SELECT_NON_NULL_COLUMNS] 'YourTableName' , 1
Les colonnes seront-elles mises à jour d'une manière ou d'une autre? Les changements sont-ils autorisés?
Les colonnes n'ont pas besoin d'être mises à jour. Ils ne peuvent pas être modifiés dans le cadre de la solution.
La principale préoccupation est la performance et ne pas écrire SQL dynamique pour générer la requête.
Ma première hypothèse serait de regarder Sys.stats, mais je suis à peu près sûr que, si SQL Server fait stocke toutes les données qui vous aideront à répondre à cette question, c'est dans une table interne uniquement. (Et je suis jolie sûr de savoir ne stocke pas tout ce qui vous aidera.)
Cela ressemble à une base de données choquante. Vous avez ma sympathie. :(
Wow, 5,8 Go gaspillé sur juste null-bitmaps!
Par conséquent, pourquoi je vérifie lesdites colonnes :)