Discussion :

[Invité]

Bonjour, en fait je travaille sur un programme, j'ai un service windows qui me fait remplir une base de donnée durant toute la journée, il envoie environ 1500 variable par seconde du coup la base de donnée se grande après quelques heures, en paralléle j'ai un Gui pour filtrer les données stocké en fonction de leur type et la date

mon problème c'est le temps d’exécutions des requête qui devient de plus en plus long avec le temps, alors que quand la base de donnée est pas trop chargé il l’exécute en moins de temps, chose qui me fait beuger le programme

exemple d'une requête exécuté sur sql server management studio

la structure de mon tableau

Code :

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

1
2
3
4
5
6
7
8
9
Create table DatafromDGV
(
ID int,
Status nvarchar(100),
Tag nvarchar(100),
[Date] datetime,
Class_name nvarchar(100)
)
Go

ça a pris plus que 5 min pour l'exécuter sachant que j'utilise les 3 colonne Classe_name, Date, et Tag de temps en temps pour la requéte

[Invité]

Quelles sont les clef primaire et indexes de la table ?

[Invité]

Quelles sont les clef primaire et indexes de la table ?

j'ai pas utilisé les clef primaire et les indexes , ils sont indispensable pour le traitement des grande base de données ?

J'ai modifier le tableau que j'utilise dans la requete en mettant id comme clef primaire

Code :

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

1
2
3
4
5
6
7
8
9
Create table DatafromDGV
(
[id] [int] NOT NULL IDENTITY PRIMARY KEY,
Status nvarchar(100),
Tag nvarchar(100),
[Date] datetime,
Class_name nvarchar(100)
)
Go

ça m a pas trop aidé

[Invité]

j'ai pas utilisé les clef primaire et les indexes , ils sont indispensable pour le traitement des grande base de données ?

lol ! OUI INDISPENSABLE !!!

[escartefigue]

Bonjour,

D'après le script communiqué, il y a bien une PK mais a priori pas d'index sur les colonnes de filtrage, du coup il faut parcourir séquentiellement toute la table pour vérifier si les dates sont dans la période recherchée et si la classe correspond aux valeurs souhaitées.

quelques remarques par ailleurs
- select * est dangereux et contre-performant
- nommer une colonne avec un nom réservé SQL comme "date" est un mauvais choix qui rend les requêtes inutilement complexes, à corriger si possible
- faire un filtre like '%%' ne présente aucun intérêt
- filtrer des dates au format 'JJ/MM/AAAA' provoque une conversion de type qui nuit aux performances, à remplacer par 'AAAA-MM-JJ'
- avec 1500 lignes par secondes, la table va rapidement devenir énorme, un index sur la colonne timestamp est donc indispensable, il faudra également réfléchir au partitionnement et à la durée de rétention
- quelles sont les valeurs possibles pour class_name ? Dans l'exemple on voit 'UPS' et 'HS' soit 2 à 3 caractères, si toutes les autres valeurs sont ce genre de codes très courts, remplacez avantageusement votre nvarchar(100) par du char(3) ou un peu plus selon le besoin
- même remarque concernant la colonne status

[Boubou2020]

Bonjour,
filtrer des dates au format 'JJ/MM/AAAA' provoque une conversion de type qui nuit aux performances, à remplacer par 'AAAA-MM-JJ'

le colonne date est définit en datetime (type obsolete) et non pas datetime2 donc c'est 'AAAAMMJJ' et non pas AAAA-MM-JJ

[SQLpro]

En plus de ce qui a été dit :
1) le type DATETIME2 est plus rapide
2) avez-vous dimensionné vos espaces de stockage de données et de transaction de manière suffisante ?

Pour cette dernière question je vous invite à lancer la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
SELECT name, DatabaseName, FileName, StartTime, EndTime, Duration,
       SUM(Duration) OVER() AS TOTAL_Duration
FROM   sys.traces
       CROSS APPLY sys.fn_trace_gettable(path, DEFAULT) TRC
       JOIN sys.trace_events AS te
            ON TRC.EventClass = te.trace_event_id
WHERE  te.name LIKE '%Auto Grow'
   OR  te.name LIKE '%Auto Shrink'

Cela vous montrera si vous passez plus de temps à bricoler les fichiers de la base au niveau système, plutôt qu'à faire du relationnel.
Et par conséquent, si tel est le cas, dimensionnez vos fichiers pour un service de données de plusieurs années.

