Discussion :

[SQLpro]

Une autre solution est d'utiliser des verrous optimistes en activant le READ COMMITTED SNAPSHOT au niveau de la base....

A +

[StringBuilder]

Voici en quelques minutes un programme C# illustrant ce qui t'arrive.

Rien d'anormal.
C'est ton programme qui gère mal le lock.

Soit tu fais un ExecuteNonQueryAsync() avec tout ce que ça implique, soit ton timer lance un thread dédié à chaque insert (qui utilise sa propre connexion et sa propre commande) sans attendre la fin du thread.

[StringBuilder]

C'est encore plus simple que je ne pensais.

Remplace juste "ExecuteNonQuery" par "ExecuteNonQueryAsync" (et rien d'autre).

Le timer ne se met donc plus en pause, car n'attends pas la fin du INSERT pour rendre la main.
Et dès que le verrou est libéré, tous les INSERT passent bien tranquillement. Et ceci alors même que j'utilise la même connexion et le même objet command !

Code csharp :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
namespace TestTimerSQL
{
    public partial class Form1 : Form
    {
        SqlConnection cnx = new SqlConnection("Server=.;Database=SandBox;Trusted_Connection=True;");
        SqlCommand cmd;
        SqlParameter p;
 
        public Form1()
        {
            InitializeComponent();
 
            cmd = cnx.CreateCommand();
            cmd.CommandText = "insert into matablequivabien (nom) values (@nom)";
            p = cmd.Parameters.Add("@nom", SqlDbType.VarChar);
            cnx.Open();
            timer1.Interval = 1000;
        }
 
        private void timer1_Tick(object sender, EventArgs e)
        {
            p.Value = DateTime.Now.ToString("yyyyMMdd HH:mm:ss fff");
            cmd.ExecuteNonQueryAsync();
            textBox1.Text += string.Concat(p.Value, "\r\n");
        }
 
        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            timer1.Enabled = checkBox1.Checked;
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
        }
 
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (timer1.Enabled)
            {
                timer1.Stop();
            }
            cnx.Close();
        }
    }
}

Attention cependant : s'il se produit des erreurs lors des INSERT, ou si l'application est fermée avant que le lock ne rendre la main, tu vas avoir des données perdues !
(mais bon, ça sera pas pire qu'actuellement)

[cestpasmoinonplus]

Nous sommes bien d'accord, ce n'est pas SQLServer qui envoi ce message.
Mon problème est que le SELECT bloque les écritures, et donc mon applicatif qui attend une réponse du serveur avant d'envoyer une nouvelle demande d'INSERT, en synchrone, n'envoie ce nouvel INSERT que plusieurs secondes plus tard.
Et, donc, en asynchrone plante avec le message en question.
Donc l'assynchrone n'est pas la solution.

Je voudrais que mon SELECT ne bloque pas les écritures.

[cestpasmoinonplus]

C'est encore plus simple que je ne pensais.

Remplace juste "ExecuteNonQuery" par "ExecuteNonQueryAsync" (et rien d'autre).

Le timer ne se met donc plus en pause, car n'attends pas la fin du INSERT pour rendre la main.
Et dès que le verrou est libéré, tous les INSERT passent bien tranquillement. Et ceci alors même que j'utilise la même connexion et le même objet command !

Non mais en fait, j'ai déjà essayé. Ca lance le query en asynchrone. Sauf que mon appli le fait en Delphi avec les composants Firedac.
Ca ne résout pas mon problème.
Je me retrouve dans le cas du plantage avec le msg pré-cité.

Attention, il faut que le SELECT dure plus longtemps que la fréquence d'écriture, d'envoi des INSERT, pour mettre le problème en lumière.

[cestpasmoinonplus]

Une autre solution est d'utiliser des verrous optimistes en activant le READ COMMITTED SNAPSHOT au niveau de la base....

A +

Déjà essayé SNAPSHOT et Cie. Résultat absolument identique.

[SQLpro]

Déjà essayé SNAPSHOT et Cie. Résultat absolument identique.

Donc, c'est bien votre code qui est foireux.... Delphi il y a 25 ans que je l'ai abandonné. Trop limité, pas adapté aux SGBDR modernes....

A +

[StringBuilder]

Non mais en fait, j'ai déjà essayé. Ca lance le query en asynchrone. Sauf que mon appli le fait en Delphi avec les composants Firedac.
Ca ne résout pas mon problème.
Je me retrouve dans le cas du plantage avec le msg pré-cité.

A mon avis, si ça plante, c'est parce que comme moi, tu utilises la même connexion et probablement aussi la même commande.
Utilise une connexion dédiée et une commande dédiée à chaque INSERT.

Avec le pooling de connexion côté SQL Server ça ne changera rien aux performances, mais devrai éviter à Firedac de se prendre les pieds dans le tapis.

Accessoirement, SQLPro a tout à fait raison à propos de Delphi : c'est (était ?) un très bon langage pour apprendre, comme le Pascal ou Ada.
Mais il est aussi complètement dépassé à l'heure actuelle.

[cestpasmoinonplus]

A mon avis, si ça plante, c'est parce que comme moi, tu utilises la même connexion et probablement aussi la même commande.
Utilise une connexion dédiée et une commande dédiée à chaque INSERT.

C'est déjà le cas.

Accessoirement, SQLPro a tout à fait raison à propos de Delphi : c'est (était ?) un très bon langage pour apprendre, comme le Pascal ou Ada.
Mais il est aussi complètement dépassé à l'heure actuelle.

Delphi n'est pas un langage mais un IDE qui a continué à faire progresser le langage Pascal objet sous-jacent.
Nous travaillons avec Delphi 10 Berlin qui est à la pointe de la pointe

[cestpasmoinonplus]

Par contre, il est possible que le problème vienne de la façon d'utiliser les composants. C'est pour ça que j'ai essayé deux collections différentes de composants. Le résultat est identique.
De toute façon je lance le SELECT dans SQLServer Studio avec l'isolation SNAPSHOT.
Il ne devrait donc pas bloquer les écritures.

[cestpasmoinonplus]

Delphi il y a 25 ans que je l'ai abandonné. Trop limité, pas adapté aux SGBDR modernes....

A +

Tu m'étonnes ! Delphi 2 ou 1 ?

[cestpasmoinonplus]

Je voulais envoyer "une démo" mais ça ne sert à rien, puisqu'il faut au-moins une table avec 17 millions de lignes.

[cestpasmoinonplus]

Voici en quelques minutes un programme C# illustrant ce qui t'arrive.

Rien d'anormal.
C'est ton programme qui gère mal le lock.

Soit tu fais un ExecuteNonQueryAsync() avec tout ce que ça implique, soit ton timer lance un thread dédié à chaque insert (qui utilise sa propre connexion et sa propre commande) sans attendre la fin du thread.

J'ai bien re-regardé ta vidéo StringBuilder, et justement, ça démontre que c'est mon problème qui n'est pas compris :
En fait tu le prends à l'inverse :
1- tu bloques la table pour faire freezer les écritures. (c'est normal)
2- ta requête sur la table non lockée est instantanée, et ne feeze donc pas les écritures. (c'est normal aussi)
Et il n'y a donc pas de problème, je suis d'accord.

Mon problème :
1- si je bloque la table, ça freeze les écritures. (c'est normal)
2- ma requête sur la table non lockée dure plus de 10 secondes, et feeze les écritures. (je ne sais pas si c'est normal, mais ça m'embête )

Du coup, je me demade s'il n'y aurait pas une limite en nombre de lignes dans une table au-delà de laquelle SQLServer ne peut plus assurer les SELECT normalement.

[StringBuilder]

Euh, la limite doit être de l'ordre de quelques milliards de milliards... probablement plus.

Soit la requête est très mal optimisée.
Soit les index sont très mal placés.
Soit la requête fait vraiment des calculs trop complexes.

Mais imagine quand tu vas sur le site des impôts, les formules de calcul, tout ça. Ben quand tu remplis ta déclaration, le calcul de l'estimation est instantané, et pourtant y'a quelques dizaines de million de lignes, rien que dans la table des utilisateurs...

[cestpasmoinonplus]

Je me pose aussi des questions de ce genre, mais ça n'explique pas le problème de fond. En SNAPSHOT, le SELECT ne devrait pas bloquer les écritures.
Demain, je vais nettoyer la table pour voir à partir de quel seuil, j'ai (ou pas) le fonctionnement attendu.

[SQLpro]

Tu m'étonnes ! Delphi 2 ou 1 ?

1 et 2 du temps de Borland.... J'étais même rédac chef de Delphi News avec notre ami Yan Hiff qui nous a quitté bien jeune !

A +

[cestpasmoinonplus]

1 et 2 du temps de Borland.... J'étais même rédac chef de Delphi News avec notre ami Yan Hiff qui nous a quitté bien jeune !

A +

Ok
Moi j'ai laissé tombé Cobol CICS pour Delphi 2 en 1997. Et depuis je n'en suis jamais sorti.
Il y a eu des hauts et des bas, mais Embarcadero semble avoir redressé la barre technique, même si le côté commercial est devenu totalement moisi.

[cestpasmoinonplus]

Euh, la limite doit être de l'ordre de quelques milliards de milliards... probablement plus.

Soit la requête est très mal optimisée.
Soit les index sont très mal placés.
Soit la requête fait vraiment des calculs trop complexes.

Mais imagine quand tu vas sur le site des impôts, les formules de calcul, tout ça. Ben quand tu remplis ta déclaration, le calcul de l'estimation est instantané, et pourtant y'a quelques dizaines de million de lignes, rien que dans la table des utilisateurs...

Oui, mais ils n'ont pas la contrainte d'écrire 1 ligne chaque seconde.

[aieeeuuuuu]

est-ce qu'on peut voir la tête de la requête SELECT dont il est question ?

[cestpasmoinonplus]

est-ce qu'on peut voir la tête de la requête SELECT dont il est question ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT *
FROM TABLE_NAME WITH (INDEX(Index_TABLE_NAME_id_TIME))  --index sur les 2 premiers champs id et TIME
WHERE TIME BETWEEN '2019-01-10 00:00:00' AND '2019-01-10 23:59:59' 
ORDER BY id, TIME ASC  
;
COMMIT TRANSACTION

Cette requête prend 20 secondes sur une table de 4,7 millions de lignes.
Soit prêt de 2 mois d'écritures.

Il y a 4 autres tables remplies dans les mêmes conditions.
Le SELECT ci-dessus freeze les écritures dans les 5 tables.

Et le résultat des freezes dans les 5 tables (sachant que c'est un exemple portant sur des tables de 4,7 Mega lig et non sur 17,4 Mega lignes qui donne des résultats bien plus problématiques) :Pièce jointe 452423