Vérouillage de la base...

Fred BROUARD

17/10/2005 à 11:20

Bonjour,

llopht a écrit:

Salut à tous,

J'ai une base (un annuaire) que se trouve vérouillée avec aucune lecture
possible lorsque l'on y fait des insertions (et on en fait pas mal par
jour, il y a 6 personnes qui y travaille avec en moyenne 1000 nouvelles
insertions par heure et par personne). Y a t'il un moyen pour
outrepasser ce blocage.

Ceci n'est pas normal. Au moins pouvez vous faire du "dirty read" soit en
pilotant un niveau d'isolation soit par un tag dans les requêtes.

Autre petite chose, l'insertion est beaucoup plus consommatrice de
ressources que la lecture. Existe t'il un moyen de limiter les
ressources utilisées par les processus d'insertion (par utilisateur,
procédure stockée...) pour favoriser ceux en lecture seule ?

Non et ce serait pire, car ilsdureraient plus longtemps donc verouillerait plus
=> contention.

La base est constituée d'un arbre intervallaire (merci à Fred Brouard
pour son excellent article) qui définit la structure de l'annuaire
(environ 470000 enregistrements) et une table spécifique qui comporte
l'ensemble des contacts de celui-ci (plus de 16 millions et elle devrait
doubler cette année).

Vérifiez que le niveau d'isolation des proc d'insertion dans l'arbre est bien
remis à READ COMMITED après usage.

Si les insertions dans l'arbre sont massive et que vous n'avez pas besoin des
dernières entrées tout de suite, déportez le traitement du recalcul de l'arbre
par un bacth planifié.
Par exemple insertion des saisies dans une table temporaire et basculement dans
l'arbre la nuit.
Dans ce cas il vous faudra implémenter une procédure de calcul des intervalles
basée sur le modéle en auto référence.

En voici un exemple :

/******************************************************************************
-- création d'une procédure de calcul des bornes des intervalles de l'arbre
******************************************************************************/
-- la procédure P_DERECURSIVE_TREE existe t-elle dans la base ?
-- Si oui, la supprimer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_CATALOG = DB_NAME()
AND ROUTINE_SCHEMA = USER
AND ROUTINE_NAME = 'P_DERECURSIVE_TREE'
AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE P_DERECURSIVE_TREE
GO

-- création de la fonction P_EXPLORE_DIR
CREATE PROCEDURE P_DERECURSIVE_TREE @TABLE_NAME sysname,
@COL_ID sysname,
@COL_ID_PERE sysname,
@COL_BG sysname,
@COL_BD sysname
AS

DECLARE @SQL NVARCHAR(4000)

-- la table TMP_TREE_SQLPRO existe t-elle dans la base B_DATASAPIENS_OS_FILE
-- pour l'utilisateur courant ? Si oui, la virer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'B_DATASAPIENS_OS_FILE'
AND TABLE_SCHEMA = USER
AND TABLE_NAME = 'TMP_TREE_SQLPRO')
BEGIN
SET @SQL = 'DROP TABLE T_OFFSET_OFS'
EXEC (@SQL)
END

-- on créé la table TMP_TREE_SQLPRO pour stocker temporairement les identifiants
-- et autorelations de la table à traiter
CREATE TABLE TMP_TREE_SQLPRO
(CLEF INT NOT NULL,
CLEF_REF INT);

-- on y insère les valeurs dedans
SET @SQL = 'INSERT INTO TMP_TREE_SQLPRO SELECT ' + @COL_ID + ', ' + @COL_ID_PERE
+ ' FROM ' + @TABLE_NAME
EXECUTE (@SQL)

-- la table TMP_STACK_SQLPRO existe t-elle dans la base B_DATASAPIENS_OS_FILE
-- pour l'utilisateur courant ? Si oui, la virer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'B_DATASAPIENS_OS_FILE'
AND TABLE_SCHEMA = USER
AND TABLE_NAME = 'TMP_STACK_SQLPRO')
BEGIN
SET @SQL = 'DROP TABLE TMP_STACK_SQLPRO'
EXEC (@SQL)
END

-- on créé la table TMP_STACK_SQLPRO pour gérer un pile afin de dérécursiver l'arbre
CREATE TABLE TMP_STACK_SQLPRO
(PILE INTEGER NOT NULL,
CLEF CHAR(10) NOT NULL,
GAUCHE INTEGER,
DROITE INTEGER);

