SQLite: le COMTE lent sur de grandes tables
Je vais avoir un problème de performance dans SQLite avec un SELECT COUNT(*) sur une des grandes tables.
Que je n'ai pas encore reçu utilisable réponse et j'ai fait quelques tests supplémentaires, j'ai édité ma question intégrer mes nouvelles découvertes.
J'ai 2 tables:
CREATE TABLE Table1 (
Key INTEGER NOT NULL,
... several other fields ...,
Status CHAR(1) NOT NULL,
Selection VARCHAR NULL,
CONSTRAINT PK_Table1 PRIMARY KEY (Key ASC))
CREATE Table2 (
Key INTEGER NOT NULL,
Key2 INTEGER NOT NULL,
... a few other fields ...,
CONSTRAINT PK_Table2 PRIMARY KEY (Key ASC, Key2 ASC))
Table1 a environ 8 millions de documents et Table2 a environ 51 millions de disques, et la databasefile est de plus de 5 GO.
Table1 a 2 plusieurs index:
CREATE INDEX IDX_Table1_Status ON Table1 (Status ASC, Key ASC)
CREATE INDEX IDX_Table1_Selection ON Table1 (Selection ASC, Key ASC)
"Statut" est un champ obligatoire, mais a seulement 6 valeurs distinctes, la "Sélection" n'est pas nécessaire et ne possède qu'environ 1,5 million de valeurs différentes de null et seulement environ 600 k valeurs distinctes.
J'ai fait quelques tests sur les deux tables, vous pouvez voir les horaires ci-dessous, et j'ai ajouté le "expliquer le plan de requête" pour chaque demande (QP). J'ai placé le fichier de base de données sur un port USB-memorystick afin que je puisse l'enlever après chaque test et d'obtenir des résultats fiables, sans ingérence de la mémoire cache du disque. Certaines demandes sont plus rapides sur une clé USB (je suppose à cause du manque de seektime), mais certains sont plus lents (les analyses de la table).
SELECT COUNT(*) FROM Table1
Time: 105 sec
QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows)
SELECT COUNT(Key) FROM Table1
Time: 153 sec
QP: SCAN TABLE Table1 (~1000000 rows)
SELECT * FROM Table1 WHERE Key = 5123456
Time: 5 ms
QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
Time: 16 sec
QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)
SELECT * FROM Table1 WHERE Selection = 'SomeValue' AND Key > 5123456 LIMIT 1
Time: 9 ms
QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Selection (Selection=?) (~3 rows)
Comme vous pouvez le voir les chiffres sont très lents, mais c'est normal sélectionne sont rapides (sauf pour le 2ème, qui a eu 16 secondes).
Il en va de même pour Table2:
SELECT COUNT(*) FROM Table2
Time: 528 sec
QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~1000000 rows)
SELECT COUNT(Key) FROM Table2
Time: 249 sec
QP: SCAN TABLE Table2 (~1000000 rows)
SELECT * FROM Table2 WHERE Key = 5123456 AND Key2 = 0
Time: 7 ms
QP: SEARCH TABLE Table2 USING INDEX sqlite_autoindex_Table2_1 (Key=? AND Key2=?) (~1 rows)
Pourquoi est-SQLite pas à l'aide de la créé automatiquement l'index de la clé primaire sur la table Table1 ?
Et pourquoi, lorsqu'il utilise l'auto-index sur Table2, il faut encore beaucoup de temps ?
J'ai créé les mêmes tables avec le même contenu et index dans SQL Server 2008 R2 et là, les chiffres sont quasi instantanées.
L'un des commentaires ci-dessous suggéré l'exécution d'ANALYSER sur la base de données. Je l'ai fait et il a pris 11 minutes.
Après cela, j'ai couru quelques-uns des tests de nouveau:
SELECT COUNT(*) FROM Table1
Time: 104 sec
QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~7848023 rows)
SELECT COUNT(Key) FROM Table1
Time: 151 sec
QP: SCAN TABLE Table1 (~7848023 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
Time: 5 ms
QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid>?) (~196200 rows)
SELECT COUNT(*) FROM Table2
Time: 529 sec
QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~51152542 rows)
SELECT COUNT(Key) FROM Table2
Time: 249 sec
QP: SCAN TABLE Table2 (~51152542 rows)
Comme vous pouvez le voir, les requêtes ont pris le même temps (à l'exception du plan de requête est maintenant montrant le nombre réel de lignes), seul le ralentissement de la sélection est maintenant aussi rapide.
Ensuite, j'ai créer dan extra index sur le champ de Clé de Table1, qui doit correspondre à l'auto-index. Je l'ai fait sur la base de données d'origine, sans les ANALYSER les données. Il a fallu plus de 23 minutes pour créer cet index (rappelez-vous, c'est sur une clé USB).
CREATE INDEX IDX_Table1_Key ON Table1 (Key ASC)
Ensuite, j'ai couru les tests à nouveau:
SELECT COUNT(*) FROM Table1
Time: 4 sec
QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Key(~1000000 rows)
SELECT COUNT(Key) FROM Table1
Time: 167 sec
QP: SCAN TABLE Table2 (~1000000 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
Time: 17 sec
QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)
Comme vous pouvez le voir, l'indice a aidé avec le count(*), mais pas avec le comte(à Clé).
Finalement, j'ai créé la table à l'aide d'une contrainte de colonne au lieu d'une contrainte de table:
CREATE TABLE Table1 (
Key INTEGER PRIMARY KEY ASC NOT NULL,
... several other fields ...,
Status CHAR(1) NOT NULL,
Selection VARCHAR NULL)
Ensuite, j'ai couru les tests à nouveau:
SELECT COUNT(*) FROM Table1
Time: 6 sec
QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows)
SELECT COUNT(Key) FROM Table1
Time: 28 sec
QP: SCAN TABLE Table1 (~1000000 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
Time: 10 sec
QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)
Bien que les plans de requête sont les mêmes, les temps sont beaucoup mieux. Pourquoi est-ce ?
Le problème est que ALTER TABLE ne permet pas de convertir un tableau et j'ai beaucoup de bases de données existantes qui je ne peux pas convertir à cette forme. En outre, en utilisant une colonne de contrainte au lieu de la table, la contrainte de ne pas travailler pour Table2.
Quelqu'un a une idée de ce que je fais de mal et comment résoudre ce problème ?
J'ai utilisé le Système.Les données.SQLite version 1.0.74.0 à la création de tables et d'exécuter les tests, j'ai utilisé SQLiteSpy 1.9.1.
Merci,
Marc
- Si vous avez des problèmes de performances avec SQLite, la solution est généralement de passer à un plus gros serveur de base de données (je recommande Postgres sur MS SQL).
- Je ne suis pas du tout avoir d'autres problèmes de performance, tous les autres sélectionne sont rapides (et d'utiliser les bons indices), insertions et mises à jour sont rapides, c'est seulement le comte qui me dérange.
- Ce qui est vraiment bizarre, parce que (pour DB2, au moins) la plupart des Sgbdr probablement utiliser efficacement les informations mises en cache - si vous demandez pour le compte de toutes les lignes (ou limité par quelque chose dans un index), il peut généralement lire que l'information de l'indice lui-même - l'indice sait le nombre d'entrées. Il est d'autant plus bizarre dans ce que vous dites tous les autres
SELECT
s fast - ils besoin de connaître le nombre d'enregistrements pour être en mesure d'optimiser correctement! À moins que quelque chose de bizarre qui se passe, et vous êtes de verrouillage de la table (lecture répétée de la transaction, ou quelque chose du genre?)... - Non, pas de verrouillage. En fait, j'obtiens les mêmes résultats l'exécution de requêtes dans SQLiteSpy.
- J'ai remarqué dans SQLiteSpy que la table n'avait aucun indice (j'ai pensé que c'était normal car il est censé être caché), mais d'une autre table, qui ne disposait que d'une clé primaire sur la base de 2 champs, a montré une sqlite_autoindex_Table2_1. J'ai donc essayé de recréer la table avec le même contenu, mais à l'aide de la "CLÉ PRIMAIRE" contrainte de colonne au lieu de la contrainte de table "CONTRAINTE PK_Table1 CLÉ PRIMAIRE (Clé ASC)". Le comte exécuté en 7 secondes (ce qui est encore lent, je pense), mais lors de l'utilisation d'EXPLIQUER "PLAN de REQUÊTE", il a montré que l'un des autres indices (qui a le champ de clé primaire comme deuxième champ) a été utilisé.
- Postgres utilise également un full table scan pour COMPTER les requêtes sans clause where, afin de changer de Postgres ne vais pas acheter de vous aucun avantage en termes de performances pour cette requête.
- Mais vous pouvez ensuite faire des déclencheurs de maintien de l'information. Ou passer le temps que vous économisez sur vos requêtes par avoir chaud dans la mémoire RAM de comptage. Aussi, InnoDB n'est pas beaucoup plus rapide dans ce domaine, et MyISAM n'a pas de transactions...
- SQLite prend également en charge les déclencheurs, de sorte que le mécanisme de garder une trace du comte serait le même.
Vous devez vous connecter pour publier un commentaire.
Si vous n'avez pas
DELETE
d tous les dossiers, à faire:Permettra d'éviter l'analyse complète de la table. Notez que
_ROWID_
est un identificateur de SQLite.MAX(_ROWID_)
n'a de sens que si vous avez une clé de substitution et, comme mentionné précédemment, de ne pas avoir supprimé toutes les lignes. Ces deux restrictions en ce qui veux dire que ce n'est pas un moyen fiable pour obtenir un nombre de lignes, peu importe comment il est rapide.De http://old.nabble.com/count(*)-slow-td869876.html
SQLite toujours un full table scan pour count(*). Il
ne pas garder des méta-informations sur les tables afin d'accélérer cette
processus de suivi.
De ne pas garder des méta-information est une action délibérée
décision. Si chaque tableau stocké un nombre (ou mieux, chaque
nœud de l'arbre stockées sur le compte), puis beaucoup plus de la mise à jour
aurait à se produire sur chaque INSÉRER ou SUPPRIMER. Cette
le ralentissement d'INSERTION et de SUPPRESSION, même dans les parties communes
cas où le comte(*) la vitesse est sans importance.
Si vous avez vraiment besoin d'un DÉCOMPTE rapide, alors vous pouvez créer
un déclencheur sur INSÉRER et de SUPPRIMER des mises à jour d'une course
nombre dans une autre table, une requête qui séparent
table pour trouver le dernier recensement.
Bien sûr, ce n'est pas la peine de garder une ligne COMPLÈTE compter que si vous
besoin de Compte dépend de l'endroit OÙ les clauses (c'est à dire OÙ champ1 > 0 et champ2 < 1000000000).
COUNT(1)
devrait être plus rapide queCOUNT(*)
et mêmeCOUNT("id")
.COUNT()
etCOUNT(*)
sont plus rapides,COUNT(1)
de prendre de doubles etCOUNT(ROWID)
prendre le triple du temps.CREATE TABLE [Elements] ([ElementId] integer PRIMARY KEY NOT NULL,[ParentId] integer REFERENCES [Elements] ([ElementId]), [Name] nvarchar, ....
et contient un peu plus de 2 millions de lignes.EXPLAIN QUERY PLAN
est toujours le même (à l'AIDE de l'un des index les INDEX de couverture), maisCOUNT()
s'exécute dans ~200ms,COUNT(1)
dans ~400ms etCOUNT(rowid)
faut ~600ms.MAX(rowid)
s'exécute dans 0 ms.Ne compte pas les étoiles, compter le nombre d'enregistrements! Ou dans une autre langue, jamais de problème
SELECT COUNT(*) from tablename;
utilisation
SELECT COUNT(ROWID) from tablename;
Appel EXPLIQUER le PLAN de REQUÊTE pour les deux pour voir la différence. Assurez-vous d'avoir un indice en place contenant toutes les colonnes mentionnées dans la clause where.
Cela peut ne pas aider beaucoup, mais vous pouvez exécuter le ANALYSER commande pour reconstruire les statistiques de votre base de données. Essayez d'exécuter "
ANALYZE;
" reconstruire des statistiques sur l'ensemble de la base de données, puis d'exécuter votre requête à nouveau et voir si c'est pas plus rapide.ANALYZE
résolu le problème de mon DB lorsque l'on fait unLEFT JOIN
Sur la question de la contrainte de colonne, SQLite cartes des colonnes qui sont déclarés
INTEGER PRIMARY KEY
à l'id de ligne interne (qui admet un certain nombre d'optimisations internes). Théoriquement, il pourrait faire de même pour les séparément,-a déclaré contrainte de clé primaire, mais il semble ne pas le faire dans la pratique, au moins avec la version de SQLite en cours d'utilisation. (Le système.Les données.SQLite 1.0.74.0 correspond à la base SQLite 3.7.7.1. Vous pouvez essayer de re-vérifier vos chiffres avec 1.0.79.0; vous ne devriez pas avoir besoin de modifier votre base de données à faire, il suffit de la bibliothèque.)La sortie pour les requêtes rapides à mettre en rapport avec le texte "QP: de la RECHERCHE". Tandis que ceux pour les requêtes lentes commencer avec le texte "QP: SCAN", ce qui suggère que sqlite est l'exécution d'un scan de l'ensemble de la table afin de générer le comte.
Googler "sqlite analyse de la table de comptage" trouve la suite, ce qui suggère que l'utilisation d'un full table scan pour récupérer un compte est juste la façon dont sqlite fonctionne, et est donc probablement inévitable.
Comme une solution de contournement, et étant donné que l'état a seulement huit valeurs, je me demandais si vous pourriez obtenir un nombre rapidement à l'aide d'une requête semblable à la suivante?
sélectionnez 1 where statut=1
union
sélectionnez 1 where statut=2
...
puis de compter les lignes dans le résultat. C'est clairement moche, mais il pourrait fonctionner si la persuade de sqlite pour exécuter la requête de recherche, plutôt que d'une analyse. L'idée d'un retour de "1" à chaque fois est d'éviter la surcharge de retour des données réelles.
SELECT COUNT (*) FROM table1 where Status in (1,2,3,4,5,6)
, il a exécuté en 86 secondes (un peu plus rapide), la personne:SEARCH TABLE Table1 USING COVERING INDEX IDX_Table1_Status (Status=?) (~60 rows); EXECUTE LIST SUBQUERY 1
. Mieux, mais pas assez bon.SELECT COUNT(*) FROM (SELECT 1 FROM Table1 WHERE Status = 1 UNION SELECT 1 FROM Table1 WHERE Status = 2 UNION...)
retourné 1, la même SOMME(*), je suppose en raison des caractéristiques d'un l'union.SELECT COUNT(*) FROM (SELECT 1 FROM Table1 WHERE Status = 1 UNION SELECT 2 FROM Table1 WHERE Status = 2 UNION...)
retournée 6. Donc finalement j'ai essayéSELECT COUNT(*) FROM (SELECT Key FROM Table1 WHERE Status = 1 UNION SELECT Key FROM Table1 WHERE Status = 2 UNION...)
qui a retourné le résultat correct, mais très lent (116 secondes). Merci pour la suggestion mais.SELECT COUNT (*) FROM table1 where Status in (1,2,3,4,5,6)
) qui était un peu mieux, ne serait pas travailler pour mon autre table (Table2).Voici une solution de contournement potentiel pour améliorer les performances de la requête. D'après le contexte, il semble que votre requête prend environ une minute et demie à exécuter.
En supposant que vous avez un date_created colonne (ou pouvez en ajouter un), exécutez une requête dans le fond chaque jour à minuit (dire à 00:05) et à la persistance de la valeur quelque part le long de la last_updated date à laquelle il a été calculé (je reviendrai sur ce sujet dans un peu).
Ensuite, la course contre votre date_created colonne (avec un index), vous pouvez éviter un full table scan en faisant une requête SELECT COUNT(*) from TABLE where date_updated > "[aujourd'HUI] 00:00:05".
Ajouter de la valeur du comptage à partir de cette requête à votre persisté valeur, et vous avez une assez rapide décompte précis.
Le seul hic, c'est qu'à partir de 12:05 12:07am (la durée pendant laquelle votre nombre total de requête est en cours d'exécution), vous avez une condition de course qui vous permet de vérifier la last_updated valeur de votre full table scan count(). Si c'est > 24 heures, alors votre nombre incrémentiel requête doit tirer une journée entière de compter plus de temps écoulé aujourd'hui. Si c'est < 24 heures, alors votre nombre incrémentiel requête besoin de tirer un jour partiel comte (juste le temps écoulé aujourd'hui).
J'ai eu le même problème, dans ma situation de VIDE de commande aidé. Après son exécution sur la base de données COUNT(*) augmentation de la vitesse de près de 100 fois. Toutefois, le commandement lui-même a besoin de quelques minutes dans ma base de données (20 millions de dossiers). J'ai résolu ce problème en exécutant le VIDE quand mon logiciel se ferme après la fenêtre principale de la destruction, de sorte que le retard n'est pas de créer des problèmes à l'utilisateur.