Bufferisation des écritures

**StringBuilder** · 22/02/2019, 11h24

Pourquoi le hint d'index ?
La colonne TIME est-elle indexée ? Comment est déclaré l'index ?
Quel volume de données est retourné ?
Quel outil utilise la requête ? Un curseur est-il ouvert dessus ? READONLY FAST_FORWARD ou d'autres options (genre RANDOM ACCESS READWRITE) ?
Enlève cet affreux "*" et indique la liste des colonnes dont tu as réellement besoin à la place.

**cestpasmoinonplus** · 22/02/2019, 12h10

On peut voir à ce niveau de volume des tables, que les dégâts ne sont pas énormes.
Mais je ne comprends pas pourquoi le mode SNAPSHOT n'empèche pas le blocage des écritures.

**cestpasmoinonplus** · 22/02/2019, 12h27

Envoyé par StringBuilder

Pourquoi le hint d'index ?

J'ai créé un index sur les 2 premiers champs, ce qui a grandement optimisé la requête. Sans le hint d'index, il semble ne pas l'utiliser car on passe de 10 à 50 secondes d temps d'exécution.

Envoyé par StringBuilder

La colonne TIME est-elle indexée ? Comment est déclaré l'index ?

(La clé primaire est sur le champ "id")
CREATE UNIQUE NONCLUSTERED INDEX [Index_TABLE_NAME_Id_TIME] ON [dbo].[TABLE_NAME]
(
[id] ASC,
[TIME] ASC
)

Envoyé par StringBuilder

Quel volume de données est retourné ?

3600 x 24 environ = 86400 lignes

Envoyé par StringBuilder

Quel outil utilise la requête ? Un curseur est-il ouvert dessus ? READONLY FAST_FORWARD ou d'autres options (genre RANDOM ACCESS READWRITE) ?

Microsoft SQLServer Management Studio

Envoyé par StringBuilder

Enlève cet affreux "*" et indique la liste des colonnes dont tu as réellement besoin à la place.

C'est pareil, dixit un spécialiste (s'il en est) de SQL. De plus au niveau du soft, on doit garder une liste dynamique des champs à retourner.

**aieeeuuuuu** · 22/02/2019, 13h42

non, ce n'est pas pareil !

essayez avec vote requête, de remplacer le "*" par les deux colonnes id et time uniquement, vous verrez que la requête ira beaucoup plus vite, car elle n'aura alors plus besoin d'accéder à la table : l'index deviendra couvrant pour cette nouvelle requête.

Concernant l'index, pourquoi un index nonclustered ? y a-t-il déjà un index cluster sur la table ?

enfin, et surtout, il conviendrait d'inverser les deux colonnes dans l'index, pour mieux répondre à la requête en question (attention toutefois s'il y a d'autres requêtes).

Avec un index cluster sur (time, id), la requête devrait être bien pus rapide.

**SQLpro** · 22/02/2019, 13h50

Envoyé par cestpasmoinonplus

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

Pour information... 1/1/2017, Microsoft SQL Server 2017 Enterprise Edition :
http://www.tpc.org/tpce/results/tpce...ric_results=no

11 357 transactions par seconde... Ces transactions écrivant beaucoup plus d'une ligne par seconde....

Exemple :

Code :

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
start transaction
 
Declare t_id TRADE_T
Declare tr_t_id TRADE_T
Declare now_dts DATETIME
 
declare pending_list forselect TR_T_ID from TRADE_REQUESTorder by TR_T_ID
 
open pending_list
do until (end_of_pending_list) 
   fetch from pending_listintotr_t_idget_current_dts ( now_dts )
   insert into TRADE_HISTORY (TH_T_ID, TH_DTS, TH_ST_ID) 
      values (tr_t_id, TH_T_IDnow_dts, TH_DTSst_submitted_id, TH_ST_ID)
   update TRADE set T_ST_ID = st_canceled_id,T_DTS = now_dts 
      where T_ID = tr_t_id
   insert into TRADE_HISTORY (TH_T_ID, TH_DTS, TH_ST_ID
      values (tr_t_id, now_dts, st_canceled_id)} 
end of pending_list
 
delete from TRADE_REQUEST
declare submit_list 
   for
      select T_ID from TRADE where T_ID >= trade_id and T_ST_ID = st_submitted_id
 