-- variables locales
DECLARE @COMPTEUR INTEGER;
DECLARE @MAX_CPTR INTEGER;
DECLARE @POINTEUR INTEGER;

-- initialisation
SET @COMPTEUR = 2;
SET @MAX_CPTR = 2 * (SELECT COUNT(*) FROM TMP_TREE_SQLPRO);
SET @POINTEUR = 1;

-- insertion de la racine de l'arbre dans la pile
INSERT INTO TMP_STACK_SQLPRO
SELECT 1, CLEF, 1, NULL
FROM TMP_TREE_SQLPRO
WHERE CLEF_REF IS NULL;

-- et on supprime la référence à la racine
DELETE FROM TMP_TREE_SQLPRO
WHERE CLEF_REF IS NULL;

-- tant que l'on a pas traité toute l'enveloppe intervallaire
WHILE @COMPTEUR <= (@MAX_CPTR)
BEGIN

-- s'il existe des lignes à traiter
IF EXISTS (SELECT *
FROM TMP_STACK_SQLPRO AS S
INNER JOIN TMP_TREE_SQLPRO AS T
ON S.CLEF = T.CLEF_REF
WHERE S.PILE = @POINTEUR)

BEGIN

-- empile tant que la ligne analysée a des fils (calcul de la borne gauche)
INSERT INTO TMP_STACK_SQLPRO
SELECT @POINTEUR + 1, MIN(T.CLEF), @COMPTEUR, NULL
FROM TMP_STACK_SQLPRO AS S
INNER JOIN TMP_TREE_SQLPRO AS T
ON S.CLEF = T.CLEF_REF
WHERE S.PILE = @POINTEUR;
-- supprime la ligne analysée
DELETE FROM TMP_TREE_SQLPRO
WHERE CLEF = (SELECT CLEF
FROM TMP_STACK_SQLPRO
WHERE PILE = @POINTEUR + 1);
-- incrémente compteur et pointeur
SET @COMPTEUR = @COMPTEUR + 1;
SET @POINTEUR = @POINTEUR + 1;

END
ELSE
BEGIN

-- dépile parce que que la ligne analysée n'a plus de fils (calcul de la borne
droite)
UPDATE TMP_STACK_SQLPRO
SET DROITE = @COMPTEUR,
PILE = - PILE -- pops the Stack
WHERE PILE = @POINTEUR

-- incrémente compteur et décrémente pointeur
SET @COMPTEUR = @COMPTEUR + 1;
SET @POINTEUR = @POINTEUR - 1;

END;
END;

-- met à jour la table cible avec ces calculs
SET @SQL = ' UPDATE ' + @TABLE_NAME +
' SET ' + @COL_BG + ' = S.GAUCHE, ' +
@COL_BD + ' = S.DROITE' +
' FROM TMP_STACK_SQLPRO S INNER JOIN ' + @TABLE_NAME + ' T ON
S.CLEF = T.'+@COL_ID
EXEC (@SQL)

GO

*******************************************

Exemple d'utilisation :
soit la table T_OSFILES_OSF avec les colonnes suivantes :
OSF_ID => identifiant
OSF_ID_PERE => auto référence du père
OSF_BG => borne gauche
OSF_BD => borne droite

Lancer :

P_DERECURSIVE_TREE 'T_OSFILES_OSF', 'OSF_ID', 'OSF_ID_PERE', 'OSF_BG', 'OSF_BD'

A +

Si vous avez besoin de complément d'info pour me conseiller n'hésitez pas.

Jérôme

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Bonjour,

llopht a écrit:

Salut à tous,

J'ai une base (un annuaire) que se trouve vérouillée avec aucune lecture
possible lorsque l'on y fait des insertions (et on en fait pas mal par
jour, il y a 6 personnes qui y travaille avec en moyenne 1000 nouvelles
insertions par heure et par personne). Y a t'il un moyen pour
outrepasser ce blocage.

Ceci n'est pas normal. Au moins pouvez vous faire du "dirty read" soit en
pilotant un niveau d'isolation soit par un tag dans les requêtes.

Autre petite chose, l'insertion est beaucoup plus consommatrice de
ressources que la lecture. Existe t'il un moyen de limiter les
ressources utilisées par les processus d'insertion (par utilisateur,
procédure stockée...) pour favoriser ceux en lecture seule ?

