Optimisation de table (select et update très lents)

[ddrmax]

Bonjour, j'ai besoin de vos connaissances concernant l’optimisation d'une table SQL (sous SQL express 2005) ->SDBG capable de changer dans le futur


Présentation du problème:
Base de donnée SQL Server 2005
Taille ~360Mo
nombre d’utilisateurs : 16 maximum, en production : 4
nombre de tables 5:
Parametres : 16 colonnes, maxi 300 enregistrements
Messages : 10 colonnes, maxi 5000 enregistrements
POI : 6 colonnes, maxi 300 enregistrements
MAV: 6 colonnes, maxi 5000 enregistrements
Historique: 7 colonnes, illimité, en production (~2.5 millions d'enregistrements)
Historique contient:
ID, Type: int, contient des doublons
Date_Heure, Type: datetime, contient des doublons
Latitude, Type: nchar(20), contient des doublons
Longitude, Type: nchar(20), contient des doublons
Type , Type: nchar(30), contient des doublons
Rue, Type: nvarchar(255), contient des doublons
Ville, Type: nvarchar(255), contient des doublons

Exemple de requettes SQL

Code :

1
2
 
SELECT * FROM Historique WHERE (Rue = '') AND (Type = 'GPS Auto')

Code :

1
2
 
UPDATE Historique SET Rue='Rue remontée', Ville='Ville remontée' WHERE Id=Id_à_modifier AND Date_Heure='Date et Heure à modifier'

Le gros du problème est que pour une recherche (Select) il me faut quasiment 1 minute pour avoir un retour (Requette SQL si dessus).
L'autre est que pour un update il me faut plus d'1 minute par update (au bout d'une heure j'e nai que 1200 enregistrements validés (800 si on ne compte pas les valeurs de retour pour rue quand il sont vides)

Merci de votre aide

[StringBuilder]

*Meeeeeeep* !

Bon, ben déjà, mauvaise nouvelle : tout est à refaire !

Il manque une clé primaire à ta table historique. Même si elle est dénuée de sens, elle est nécessaire pour certains traitement, donc il faut la créer.
Ton couple "id + date" semble être une bonne clé alternative. Est-il défini comme tel ? Y a-t-il une contrainte unique dessus ? Et un index unique ? Clustered ? => SURTOUT PAS ! (d'où le ID interne comme PK, qui lui, sera clustered)

Ensuite, il faut créer une table "type", avec un id int, et ta table historique doit porter une fk qui pointe sur la table type, au lieu de te trimbaler des varchar.
Latitude et longitude devrait être un float ou un decimal.
Ville devrait suivre le même principe que la table type.

Et même mieux, rue aussi.
Historique ne devrait faire un lien que vers rue,qui elle-même fait un lien vers ville : en effet "rue des roses", c'est pas la même rue qu'elle soit à Paris ou à Lyon. Donc les données n'ont pas à être identiques !

Ensuite, une fois tes nvarchar et autres nchar remplacés par des types adéquats, ça ira bien mieux.

Et enfin, tu pourras songer à indexer certaines colonnes en fonction des requêtes que tu fais généralement sur ta table.

[ddrmax]

Envoyé par StringBuilder

Bon, ben déjà, mauvaise nouvelle : tout est à refaire !

De ce coté la j'ai une totale maitrise sur le code des softs qui les utilisent.
donc je peu me permettre ce genre de choses.

Envoyé par StringBuilder

Il manque une clé primaire à ta table historique. Même si elle est dénuée de sens, elle est nécessaire pour certains traitement, donc il faut la créer.
Ton couple "id + date" semble être une bonne clé alternative. Est-il défini comme tel ? Y a-t-il une contrainte unique dessus ? Et un index unique ? Clustered ? => SURTOUT PAS ! (d'où le ID interne comme PK, qui lui, sera clustered)

c'est vrai que j'ai aucun index, mais en fesant un test sur un backup de la BDD client, j'ai ajouté id en clé primaire. et un index sur type (Id est en IX et Type en PK). les updates semblent un poil plus rapides (je les voies en temps reel)

Envoyé par StringBuilder

Ensuite, il faut créer une table "type", avec un id int, et ta table historique doit porter une fk qui pointe sur la table type, au lieu de te trimbaler des varchar.

Sur un nouveau Soft j'ai fait un truc dans le genre la BDD est en access
les commentaires sont dans une autre table et les index sont des GUID

Envoyé par StringBuilder

Latitude et longitude devrait être un float ou un decimal.
Ville devrait suivre le même principe que la table type.
Et même mieux, rue aussi.

No Problem de ce coté, je vais devoir revoir la partie jonction dans SQL

Envoyé par StringBuilder

Historique ne devrait faire un lien que vers rue,qui elle-même fait un lien vers ville : en effet "rue des roses", c'est pas la même rue qu'elle soit à Paris ou à Lyon. Donc les données n'ont pas à être identiques !

par contre chose a voir, des fois j'ai le numéro de rue dans le resultat (même si c'est inutile dans mon cas, donc pour une même rue je vais avoir X idenifiants.

Envoyé par StringBuilder

Ensuite, une fois tes nvarchar et autres nchar remplacés par des types adéquats, ça ira bien mieux.

pour les rues, la fonction me retourne parfois plusieures rues (dans le cadre d'un croisement par exemple)
pour ville je peut passer sur du nchar(taille du non de ville la plu grande de france)

Envoyé par StringBuilder

Et enfin, tu pourras songer à indexer certaines colonnes en fonction des requêtes que tu fais généralement sur ta table.

A voir en ressortant toutes mes requêtes de mon code applicatif



BIGEDIT:
Si j'ai bien compris, je devrait avoir une table un peut dans ce genre (ou peut etre que j'ai tout faux):


Table Historique:
Index (clé primaire, type géré automatiquement par le SGDB)
ID, Type: int, contient des doublons
Date_Heure, Type: datetime, contient des doublons
Type , Type: char(1), contient des doublons
Complement , Type: celui de l'Index de la table Complement , ne contient pas de doublons -> index 0 si pas de positions GPS ?


Table Complement :
Index (clé primaire, type géré automatiquement par le SGDB)
Latitude, Type: float, contient des doublons
Longitude, Type: float, contient des doublons
Rue, Type: ?, contient des doublons
Ville, Type: Type: celui de l'Index de la table Villes, contient des doublons



Table Villes:
Index (clé primaire, type géré automatiquement par le SGDB)
Ville, Type: nvarchar(255) , ne contient pas de doublons


Table Types:
Index char (1) , ne contient pas de doublons
Type , Type: nchar(30) , ne contient pas de doublons

EDIT 2:
http://www.culture-generale.fr/geogr...-le-plus-court

Le nom de ville le plus long de France est celui d’une commune de la Marne de 592 habitants (en 1999) :Saint-Remy-en-Bouzemont-Saint-Genest-et-Isson 45 caractères !!!

J’ai beau le lire plusieur fois, je n’arrive pas à le retenir en tout cas

Le nom de ville le plus court de France est celui d’une commune de la Somme de 89 habitants (en 1999) : Y (prononcer i)

Read more: http://www.culture-generale.fr/geogr...#ixzz1zrCLepJh

Donc:

Table Villes:
Index (clé primaire, type géré automatiquement par le SGDB)
Ville, Type: nvarchar(50) , ne contient pas de doublons
EDIT 3:
voici une image en expliquant mon architecture logicielle:


Les select pour le viewver sont
POI,Parametres: Simple Select sans conditions where
MAV, Messages, Historique :
- ID (facultatif)
- Date_Heure (une de début et une de fin, obligatoires)
- Type (facultatif)
- Rue, Ville (facultatifs, Historique seulement)
- Secteur (nouvelle info, facultatif, Historique seulement)

Les select pour le Serveur sont
Parametres: Simple Select sans conditions where

Les select pour le Patcheur sont
Historique:
- Type : "GPS"
- Rue : ""
EDIT 4: Le serveur ajoute entre 5 et 100 lignes d'historique par minute dont 90 % sont des positions GPS (en comptant les positions invalides: GPS en synchronisation ou hors couverture, on oscille entre 50 et 80% de positions GPS valides)

[ddrmax]

Désolé du doublon mais je ne sais pour quelle raison ma page perso a été vidée, donc voici le nouveau lien de l'edit 3

[SQLpro]

Pourquoi utilisez vous systématiquement du nchar et du nvarchar ? Avez vous prévu de stoker des données avec les alphabets mandarin, japonais (katakana, hiragana...) ou encore thaï ?

Quel est le type de données de la colonne complément ? Parce que si c'est un LOB...

Comment mesurez vous vos temps de réponse ?
Avec SET STATISTICS TIME ON ???

A +

[ddrmax]

Envoyé par SQLpro

Pourquoi utilisez vous systématiquement du nchar et du nvarchar ? Avez vous prévu de stoker des données avec les alphabets mandarin, japonais (katakana, hiragana...) ou encore thaï ?

Quel est le type de données de la colonne complément ? Parce que si c'est un LOB...

Comment mesurez vous vos temps de réponse ?
Avec SET STATISTICS TIME ON ???

A +

Si j'utilise du text ou ntext, les infos sont inutilisables sur C++ builder (DBgrid standard ou JVCL, car tout il affiche Widetext a chaque fois.
Sinon quelle est la différence entre n(var)char(X) et (var)char(X)?

EDIT : nchar -> ANSI / nchar -> Unicode
Je peut donc passer mes colonnes en char ou varchar
je se savais pas la différence

Mes temps de réponse sont basés sur le calcul fait dans builder (Gettickcount)qui vérifie chaque long traitement (recherche de rue, exécution du Select et exécution de l'insert)
select TOP 1000 * ......
Temps d'execution (~50s) Min : 47569 ms Max: 52145ms (sur 9 itérations)
UPDATE.....
Temps d'execution (~85s) Min : 59375 ms Max: 102981 (sur ~32000 itérations)

J'ai aussi créé une fonction pour nettoyer les nom de rue (plus de chiffres au debut) pour faciliter la recherche

[SQLpro]

Si vous faites un traitement itératif sur des données de nature ensemblistes, ne vous étonnez pas des lenteurs. Elles ne sont pas liées au SGBDR, mais à la façon dont vous traitez les données et en particulier aux aller et retour réseau entre le SGBDR et votre code...

Commencez par nous détailler la nature de votre traitement, et on vous conseillera !

A+

[ddrmax]

Test sur un serveur HP avec un processeur INTEL XEON (3.0GHz) et 2 Go de RAM

Exécution

Code :

SELECT TOP 1000 * FROM HISTORIQUE WHERE TYPE = 'GPS Auto' AND RUE=''

Instantanée
Sur mon PC avec un Backup fraîchement mis en place (P4 3GHz HT et 2Go RAM)
environ 2 secondes (remontée du compteur SSMSS)

Exécution

Code :

UPDATE Historique SET Rue='RESA', Ville='CEEFF' WHERE Id=102 AND Date_Heure='06/03/2012 16:24:50'

Temps d'analyse et de compilation de SQL Server :
, Temps UC = 0*ms, temps écoulé = 1*ms.
Temps d'analyse et de compilation de SQL Server :
, Temps UC = 0*ms, temps écoulé = 0*ms.

SQL Server \endash Temps d'exécution*:
, Temps UC = 469*ms, temps écoulé = 15098*ms.

(1*ligne(s) affectée(s))

sur mon PC

Temps d'analyse et de compilation de SQL Server :
, Temps UC = 0*ms, temps écoulé = 19*ms.
Temps d'analyse et de compilation de SQL Server :
, Temps UC = 0*ms, temps écoulé = 0*ms.

SQL Server \endash Temps d'exécution*:
, Temps UC = 812*ms, temps écoulé = 3858*ms.

(1*ligne(s) affectée(s))

La table d est présentée dans mon premier Post, Cette Base de données n'est pas de ma conception,mon soft a été conçu pour pouvoir être rétrocompatible pour faciliter la transition de l'ancien système, seul l'ajout des colonnes RUE et VILLE, ont été rajoutées par nos soins.

se rapporter au premier post pour tout autre information.
Pour info tout le traitement annexe a un temps sous la seconde par itération

EDIT: Je comprends pas pourquoi mon update Select soit plus rapide que lors de mes tests, en relançant des tests ce matin j'ai a peu près le même résultat en exécution, mais le problème chez le client est toujours présent.
Pour info je trouve que l'update est toujours trop long et au bout d'une centaine d'itérations, la performance chute vraiment

[SQLpro]

Indiquez nous la structure de la table historique et de toutes les tables qui lui sont liées (IR) sous forme DDL et n'oubliez pas TOUS les index.

Spécifiez comment sont structurés vos fichiers pour la base de données.

Que donne la requête :

Code :

1
2
3
4
SELECT * 
FROM   sys.dm_exec_query_stats AS QS
       CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE  text LIKE 'UPDATE Historique SET %'

À partir de cela nous pourrons trouver ce qui cloche.

A +

[ddrmax]

La commande ne renvoie aucun résultat, en retirant la clause "where", il me renvoie beaucoup de lignes dont le "text" contient beaucoup "SELECT" et quelques "CREATE" mais pas de "INSERT" ou "UPDATE"
J'ai pas de schema de la base de données car il n'y a aucune relation entre les tables.

[CinePhil]

Envoyé par ddrmax

J'ai pas de schema de la base de données car il n'y a aucune relation entre les tables.

Ça sert à quoi de faire une base de données alors ?

C'est comme si après un déménagement vous aviez vidé au hasard les cartons dans els placards. Le matin vous allez chercher vos chaussettes peut-être dans le coffre à jouet de votre enfant ou dans le garage !

Une base de données se construit à partir de règles de gestion des données qui décrivent les associations entre des concepts sémantiques. Une base de données sans associations entre les tables, c'est le bordel !

[ddrmax]

Envoyé par CinePhil

Ça sert à quoi de faire une base de données alors ?

C'est comme si après un déménagement vous aviez vidé au hasard les cartons dans els placards. Le matin vous allez chercher vos chaussettes peut-être dans le coffre à jouet de votre enfant ou dans le garage !

Une base de données se construit à partir de règles de gestion des données qui décrivent les associations entre des concepts sémantiques. Une base de données sans associations entre les tables, c'est le bordel !

Ma base de données actuelle Contient 4 tables sans aucunes relations, elle sont indépendantes.
Il n'y a pas de relation directe entre ma table historique et POI ou ma table paramètres et historique

On peu dire que leur relation est gérée par mon soft (un id peut exister dans l'historique mais ne jamais exister dans paramètres (ex ID 1000 alors que je ne gère des paramètres uniquement jusqu’à 255)

[SQLpro]

C'est le temps UC (donc traitement) qui est important. Le temps écoulé n'est pas le plus important, car il dépend de la volumétrie des données à retourner ainsi que des ressources disponible sur le serveur au moment ou s'exécute la requête...

Au final votre serveur est donc 2 fois plus rapide pour traiter la requête....

A +