J'ai trois tables. Chaque tableau contient plus de 3 millions de lignes. J'exécute le code suivant:
SELECT * FROM ( SELECT col_1, col_2, col_3, [date], 1 as type FROM table_1 UNION SELECT col_1, col_2, col_3, [date], 2 as type FROM table_2 UNION SELECT col_1, col_2, col_3, [date], 3 as type FROM table_3 ) AS tb tb.[date] BETWEEN (start_date) AND (end_date) ORDER BY [date] DESC OFFSET n ROWS FETCH NEXT m ROWS ONLY
Mais quand j'obtiens un grand intervalle de date, la requête s'exécute plus lentement. Par exemple: lorsque j'obtiens l'intervalle du 01/01/2019 et du 01/04/2019, la requête s'exécute environ 13-14 secondes:
Ce résultat est très mauvais. Je veux obtenir un résultat en 1 seconde. Que puis-je faire?
3 Réponses :
Commencez par utiliser UNION ALL
plutôt que UNION
:
SELECT * FROM (SELECT col_1, col_2, col_3, [date], 1 as type FROM table_1 UNION ALL SELECT col_1, col_2, col_3, [date], 2 as type FROM table_2 UNION ALL SELECT col_1, col_2, col_3, [date], 3 as type FROM table_3 ) AS tb WHERE tb.[date] BETWEEN (start_date) AND (end_date) ORDER BY [date] DESC OFFSET n ROWS FETCH NEXT m ROWS ONLY;
SQL entraîne une surcharge pour supprimer les doublons avec UNION code>.
UNION ALL
n'entraîne pas cette surcharge.
De plus, un index à la date
dans chacun des tableaux devrait aider. SQL Server a un bon optimiseur qui pousse généralement ces conditions aux requêtes individuelles dans une sous-requête UNION
/ UNION ALL
.
Cette réponse suppose qu'il est acceptable ou souhaitable que les doublons apparaissent. Cela peut ou non être le cas, selon les exigences du projet. En outre, bien sûr, il y a des frais généraux de transmission pour plus de lignes renvoyées.
@JosephDoggie: comme il n'y a pas de chevauchement dans les sous-requêtes (en raison des différents types
s), les doublons n'apparaissent que s'ils sont déjà présents dans la table de base, puis un DISTINCT
sur les sous-requêtes les trierait toujours plus efficacement que UNION
.
Dans ce cas, il devra toujours combiner les trois flux dans l'ordre des dates triées (probablement avec une jointure de fusion en supposant que l'index est le date
), donc la suppression des doublons ne devrait pas être une étape supplémentaire extrêmement coûteuse de toute façon,
@JosephDoggie. . . Il n'y a pas de doublons - au moins entre les tables - à cause de la colonne type
.
Je suggère de créer un index de couverture sur chaque table similaire à:
WITH cte1 AS ( -- find the first date after n + m window SELECT date FROM ( SELECT date FROM table_1 UNION ALL SELECT date FROM table_2 UNION ALL SELECT date FROM table_3 ) AS x WHERE date BETWEEN '2019-01-01' AND '2019-04-01' ORDER BY date DESC OFFSET (n + m) ROWS FETCH NEXT 1 ROW ONLY ), cte2 AS ( SELECT date, column_1, column_2, column_3, 1 AS type FROM table_1 UNION ALL SELECT date, column_1, column_2, column_3, 1 AS type FROM table_2 UNION ALL SELECT date, column_1, column_2, column_3, 1 AS type FROM table_3 ) SELECT * FROM cte2 WHERE date <= '2019-04-01' AND date > (SELECT date FROM cte1) ORDER BY date DESC OFFSET n ROWS FETCH NEXT m ROWS ONLY
Cela devrait aider avec la clause WHERE. De plus, SQL Server n'aura pas à toucher les tables car toutes les informations requises sont présentes dans l'index.
Voici un autre essai à ce sujet. En supposant que OFFSET n ROWS FETCH NEXT m ROWS ONLY
correspond à un très petit pourcentage de lignes entre les dates de début et de fin, écrivez une requête comme celle-ci:
CREATE INDEX ix1 ON table_1 (date) INCLUDE (column1, column2, column3)
Ou ils pourraient envisager de faire de la date la première colonne d'un index clusterisé sur toutes ces tables car elles sont toutes des tas pour le moment. Que ce soit une bonne idée ou non, il n'est pas possible de savoir avec les informations actuelles
Je ne suis pas sûr que le planificateur de requêtes soit suffisamment intelligent pour limiter les résultats de l'union par la clause where en dehors de l'union, alors essayez de déplacer la condition de date dans chacune des requêtes de l'union, afin 'pas d'unir l'intégralité des trois tables ensemble avant d'opérer sur la condition:
SELECT * FROM ( SELECT col_1, col_2, col_3, [date], 1 as type FROM table_1 where table_1.[date] between (start_date) and (end_date) UNION SELECT col_1, col_2, col_3, [date], 2 as type FROM table_2 where table_2.[date] between (start_date) and (end_date) UNION SELECT col_1, col_2, col_3, [date], 3 as type FROM table_3 where table_3.[date] between (start_date) and (end_date) ) AS tb ORDER BY [date] DESC OFFSET n ROWS FETCH NEXT m ROWS ONLY
SQL 2012 semble déplacer la clause where automatiquement.
Vous pouvez essayer d'ajouter la condition where dans les requêtes individuelles.
Pour commencer, comme vos requêtes ne se chevauchent jamais (mais SQL Server ne peut pas le savoir), vous pouvez utiliser
UNION ALL
plutôt queUNION
pour enregistrer une étape de tri. De plus, l'optimiseur ne peut pas toujours replier efficacement les conditions externes en requêtes internes, vous pouvez donc répéter la clauseWHERE date BETWEEN
dans chacune des requêtes.Cette requête nécessite également une indexation vitale dans toutes les tables de la colonne
date
, sinon des analyses de table énormes et inefficaces sont inévitables. Enfin,OFFSET .. FETCH NEXT
est une construction intrinsèquement inefficace qui ralentit au fur et à mesure que leOFFSET
augmente, alors assurez-vous de ne pas essayer de faire la base de données fait des choses qu'elle ne peut tout simplement pas faire rapidement.J'écris individuellement où mais je ne me donne que 1 à 2 secondes plus vite. et j'utilise aussi la clause union all. mais ça ne m'a pas aidé aussi
Oui, je crée un index sur la colonne de date de chaque table. après le faire, le temps d'exécution passe à 13-14 secondes.
Si l'index ne couvre pas et que la plage de dates est longue, SQL Server peut ne pas l'utiliser (ou s'il l'utilise, il devra peut-être effectuer de nombreuses recherches. Dans ce dernier cas, vous constaterez peut-être qu'il finit par rechercher des lignes. rejetée par
OFFSET .. FETCH
. Quelles sont les définitions de table (y compris les index) et à quoi ressemble le plan d'exécution?le plan d'exécution tel que:
Combien de lignes la requête ci-dessus renvoie-t-elle? est-ce lent parce que le serveur doit envoyer trop de lignes?
j'ajoute une image du plan d'exécution.
Je veux seulement obtenir 100 lignes
Pour partager efficacement un plan d'exécution, utilisez Coller le plan , bien plus agréable qu'une image.
Donc, votre index n'est pas utilisé de toute façon. Probablement parce qu'il ne couvre pas toutes les colonnes utilisées dans la liste de sélection.