Non et ce serait pire, car ilsdureraient plus longtemps donc verouillerait plus
=> contention.

La base est constituée d'un arbre intervallaire (merci à Fred Brouard
pour son excellent article) qui définit la structure de l'annuaire
(environ 470000 enregistrements) et une table spécifique qui comporte
l'ensemble des contacts de celui-ci (plus de 16 millions et elle devrait
doubler cette année).

Vérifiez que le niveau d'isolation des proc d'insertion dans l'arbre est bien
remis à READ COMMITED après usage.

Si les insertions dans l'arbre sont massive et que vous n'avez pas besoin des
dernières entrées tout de suite, déportez le traitement du recalcul de l'arbre
par un bacth planifié.
Par exemple insertion des saisies dans une table temporaire et basculement dans
l'arbre la nuit.
Dans ce cas il vous faudra implémenter une procédure de calcul des intervalles
basée sur le modéle en auto référence.

En voici un exemple :

/******************************************************************************
-- création d'une procédure de calcul des bornes des intervalles de l'arbre
******************************************************************************/
-- la procédure P_DERECURSIVE_TREE existe t-elle dans la base ?
-- Si oui, la supprimer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_CATALOG = DB_NAME()
AND ROUTINE_SCHEMA = USER
AND ROUTINE_NAME = 'P_DERECURSIVE_TREE'
AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE P_DERECURSIVE_TREE
GO

-- création de la fonction P_EXPLORE_DIR
CREATE PROCEDURE P_DERECURSIVE_TREE @TABLE_NAME sysname,
@COL_ID sysname,
@COL_ID_PERE sysname,
@COL_BG sysname,
@COL_BD sysname
AS

DECLARE @SQL NVARCHAR(4000)

-- la table TMP_TREE_SQLPRO existe t-elle dans la base B_DATASAPIENS_OS_FILE
-- pour l'utilisateur courant ? Si oui, la virer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'B_DATASAPIENS_OS_FILE'
AND TABLE_SCHEMA = USER
AND TABLE_NAME = 'TMP_TREE_SQLPRO')
BEGIN
SET @SQL = 'DROP TABLE T_OFFSET_OFS'
EXEC (@SQL)
END

-- on créé la table TMP_TREE_SQLPRO pour stocker temporairement les identifiants
-- et autorelations de la table à traiter
CREATE TABLE TMP_TREE_SQLPRO
(CLEF INT NOT NULL,
CLEF_REF INT);

-- on y insère les valeurs dedans
SET @SQL = 'INSERT INTO TMP_TREE_SQLPRO SELECT ' + @COL_ID + ', ' + @COL_ID_PERE
+ ' FROM ' + @TABLE_NAME
EXECUTE (@SQL)

-- la table TMP_STACK_SQLPRO existe t-elle dans la base B_DATASAPIENS_OS_FILE
-- pour l'utilisateur courant ? Si oui, la virer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'B_DATASAPIENS_OS_FILE'
AND TABLE_SCHEMA = USER
AND TABLE_NAME = 'TMP_STACK_SQLPRO')
BEGIN
SET @SQL = 'DROP TABLE TMP_STACK_SQLPRO'
EXEC (@SQL)
END

-- on créé la table TMP_STACK_SQLPRO pour gérer un pile afin de dérécursiver l'arbre
CREATE TABLE TMP_STACK_SQLPRO
(PILE INTEGER NOT NULL,
CLEF CHAR(10) NOT NULL,
GAUCHE INTEGER,
DROITE INTEGER);

-- variables locales
DECLARE @COMPTEUR INTEGER;
DECLARE @MAX_CPTR INTEGER;
DECLARE @POINTEUR INTEGER;

-- initialisation
SET @COMPTEUR = 2;
SET @MAX_CPTR = 2 * (SELECT COUNT(*) FROM TMP_TREE_SQLPRO);
SET @POINTEUR = 1;

-- insertion de la racine de l'arbre dans la pile
INSERT INTO TMP_STACK_SQLPRO
SELECT 1, CLEF, 1, NULL
FROM TMP_TREE_SQLPRO
WHERE CLEF_REF IS NULL;

-- et on supprime la référence à la racine
DELETE FROM TMP_TREE_SQLPRO
WHERE CLEF_REF IS NULL;

-- tant que l'on a pas traité toute l'enveloppe intervallaire
WHILE @COMPTEUR <= (@MAX_CPTR)
BEGIN