open submit_list
do until (end_of_submit_list) 
   fetch from submit_list into t_id
   get_current_dts ( now_dts 
   update TRADE set T_ST_ID = st_canceled_id, T_DTS = now_dts 
      whereT_ID =t_id
   insert into TRADE_HISTORY (TH_T_ID, TH_DTS, TH_ST_ID) 
   values ( TH_T_ID, TH_DTS, TH_ST_ID
end of submit_list
 
commit transaction

A +

**cestpasmoinonplus** · 22/02/2019, 14h00

Envoyé par aieeeuuuuu

non, ce n'est pas pareil !
essayez avec vote requête, de remplacer le "*" par les deux colonnes id et time uniquement, vous verrez que la requête ira beaucoup plus vite, car elle n'aura alors plus besoin d'accéder à la table : l'index deviendra couvrant pour cette nouvelle requête.

Ok, mais je veux tous les champs.

Envoyé par aieeeuuuuu

Concernant l'index, pourquoi un index nonclustered ? y a-t-il déjà un index cluster sur la table ?

Oui. Sur id.

Envoyé par aieeeuuuuu

enfin, et surtout, il conviendrait d'inverser les deux colonnes dans l'index, pour mieux répondre à la requête en question (attention toutefois s'il y a d'autres requêtes).
Avec un index cluster sur (time, id), la requête devrait être bien pus rapide.

Ok, mais on ne peut en avoir qu'un seul. Il existe sur id (sous-entendu : on ne peut pas y toucher) . Par contre, je ne vois pas l'intérêt d'inverser les deux colonnes ?

**cestpasmoinonplus** · 22/02/2019, 14h15

Ah ouai, au temps pour moi, la capture n'est pas très explicite.
Les premiers champs ramenés sont les id.

Finalement, pour résoudre le problème, on va bufferiser les écritures. la boucle qui les envoie à SQLServer aura tout son temps pour le faire.
Du coup, on s'affranchi des performances du SGBD et du hardware qui sont forcément aléatoires.
En tout cas merci à tous de m'avoir permis de faire le tour du problème et bon WE.

Juste pour finir, je viens de repasser la base en "non SNAPSHOT", la différence de freeze est énooooorme.

**aieeeuuuuu** · 22/02/2019, 15h24

Envoyé par cestpasmoinonplus

Oui. Sur id.

??? s'il y a un index sur id, que l'on peut supposer unique, alors l'index non cluster sur (id, time) est redondant, et fort peu utile.
Il n'est pas étonnant que le moteur ne l'utilise pas sans l'indicateur. Il est en revanche étrange que la requête mette plus de temps sans...
Quelle est la structure de la table ?

Envoyé par cestpasmoinonplus

Ok, mais on ne peut en avoir qu'un seul. Il existe sur id (sous-entendu : on ne peut pas y toucher) . Par contre, je ne vois pas l'intérêt d'inverser les deux colonnes ?

En inversant les deux colonnes, les lignes seront triées sur la colonne time, et le scan d'index deviendra une recherche d'index, bien plus performante.

De fait, vous pouvez créer un index non cluster avec colonnes incluses afin d'éviter les accès table :

Code :

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

CREATE [UNIQUE ?] NONCLUSTERED INDEX CUIX_LaTable on LaTable(time) INCLUDE(les autres colonnes)

ou, simplement indexer la colonne time, (ce qui fera un index plus léger, mais obligera à des accès table)

**StringBuilder** · 22/02/2019, 15h28

Envoyé par aieeeuuuuu

De fait, vous pouvez créer un index non cluster avec colonnes incluses afin d'éviter les accès table :

Code :

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

CREATE [UNIQUE ?] NONCLUSTERED INDEX CUIX_LaTable on LaTable(time) INCLUDE(les autres colonnes)

ou, simplement indexer la colonne time, (ce qui fera un index plus léger, mais obligera à des accès table)

Je pense que l'ID est totalement inutile (ou presque) car, sauf cas exceptionnel lié à un problème dans la procédure d'insertion, les ID et les TIME sont croissants tous deux dans le même ordre.

Par conséquent, un index sur TIME uniquement ne devrait pas changer grand chose à TIME include (toutes les colonnes) car au final un BETWEEN sur TIME va retourner des lignes contigües dans des pages contigües, ce qui ne devrait pas être bien gênant par rapport à la lecture directe dans un index.

**cestpasmoinonplus** · 22/02/2019, 17h11

Merci aieeeuuuuu , ça vaut le coup que je vérifie tout ça lundi.

**cestpasmoinonplus** · 22/02/2019, 17h18

Envoyé par StringBuilder

Je pense que l'ID est totalement inutile (ou presque) car, sauf cas exceptionnel lié à un problème dans la procédure d'insertion, les ID et les TIME sont croissants tous deux dans le même ordre.

Tout juste StringBuilder. C'est tout à fait ça.
Le problème est que je dois faire avec.

Envoyé par StringBuilder

Par conséquent, un index sur TIME uniquement ne devrait pas changer grand chose à TIME include (toutes les colonnes) car au final un BETWEEN sur TIME va retourner des lignes contigües dans des pages contigües, ce qui ne devrait pas être bien gênant par rapport à la lecture directe dans un index.

Absolument.
Mais en l'état actuel des tables, j'ai constaté qu'un select sur un index créé sur les deux champs id et TIME appelé avec le hint, était beaucoup plus rapide qu'un select par défaut. Ce qui n'est pas très logique à priori.

Bon WE, moi je vais me saouler

.

**StringBuilder** · 22/02/2019, 17h36

Ce serait quand même bien de tester sans hint après avoir créé cet index :

Code sql :

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

1
2
 
create index ix_time on matable (time);

Si time est garanti unique, alors tu peux aussi tenter avec "create unique index" ça passe pas, tenter sans unique. Ca devrait pas changer grand chose de toute façon.

**StringBuilder** · 22/02/2019, 19h06

Pour information, j'ai créé la table suivante, et je l'ai peuplée avec 100 millions de lignes.

Code sql :

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
 
create table t_time
(
	id int not null primary key identity,
	[time] datetime not null,
	col_a char(8) not null,
	col_b int not null,
	col_c decimal(10,2) not null,
	col_d varbinary(8) not null
);
go
 
declare @i int;
declare @dte datetime;
 
select @i = 100000, @dte = getdate();
 
while @i > 0
begin
	with tmp ([time])
	as
	(
		select dateadd(second, @i * -1000, @dte)
		union all
		select dateadd(second, 1, tmp.[time])
		from tmp
		where tmp.time < dateadd(second, ((@i - 1) * -1000) - 1, @dte)
 
	)
	insert into t_time ([time], col_a, col_b, col_c, col_d)
	select [time], DATENAME(weekday, [time]), DATEPART(month, [time]), DATEPART(minute, [time]) + cast(DATEPART(second, [time]) as decimal) / 100, cast(DATEPART(nanosecond, [time]) as varbinary(8))
	from tmp
	option (maxrecursion 0);
 
	set @i = @i - 1;
end;
go
 
 
create index ix_time on t_time ([time]);
go

Code sql :

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

1
2
 
select count(*) from t_time

-----------
100000000

(1 ligne affectée)

La création, alimentation de la table et création de l'index ont duré 28 minutes en tout sur mon PC perso qui est un vieux Core i5-4750 (3,2 Ghz).

Version de SQL Server :

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64)
Jul 21 2018 07:47:45
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 18342: )

