Amélioration de performances d'une base SQL Server 2000

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : Amélioration de performances d'une base SQL Server 2000

Fry85

Bonjour à tous,

je suis actuellemnt dans les phases de tests grandeur nature de mon application php / SQL Server 2000.
Je dois notamment gérer une table d'une 20aines de champs (15 varchar, 3 dates ..., un index sur le n° de tel et un sur les autre champs de type varchar+les dates).
Elle contient les coordonnées de prospects, j'ai créé une procédure stockée permettant la mise à jour de ces données, elle-même contenue dans un travail planifié. Elle est chargée d'insérer dans une table temporaire tous les prospects récupérés dans un fichier texte à partir d'une autre application puis de les insérer en bloc (BULK INSERT) dans une table temporaire. Ensuite elle lis chaque ligne de cette table et vérifie que le numéro de téléphone n'est pas déjà enregistré dans la véritable table :
- s'il est présent et coché au rebut (BIT à 1), elle regarde alors si la ville, le code postal et le nom ne sont pas tout à fait ressemblant à ce qui est déjà présent (NOT LIKE), alors elle met à jour et enlève ce prospect des rebuts (BIT à 0), sinon elle met seulement à jour sans décoché le bit ;
- s'il est présent mais pas au rebut, elle le met à jour tout simplement.

Cette procédure (un peu compliqué mais nécessaire) permet d'éviter les doublons de n° de tél ainsi que des codes postaux non souhaités etc.

Lors du lancement de ces travaux, les fichiers de départ contiennent entre 10 000 et 70 000 lignes (donc prospects)
Ainsi la mise à jour d'une base vide (donc insertion dans les premiers temps) me donne ces résultats : 13 800 prospects dans la base en 34 min
puis 18 000 en 1h 20 etc

La mise à jour prend de plus en plus de temps, c'est tout à fait compréhensible puisqu'avant d'insérer une nouvelle ligne, la procédure doit scanner de plus en plus de données.

J'en arrive maintenant à quelques 300 000 prospects dans la base et la dernière mise à jour me donne : 57 800 prospects en 19h

Voilà à peu près mes résultats, mais j'aimerais savoir s'il était possible de les booster encore.
Je précise que SQL Server tourne sur un serveur cadencé à 3Ghz avec 1Go de RAM dédié à cette base et à la petite application php servant d'interface sous Win Server 2003. Il y aussi 2 DD de 73Go monté en RAID (enfin il me semble que c'est ce que HP nous a dit).

Enfin la base fait 90 Mo pour l'instant et le fichier de log en est à 750 Mo (je sais qu'on peut le nettoyer, comment faire et celà va-t-il regagner un peu de temps ??)

Merci d'avoir lu jusqu'ici, et de votre aide

Publicité

Arjuna

Aircraft Ident.: F-MBSD

Essaie de faire un UPDATE FROM, en faisant une jointure plutôt qu'un curseur sur chaque ligne du fichier puis une requête par numéro de téléphone.
Ca devrait grandement améliorer les résultats

Par contre, là où ça va être chaud, c'est ensuite de retrouver les lignes à flager dans ta table temporaire...

Fry85

Ok, je te remercie on planche dessus voir si c possible, on risque aussi de faire la même chose pour les INSERT, mais y'a les critères à respecter et voir comment on peut mettre à jour seulement les lignes non insérées, je pense qu'on doit faire la mise à jour avant l'insertion.
Sinon t'entends koi par :
"Par contre, là où ça va être chaud, c'est ensuite de retrouver les lignes à flager dans ta table temporaire..."

Et aussi, comment faire pour nettoyer le log
Merci

Arjuna

Aircraft Ident.: F-MBSD

Ben c'est justement ce que tu dis : c'est que mettons que t'as un traîtement du genre : (ça ressemble un peu à ce dont tu parles)

J'ai copies les lignes de la table A ayant le flag 0 et qui ne sont pas présentes dans la table B, puis le passe le flag à 1.
Je ne traîte par contre pas les lignes dont le flag est à 1.

=> Résultat, si tu fais le traîtement avant de passer au flag à 1, ben quand tu refais une requête pour trouver lesquelles tu viens de traîter, elles ne répondent plus au critère. Et vice versa, si tu les flag avant, alors elle ne répondent plus au critère de recopie.

J'ai l'impression que ce que tu décris contient ce genre de problématique

Sinon, pour la maintenance des logs, je ne me souviens plus du tout. Normalement, ça se fait via les plans de maintenance de SQL Server, au moment du backup. Par contre, il me semble que l'action "de base" qu'il fait lui ne suffit pas, mais je ne me souviens plus dans le détail
J'ai galéré un coup avec pendant 2 jours, et depuis je ne m'en occupe plus, je délègue