-- s'il existe des lignes à traiter
IF EXISTS (SELECT *
FROM TMP_STACK_SQLPRO AS S
INNER JOIN TMP_TREE_SQLPRO AS T
ON S.CLEF = T.CLEF_REF
WHERE S.PILE = @POINTEUR)

BEGIN

-- empile tant que la ligne analysée a des fils (calcul de la borne gauche)
INSERT INTO TMP_STACK_SQLPRO
SELECT @POINTEUR + 1, MIN(T.CLEF), @COMPTEUR, NULL
FROM TMP_STACK_SQLPRO AS S
INNER JOIN TMP_TREE_SQLPRO AS T
ON S.CLEF = T.CLEF_REF
WHERE S.PILE = @POINTEUR;
-- supprime la ligne analysée
DELETE FROM TMP_TREE_SQLPRO
WHERE CLEF = (SELECT CLEF
FROM TMP_STACK_SQLPRO
WHERE PILE = @POINTEUR + 1);
-- incrémente compteur et pointeur
SET @COMPTEUR = @COMPTEUR + 1;
SET @POINTEUR = @POINTEUR + 1;

END
ELSE
BEGIN

-- dépile parce que que la ligne analysée n'a plus de fils (calcul de la borne
droite)
UPDATE TMP_STACK_SQLPRO
SET DROITE = @COMPTEUR,
PILE = - PILE -- pops the Stack
WHERE PILE = @POINTEUR

-- incrémente compteur et décrémente pointeur
SET @COMPTEUR = @COMPTEUR + 1;
SET @POINTEUR = @POINTEUR - 1;

END;
END;

-- met à jour la table cible avec ces calculs
SET @SQL = ' UPDATE ' + @TABLE_NAME +
' SET ' + @COL_BG + ' = S.GAUCHE, ' +
@COL_BD + ' = S.DROITE' +
' FROM TMP_STACK_SQLPRO S INNER JOIN ' + @TABLE_NAME + ' T ON
S.CLEF = T.'+@COL_ID
EXEC (@SQL)

GO

*******************************************

Exemple d'utilisation :
soit la table T_OSFILES_OSF avec les colonnes suivantes :
OSF_ID => identifiant
OSF_ID_PERE => auto référence du père
OSF_BG => borne gauche
OSF_BD => borne droite

Lancer :

P_DERECURSIVE_TREE 'T_OSFILES_OSF', 'OSF_ID', 'OSF_ID_PERE', 'OSF_BG', 'OSF_BD'

A +

Si vous avez besoin de complément d'info pour me conseiller n'hésitez pas.

Jérôme

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Vous avez filtré cet utilisateur ! Consultez son message

Bonjour,

llopht a écrit:

Salut à tous,

J'ai une base (un annuaire) que se trouve vérouillée avec aucune lecture
possible lorsque l'on y fait des insertions (et on en fait pas mal par
jour, il y a 6 personnes qui y travaille avec en moyenne 1000 nouvelles
insertions par heure et par personne). Y a t'il un moyen pour
outrepasser ce blocage.

Ceci n'est pas normal. Au moins pouvez vous faire du "dirty read" soit en
pilotant un niveau d'isolation soit par un tag dans les requêtes.

Autre petite chose, l'insertion est beaucoup plus consommatrice de
ressources que la lecture. Existe t'il un moyen de limiter les
ressources utilisées par les processus d'insertion (par utilisateur,
procédure stockée...) pour favoriser ceux en lecture seule ?

Non et ce serait pire, car ilsdureraient plus longtemps donc verouillerait plus
=> contention.

La base est constituée d'un arbre intervallaire (merci à Fred Brouard
pour son excellent article) qui définit la structure de l'annuaire
(environ 470000 enregistrements) et une table spécifique qui comporte
l'ensemble des contacts de celui-ci (plus de 16 millions et elle devrait
doubler cette année).

Vérifiez que le niveau d'isolation des proc d'insertion dans l'arbre est bien
remis à READ COMMITED après usage.

Si les insertions dans l'arbre sont massive et que vous n'avez pas besoin des
dernières entrées tout de suite, déportez le traitement du recalcul de l'arbre
par un bacth planifié.
Par exemple insertion des saisies dans une table temporaire et basculement dans
l'arbre la nuit.
Dans ce cas il vous faudra implémenter une procédure de calcul des intervalles
basée sur le modéle en auto référence.

