Utilisation de SQL Server 2016. J'ai le tableau suivant qui contient un grand nombre d'enregistrements (30 mil +).
SELECT
[DATE_TIME], [TEXT], [MSG], [MSGID], [SEVERITY]
FROM
[TABLE1]
WHERE
FORMAT([DATE_TIME], 'yyyy-MM-dd') IN ('2019-06-25', '2019-06-24',etc.....)
AND [MSGID] IN (8016, 11, 3072, 23, 3062, etc....)
AND [SEVERITY] <> 'Medium'
ORDER BY
[DATE_TIME] DESC
La requête suivante est très lente:
CREATE TABLE [dbo].[TABLE1]
(
[DATE_TIME] [DATETIME] NULL,
[TEXT] [VARCHAR](500) NULL,
[MSG] [VARCHAR](500) NULL,
[MSGID] [INT] NULL,
[SEVERITY] VARCHAR(50) NULL
)
CREATE NONCLUSTERED INDEX [TABLE1_IX1]
ON [dbo].[TABLE1] ([DATE_TIME] ASC, [MSGID] ASC, [SEVERITY] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Veuillez aider à optimiser.
3 Réponses :
Vous avez mentionné que la colonne DATE_TIME contient une partie de l'heure. Dans ce cas, je suggérerais de créer une colonne calculée persistante qui contient uniquement la partie de date:
WHERE [X_DATE] IN ('2019-06-25', '2019-06-24')
AND [MSGID] IN (8016, 11, 3072, 23, 3062)
AND [SEVERITY] <> 'Medium'
Et de l'indexer et de l'utiliser dans votre clause where:
X_DATE AS CAST(DATE_TIME AS DATE) PERSISTED
Ou, peut-être mieux encore, si l'OP n'a pas vraiment besoin du composant de temps, stockez simplement les dates pour commencer.
Le format a de très bonnes fonctionnalités, mais les performances peuvent en pâtir.
Essayez
WHERE convert(date,[DATE_TIME]) in (...)
Utilisez des comparaisons directes de dates. Parce que vous semblez avoir une plage, je recommanderais:
WHERE [DATE_TIME] >= ? AND
[DATE_TIME] < '2019-06-26' AND
[MSGID] IN (8016, 11, 3072, 23, 3062, etc....) AND
[SEVERITY] <> 'Medium'
Cela peut faire une utilisation optimale d'un index et de partitions, si disponibles. Vous pouvez également convertir en une date . C'est la seule utilisation d'une fonction qui utilisera également un index. Je ne sais pas si convert va élaguer les partitions.
Merci Gordon, prendra note de cette méthode! Je suis cependant satisfait de la solution ci-dessus utilisant la fonction CONVERT qui a résolu mon problème
Considérant que [DATE_TIME] est bien une date / heure, alors convertissez en date ... WHERE convertissez (date, [DATE_TIME]) en (...)
Oui, cela fonctionne beaucoup plus rapidement avec cette conversion, merci John pour votre réponse rapide. Veuillez le publier afin que je puisse marquer comme "répondu"
Comme vous l'avez découvert. format () a de très bonnes fonctionnalités, mais les performances en souffriront. J'essaye d'utiliser format () avec parcimonie ou sur les résultats finaux