(1 ligne affectée)

La base + log sont sur un vieux disque de portable de 500 Go d'une lenteur abominable.

La requête suivante :

Code sql :

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

1
2
3
4
 
select count(*)
from t_time
where [time] between '2018-01-01 12:34:56.798' and '2018-02-01 23:38:51.098'

Est instantanée. (moins de 1 seconde)

-----------
126234

(1 ligne affectée)

La requête suivante :

Code sql :

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

1
2
3
4
5
 
select *
from t_time
where [time] between '2018-01-01 12:34:56.798' and '2018-06-01 23:38:51.098'
and id % 8 = 5 and col_c % 1 between .5 and .6

Est aussi instantanée. (toujours moins de 1 seconde)

id time col_a col_b col_c col_d
----------- ----------------------- -------- ----------- --------------------------------------- ------------------
64006957 2018-01-02 03:56:54.320 mardi 1 56.54 0x1312D000
64007013 2018-01-02 03:57:50.320 mardi 1 57.50 0x1312D000
64007021 2018-01-02 03:57:58.320 mardi 1 57.58 0x1312D000
64007077 2018-01-02 03:58:54.320 mardi 1 58.54 0x1312D000
64007133 2018-01-02 03:59:50.320 mardi 1 59.50 0x1312D000
64007141 2018-01-02 03:59:58.320 mardi 1 59.58 0x1312D000

