Ne garder que la date d'un champs datetime

Version imprimable

05/05/2008, 20h11
critok

Ne garder que la date d'un champs datetime

Bonjour,

Voila, j'ai developpé une requête qui dans son select contient une sous requête qui retourne un count(*).
Dans cette sous requête, je dois comparer la date T1 d'un champ datetime de la sous requête, et la date T2 d'un champ datetime de la requête mère.

Les données sont composées de date et heure pour ces champs.
Cependant la comparaison doit se faire exclusivement sur la date:
Après avoir cherché sur le net j'ai trouvé différentes manières pour garder uniquement la date:

Apparament la méthode la moins couteuse est la mèthode:
CAST(CAST(CAST(X as float) as int) as datetime)

J'ai donc choisi cette solution et j'ai fait mes comparaisons de cette manière:
CAST(CAST(CAST(T1 as float) as int) as datetime) <= CAST(CAST(CAST(T2 as float) as int) as datetime)

Cependant les temps de réponses sont très mauvais lorsqu'il y a de nombreux enregistrements (20s à 45s).

Connaissez-vous une autre méthode qui me permettrais d'avoir de meilleurs temps de réponses?
Je travaille sous sql server

cordialement.

merci pour votre aide
05/05/2008, 20h19
ylarvor

avez vous indexer T1 et T2 ?
05/05/2008, 22h16
elsuket

Bonsoir

Même question + votre code + utilisez vous des CTE ?

http://msdn.microsoft.com/fr-fr/library/ms175972.aspx
05/05/2008, 23h23
SQLpro

Citation:

avez vous indexer T1 et T2 ?

Cela ne servira à rien car des fonctions sont appelées sur ces colonnes.

Les deux seuls moyens de faire en sorte que ces requêtes soient "sargable" et donc aient un temps de réponse rapide est :
1) de rajouter une colonnes calculée indexée ayant la date seule sans heure. pour cela vous pouvez prendre une colonne de type entier et faire le calculs suivant :
ALTER TABLE matable
ADD MA_DATE AS CAST(CEILING(CAST(T1 AS FLOAT)) AS INTEGER)
2) d'indexer vos colonnes T1 et T2 et d'utiliser une table des dates en théta jointure (inéqui jointure avec max ou min).

A +

Bonjour,

Pour ylarvor et SQLpro:
avez vous indexer T1 et T2
rajouter une colonnes calculée indexée

Je ne peux malheureusement pas toucher à la base de données. Cette base est une base de prod. Ce qui veut dire que je ne peux pas indexer, ni créer de colonne, ni utiliser le showplan :evilred:
Je peux uniquement faire des select sur cette base.
Donc dans mon cas ma seule solution est d'optimiser la requête si cela est possible

Voici ma requête:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
select 
distinct CAST(CAST(CAST(T24.action_timestamp as float) as int) as datetime) AS Date,
(select
count(T19.dbid)
from tmtestcase T19,history T249,tmtestplan T29,tmtestplan T349,tmtestplan T359
where T19.dbid = T249.entity_dbid
and 16777253 = T249.entitydef_id
and T19.parentplan = T29.dbid
and T29.parentplan = T349.dbid
and T349.parentplan = T359.dbid
and (T19.dbid <> 0 and ((T29.dbid = @Param_TestPlanN2 or T349.dbid = @Param_TestPlanN2 or T359.dbid = @Param_TestPlanN2)))
and CAST(CAST(T249.action_timestamp as float) as int) <= CAST(CAST(T24.action_timestamp as float) as int)
and T249.new_state ='Planned'
and T249.dbid =
(select max(T2000.dbid)
from tmtestcase T1000,history T2000
where T1000.dbid = T2000.entity_dbid  
and 16777253 = T2000.entitydef_id
and (T1000.dbid <> 0)
and T1000.dbid = T19.dbid
and CAST(CAST(T2000.action_timestamp as float) as int) <= CAST(CAST(T24.action_timestamp as float) as int)
)
) AS Planned,
(select
count(distinct T19.dbid)
from tmtestcase T19,history T249,tmtestplan T29,tmtestplan T349,tmtestplan T359
where T19.dbid = T249.entity_dbid
and 16777253 = T249.entitydef_id
and T19.parentplan = T29.dbid
and T29.parentplan = T349.dbid
and T349.parentplan = T359.dbid
and (T19.dbid <> 0 and ((T29.dbid = @Param_TestPlanN2 or T349.dbid = @Param_TestPlanN2 or T359.dbid = @Param_TestPlanN2)))
and CAST(CAST(T249.action_timestamp as float) as int) <= CAST(CAST(T24.action_timestamp as float) as int)
and CAST(CAST((select top 1 T2000.action_timestamp
from tmtestcase T1000,history T2000
where T1000.dbid = T2000.entity_dbid  
and 16777253 = T2000.entitydef_id
and (T1000.dbid <> 0)
and T1000.dbid = T19.dbid
order by T2000.dbid asc
)as float) as int) <= CAST(CAST(T24.action_timestamp as float) as int)
) AS Total
from tmtestcase T1,history T24,tmtestplan T2,tmtestplan T34,tmtestplan T35 
where T1.dbid = T24.entity_dbid  
and 16777253 = T24.entitydef_id  
and T1.parentplan = T2.dbid 
and T2.parentplan = T34.dbid 
and T34.parentplan = T35.dbid 
and (T1.dbid <> 0 and ((T2.dbid = @Param_TestPlanN2 or T34.dbid = @Param_TestPlanN2 or T35.dbid = @Param_TestPlanN2)))
and T24.action_timestamp > dateadd(D,-CAST (@Param_Historique AS Integer),getdate())