En voici un exemple :

/******************************************************************************
-- création d'une procédure de calcul des bornes des intervalles de l'arbre
******************************************************************************/
-- la procédure P_DERECURSIVE_TREE existe t-elle dans la base ?
-- Si oui, la supprimer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_CATALOG = DB_NAME()
AND ROUTINE_SCHEMA = USER
AND ROUTINE_NAME = 'P_DERECURSIVE_TREE'
AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE P_DERECURSIVE_TREE
GO

-- création de la fonction P_EXPLORE_DIR
CREATE PROCEDURE P_DERECURSIVE_TREE @TABLE_NAME sysname,
@COL_ID sysname,
@COL_ID_PERE sysname,
@COL_BG sysname,
@COL_BD sysname
AS

DECLARE @SQL NVARCHAR(4000)

-- la table TMP_TREE_SQLPRO existe t-elle dans la base B_DATASAPIENS_OS_FILE
-- pour l'utilisateur courant ? Si oui, la virer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'B_DATASAPIENS_OS_FILE'
AND TABLE_SCHEMA = USER
AND TABLE_NAME = 'TMP_TREE_SQLPRO')
BEGIN
SET @SQL = 'DROP TABLE T_OFFSET_OFS'
EXEC (@SQL)
END

-- on créé la table TMP_TREE_SQLPRO pour stocker temporairement les identifiants
-- et autorelations de la table à traiter
CREATE TABLE TMP_TREE_SQLPRO
(CLEF INT NOT NULL,
CLEF_REF INT);

-- on y insère les valeurs dedans
SET @SQL = 'INSERT INTO TMP_TREE_SQLPRO SELECT ' + @COL_ID + ', ' + @COL_ID_PERE
+ ' FROM ' + @TABLE_NAME
EXECUTE (@SQL)

-- la table TMP_STACK_SQLPRO existe t-elle dans la base B_DATASAPIENS_OS_FILE
-- pour l'utilisateur courant ? Si oui, la virer !
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'B_DATASAPIENS_OS_FILE'
AND TABLE_SCHEMA = USER
AND TABLE_NAME = 'TMP_STACK_SQLPRO')
BEGIN
SET @SQL = 'DROP TABLE TMP_STACK_SQLPRO'
EXEC (@SQL)
END

-- on créé la table TMP_STACK_SQLPRO pour gérer un pile afin de dérécursiver l'arbre
CREATE TABLE TMP_STACK_SQLPRO
(PILE INTEGER NOT NULL,
CLEF CHAR(10) NOT NULL,
GAUCHE INTEGER,
DROITE INTEGER);

-- variables locales
DECLARE @COMPTEUR INTEGER;
DECLARE @MAX_CPTR INTEGER;
DECLARE @POINTEUR INTEGER;

-- initialisation
SET @COMPTEUR = 2;
SET @MAX_CPTR = 2 * (SELECT COUNT(*) FROM TMP_TREE_SQLPRO);
SET @POINTEUR = 1;

-- insertion de la racine de l'arbre dans la pile
INSERT INTO TMP_STACK_SQLPRO
SELECT 1, CLEF, 1, NULL
FROM TMP_TREE_SQLPRO
WHERE CLEF_REF IS NULL;

-- et on supprime la référence à la racine
DELETE FROM TMP_TREE_SQLPRO
WHERE CLEF_REF IS NULL;

-- tant que l'on a pas traité toute l'enveloppe intervallaire
WHILE @COMPTEUR <= (@MAX_CPTR)
BEGIN

-- s'il existe des lignes à traiter
IF EXISTS (SELECT *
FROM TMP_STACK_SQLPRO AS S
INNER JOIN TMP_TREE_SQLPRO AS T
ON S.CLEF = T.CLEF_REF
WHERE S.PILE = @POINTEUR)

BEGIN

-- empile tant que la ligne analysée a des fils (calcul de la borne gauche)
INSERT INTO TMP_STACK_SQLPRO
SELECT @POINTEUR + 1, MIN(T.CLEF), @COMPTEUR, NULL
FROM TMP_STACK_SQLPRO AS S
INNER JOIN TMP_TREE_SQLPRO AS T
ON S.CLEF = T.CLEF_REF
WHERE S.PILE = @POINTEUR;
-- supprime la ligne analysée
DELETE FROM TMP_TREE_SQLPRO
WHERE CLEF = (SELECT CLEF
FROM TMP_STACK_SQLPRO
WHERE PILE = @POINTEUR + 1);
-- incrémente compteur et pointeur
SET @COMPTEUR = @COMPTEUR + 1;
SET @POINTEUR = @POINTEUR + 1;

