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 SELECTs 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.

InformationsquelleAutor Marc | 2012-01-24