@Param_Historique et @Param_TestPlanN2 sont deux paramêtres.

En gros ma requête mère retourne une liste de dates pour lesquelles il y a eu un changement dans ma table historique, et les deux sous requêtes (Planned et Total) calculent un nombre en fonction de la date de la requête mère.

J'ai reussi un améliorer un peu le temps de réponse (35s pour un historique sur 90j avec peu de données derrière).

J'espère que vous allez pouvoir m'aider.

Merci beaucoup

06/05/2008, 19h18
ylarvor

j'ai écrit un article sur mon blog sur l'optimisation de code sql ( http://blog.developpez.com/index.php...optimiser_comm ), ne pas utiliser de fonction table à gogo, ne pas factoriser... cela fait gagner beaucoup en performance mais il semble que tu n'as pas commis cette erreur donc aucun gain de performance de ce coté là.

Lorsque l'on travaille que sur le SQL, on est trés limité en terme d'optimisation. il y a des erreurs à ne pas commettre que je cite dans mon article... il y a quelques régles d'optimisation que sql pro cite dans son article 9 - transformation usuelle ( http://sqlpro.developpez.com/cours/optimiser/ ) tu peux regarder si cela peux t'aider...

Tout cela pour dire que je ne vois pas trop ce que je peux faire pour ta requete... Bonne chance.
06/05/2008, 19h43
SQLpro

Citation:

Je ne peux malheureusement pas toucher à la base de données. Cette base est une base de prod. Ce qui veut dire que je ne peux pas indexer, ni créer de colonne, ni utiliser le showplan

Ceci est un non sens absolu !
C'est d'ailleurs ce que j'ai déjà dénoncé dans un de mes articles sur l'optimisation... En effet si cette base de données contient des données qui vous appartiennent alors il n'y a aucune raison ni logique ni juridique qui vous empêche d'ajouter votre propres données supplémentaires.
D'ailleurs le Dr. Codd, inventeur des bases de données relationnelles l'avait prévu à l'origine et mainte fois spécifiée notamment dans ses règles sur les SGBDR :
"
RÈGLE 9 - Indépendance logique des données : les applications et les programmes terminaux sont logiquement inaffectés, quand des changements de tous ordres, préservant les informations et qui ne leur portent théoriquement aucune atteinte, sont apportés aux tables de base (restructuration).
"
AUTREMENT DIT :
Une modification de la structure d'une table ou d'une vue sur des éléments qui n'ont pas été utilisés dans le code ne doit pas perturber le fonctionnement de l'existant.
A fortiori, le rajout d'une colonne, d'une table, d'une vue... ne perturbe en aucun cas l'application.

Dès lors et si la base de données a été créée dans les règles de l'art, le fait d'ajouter des tables, des vues, des index, voir même des colonnes à des tables ou des vues, n'a aucune conséquence sur le fonctionnement de l'application. Et si par un hasard extraordinaire, une telle modification entraînait le dysfonctionnement de l'application, alors si l'application a été faite par un éditeur ou une SSII vous seriez en droit de demander au titre de la garantie des vices cachés, que le prestataire rectifie son programme.

Si vous ne pouvez pas faire cela pour une raison mystérieuse alors ne rêvez pas : toute optimisation sérieuse est IMPOSSIBLE !

A +

PS : je voit malheureusement de plus en plus dans mes audits de personnes persuadées de cette ineptie !
08/05/2008, 02h33
critok

Pour ylarvor: Merci je vais jeter un oeil àvotre article.

Pour SQLpro: Je suis tout à fait d'accord avec vous.
En fait il s'agit de la base données permettant de faire tourner IBM Rational ClearQuest (Gestion des defects, scope change, et plus récement la gestion des tests).
Je pense également, que l'ajout d'une colonne ou création d'un index, n'effecteraient en rien le bon fonctionnement de l'application.
Seulement cette base est utilisée tous les jours (par + de 200 developpeurs).
Tous les projets sont gérés avec cette application.
Vous comprendrez donc que les admins soient un peu réticents à toute mofication...

On ne peut malheureusement pas toujours travailler dans des conditions optimales. :?
23/03/2010, 20h09
eomer212

autre méthode

je suis tombé (AIE :aie:) sur ce post, et j'ai envie de vous donner ma vue sur le sujet.
plutot que de perdre enormement de temps pour que la base de données convertisse les champs datetime en date seule et verifier la concordance avec ce qui me semble etre une borne temporelle, datedebut,datefin,
il me semble plus logique et efficace de faire l'inverse..
soit transformer les bornes temporelles de filtrage en format datetime et comparer directement avec le format natif stocké dans la base.
exemple: on veut tous les evenements d'une journée précise.
datedebut=date à 00:00:00
datefin=date à 23:23:59
et là, la comparaison est hyper efficace.
pour des bornes plus importantes, je vous laisse faire, c'est trop simple.
bien sur, reste toujours la solution de rajouter un champ, une table,un index, avec des date preremplies ou complétées à la volée, masi c'est vraiment pas economique en terme d'utilisation, perte de temps, de resources, montée en charge inutile de la base, et en plus il se posera toujours le probleme de la mise à jour de ces dates, une solution que je ne privilégierais pas..
car celle ci est simple, non intrusive, en total respect avec l'architecture existante.. et rudement efficace.

si le probléme te parait trop haut, change de point de vue..
24/03/2010, 23h46
ylarvor

idée intéressante, je ne suis pas sûr qu'elle s'applique à ce cas précis mais il fallait nous en faire part.

super conclusion.

bonne soiree.;)
25/03/2010, 20h03
soazig