A +

[aieeeuuuuu]

Je rajouterai...

Avez-vous réellement besoin de NVARCHARIl y a de fortes chances pour que le VARCHAR soit suffisant, voire le CHAR

[escartefigue]

Je rajouterai...

Avez-vous réellement besoin de NVARCHARIl y a de fortes chances pour que le VARCHAR soit suffisant, voire le CHAR

Déjà cité

- quelles sont les valeurs possibles pour class_name ? Dans l'exemple on voit 'UPS' et 'HS' soit 2 à 3 caractères, si toutes les autres valeurs sont ce genre de codes très courts, remplacez avantageusement votre nvarchar(100) par du char(3) ou un peu plus selon le besoin
- même remarque concernant la colonne status

[Invité]

Bonjour,

D'après le script communiqué, il y a bien une PK mais a priori pas d'index sur les colonnes de filtrage, du coup il faut parcourir séquentiellement toute la table pour vérifier si les dates sont dans la période recherchée et si la classe correspond aux valeurs souhaitées.

quelques remarques par ailleurs
- select * est dangereux et contre-performant
- nommer une colonne avec un nom réservé SQL comme "date" est un mauvais choix qui rend les requêtes inutilement complexes, à corriger si possible
- faire un filtre like '%%' ne présente aucun intérêt
- filtrer des dates au format 'JJ/MM/AAAA' provoque une conversion de type qui nuit aux performances, à remplacer par 'AAAA-MM-JJ'
- avec 1500 lignes par secondes, la table va rapidement devenir énorme, un index sur la colonne timestamp est donc indispensable, il faudra également réfléchir au partitionnement et à la durée de rétention
- quelles sont les valeurs possibles pour class_name ? Dans l'exemple on voit 'UPS' et 'HS' soit 2 à 3 caractères, si toutes les autres valeurs sont ce genre de codes très courts, remplacez avantageusement votre nvarchar(100) par du char(3) ou un peu plus selon le besoin
- même remarque concernant la colonne status

--------------
.Je vous remercie pour votre réponse bien détaillé :

la j'ai changé un peut ce que vous m'avez dit, pour mon tableau c'est devenu :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Create table DatafromDGV2
(
ID [int] NOT NULL IDENTITY PRIMARY KEY,
Status char(25),
Tag char(25),
[Date_entr] datetime,
Class_name char(12),
index Classename (Class_name),
index Tagrecherche (Tag),
index Datesearch (Date_entr),
)
Go

le resultat de la requête ça prends encore vers les 26 secondes pour environs 100 000 lignes

Pouvez vous m'expliquer le message index absent sachant que j'ai indexer les 3 colonnes que j'utilise pour filtrer

[Invité]

En plus de ce qui a été dit :
1) le type DATETIME2 est plus rapide
2) avez-vous dimensionné vos espaces de stockage de données et de transaction de manière suffisante ?

Pour cette dernière question je vous invite à lancer la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
SELECT name, DatabaseName, FileName, StartTime, EndTime, Duration,
       SUM(Duration) OVER() AS TOTAL_Duration
FROM   sys.traces
       CROSS APPLY sys.fn_trace_gettable(path, DEFAULT) TRC
       JOIN sys.trace_events AS te
            ON TRC.EventClass = te.trace_event_id
WHERE  te.name LIKE '%Auto Grow'
   OR  te.name LIKE '%Auto Shrink'

Cela vous montrera si vous passez plus de temps à bricoler les fichiers de la base au niveau système, plutôt qu'à faire du relationnel.
Et par conséquent, si tel est le cas, dimensionnez vos fichiers pour un service de données de plusieurs années.

A +

Svp comment je peux analyser les résultats de la requête

[aieeeuuuuu]

essayez cet index :

Code :

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

1
2
 
CREATE INDEX IX_Date_Classe ON DatafromDGV2([Date], Class_name) INCLUDE(Tag, Status)

[Invité]

essayez cet index :

Code :

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

1
2
 
CREATE INDEX IX_Date_Classe ON DatafromDGV2([Date], Class_name) INCLUDE(Tag, Status)

---------------
C'est beaucoup plus rapide, je vous remercie

[SQLpro]

Svp comment je peux analyser les résultats de la requête

Il faut lancer la requête ! Pas l'analyser !!!!!

A +