Arjuna

Aircraft Ident.: F-MBSD

Bon, je regarde ton truc

Alors, j'ai besoin d'explications :

- s'il est présent et coché au rebut (BIT à 1), elle regarde alors si la ville, le code postal et le nom ne sont pas tout à fait ressemblant à ce qui est déjà présent (NOT LIKE), alors elle met à jour et enlève ce prospect des rebuts (BIT à 0), sinon elle met seulement à jour sans décoché le bit ;

J'ai besoin d'explications quant au point en gras, parceque je ne sais pas ce que tu fais comme traîtement. Pour le moment, je fais simplement faire "#tmp.cp like tbl.cp" vu que je ne vois pas ce que tu entends pas des lignes et "vraiment ressemblant"

Sinon, je viens de voir... 1 Go de RAM pour un serveur de base de données... Comment dire... Votre société est vraiment sur la paille ?

Arjuna

Aircraft Ident.: F-MBSD

sinon euh...

tu fais que des update ?

Arjuna

Aircraft Ident.: F-MBSD

C'est pas ça que tu veux en fait ? (en très simple finalement)

Code :

begin tran
select * from prospect
select * from prospect_tmp
update prospect
set prospect.nom = prospect_tmp.nom, prospect.cp = prospect_tmp.cp, prospect.ville = prospect_tmp.ville, prospect.rebut = 0
from prospect_tmp
where prospect.numtel = prospect_tmp.numtel
and ((prospect.rebut = 1 and not (prospect.ville like prospect_tmp.ville and prospect.nom like prospect_tmp.nom and prospect.cp like prospect_tmp.cp))
or prospect.rebut = 0)
select * from prospect
rollback

