IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Outils SQL Server Discussion :

Bufferisation des écritures


Sujet :

Outils SQL Server

  1. #1
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut Bufferisation des écritures
    Bonjour à tous.tes
    Concernant le fonctionnement de ma base, j'ai besoin de m'affranchir totalement des performances, tant de SQLServer au niveau software, que du serveur au niveau hardware.
    Afin d'éviter une réécriture trop lourde de mes appli Delphi, j'aimerais trouver un outils permettant de bufferiser les INSERT envoyées à SQLServer chaque seconde.
    Quelque soit le niveau d'occupation du serveur, aucune de ces écritures ne doit être perdue.

    La meilleure solution que je connaisse actuellement, est l'écriture de ces données dans un fichier csv, lui-même "purgé" par une appli envoyant les INSERT à SQLServer.
    Ce qui m'oblige à réécrire.

    Je me dis qu'il existe peut-être déjà un outil permettant de bufferiser ces écritures directement dans SQLServer avec un peu de chance...
    Cela parle t-il à quelqu'un ?

    Merci d'avance.
    Pas changer assiettes pour fromage.

  2. #2
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    4 057
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 4 057
    Points : 7 272
    Points
    7 272
    Billets dans le blog
    1
    Par défaut
    Exécuter les INSERT de manière asynchrone plutôt que synchrone ?
    On ne jouit bien que de ce qu’on partage.

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 785
    Points : 49 240
    Points
    49 240
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par cestpasmoinonplus Voir le message
    Bonjour à tous.tes
    Concernant le fonctionnement de ma base, j'ai besoin de m'affranchir totalement des performances, tant de SQLServer au niveau software, que du serveur au niveau hardware.
    Afin d'éviter une réécriture trop lourde de mes appli Delphi, j'aimerais trouver un outils permettant de bufferiser les INSERT envoyées à SQLServer chaque seconde.
    Quelque soit le niveau d'occupation du serveur, aucune de ces écritures ne doit être perdue.
    C'est toujours le cas, quelque soit la manière de travailler avec SQL Server

    La meilleure solution que je connaisse actuellement, est l'écriture de ces données dans un fichier csv, lui-même "purgé" par une appli envoyant les INSERT à SQLServer.
    L'écriture dans un fichier est beaucoup moins fiable (par de journalisation, donc pas de reprise en cas d'incident) et beaucoup moins rapide que ce que fera SQL Server
    Ce qui m'oblige à réécrire.

    Je me dis qu'il existe peut-être déjà un outil permettant de bufferiser ces écritures directement dans SQLServer avec un peu de chance...
    Cela parle t-il à quelqu'un ?

    Merci d'avance.
    SQL Server bufferise naturellement les écritures.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    SQL Server bufferise naturellement les écritures.
    Super, du coup ...
    Citation Envoyé par StringBuilder Voir le message
    Exécuter les INSERT de manière asynchrone plutôt que synchrone ?
    ...ce serait la solution

    Je vais fouiller la doc.
    Merci à tous.

    StringBuilder, tu devrais écrire un bouquin sur SQLServer
    Pas changer assiettes pour fromage.

  5. #5
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2005
    Messages
    5 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : août 2005
    Messages : 5 410
    Points : 12 752
    Points
    12 752
    Par défaut
    ...ce serait la solution
    A voir cependant si cela vaut le coup et s'il y a une bonne raison de le faire (par exemple utiliser Service Broker) car cela introduit une certaine complexité.

    ++

  6. #6
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    4 057
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 4 057
    Points : 7 272
    Points
    7 272
    Billets dans le blog
    1
    Par défaut
    Euh…

    Je pige pas.

    T'as un timeout de 1 seconde ???

    Si un SELECT effectue un LOCK (ce qui en soit n'est pas très naturel), les nouveaux INSERT sont mis en attente jusqu'à ce que le lock soit terminé…
    Avec un timeout raisonnablement long (par défaut ça doit être dans les 30 secondes) t'es pas censé aboutir à ce genre d'erreur.

    Et sinon, pourquoi ton SELECT lock les lignes/tables ? En a-t-il vraiment besoin ?
    On ne jouit bien que de ce qu’on partage.

  7. #7
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Attends, j'ai supprimé mon msg précédent trop tard.
    Je comprends ta question, je développe dans le msg suivant .
    Pas changer assiettes pour fromage.

  8. #8
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    4 057
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 4 057
    Points : 7 272
    Points
    7 272
    Billets dans le blog
    1
    Par défaut
    Illustration de la mise en attente des INSERT sans pour autant que ça les plante…

    On ne jouit bien que de ce qu’on partage.

  9. #9
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Mais si ça plante :

    Cannot perform the action, because the previous action is in progress.

    StringBuilder je regarde ce que tu m'envoies, ça me paraît très joli. Merci.
    Pas changer assiettes pour fromage.

  10. #10
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Non, ça ne resout pas mon problème :

    J'ai une appli qui écrit des données dans 1 table, toutes les secondes. Ce qui fonctionne bien.
    Quand je lance un SELECT, un peu lourd qui dure 10 à 15 secondes via une autre appli ou depuis MSSM studio, sur la table, mes INSERT sont bloqués par ce SELECT.
    Du coup, mon appli, qui ne bufferise pas les données qu'elle envoie à l'écriture, attend d'avoir reçu la réponse du serveur pour envoyer un autre ordre d'écriture de données qui est plus de 10 secondes plus vieux que le précédent exécuté.

    résultat dans la base :

    id - temps en s - data1 - data2- ect...
    35 - 35 - data1 - data2- ect...
    36 - 36 - data1 - data2- ect...
    37 - 37 - data1 - data2- ect...
    38 - 38 - data1 - data2- ect...
    39 - 51 - data1 - data2- ect...
    40 - 52 - data1 - data2- ect...

    On voit un trou dans les données entre 38 et 51 secondes, qui n'existe pas si on ne lance pas de SELECT.
    J'ai simplifié car il n'y a pas qu'un trou de 13 secondes mais de façon aléatoire, des trous de 8, 2, 3 seconde par exemple.
    Ces trous traduisent le fait que SQLServer recommence à écrire les données envoyées, par mon appli, 13 secondes plus tard.

    Je pensais que SQLServer bufferisait les données envoyées en attendant de pouvoir les écrire. Ce qui, trié sur le temps en secondes, serait transparent. Mais les données sont juste perdues .
    Pas changer assiettes pour fromage.

  11. #11
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    4 057
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 4 057
    Points : 7 272
    Points
    7 272
    Billets dans le blog
    1
    Par défaut
    D'après ton erreur et le comportement que tu indiques, je dirais que c'est ton programme qui ne traitement pas le lock comme il faut.

    Tu indiques que tu écris depuis un thread.
    Est-ce un thread par INSERT ?

    Si oui, es tu certain que ce thread n'utilise pas des objets (connexion, commande) utilisé par les autres thread de la même action ?
    Car on dirait bien que si deux INSERT tournent en même temps (chacun dans son thread) ton application utilise la même connexion/commande pour l'exécuter.
    => Ce qui explique pourquoi tu as des erreurs à propos de l'opération précédente toujours en cours.

    -- Edit : Enlève-moi d'un doute... Tu passerais pas par un timer par hasard pour écrire toutes les secondes ? Si oui, normal, le thread du tick se met en pause en attendant que le lock soit terminé, et le timer attends que le thread du tick soit terminé pour en lancer un nouveau.
    On ne jouit bien que de ce qu’on partage.

  12. #12
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Non, j'ai simplifié le problème au maximum. Plus d'histoire de thread, ni de plusieurs tables.
    L'appli écrit directement dans une seule table avec un pas de 1 seconde.

    Dans M... Studio, je lance un SELECT dont l'exécution dure plus de 10 secondes.
    Et j'obtient le résultat décrit.

    Je pensais qu'en faisant :

    SELECT *
    FROM table WITH (TABLOCK XLOCK)

    ou autre SNAPSHOT ou NO LOCK, je parviendrais à dire à SQLServer de continuer à écrire pendant l'exécution du SELECT, ou à imposer la priorité aux écritures sur la lecture.
    Pas changer assiettes pour fromage.

  13. #13
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    4 057
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 4 057
    Points : 7 272
    Points
    7 272
    Billets dans le blog
    1
    Par défaut
    Comment gères-tu le "pas de 1 seconde" ?

    Par un timer ? une boucle ?
    Tu as vérifié que c'est pas ton programme qui se met en pause en attendant la fin du INSERT plutôt que SQL Server qui rejette la ligne ?
    On ne jouit bien que de ce qu’on partage.

  14. #14
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    Comment gères-tu le "pas de 1 seconde" ?

    Par un timer ? une boucle ?
    Par un timer.
    Pas changer assiettes pour fromage.

  15. #15
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    Tu as vérifié que c'est pas ton programme qui se met en pause en attendant la fin du INSERT plutôt que SQL Server qui rejette la ligne ?
    Oui, car les données écrites sont bonnes. Il manque juste celles des secondes précédentes.
    Pas changer assiettes pour fromage.

  16. #16
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    4 057
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 4 057
    Points : 7 272
    Points
    7 272
    Billets dans le blog
    1
    Par défaut
    Ben donc c'est ça.
    Le timer attends que le précédent tick soit terminé pour en relancer un nouveau.
    Donc si un INSERT met 20 secondes à passer, le timer ne lancera qu'un seul tick durant ces 20 secondes.

    En même temps que tu fais l'INSERT, consigne ce dernier dans un fichier TEXTE, histoire de vérifier.

    Mais je suis certain que les INSERT manquants ne sont jamais générés par ton programme.

    Sinon, accessoirement, un SELECT n'a en règle générale, pas besoin de poser de lock.
    Essaie de regarder de ce côté aussi si tu ne peux pas améliorer ce dernier afin de contourner le problème en évitant ce lock.
    On ne jouit bien que de ce qu’on partage.

  17. #17
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    Ben donc c'est ça.
    Le timer attends que le précédent tick soit terminé pour en relancer un nouveau.
    Donc si un INSERT met 20 secondes à passer, le timer ne lancera qu'un seul tick durant ces 20 secondes.

    En même temps que tu fais l'INSERT, consigne ce dernier dans un fichier TEXTE, histoire de vérifier.

    Mais je suis certain que les INSERT manquants ne sont jamais générés par ton programme.

    Sinon, accessoirement, un SELECT n'a en règle générale, pas besoin de poser de lock.
    Essaie de regarder de ce côté aussi si tu ne peux pas améliorer ce dernier afin de contourner le problème en évitant ce lock.
    Oui, C'est exactement ça le problème.
    Mais ce n'est pas l'INSERT qui pose problème de temps, c'est le SELECT. Si personne ne lance d'interrogation sur cette table, tout ce passe très bien. L'INSERT est quasi instantané.
    Mais dès qu'on lance un SELECT un peu lourd, toute la machine se bloque. Et le Timer se redéclenche plusieurs secondes plus tard.
    Pas changer assiettes pour fromage.

  18. #18
    Membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    182
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 182
    Points : 42
    Points
    42
    Par défaut
    Je dois partir, mais demain, je vais mettre à dispo une petite appli hyper simplifiée qui écrira toutes les seconde dans une table TABLE avec le code.
    Et un SELECT bidon et nul SQLment parlant, mais qui dont le temps d'exécution devra être le l'ordre de 10 seconde.
    Ca permettra de voir les mêmes choses que moi (ou pas).

    Merci beaucoup en attendant et à demain.
    Pas changer assiettes pour fromage.

  19. #19
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 641
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 641
    Points : 13 067
    Points
    13 067
    Par défaut
    Citation Envoyé par cestpasmoinonplus Voir le message
    Cannot perform the action, because the previous action is in progress.
    ça, ce n'est pas un message de SQL Server.
    Je pense que c'est plutôt l'applicatif en charge des insertions qui gère mal le truc, et c'est lui qu'il faut modifier.

    C'est bien l'application, et non SQL Server qui "perds" les données. (ou plutôt ne les insert pas parce que l'insertion précédente n'est pas finie). Il faudrait donc une queue pour les insertions.

    Coté SQL Server, si les écritures des données sur le disque sont en effet asynchrone, la modification en mémoire, et surtout l'écriture dans le journal des transactions est quant à lui synchrone, ce qui explique la latence des insertions à cause des verrous posés par le SELECT : un SELECT pose bien des verrous, toujours, mais leur type dépend du niveau d'isolation de la transaction.

  20. #20
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 641
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 641
    Points : 13 067
    Points
    13 067
    Par défaut
    pour vous en convaincre, vous pouvez créer une deuxième table "tampon" de même structure que votre table.
    Vous redirigez les insertions vers cette table, et vous créez un job qui transférera régulièrement toutes les lignes de cette table vers la table initiale.

    Vous verrez alors que vous n'aurez plus ces pertes de données.

    Par ailleurs, même si cela ne résoudra pas complétement le problème, indexer pour couvrir la requête de sélection permettrait de réduire le temps d'exécution du SELECT et surtout de réduire la contention due au verrouillage

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 4 1234 DernièreDernière

Discussions similaires

  1. Erreur de connexion, Bufferisation des rêquetes ?
    Par Bob7554 dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 19/11/2007, 21h25
  2. Réponses: 3
    Dernier message: 20/05/2007, 21h35
  3. Quelques questions sur des écritures en perl
    Par Premium dans le forum Langage
    Réponses: 4
    Dernier message: 05/12/2006, 23h44
  4. [winpcap] Bufferisation des données capturées
    Par funtix dans le forum Développement
    Réponses: 4
    Dernier message: 07/09/2006, 20h24
  5. [CSS] Explication des écriture
    Par Yoshio dans le forum Mise en page CSS
    Réponses: 2
    Dernier message: 08/08/2006, 19h17

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo