pb de lenteur avec Index

**LeNeutrino** · 12/12/2006, 10h29

salut à tous !
j'utilise SQL Server 2000 et j'ai une table qui contient des données volumineuses (des milliers de lignes sont ramenées par mes requetes).sur recommandation du forum j'ai créé un index sur la colonne date parce que les données sont rammenées par plage, par periode ! j'ai moi même chroniométré et avant l'index l'execution de la requete et l'export vers excel se fesaient en 37 min .
apres l'index le processus s'effectue aussi en 37 min.
je ne comprends pas parce que avec l'index ça devait aller plus vite!
est que quelqu'un peut m'en dire quelque chose ?
je vous remercie

**m-mas** · 12/12/2006, 11h49

en fait des fois la création des index ralenti le process ! exemple il est déconseillé d'avoir des index dans une table si on va insérer des données !
c'est la même chose pour la sélection des données, il faut bien choisir de faire un index sur une colonne ou sur plusieurs colonnes, index ordonné, critère de choix ... tout ceci ne dépend que de ta table et des données !

Pour plus d'informations, il sera utile de lire ceci http://www.microsoft.com/france/tech...06kitDI_1.mspx (voir conseils dindexation)

**usf70** · 12/12/2006, 11h50

Essayez de voir le plan d'exécution de votre requête afin de savoir si l'index est utilisé ou pas.
Sinon, lancer l'assistant de paramètrage d'index pour voir ce qu'il vous conseille ... Il donne aussi une estimation du temps gagné pour le nouvel index.

Autre chose, si vos tables sont modifiées souvent, n'oubliez pas de recalculer les stats régulièrement.

**SQLpro** · 12/12/2006, 13h51

UN index ne sera utilisé que si vous filtrez les données. AUtrement dit si vous avez par exemple une clause WHERE avec COlonneDATE = tellevaleur.

Si vous remanez toutes les données l'index ne sera pas utilisé.

De plus ce n'est probablement pas la requête SELECT qui cause ce temps, mais la migration des donées de SQL Server à Excel. EN effet execel est une applicaion consommatrice de fichier et sa logique n'a rien à voir avec celle d'un SGBDR...

Enfin, postez votre requête, le ddl des tables en jeu et l'index créé afin que l'on puisse plus vous aider.

A +

**LeNeutrino** · 12/12/2006, 15h47

Enfin, postez votre requête, le ddl des tables en jeu et l'index créé afin que l'on puisse plus vous aider.

j'ai pas compris !
au fait ma clause where est du genre ColonneDate BETWEEN 'Date1' AND 'Date2'

**SQLpro** · 13/12/2006, 09h02

DDL : Data Definition LAnguage, c'est à dire CREATE ALTER DROP..

LIsez ceci et conformez-y vous :
http://www.developpez.net/forums/showthread.php?t=944
http://www.developpez.net/forums/showthread.php?t=96701

A +

**LeNeutrino** · 13/12/2006, 10h18