(sans le rollback évidement, mais c'est chiant de tout se retaper à chaque fois )

Sortie :

Code :

numtel nom cp ville rebut
---------- ---------- ---------- ---------- -----
0123456789 toto5 12345 paris 0
7410369852 test 98745 limoges 1
8521479630 truc 65478 nice 1
9876543210 titi 54321 lille 0
(4 ligne(s) affectée(s))
numtel nom cp ville
---------- ---------- ---------- ----------
0123456789 toto 12345 paris
7410369852 chose 98745 limoges
8521479630 truc 65478 nice
9842987527 tintin 54321 lille
9876543210 yo 65745 nantes
(5 ligne(s) affectée(s))
(3 ligne(s) affectée(s))
numtel nom cp ville rebut
---------- ---------- ---------- ---------- -----
0123456789 toto 12345 paris 0
7410369852 chose 98745 limoges 0
8521479630 truc 65478 nice 1
9876543210 yo 65745 nantes 0
(4 ligne(s) affectée(s))

Fry85

Merci pour ta soluce, on avait pas pensé à la clause OR qui va nous permettre de combiner 3 UPDATE en 1
Après il reste le INSERT, mais s'il est exécuté ensuite les insertion des lignes déjà mises à jour seront impossibles !

Et pour le Go de RAM ben non c juste que c t fourni avec mais surement qu'on augmentera ca si apparement c'est un peu limite
Oki merci

Fry85

Je viens de découvrir un autre problème :
Après mes UPDATE (qui fonctionnent me semble-t-il), je fais un INSERT INTO... SELECT... pour insérer tous les prospects de la table temporaire qui ne sont pas encore enregistrés.
Mais pour donner un index unique, j'ai placé un trigger sur cette table qui se charge de trouver les emplacements d'id vides.
Mais apparement quand j'exécute ma procédure, le trigger n'est appelé qu'une seule fois alors qu'il faudrait qu'il le soit pour chaque ligne insérée.
Est-ce que c'est possible qu'il y ait un problème à ce niveau et si oui comment le contourner ??
Merci.

Arjuna

Aircraft Ident.: F-MBSD

Il faut faire un curseur dans ton trigger, qui va parcourir la table "inserted".
A moins que tu puisses t'en tirer avec une jointure entre inserted et une autre table que tu mets à jour.

Publicité

Fry85

Bon on part sur la jointure, mais le problème c'est qu'on ne sait pas comment trouver un certain nombre d'index qui ne sont pas utilisés dans la table Prospect.

Y'aurait la soluce de créer une table bidon avec tous les id possible (de 1 à 99 999 999) et de sélectionner dans cette liste, ceux qui ne sont présents ni dans Prospect ni dans la temporaire d'id prévu pour faire la jointure avec Inserted. Mais comment créer cette table bidon sans boucle!!

pour l'instant on arrive à sélectionner seulement les id non utilisés qui sont précédés par un id existant avec ça :

Code :

SELECT MIN(p.idProspect + 1)
FROM Prospect p
WHERE p.idProspect BETWEEN 1 AND 99999999
AND NOT EXISTS (SELECT p1.idProspect FROM Prospect p1 WHERE p1.idProspect = p.idProspect + 1)

Mais si plusieurs trous se suivent on ne peut pas les trouver comme ça. Alors il faudrait aussi comparer avec les id entrés dans la temporaire. Sauf que :

Code :

SELECT MIN((p.idProspect + 1)(t.idProspect + 1))
FROM Prospect p, #tmpProspect t
... (avec qq conditions en plus je suppose)

eh ben c impossible !!

Une solution ??

Message édité par Fry85 le 28-07-2005 à 11:31:35

Arjuna

Aircraft Ident.: F-MBSD

Je comprends rien à tes explications

Un exemple vaut mieu qu'un long discours :
-> 10 lignes de chaque table en partant du principe que "c'est toute la table"
-> Et le résultat attendu

Si j'ai bien compris, tu veux extraire la liste des ID disponibles dans ta table histoire par exemple de boucher les trous avec les insert futurs c'est ça ?

Arjuna

Aircraft Ident.: F-MBSD

hmmm... je viens de comprendre ce que tu veux faire...

c'est lors du insert des nouvelles lignes, tu ne veux pas planter si l'id est déjà pris c'est ça ?

pourquoi tu n'utilises pas simplement un auto-incrément ? c'est pas bien gênant si t'as des trous.

Arjuna

Aircraft Ident.: F-MBSD

En répondant à ce topic :

http://forum.hardware.fr/hardwaref [...] m#t1163021

Je viens d'avoir l'idée du sciècle !

=> Tu passes par un numéro auto, qui va garder les trous.
=> Tu le changes ensuite en simple champ numérique.
=> Tu crées un champ numéroauto dans la table (lui, il n'y aura plus de trous)
=> Tu mets à jour l'ancienne clé avec les valeurs de cette nouvelle colonne.
=> Tu détruits cette nouvelle colonne !

Evidement, tu pourras pas faire ça dans un trigger, je doute qu'il accepte que tu fasses un ALTER TABLE (surtout si c'est dans la table du trigger )

Fry85

Ouch, g pas tt pigé à ton idée de génie, tu peux me la refaire la ?? pke ... :??:
Quand est-ce que tu met le numéro auto ?
Quand est-ce que tu le change en simple champ ? ...

Sinon je creuse tjs la piste d'une jointure, sauf qu'on s'est rendu compte qu'on avait pas de colonne permettant cette jointure, donc pour une insertion de 16 lignes, la jointure nous sort 256 lignes -> y'a pas une requête qui empêche ça !
ex :
table idTrous table Prospect
13 paul,...
45 jacques,...
231 jean,....
686 yves,...
53145 pierre,...

et avoir comme table résultat :
13,paul,...
45,jacques,...
231,jean,....
686,yves,...
53145,pierre,...

Message édité par Fry85 le 29-07-2005 à 09:51:43

Arjuna

Aircraft Ident.: F-MBSD

Ma solution, en couleurs (enfin... en script )

Code :

-- Fait gaffe avant de lancer le script que ça n'efface pas tes tables :D
drop table fichier
go
drop table prospects
go
create table prospects (id numeric IDENTITY (1, 1) NOT NULL, nom varchar(50))
go
create table fichier (nom varchar(50))
go
-- Insertion de données dans la base, avec un peu de vie (génération de trous)
insert into prospects (nom) values ('André')
insert into prospects (nom) values ('Bill')
insert into prospects (nom) values ('')
delete prospects where id = 3
insert into prospects (nom) values ('Camille')
insert into prospects (nom) values ('')
delete prospects where id = 5
insert into prospects (nom) values ('')
delete prospects where id = 6
insert into prospects (nom) values ('')
delete prospects where id = 7
insert into prospects (nom) values ('')
delete prospects where id = 8
insert into prospects (nom) values ('Denis')
insert into prospects (nom) values ('Emilie')
insert into prospects (nom) values ('')
delete prospects where id = 11
insert into prospects (nom) values ('Farid')
insert into prospects (nom) values ('')
delete prospects where id = 13
insert into prospects (nom) values ('')
delete prospects where id = 14
insert into prospects (nom) values ('')
delete prospects where id = 15
insert into prospects (nom) values ('')
delete prospects where id = 16
insert into prospects (nom) values ('')
delete prospects where id = 17
insert into prospects (nom) values ('')
delete prospects where id = 18
insert into prospects (nom) values ('')
delete prospects where id = 19
insert into prospects (nom) values ('')
delete prospects where id = 20
insert into prospects (nom) values ('')
delete prospects where id = 21
insert into prospects (nom) values ('')
delete prospects where id = 22
insert into prospects (nom) values ('')
delete prospects where id = 23
insert into prospects (nom) values ('')
delete prospects where id = 24
insert into prospects (nom) values ('Gérard')
insert into prospects (nom) values ('Hervé')
insert into prospects (nom) values ('')
delete prospects where id = 27
insert into prospects (nom) values ('')
delete prospects where id = 28
insert into prospects (nom) values ('Isidore')
insert into prospects (nom) values ('Julie')
-- Un fichier arrive
insert into fichier values ('Kader')
insert into fichier values ('Laëtitia')
insert into fichier values ('Moïse')
insert into fichier values ('Noémie')
insert into fichier values ('Oscar')
insert into fichier values ('Pascal')
insert into fichier values ('Quentin')
insert into fichier values ('Ridoine')
insert into fichier values ('Sophie')
insert into fichier values ('Tony')
-- Du coup on le copie dans la table prospects sans se soucier des id
insert into prospects (nom)
select nom
from prospects
-- Maintenant, on passe aux choses sérieuses !
-- On backup l'ancien ID
alter table prospects add old_id numeric
go
update prospects set old_id = id
go
-- On détruit l'ID actuel
alter table prospects drop column id
go
-- Et on en refait un tout bô tout neuf sans trous
alter table prospects add id numeric identity(1,1) not null
go
-- On vérifie que ça marche bien
select *
from prospects
-- On peut détruire l'ancien ID (mais faut bien vérifier que les références sont mises à jour partout)
--alter table prospects drop column id
--go

Bon, OK, c'est un peu gore, d'autant plus si tu as des FK un peu partout qui utilisent ton id de la table prospects...

Fry85

Bon ok ,avec le script j'ai pigé, mais le problème (Encore ??) c'est qu'on ne doit pas renuméroter les id de prospects parce qu'on les a imprimer sur des feuilles et qu'il doivent rester inchangé jusqu'à leur suppression de la base !
Enfin c'était une méthode hein

Pour l'instant, on fait ceçi :

Code :

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'InsertProspect' AND type = 'TR')
DROP TRIGGER InsertProspect
GO
CREATE TRIGGER InsertProspect
ON Prospect
INSTEAD OF INSERT
AS
/*Insertion des nouveaux prospects dans une table temporaire indexée*/
SELECT IDENTITY(INT,0,1) AS indexTmp1,
civiliteProspect, nomProspect,
prenomProspect,...
INTO #tmp1
FROM Inserted
/*récupération des trous de la table Prospect*/
SELECT IDENTITY(INT,0,1) AS indexTmp2, idPros AS idProsDispo,
INTO #tmp2
FROM #tmpTest
WHERE idPros NOT IN (SELECT idProspect FROM Prospect)
ORDER BY idPros ASC
/*#tmpTest contient seulement une colonne INT avec tous les chiffres de 1 à 1 000 000*/
INSERT INTO Prospect (idProspect,civiliteProspect,nomProspect,prenomProspect,...)
SELECT t.idPros,
i.civiliteProspect,
i.nomProspect,
i.prenomProspect,...
FROM #tmp1, #tmp2
WHERE indexTmp1 = indexTmp2
GO

Le seul problème c'est la table #tmpTest qui est générée avec une boucle qui dure surement plus de 2h ! C'est la seule solution qu'on a trouvé pour la créer !
Y'en a une autre plus facile pke passer 2h pour la création de cette table seulement, c'est pas top

Enfin sinon ca fnctionne :pt1cable:

Message édité par Fry85 le 29-07-2005 à 12:03:35

FORUM HardWare.fr

Programmation

SQL/NoSQL

Amélioration de performances d'une base SQL Server 2000

Sujets relatifs
SQL Server: Select date qui ont minimum une heure d'ecart	SQL Server et le XML
[SQL] Problème écriture requête	comment ouvrir une base excel en vb6?
autoriser accès à une page avec paswword dans base access	Aide pour amelioration script PHP
Structure base de données MySQL : correcte ou pas ?	[Article] SQL Server : Eviter les colonnes calculées comme la peste
Plus de sujets relatifs à : Amélioration de performances d'une base SQL Server 2000

Page générée en 0.092 secondes