END
ELSE
BEGIN

-- dépile parce que que la ligne analysée n'a plus de fils (calcul de la borne
droite)
UPDATE TMP_STACK_SQLPRO
SET DROITE = @COMPTEUR,
PILE = - PILE -- pops the Stack
WHERE PILE = @POINTEUR

-- incrémente compteur et décrémente pointeur
SET @COMPTEUR = @COMPTEUR + 1;
SET @POINTEUR = @POINTEUR - 1;

END;
END;

-- met à jour la table cible avec ces calculs
SET @SQL = ' UPDATE ' + @TABLE_NAME +
' SET ' + @COL_BG + ' = S.GAUCHE, ' +
@COL_BD + ' = S.DROITE' +
' FROM TMP_STACK_SQLPRO S INNER JOIN ' + @TABLE_NAME + ' T ON
S.CLEF = T.'+@COL_ID
EXEC (@SQL)

GO

*******************************************

Exemple d'utilisation :
soit la table T_OSFILES_OSF avec les colonnes suivantes :
OSF_ID => identifiant
OSF_ID_PERE => auto référence du père
OSF_BG => borne gauche
OSF_BD => borne droite

Lancer :

P_DERECURSIVE_TREE 'T_OSFILES_OSF', 'OSF_ID', 'OSF_ID_PERE', 'OSF_BG', 'OSF_BD'

A +

Si vous avez besoin de complément d'info pour me conseiller n'hésitez pas.

Jérôme

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

llopht

17/10/2005 à 14:04

Bonjour,

Ceci n'est pas normal. Au moins pouvez vous faire du "dirty read" soit
en pilotant un niveau d'isolation soit par un tag dans les requêtes.

Je vais effectivement modifier le niveau d'isolation pour mettre un Read
Uncommitted. A part un tag dans mes différentes procédures stockées
c'est que la solution de "pilotage" du niveau d'isolation ?

Non et ce serait pire, car ilsdureraient plus longtemps donc
verouillerait plus => contention.

Ok, ça parait logique

Vérifiez que le niveau d'isolation des proc d'insertion dans l'arbre est
bien remis à READ COMMITED après usage.

Je vais vérifier ça de suite...

Si les insertions dans l'arbre sont massive et que vous n'avez pas
besoin des dernières entrées tout de suite, déportez le traitement du
recalcul de l'arbre par un bacth planifié.
Par exemple insertion des saisies dans une table temporaire et
basculement dans l'arbre la nuit.
Dans ce cas il vous faudra implémenter une procédure de calcul des
intervalles basée sur le modéle en auto référence.

En fait comme l'ajout d'une branche ce fait systématiquement en "fin" de
table c'est notre logiciel qui calcule automatiquement les bords et
évite donc ce genre de problème. Il reste juste à mettre à jour la
racine ce qui reste une opération simple et non couteuse. Mais votre
procédure reste très intéressanteet et je vais l'étudier de plus près...

Tiens d'ailleurs une petite chose qui manque à l'article c'est une
procédure optimisée pour déplacer une branche dans une autre. Les
différentes tests que j'ai effectué me donne des temps conséquents
(surtout sur 470 000 enregistrements :) ce qui rend le procédé
inutilisable dans mon logiciel (j'aimerai pouvoir faire un drag'n'drop
d'une branche dans mon treeview, en moyenne un branche complète
d'annuaire doit faire 1000 à 2000 enregistrements). Si vous avez une
nouvelle astuce à me faire profiter :) :).

Jérôme

Fred BROUARD

17/10/2005 à 14:57

llopht a écrit:

Bonjour,

Ceci n'est pas normal. Au moins pouvez vous faire du "dirty read" soit
en pilotant un niveau d'isolation soit par un tag dans les requêtes.

Je vais effectivement modifier le niveau d'isolation pour mettre un Read
Uncommitted. A part un tag dans mes différentes procédures stockées
c'est que la solution de "pilotage" du niveau d'isolation ?

Attention, dans les PS de gestion de l'arbre, le niveau d'isoaltion doit rester
en SERIALIZABLE. Le mieux est de rétablir un READ COMMITED en fin de SP.

