Discussion :

[clavier12AZQSWX]

bonjour,

j'ai une table toute simple :
id_mission
id_personne
date : date
debut : time
fin : time

j'aimerai savoir si il y a un moyen en sql (car là je fais en itératif sur le serveur avec une boucle lourde) de trouver les id_mission qui sont en date croisée (missions superposées).

par exemple, toto qui a une mission le 1/8/2012 de 8h à 11h et une autre le même jour de 8h à 9h (ce qui est impossible physiquement).

mon contenue de table serait :
mission personne date debut fin
1 1 20130801 08 09
2 1 20130801 8 11
3 1 20130802 8 11

il faudrait que la requête me ressorte l'id mission 1 (ou 2)

Je suis certains que c'est une question que quelqu'un s'est déjà posé, ça me semble être un cas d'école pourtant je ne trouve pas de requête tordue qui puisse faire ça sans passer par une itération.

Je crois (après avoir pensé et essayé moultes solutions en vain) que je dois mixer l'operateur ANY avec une condition à base de OVERLAPS mais pas moyen de sortir un truc qui fonctionne...

quelqu'un a-t-il une idée ?


ps : désolé de sortir un tel casse-tête chinois un jour de vacance, mais je travaille !

[punkoff]

bonjour,

quelle requête avez vous essayez ?

Un simple test d'existante (EXISTS) devrait venir à bout de ceci.

[SQLpro]

Il serait beaucoup plus intéressant de mettre une contrainte CHECK qui empêcherait une telle saisie plutôt que de faire des tests à postériori.

A me lire :
http://blog.developpez.com/sqlpro/p9...recouvrement_p
http://blog.developpez.com/sqlpro/p9...es_externe_ave

A +

[estofilo]

L'idée qui doit manquer est de joindre la table à elle-même.
Les conditions de jointure seraient:
Même personne
ET numéros de mission différents
ET OVERLAPS positif entre les intervalles de temps de chaque côté de la jointure.

[tse_jc]

Bonjour,

Je suis de l'avis de SQLpro à passer par une contrainte check.

Pourquoi? Tout simplement car il ne faut pas perdre de vue le rôle essentiel du SGBDR dans votre application : S'assurer de préserver non seulement l'acidité de vos données mais également leur qualité. Or effectuer un contrôle a postériori, signifie permettre l'enregistrement de données non conformes à vos contraintes métier.
S'assurer de cela devrait être votre première priorité.

Il n'est déjà pas forcément évident de gérer et de requêter des données valides à 100% au quotidien dans un contexte optimisé, pour en plus devoir s'imposer l'écriture de requêtes dédiées à l'extraction de données conformes préalablement à tous vos traitements habituels.

Ensuite si vous voulez vous amuser à faire du bricolage ou à créer de la volumétrie "poubelle" dans votre BD pour l'exercice, libre à vous, mais bon vous savez ce que j'en pense maintenant.

Cordialement,

Jc.

[clavier12AZQSWX]

je comprends votre point de vue sur les contraintes seulement le cas que je cherche n'arrive que peut-être 5% des cas. donc pourquoi imputer à tout le monde un traitement qui sera long et aura un impact dans la réactivité de l'outil au moment où ça arrive....

[tse_jc]

Bonjour,

seulement le cas que je cherche n'arrive que peut-être 5% des cas. donc pourquoi imputer à tout le monde un traitement qui sera long et aura un impact dans la réactivité de l'outil au moment où ça arrive....

Justement, sauf erreur de ma part, c'est dans ces 5% de cas - en ne passant pas par une contrainte check - que vous imposerez ceci à votre équipe et à tous les utilisateurs de l'outil.

A vous de décider.

Cordialement.

Jc.

[tse_jc]

Re,

Excusez-moi, ce que j'ai dit est inexact. En fait la réalité est que vous avez que deux cas de figure : De telles données sont présentes dans votre base ou non.

A partir du moment où il existe au moins une ligne représentant ce cas de figure dans votre base de données, le(s) traitement(s) additionnel(s) pour les traiter s'imposeront et seront de fait permanent(s).

L'impact sur l'outil suivra la même logique.

La contrainte check est vraiment ce qu'il y a de mieux pour vous, je persiste.

Cordialement,

Jc.

[SQLpro]

je comprends votre point de vue sur les contraintes seulement le cas que je cherche n'arrive que peut-être 5% des cas. donc pourquoi imputer à tout le monde un traitement qui sera long et aura un impact dans la réactivité de l'outil au moment où ça arrive....

Je suppose que vous avez fait un benchmark pour affirmer cela ?