Bonjour,
Je suis d'accord avec eomer212, sauf sur sa date de fin dans son exemple, parce qu'il reste encore tout ce qui se passe entre minuit moins une et minuit.
En revanche on peut prendre
exemple: on veut tous les evenements d'une journée précise.
datedebut=date à 00:00:00
datefin=date+ 1 jour
et critère ma_col_date>=datedebut and ma_col_date<datefin

Mais sur le fond je suis 100% d'accord, j'ai vu des ignominies.
du genre

Code:

WHERE CAST(CAST(CAST(T24.action_timestamp AS float) AS int) AS datetime)=CAST(CAST(CAST(datedebut AS float) AS int) AS datetime)

et là au secours les perf! et la lisibilité de la requete.
A+
Soazig
25/03/2010, 23h16
eomer212

ARF

:oops:
j'en rougirais presque..:aie:
merci de ce franc correctif.
en effet, une seconde peut tout changer dans une vie.

je dirais meme que c'est une solution que j'applique depuis pas mal de temps.
et que j'ai tapé trop vite.
pour ceux qui chercheraient la solution toute faite:
comparaison egalite sur un jour, disons le 15 janvier 2010. on construit deux données de format datetime avec:
date1=le jour à 00H00m00s soit 2010-01-15 00:00:00
date2=le jour +1 à 00h00m00s soit 2010-01-16 00:00:00

comparaison avec une datetime stockée date
correspondance si ((date1<=date) and (date<date2))
développons..
hors période si ((date1>date) or (date2<=date))
vala, je pense que ca devient un peu plus complet.
pour savoir si la date comparée est dans une pêriode de jours, ca donne:
jour de debut de periode inclus exemple: 15 janvier 2010
jour de fin de période inclus exemple: 18 janvier 2010
date1=le jour de debut à 00H00m00s soit 2010-01-15 00:00:00
date2=le jour de fin+1 à 00h00m00s soit 2010-01-19 00:00:00
les mêmes comparaisons que pour un jour s'appliquent.

vala, bonne soirée à tous:ccool: