Discussion :

[cestpasmoinonplus]

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.

[StringBuilder]

Exécuter les INSERT de manière asynchrone plutôt que synchrone ?

[SQLpro]

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 +

[cestpasmoinonplus]

SQL Server bufferise naturellement les écritures.

Super, du coup ...

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

[mikedavem]

...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é.

++

[StringBuilder]

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 ?

[cestpasmoinonplus]

Attends, j'ai supprimé mon msg précédent trop tard.
Je comprends ta question, je développe dans le msg suivant .

[StringBuilder]

Illustration de la mise en attente des INSERT sans pour autant que ça les plante…

[cestpasmoinonplus]

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.

[cestpasmoinonplus]

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 .

[StringBuilder]

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.

[cestpasmoinonplus]

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.

[StringBuilder]

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 ?

[cestpasmoinonplus]

Comment gères-tu le "pas de 1 seconde" ?

Par un timer ? une boucle ?

Par un timer.

[cestpasmoinonplus]

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.

[StringBuilder]

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.

[cestpasmoinonplus]

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.

[cestpasmoinonplus]

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.

[aieeeuuuuu]

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.

[aieeeuuuuu]

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