Parce que les métriques que j'ai donné pour de telles requêtes pour le cas de SQL Server montre que c'est moins (et souvent beaucoup moins) que 10% de temps en plus....
Or comme la plupart des mises à jours sont faites de manière unitaire et au fil de l'eau, l'utilisateur va donc passer 1,1 milliseconde devant son écran au lieu de 1 milliseconde... Ce qui bien entendu est inacceptable pour Lucky Luke qui tire plus vite que son ombre !!!!!

A +

[clavier12AZQSWX]

nous n'avaons pas un réserveur rapide (mais en très bon état!).
l'enregistrement du planning coûte déjà bcp en temps.
Ce n'est pas une appli web mais client-serveur avec un client lourd et il y a 30 utilisateurs. Et comme les planning sont consulés et modifiés de maintes fois, je ne peux pas engager une seconde plus pour une personne (car ça influe aussi sur les autres).

[tse_jc]

Bonjour,

En toute sincérité, je me demande si vous avez pris la mesure de l'ensemble des réponses qui vous ont été apportées ici vis-à-vis de votre applicatif.

Ce n'est pas une appli web mais client-serveur avec un client lourd et il y a 30 utilisateurs.

Ensuite la différence entre une appli web et une appli client-serveur, à part une limitation relative des contentions dans la deuxième, il n'y en a pas. Par rapport à cela et dans ce contexte, le fait que l'enregistrement du planning semble vous coûter en temps, me rends perplexe et m'incite à vous encourager à revoir votre copie quant à vos certitudes.

Ensuite qu'entendez-vous par client lourd? IHM complexe et riche? ou parlez-vous d'un client épais dans le sens architectural du terme?

Cordialement,

Jc.

[clavier12AZQSWX]

je veux dire par là que lorsqu'un traitement est fait, il paralyse l'écran et l'utilisateur ne peut rien faire en attendant la fin. La communication est en xml-rp, le client lourd est en python (généré en exe biensur), i ln'y a rien de multithread dans l'appli côté client, ni serveur, c'est une vieille archi (logicielle et matérielle) mais elle fonctionne, elle est juste lente.
J'ai l'impresssion que vous n'imaginez pas ce que c'est que de demander à tout le monde d'attendre 1 à 2s de plus dans des traitements récurrents pour détecter les erreurs de qq personnes (doublon de planification).
Quand les gens sont au téléphone avec des clients ou des salariés, le temps compte.

ps : non, pas de moyens pour un upgrade matériel. C'est pour cela que je cherche un autre voix logicielle en optimisant le code.

[SQLpro]

J'ai l'impresssion que vous n'imaginez pas ce que c'est que de demander à tout le monde d'attendre 1 à 2s de plus dans des traitements récurrents pour détecter les erreurs de qq personnes (doublon de planification).
Quand les gens sont au téléphone avec des clients ou des salariés, le temps compte.

ps : non, pas de moyens pour un upgrade matériel. C'est pour cela que je cherche un autre voix logicielle en optimisant le code.

Si si, c'est bien pour cela que je vous préconise de mettre une contrainte pour vérifier cela. Un déclencheur limite la porté aux nouvelles données ce qui fait qu'il sera quai instantané car dilué au fil de l'eau par rapport à votre traitement qui portera sur toute la masse et qui plus est fait du coté client, c'est à dire nécessitant des round trip réseau très pénalisant sur les temps de réponse !!!!!

A +

[ericd69]

salut,

ecoute se qu'ils se tuent à te dire...

en fait ton raisonnement par dans bon principe d'ordre général mais pas du tout dans les sgbdr:

• si tu bloques l'insertion de données incohérentes alors pas de traitement à postériori, juste lors des inserts qui sont moins fréquents que les selects
• si tu insères n'importe quoi tu seras obligé de te taper A TOUS les selects le teste de validité de tes données puisque tu ne sauras jamais les quels font partie des 5% et pire quand 2 plages se recouvrent laquelle est mauvaise et si elle l'est elle est considérée comme bonne puisque insérée déjà et donc donnée au client... bref pourquoi tester alors?

un simple test avec un trigger avant insert qui génère une erreur si c'est pas bon est très rapide et efficace..

ton vrai pb c'est la mauvaise conception de ton appli au départ:

• déjà python n'est pas vraiment pensé pour ce genre d'application
• pour une forte volumétrie une seule règle pour fluidifier: diviser pour mieux régner

concrètement si on compte une moyenne de 365*3600*8 inserts (1 par secondes) par an, on obtient 8 millions d'inserts/an. il serait bon de partitionner ta table par année, semestres, trimestre ou mois.
les partitions sont des tables dérivées de la table principale. on insère rien dans la table principale et un trigger se charge de mettre les données dans la bonne table dérivée. Il faut aussi faire un trigger avant suppression pour gérer les deletes dans la bonne table. on indexe les colonnes des tables dérivée ce qui rend la recherche/insertion doublement accélérée:

• le sgbdr cherche la(es) partition(s) concernée(s)
• on n'impacte que les sous-jeux d'index et de données nécessaires...
• la copie ou suppression revient à la copie ou sup

et ça tombe bien de toute façon tu dois faire un trigger avant insertion...

bref oui tu peux avoir des améliorations logicielles mais pas ce que tu proposes et après le matériel et les réglages du sgbdr jouent aussi

[estofilo]

Il faut quand même rappeler aux fanatiques du trigger pour tester une contrainte inter-lignes que cette solution ne fonctionne pas en cas d'insertions concurrentes.

En effet un trigger ne voit que les données de sa transaction et pas celles de la transaction d'à côté non encore commitée.

A cause de cela, il est tout à fait possible que 2 insertions concurrentes supposément incompatibles aboutissent avec les 2 exécutions de trigger qui n'y voient que du feu.

Et le problème est le même avec une contrainte CHECK mise en oeuvre avec une fonction qui requêterait le reste de la table. Elle n'a pas plus de visibilité que le trigger sur les écritures en cours dans d'autres transactions.

[ericd69]

ah bon?

c'est pour ça que la contrainte check ou les triggers font partie intégrante de la transaction et qu'il existe divers niveau d'isolation dans les transactions

et souvent tu as même des mécanisme de verrous en écriture et/ou lecture qui sont proposés en plus pour les moteurs de table qui ne suppporte pas les transactions...

c'est pas qu'un problème de trigger ou check mais quand tu fais du concurrentiel si tu t’intéresses pas au paramétrage du contexte d'exécution...

c'est vrai qu'on en a pas parlé explicitement... j'avoue

[clavier12AZQSWX]

je ne peux pas le faire en temps réel car l'interface ne le permet pas.
l'interface permet de dessinner (étaler, planter) le rdv , écrire tous les détails et une fois qu'on enregistre c'est enregistré.
au moment où l'on relache la souris (l'heure de fin établie), le sql n'est même pas encore exécuté. il n'y a pas d'ajax, pas d'échange à ce moment.
Et même si un trigger faisait peut-être le job, l'interface cliente ne permet pas de gérer l'opération en retour.

en plus on fait plus de modifs que d'insertions, donc ça veut dire qu'il faudrait aussi un trigger sur les update..

je ne suis pas en face de création où j'ai carte blanche sur l'ensemble de l'applicatif.

[SQLpro]

Il faut quand même rappeler aux fanatiques du trigger pour tester une contrainte inter-lignes que cette solution ne fonctionne pas en cas d'insertions concurrentes.

En effet un trigger ne voit que les données de sa transaction et pas celles de la transaction d'à côté non encore commitée.

A cause de cela, il est tout à fait possible que 2 insertions concurrentes supposément incompatibles aboutissent avec les 2 exécutions de trigger qui n'y voient que du feu.

Et le problème est le même avec une contrainte CHECK mise en oeuvre avec une fonction qui requêterait le reste de la table. Elle n'a pas plus de visibilité que le trigger sur les écritures en cours dans d'autres transactions.

Tu oublie juste de jouer sur les niveaux d'isolation. Notamment SERIALIZABLE qui empêche toute insertion concurrente !

A +

[estofilo]

Le mode SERIALIZABLE ne résoud pas le problème et de fait on pourrait dire qu'avec REPEATABLE READ il l'aggrave en isolant encore plus une transaction d'une autre alors que la problématique est justement qu'une transaction ne voit pas ce que font les autres avant commit (=dirty read, impossible avec PG).


Dans le cas précis de cette discussion sur une exclusion inter-lignes je pense qu'une "contrainte d'exclusion" non pas bidouillée à la main mais avec la directive EXCLUDE et l'index associé plus l'opérateur OVERLAPS pourrait faire l'affaire mais ça resterait à tester.

[ericd69]

le but de SERIALIZABLE est justement de sérialiser le pool d'actions donc tu n'as plus de transactions parallèles et tu t'assures que chacune voit bien la situation laissée par la précédente...

c'est le plus haut niveau d'isolation et le plus contraignant en terme de temps d'exécution mais il permet justement de gérer la concurrence en écriture quand on doit garantir que les actions se "voient" alors qu'elles arrivent en parallèle...
sinon tu ne pourrais jamais avoir de gestion possible ce qui serait un comble pour une application multithread comme un sgbdr...

donc je ne vois pas où es ton problème avec SERIALIZABLE

quant au problème de remy, si ton application en entrée tu ne veux/peux la modifier et que chacun peut créer ou modifier en mettant n'importe quoi de toute façon... ton traitement à postériori ne sert à rien... autan laisser les opérateurs se débrouiller à la main...

et pour ton problème de temps d'exécution la tu peux agir sur la table comme je te l'ai dit ou faire des purges dans les données régulières histoire de retomber sur des performances acceptable par la machine sur laquelle tu es...