Non et ce serait pire, car ilsdureraient plus longtemps donc
verouillerait plus => contention.

Ok, ça parait logique

Vérifiez que le niveau d'isolation des proc d'insertion dans l'arbre
est bien remis à READ COMMITED après usage.

Je vais vérifier ça de suite...

Si les insertions dans l'arbre sont massive et que vous n'avez pas
besoin des dernières entrées tout de suite, déportez le traitement du
recalcul de l'arbre par un bacth planifié.
Par exemple insertion des saisies dans une table temporaire et
basculement dans l'arbre la nuit.
Dans ce cas il vous faudra implémenter une procédure de calcul des
intervalles basée sur le modéle en auto référence.

En fait comme l'ajout d'une branche ce fait systématiquement en "fin" de
table c'est notre logiciel qui calcule automatiquement les bords et
évite donc ce genre de problème. Il reste juste à mettre à jour la
racine ce qui reste une opération simple et non couteuse. Mais votre
procédure reste très intéressanteet et je vais l'étudier de plus près...

Tiens d'ailleurs une petite chose qui manque à l'article c'est une
procédure optimisée pour déplacer une branche dans une autre. Les
différentes tests que j'ai effectué me donne des temps conséquents
(surtout sur 470 000 enregistrements :) ce qui rend le procédé
inutilisable dans mon logiciel (j'aimerai pouvoir faire un drag'n'drop
d'une branche dans mon treeview, en moyenne un branche complète
d'annuaire doit faire 1000 à 2000 enregistrements). Si vous avez une
nouvelle astuce à me faire profiter :) :).

Tout dépend de l'outil que vous utilisez pour ce faire. Le mieux est de
désynchroniser entre le visuel et le data.
Ceci est facile en déconnecté (internet). C'est moins facile en client lourd car
il faut lancer un thread.

Enfin dans mon cours sur l'optimisation des bases de données sous SQL Server, je
donne une autre voie qui permet d'éviter le recalcul des intervalles. Mais cela
nécessite des réglages et une optimisation poussée.

A +

Jérôme

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

llopht a écrit:

Bonjour,

Ceci n'est pas normal. Au moins pouvez vous faire du "dirty read" soit
en pilotant un niveau d'isolation soit par un tag dans les requêtes.

Je vais effectivement modifier le niveau d'isolation pour mettre un Read
Uncommitted. A part un tag dans mes différentes procédures stockées
c'est que la solution de "pilotage" du niveau d'isolation ?

Attention, dans les PS de gestion de l'arbre, le niveau d'isoaltion doit rester
en SERIALIZABLE. Le mieux est de rétablir un READ COMMITED en fin de SP.

Non et ce serait pire, car ilsdureraient plus longtemps donc
verouillerait plus => contention.

Ok, ça parait logique

Vérifiez que le niveau d'isolation des proc d'insertion dans l'arbre
est bien remis à READ COMMITED après usage.

Je vais vérifier ça de suite...

Si les insertions dans l'arbre sont massive et que vous n'avez pas
besoin des dernières entrées tout de suite, déportez le traitement du
recalcul de l'arbre par un bacth planifié.
Par exemple insertion des saisies dans une table temporaire et
basculement dans l'arbre la nuit.
Dans ce cas il vous faudra implémenter une procédure de calcul des
intervalles basée sur le modéle en auto référence.

En fait comme l'ajout d'une branche ce fait systématiquement en "fin" de
table c'est notre logiciel qui calcule automatiquement les bords et
évite donc ce genre de problème. Il reste juste à mettre à jour la
racine ce qui reste une opération simple et non couteuse. Mais votre
procédure reste très intéressanteet et je vais l'étudier de plus près...

Tiens d'ailleurs une petite chose qui manque à l'article c'est une
procédure optimisée pour déplacer une branche dans une autre. Les
différentes tests que j'ai effectué me donne des temps conséquents
(surtout sur 470 000 enregistrements :) ce qui rend le procédé
inutilisable dans mon logiciel (j'aimerai pouvoir faire un drag'n'drop
d'une branche dans mon treeview, en moyenne un branche complète
d'annuaire doit faire 1000 à 2000 enregistrements). Si vous avez une
nouvelle astuce à me faire profiter :) :).

Tout dépend de l'outil que vous utilisez pour ce faire. Le mieux est de
désynchroniser entre le visuel et le data.
Ceci est facile en déconnecté (internet). C'est moins facile en client lourd car
il faut lancer un thread.

Enfin dans mon cours sur l'optimisation des bases de données sous SQL Server, je
donne une autre voie qui permet d'éviter le recalcul des intervalles. Mais cela
nécessite des réglages et une optimisation poussée.

A +

Jérôme

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Vous avez filtré cet utilisateur ! Consultez son message

llopht a écrit:

Bonjour,

Ceci n'est pas normal. Au moins pouvez vous faire du "dirty read" soit
en pilotant un niveau d'isolation soit par un tag dans les requêtes.

Je vais effectivement modifier le niveau d'isolation pour mettre un Read
Uncommitted. A part un tag dans mes différentes procédures stockées
c'est que la solution de "pilotage" du niveau d'isolation ?

Attention, dans les PS de gestion de l'arbre, le niveau d'isoaltion doit rester
en SERIALIZABLE. Le mieux est de rétablir un READ COMMITED en fin de SP.

Non et ce serait pire, car ilsdureraient plus longtemps donc
verouillerait plus => contention.

Ok, ça parait logique

Vérifiez que le niveau d'isolation des proc d'insertion dans l'arbre
est bien remis à READ COMMITED après usage.

Je vais vérifier ça de suite...

Si les insertions dans l'arbre sont massive et que vous n'avez pas
besoin des dernières entrées tout de suite, déportez le traitement du
recalcul de l'arbre par un bacth planifié.
Par exemple insertion des saisies dans une table temporaire et
basculement dans l'arbre la nuit.
Dans ce cas il vous faudra implémenter une procédure de calcul des
intervalles basée sur le modéle en auto référence.

En fait comme l'ajout d'une branche ce fait systématiquement en "fin" de
table c'est notre logiciel qui calcule automatiquement les bords et
évite donc ce genre de problème. Il reste juste à mettre à jour la
racine ce qui reste une opération simple et non couteuse. Mais votre
procédure reste très intéressanteet et je vais l'étudier de plus près...

Tiens d'ailleurs une petite chose qui manque à l'article c'est une
procédure optimisée pour déplacer une branche dans une autre. Les
différentes tests que j'ai effectué me donne des temps conséquents
(surtout sur 470 000 enregistrements :) ce qui rend le procédé
inutilisable dans mon logiciel (j'aimerai pouvoir faire un drag'n'drop
d'une branche dans mon treeview, en moyenne un branche complète
d'annuaire doit faire 1000 à 2000 enregistrements). Si vous avez une
nouvelle astuce à me faire profiter :) :).

Tout dépend de l'outil que vous utilisez pour ce faire. Le mieux est de
désynchroniser entre le visuel et le data.
Ceci est facile en déconnecté (internet). C'est moins facile en client lourd car
il faut lancer un thread.

Enfin dans mon cours sur l'optimisation des bases de données sous SQL Server, je
donne une autre voie qui permet d'éviter le recalcul des intervalles. Mais cela
nécessite des réglages et une optimisation poussée.

A +

Jérôme

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Nathan NAU

18/10/2005 à 14:43

Bonjour

llopht a écrit:

Attention, dans les PS de gestion de l'arbre, le niveau d'isoaltion doit
rester en SERIALIZABLE. Le mieux est de rétablir un READ COMMITED en fin
de SP.

J'ai testé avec tous les niveaux d'isolations possible, mais la base se
bloque à chaque fois.
SqlServer fait-il une différence en un simple SELECT et une procédure
stockée qui fait ce SELECT ?

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Nathan

Fred BROUARD

18/10/2005 à 17:03

Nathan NAU a écrit:

Bonjour

llopht a écrit:

Attention, dans les PS de gestion de l'arbre, le niveau d'isoaltion doit
rester en SERIALIZABLE. Le mieux est de rétablir un READ COMMITED en fin
de SP.

J'ai testé avec tous les niveaux d'isolations possible, mais la base se
bloque à chaque fois.

pouvez vous investiguez ce qui bloque à l'aide de la table sysprocesses et de la
proc sp_who ?

SqlServer fait-il une différence en un simple SELECT et une procédure
stockée qui fait ce SELECT ?

Oui, mais peu de conséquences.

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Nathan

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Vérouillage de la base...

5 réponses

Veuillez sélectionner un problème