Est-ce une bonne idée d'indexer chaque colonne si les users peuvent filterr par n'importe quelle colonne.

Dans mon application, les users peuvent créer des tables personnalisées avec trois types de colonnes. Texte, Numérique et Date ils peuvent avoir jusqu'à 20 colonnes. Je crée une table SQL basée sur leur schéma en utilisant nvarchar (430) pour le text, décimal (38,6) pour le numérique et le datetime. Avec une colonne Identity Id.

Un grand nombre de ces tables peuvent être créées par différents users et datatables peuvent être mises à jour fréquemment par les users qui téléchargent de nouveaux files CSV. Pour get les meilleures performances lors du téléchargement des données de l'user, nous tronquons la table pour se débarrasser des données existantes, puis faire des lots de BULK INSERT.

L'user peut effectuer une sélection en fonction d'un filter qu'il peut créer et qui peut inclure n'importe quel nombre de colonnes. Mon problème est que certaines tables avec beaucoup de lignes auront de mauvaises performances au cours de cette sélection. Pour lutter contre cela, j'ai pensé à append des index mais comme nous ne soaps pas quelles colonnes seront incluses dans la condition WHERE, nous devrons indexer chaque colonne.

Par exemple, sur un server SQL local, une table avec un peu plus d'un million de lignes et une condition WHERE sur 6 de ses colonnes prend environ 8 secondes la première fois qu'elle s'exécute, puis less d'une seconde pour les exécutions suivantes. Avec des index sur chaque colonne, il s'exécutera en less d'une seconde la première fois que la requête est exécutée. Ce problème de performance est amplifié lorsque nous testons sur une database SQL Azure où la même requête prend plus d'une minute la première fois qu'elle s'exécute et ne s'améliore pas lors des exécutions suivantes, mais avec les index, cela prend 1 seconde.

Donc, serait-ce une solution appropriée pour append un index sur chaque colonne lorsqu'un user crée une colonne ou existe-t-il une meilleure solution?

Oui, c'est une bonne idée étant donné votre model. Il y aura bien sûr plus d'overhead en conservant les index sur insert, mais s'il n'y a vraiment pas de jeu de colonnes commun prévisible dans les requêtes, vous n'avez pas beaucoup de choix.

Si par 'fréquemment mis à jour' vous voulez append des données par téléchargement plutôt que par des files existants, vous pouvez envisager une des bases de données non-sql (comme Apache Lucene ou variantes) qui permettent une interrogation efficace sur n'importe quelle combinaison de données. Pour lire d'énormes sets de données «plats», ils sont étonnamment rapides.