[…]

64421253 2018-01-06 23:01:50.320 samedi 1 1.50 0x1312D000
64421261 2018-01-06 23:01:58.320 samedi 1 1.58 0x1312D000
64423357 2018-01-06 23:36:54.320 samedi 1 36.54 0x1312D000
64423413 2018-01-06 23:37:50.320 samedi 1 37.50 0x1312D000
64423421 2018-01-06 23:37:58.320 samedi 1 37.58 0x1312D000
64423237 2018-01-06 23:34:54.320 samedi 1 34.54 0x1312D000

(11795*lignes affectées)

J'oubliais : ça fait quelques mois que le ventilateur de mon CPU est grillé et que donc le CPU est fortement downclocké dès qu'il est un peu sollicité.

Donc avec les éléments que tu donnes, y'a clairement un souci avec ta base ou ta requête...

**cestpasmoinonplus** · 25/02/2019, 10h19

Envoyé par StringBuilder

Pourquoi le hint d'index ?
La colonne TIME est-elle indexée ? Comment est déclaré l'index ?

Pour revenir sur l'index : finalement, ça marche aussi bien sur l'index TIME, mais en l'appelant explicitement : ... FROM NomTable WITH(INDEX(Index_NomTable_TIME)) .
Bien qu'il existe, si je fais ... FROM NomTable , c'est beaucoup plus long.

**cestpasmoinonplus** · 25/02/2019, 11h03

Envoyé par StringBuilder

Pour information, j'ai créé la table suivante, et je l'ai peuplée avec 100 millions de lignes.

Désolé si je dis une conn.... mais il ne te reste plus qu'à créer une appli qui écrit une ligne dans cette table avec une régularité de 1 seconde.
Et, une fois cette appli démarrée, lancer ta requête, après avoir effacé le cache pour être sûr qu'elle ne bénéficie pas, ponctuellement, de l'optimisation du SGBD.

**StringBuilder** · 25/02/2019, 11h58

Qu'est-ce que ça donne si tu fais un REBUILD de ton index ?
Une mise à jour de statistiques ?

C'est pas normal de devoir indiquer explicitement un HINT d'index, surtout sur une requête aussi triviale.
Ca cache quelque chose d'autre.

**SQLpro** · 25/02/2019, 12h39

Quels sont les paramétrages de la base au sujet des stats ?
Est-ce que le TF 2371 est actif ?

A +

**cestpasmoinonplus** · 26/02/2019, 09h01

Envoyé par StringBuilder

Qu'est-ce que ça donne si tu fais un REBUILD de ton index ?
Une mise à jour de statistiques ?

C'est pas normal de devoir indiquer explicitement un HINT d'index, surtout sur une requête aussi triviale.
Ca cache quelque chose d'autre.

Ben, en fait j'ai essayé sur plusieurs tables, j'ai créé deux index juste pour essayer sur une table que je venais de créer, et il n'y a pas photo, la même requête lancée avec ou sans le hint c'est du simple au double pour la durée d"exécution.
Pour la mise à jour de statistiques, je ne sais pas faire, je ne sais même pas de quoi ça parle

. Je vais voir ce qu'il en est, ça ne me fera pas de mal, de toute façon.

**cestpasmoinonplus** · 26/02/2019, 09h05

Envoyé par SQLpro

Quels sont les paramétrages de la base au sujet des stats ?
Est-ce que le TF 2371 est actif ?

A +

Merci, je regarde cette histoire de stats.

**cestpasmoinonplus** · 26/02/2019, 11h55

Ok pour les statistiques. J'ai à peu près compris,. Mais je n'ai trouvé aucune allusion à TF 2371.

Question : Avez-vous touché à quelque chose sur ma base par la pensée ?

Parce que depuis ce matin ma requête avec le hint d'index est plus lente que sans le hint. Je n'en puis plus

Dois-je continuer à essayer d'y comprendre quelque chose ?