Discussion :

[Franck2mars]

Salut a tous,

j'exécute une procédure stockée permettant d'insérer ou de mettre a jour des données dans une table. Cependant son temps d'exécution est tres long, quelqu'un aurait il une idée pour optimiser son temps d'exécution :

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
CREATE PROCEDURE [dbo].[ps_I_importdata]
@nom_table CHAR (255),
@champs char (255),
@data  CHAR (255)
 
as
declare @data2 as CHAR (255)
declare @compteur as int
declare @longueur int
declare @position int
declare @cle varchar(255)
declare @expression char(255)
declare @parametre varchar(1000)
declare @query varchar(1000)
 
DECLARE @i INT, @sql NVARCHAR(1000) 
declare @champ varchar(250)
 
begin
	set @compteur=0
        set @data2=@data      
  	declare curseur1 CURSOR for SELECT column_name as champ FROM INFORMATION_SCHEMA.columns WHERE table_name =@nom_table AND IS_NULLABLE='NO'--Curseur parcourant les clés de la table spécifiée
        open curseur1
        fetch curseur1 into @cle
        while @@FETCH_STATUS=0
        begin
	        set @longueur=len(@data2)--Récupération de la longueur de la chaine de caracteres a insérer dans la base de données
                set @position=PATINDEX('%,%',@data2)--Récupération de la position de la premiere virgule(séparateur de champs) dans la chaine
                set @expression=substring(@data2,0,@position)--Cette variable prend successivement les valeurs des clés de la table
                set @data2=substring(@data2,@position +1,@longueur - @position)--On enleve de la chaine de caracteres le champ qui vient d'etre inséré dans la chaine d'insertion
	        if @compteur=0  begin select @parametre = 'Where '+rtrim(@cle) +'="'+rtrim(@expression)  end else begin select @parametre= rtrim(@parametre) + ' AND '+rtrim(@cle)+'='+rtrim(@expression) END--Si c'est le 1er passage where.... sinon c'est and ....
                set @compteur=@compteur+1--Compteur d'itérations du curseur
                fetch next from curseur1 into @cle 
	end
	set @data=rtrim(@data)
	set @query='Select count(*) from ' +@nom_table + rtrim(@parametre)--Requete permettant de savoir si on va insérer ou mettre a jour des données
	select @query= replace(@query,'"',char(39))--Remplacement de double quote par simple quote
	SET @sql = 'SELECT @i = count(*) from '+rtrim(@nom_table)+' ' + rtrim(@parametre)--Construction de la requete avec attribution à @i de la valeur renvoyée par la requete
        select @sql= replace(@sql,'"',char(39))
        EXEC sp_executesql 
   		@query = @sql, 
   		@params = N'@i INT OUTPUT', 
   		@i = @i OUTPUT 
	if @i = 0 --Dans le cas ou l'enregistrement avec la clé spécifiée n'existe pas(@i=0)---->insertion
	begin
            select @query='insert into ' + rtrim(@nom_table) + '(' + rtrim(@champs) + ') values("' + rtrim(@data) +'")'
 
	end
	else--Dans le cas ou l'enregistrement avec la clé spécifiée existe déja (@i<>0)---->Mise a jour
	begin 
            set @query='update '+ rtrim(@nom_table) + ' set '
            declare curseur2 CURSOR for SELECT column_name FROM INFORMATION_SCHEMA.columns WHERE table_name =@nom_table --Curseur parcourant les champs de la table spécifiée
            open curseur2
            fetch curseur2 into @champ
            set @compteur=0
            while @@FETCH_STATUS=0
      	    begin
            	set @longueur=len(@data)--Récupération de la longueur de la chaine de caracteres a insérer dans la base de données
                set @position=PATINDEX('%,%',@data)--Récupération de la position de la premiere virgule(séparateur de champs) dans la chaine
                set @expression=substring(@data,0,@position)--Cette variable prend successivement les valeurs des champs à insérer dans la base
                set @data=substring(@data,@position+1,@longueur - @position)--On enleve de la chaine de caracteres le champ qui vient d'etre inséré dans la chaine d'insertion
                if @compteur=0  begin set @query=rtrim(@query) + ' '+rtrim(@champ) + ' ="' + rtrim(@expression) + ',' end else begin set @query=rtrim(@query) + ' '+rtrim(@champ) + ' =' + rtrim(@expression) + ','  END
                fetch next from curseur2 into @champ--Enregistrement suivant.
                set @compteur=@compteur+1--Compteur d'itérations du curseur 
            end
            set @longueur=len(@query)
	    set @query=substring(@query,0,@longueur)
            set @query=rtrim(@query)+ rtrim(@data)+ '"'+' ' + rtrim(@parametre) --Construction de la fin de la requete avec les critères de sélection sur les clés
            close curseur2-- Fermeture du curseur2
            deallocate curseur2-- Suppression de la mémoire du curseur2
	end
	close curseur1-- Fermeture du curseur1
	deallocate curseur1-- Suppression de la mémoire du curseur1
	select @query= replace(@query,'"',char(39))--Remplacement ds la requete du double quote par le simple quote
	EXEC(@query)--Exécution de la requete
end
GO

Merci,

[Fabien Celaia]

L'exécution de ce type de SQL dynamique, avec des tablescans à l'envie risque effectivement de ne pas être performant...

[SQLpro]

1) évitez le SQL dynamique
2) évitez d'utiliser des curseurs.

Sur ce dernier sujet, lisez l'article que j'ai écrit :
http://sqlpro.developpez.com/cours/s...r_avoidCursor/

A +