Délai d'exécution de requête trop long

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:

---

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

Pièce jointe 537999

Quelles sont les clef primaire et indexes de la table ?

Citation:

---

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

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:

---

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é

Citation:

---

Envoyé par magmaaka

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

---

:mouarf:
lol ! OUI INDISPENSABLE !!!

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

Citation:

---

Envoyé par escartefigue

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

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:

---

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 +

Je rajouterai...

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

Citation:

---

Envoyé par aieeeuuuuu

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é ;)

Citation:

---

Envoyé par escartefigue

- 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

---

Citation:

---

Envoyé par 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

---

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

---

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

Pièce jointe 538307

Citation:

---

Envoyé par 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:

---

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

Pièce jointe 538309

essayez cet index :

Code:

---

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

---

Citation:

---

Envoyé par aieeeuuuuu

essayez cet index :

Code:

---

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

---

---

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

Citation:

---

Envoyé par magmaaka

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

Pièce jointe 538309

---

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

A +