voici le script de création de ma table :

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
36
37
38
39
40
41
CREATE TABLE [dbo].[CREATE_ADJC] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[DATE] [datetime] NULL ,
	[SBS_NAME] [varchar] (250) COLLATE French_CI_AS NULL ,
	[SYMBOLIC_NAME] [varchar] (250) COLLATE French_CI_AS NULL ,
	[TARGET_CELL] [varchar] (250) COLLATE French_CI_AS NULL ,
	[NAME] [varchar] (250) COLLATE French_CI_AS NULL ,
	[TGTCELL] [varchar] (250) COLLATE French_CI_AS NULL ,
	[RXLEVMIN] [varchar] (250) COLLATE French_CI_AS NULL ,
	[HOM] [varchar] (250) COLLATE French_CI_AS NULL ,
	[HOMSOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[HOMDTIME] [varchar] (250) COLLATE French_CI_AS NULL ,
	[HOMDOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[MICROCELL] [varchar] (250) COLLATE French_CI_AS NULL ,
	[FHORLMO] [varchar] (250) COLLATE French_CI_AS NULL ,
	[TIMERFHO] [varchar] (250) COLLATE French_CI_AS NULL ,
	[PLNC] [varchar] (250) COLLATE French_CI_AS NULL ,
	[PPLNC] [varchar] (250) COLLATE French_CI_AS NULL ,
	[LEVONC] [varchar] (250) COLLATE French_CI_AS NULL ,
	[TINHBAKHO] [varchar] (250) COLLATE French_CI_AS NULL ,
	[TINHFAIHO] [varchar] (250) COLLATE French_CI_AS NULL ,
	[GSUP] [varchar] (250) COLLATE French_CI_AS NULL ,
	[GTEMPOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[GPENTIME] [varchar] (250) COLLATE French_CI_AS NULL ,
	[GRESOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[GHCSTH] [varchar] (250) COLLATE French_CI_AS NULL ,
	[GHCSPC] [varchar] (250) COLLATE French_CI_AS NULL ,
	[FULHOC] [varchar] (250) COLLATE French_CI_AS NULL ,
	[FULRXLVMOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[TRFHOM] [varchar] (250) COLLATE French_CI_AS NULL ,
	[BHOFOT] [varchar] (250) COLLATE French_CI_AS NULL ,
	[LEVHOM] [varchar] (250) COLLATE French_CI_AS NULL ,
	[QUALLEVHOM] [varchar] (250) COLLATE French_CI_AS NULL ,
	[USG] [varchar] (250) COLLATE French_CI_AS NULL ,
	[NCGRESOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[NCGTEMPOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[NCGPENTIME] [varchar] (250) COLLATE French_CI_AS NULL ,
	[NCC1THADJC] [varchar] (250) COLLATE French_CI_AS NULL ,
	[TRFHORXLVMOFF] [varchar] (250) COLLATE French_CI_AS NULL ,
	[BARINSYSINF10] [varchar] (250) COLLATE French_CI_AS NULL 
) ON [PRIMARY]

Le script de l'index

Code :

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

1
2
3
4
5
6
7
8
 
CREATE 
  INDEX [date] ON [dbo].[CREATE_ADJC] ([DATE])
WITH
    PAD_INDEX
    ,FILLFACTOR = 80
    ,DROP_EXISTING
ON [PRIMARY]

je signale qu'un index est automatiquement créé sur ma colonne ID comme suis :

Code :

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

1
2
3
4
5
6
 
CREATE UNIQUE CLUSTERED
  INDEX [PK_CREATE_ADJC] ON [dbo].[CREATE_ADJC] ([ID])
WITH
    DROP_EXISTING
ON [PRIMARY]

voici la requete que j'execute

Code :

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

1
2
3
4
 
SELECT * 
FROM CREATE_ADJC 
WHERE DATE BETWEEN '12/12/2006 00:00:00' AND '13/12/2006 00:00:00'

**SQLpro** · 13/12/2006, 14h33

Votre modèle de table est particulièrement épouvantable !

En effet, allez -vous réellement mettre 250 caarctères dans chacune des colonnes qui la compose ? Dans un tel cas vous aurez la surprise de voir que SQL Server va vous rejetez l'insertion? EN effet une ligne ne peut pas dépasser 8000 octets, or vous en êtes à 10000 !
DE plus le type VARCHAR provoque certains effets de bords particulier qui ralentit les lectures, notamment si de fréquentes mises à jour sont faites dans la table. Bref, commencez par revoir votre table et faire des colones judicieuses, cela s'apelle la modélisation des données...

Avez-vous besoin de toutes ces colones ? Certaines ne contienent -elles pas les mêmes donées répétées de lignes en ligne ? SI c'est le cas votre modèle de données viole certaines formes normales et impose des lectures inutiles qu'une jointure avec diférentes tables filles auraient évitées.
Cotrairement à une idée reçue, en mettant toutes vos données dans une unique table vous irez beaucoup moins vite en lecture qu'en éclatant judicieusement votre modèle de donées dans des tables filles.

Dans le même sens , je ne voit pas de création de clef primaire, bien que vous indiquiez qu'il y a un index unique CLUSTERED ce qui n'est tout de même pas la même chose.

ENfin votre SELECT s'appui sur des dates dont le format n'est pas conforme. VOus devez utiliser le format suivant :
AAAAMMJJ (iso).
DE plus le SELECT * oblige à relire toutes les données de la table alors que peut être seules certaines colonnes sont intéressantes. EVitez toujours le SELECT * même si vous devez prendre toutes les colonnes.

POur terminer, sachez qu'un index ne sera utilisé que si le nombre de lignes que l'on doit retourner est franchement moindre que le nombre de lignes de le table. AUtrement dit, la valeur de filtrage doit être sufisament sélective (moins de 10% environs) afin que l'index soit utilisé.
En efft, la lecture d'un index est plus couteuse que la lecture d'une table.

A +

**WOLO Laurent** · 13/12/2006, 16h48

Il vous faut aussi poster le plan d'execution de la requête sous Query Analyser.
Etes-vous sûr que c'est la reqête qui consomme le plus de temps ?
Quelle est la procédure que vous utilisez pour importer vos données vers excel ?
Je vous sugère d'importer le resultat en fichier texte avec l'extension .csv.
Vous pourez l'ouvrir sous excel sans soucis.
Que dites-vous ?

**ZERS** · 14/12/2006, 09h12

Salut,

soyons clairs et factuels.

Au lieu de faire un

Code :

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

 SELECT *

, lance plutot :

Code :

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

1
2
SELECT DISTINCT [ID], [NAME] FROM CREATE_ADJC 
WHERE DATE BETWEEN '20061212' AND '20061213'

sans index et avec index et compare les deux temps de traitement.
Si ils sont quasi identiques, regarde ensuite si ton index est utilisé en jettant un coup d'oeil sur le plan d'exécution.

Une dernière remarque sur la forme : il est plus que déconseillé d'utiliser les mots clefs SQL SERVER comme noms de colonnes... Bref, ID, DATE, NAME, .... sont à proscrire...
a+

**WOLO Laurent** · 14/12/2006, 11h01

Mais avant de penser à faire une optimisation, il faut chercher à connaitre là où se trouver le goulot d'étranglement :
Est ce au niveau de la reqette SQL ?
Est ce au niveau des échanges entre SQL Serveur et Excel (DDE ou DTS) ?

Sans quoi, vous perdrez du temps.

**LeNeutrino** · 19/12/2006, 11h48

En effet, allez -vous réellement mettre 250 caarctères dans chacune des colonnes qui la compose ? Dans un tel cas vous aurez la surprise de voir que SQL Server va vous rejetez l'insertion? EN effet une ligne ne peut pas dépasser 8000 octets, or vous en êtes à 10000 !

je traite beaucoup de lignes et il y'a des colonnes qui ont des valeurs assez grandes.Et comme le nombre de colonnes est aussi grand dans mon script de creation de ma BD j'ai fixé la valeur à varchar(250). je peux toute fois diminuer ! je vais voir ça de plus pres.

Avez-vous besoin de toutes ces colones ? Certaines ne contienent -elles pas les mêmes donées répétées de lignes en ligne ? SI c'est le cas votre modèle de données viole certaines formes normales et impose des lectures inutiles qu'une jointure avec diférentes tables filles auraient évitées.

oui j'ai besoin de toutes ces colonnes parce que le service pour le quel je fais ce programme a besoin de voir les paramatres de toutes ces colonnes ! meme si ce sont les memes valeurs pour toutes les lignes. j'ai dans ma BD 54 tables sans compter mes tables de parametrage et autres.
54 tables parce qu'il existe 54 types de lignes et pour chaque type il y'a un nombre de colonne different .Donc je range mes lignes par type ("CREATE_ADJC","CREATE_BTS","SET_HAND",.....)

Dans le même sens , je ne voit pas de création de clef primaire, bien que vous indiquiez qu'il y a un index unique CLUSTERED ce qui n'est tout de même pas la même chose.

mes tables contiennent bel et bien une clé primaire que j'appelle "ID" et qui est de type int et autoincrémentable.

EVitez toujours le SELECT * même si vous devez prendre toutes les colonnes.

c'est pas possible parce que le nombre de colonnes est exhorbitant et variable par type de lignes (par Table je veux dire) à rapporter. on a des lignes avec plus de 100 connes . et mes requetes renvoient generalement des milliers de lignes.
Pour finir j'ai trouvé ma recette ! je pense vider toutes mes tables (les sauvegarder) et faire mes INSERT et mes SELECT ensuite. mais c'est là toute une etude egalement parce que j'ai besoin de ces lignes pour certaines comparaisons par date.
je vous remercie tous pour votre apport et je tiendrai compte de toutes vos remarques et suggestions.

pb de lenteur avec Index

MS SQL Server

Discussions similaires

Partager

Partager