Je me demande s'il y a une requête bien performante pour sélectionner des dates distinctes (entraver les temps) à partir d'une table avec un champ DateTime dans SQL Server.
Mon problème ne reçoit pas le serveur de faire cela (j'ai vu Cette question déjà, et nous avions déjà quelque chose de similaire en place en utilisant distinct). Le problème est de savoir s'il y a des astuces pour que cela soit fait plus rapidement. Avec les données que nous utilisons, notre requête actuelle renvoie ~ 80 jours distincts pour lesquels il y a environ 40 000 rangées de données (après filtrage sur une autre colonne indexée), il existe un index sur la colonne Date et la requête réussit toujours à prendre 5+ secondes. Qui est trop lent.
Changer la structure de la base de données peut être une option, mais moins souhaitable.
10 Réponses :
J'ai utilisé ce qui suit:
CAST(FLOOR(CAST(@date as FLOAT)) as DateTime);
Le moyen le plus simple consiste à ajouter une colonne calculée pour une seule partie de la date de date et de sélectionner à ce sujet. Vous pouvez le faire dans une vue si vous ne voulez pas changer la table.
solution ci-dessous testée pour une efficacité sur un tableau code> 2M CODE> et prend mais Plain Voir cette entrée dans mon blog pour les détails de performance: p> malheureusement, C'est toujours Vous pouvez construire une liste de dates possibles en utilisant un RECURSIVE 40 ms code>. p> distinct code> sur une colonne calculée indexée a pris 9 secondes code>. P>
SQL Server code>: efficace li>
ul> distinct code> sur les dates strong>
SQL Server SQL Optimizer ne peut faire aucun oracle Skip Scan code> ni MySQL code> S. > Index pour groupe-by code>. P> Stream agrégate code> qui prend de longs. P> CTE CODE> et joignez-la avec votre table: P> WITH rows AS (
SELECT CAST(CAST(CAST(MIN(date) AS FLOAT) AS INTEGER) AS DATETIME) AS mindate, MAX(date) AS maxdate
FROM mytable
UNION ALL
SELECT mindate + 1, maxdate
FROM rows
WHERE mindate < maxdate
)
SELECT mindate
FROM rows
WHERE EXISTS
(
SELECT NULL
FROM mytable
WHERE date >= mindate
AND date < mindate + 1
)
OPTION (MAXRECURSION 0)
Construire une table de date, puis semi-jointures à l'original est une excellente solution. IMHO Les frais généraux supplémentaires d'une colonne persistée avec un index ou une vue indexée ne sont logiques que si vous deviez faire cette opération très fréquemment (devinez arbitraire: comme quelques centaines de fois par jour). Je préférerais toujours d'abord essayer de trouver une meilleure requête que d'ajouter plus de complexité / aérien sur la structure de la DB.
Si vous souhaitez éviter l'extraction de pas ou reformater la date - qui est probablement la principale cause du délai (en forçant une balayage de table complète) - vous n'avez aucune alternative, mais pour stocker la date de la date de la DateTime, Ce qui nécessitera malheureusement une modification de la structure de la base de données.
Si votre utilisation de SQL Server 2005 ou ultérieure, un champ calculé persisté est le moyen d'aller
La principale cause du délai est la numérisation et le tri pour produire le distinct. Sauf si quelque chose extramly complexe se produit dans une opération scalaire, les retards dans une base de données sont toujours liés à l'accès aux données et aux opérations scalaires.
C'est la principale cause du retard, car elle oblige une table de table complète - Désolé, aurait dû faire ce clair
Quel est votre prédicat sur cette autre colonne filtrée? Avez-vous essayé si vous obtenez une amélioration d'un index sur cette autre colonne filtrée, suivie du champ DateTime?
Je suppose largement ici, mais 5 secondes pour filtrer un ensemble de 100 000 rangées jusqu'à 40000, puis faire une sorte (ce qui est probablement ce qui se passe) ne semble pas être un temps déraisonnable. Pourquoi dites-vous que c'est trop lent? Parce qu'il ne correspond pas aux attentes?
Chaque option impliquant la manipulation de moulage ou de tronquage ou de datePart sur le champ DateTime a le même problème: la requête doit analyser l'ensemble des résultatsset (le 40K) afin de trouver les dates distinctes. La performance peut varier marginalement entre divers outils de mise en œuvre.
Ce dont vous avez vraiment besoin est d'avoir un index qui peut produire la réponse en un clin d'œil. Vous pouvez avoir une colonne calculée persistante avec et indexer qui (nécessite des modifications de la structure de table) ou une vue indexée ( Nécessite l'édition d'entreprise pour Qo pour prendre en compte l'index out-of the-box). p>
colonne calculée persistée: P>
create view v_foo_with_date_only
with schemabinding as
select
convert(char(8), [d], 112) as date_only
, count_big(*) as [dummy]
from dbo.foo
group by convert(char(8), [d], 112)
create unique clustered index idx_v_foo on v_foo_with_date_only(date_only)
Y a-t-il un moyen d'utiliser Skip Scan dans SQL Server ? Je viens d'essayer votre solution sur une table 2M et il a encore pire ( Cast Distinct (...) sur un DateTime Le champ a pris 850 ms avec un Hash match Agrégate , Date distincte a pris 1800 ms avec un ensemble d'agrégats de flux ). Oracle et MYSQL Je voudrais simplement sauter sur les champs distincts de l'index, SQL Server ne le fait pas.
Vous devez sélectionner Date_only distinct après qu'un index a été créé dessus.
@REMUS : J'ai créé un index et l'optimiseur l'utilisait.
Dans mes tests avec 2M, des enregistrements étaient beaucoup plus rapides, un scan et un agrégat. Le Skip Scan est quelque chose d'autre, si vous avez l'indice IDX1 sur (Cola, Colb) et un prédicat sur Colb 'Skip Scan' utilisera l'IDX1 malgré le fait que Cola n'a pas de prédicat. VRAI Bien qu'il n'y ait pas de scanner sur SQL Server.
L'élément Microsoft Connect sur le Skip Scan: connect.microsoft .Com / SQLServer / Feedback / Détails / 695044 / ...
Je ne sais pas pourquoi votre requête existante prendrait plus de 5 ans pour 40 000 rangées.
Je viens d'essayer la requête suivante contre une table avec 100 000 rangées et je suis retourné en moins de 0,1s. P>
SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, your_date_column)) FROM your_table
Il suffit de convertir la date: daadd (dd, 0, datrodiff (dd, 0, [quelque_column]))
J'ai utilisé ce
Pas sûr de l'efficacité, mais c'est certainement le plus beau moyen de le faire.
Cela fonctionne pour moi:
SELECT distinct(CONVERT(varchar(10), {your date column}, 111))
FROM {your table name}