[TSQL]optimisation procedure stockée

[tibob.lgo]

Bonjour,
Je travaille sur un projet de migration de procedures stockées sybase vers sql server et je me retrouve confronté à problème. En effet, la procédure d'origine sybase comporte une requête assez énorme et n'arrive pas a renvoyer les resultats.

Code :

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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
CREATE PROCEDURE lec_efacils1
//@dateinf	datetime,
//@datesup datetime
 
 
AS
BEGIN
 
SELECT	DISTINCT
         facture_cli.efac_codsit,   
         facture_cli.efac_numfaccli,   
         facture_cli.efac_typfaccli,   
         facture_cli.efac_indcdinot,   
         facture_cli.efac_codstafac,   
         facture_cli.efac_indimp,   
         facture_cli.efac_numdos,   
         facture_cli.efac_tgm,   
         facture_cli.efac_numvyg,   
         facture_cli.efac_codclifac,   
         facture_cli.efac_datfaccli,   
         facture_cli.efac_datcptcli,   
         facture_cli.efac_datpaicli,   
         facture_cli.efac_indenvcpt,   
         facture_cli.efac_mttdvi_ht,   
         facture_cli.efac_coddvi,   
         facture_cli.efac_tauchg,   
         facture_cli.efac_mttero_ht,   
         facture_cli.efac_codtva,   
         facture_cli.efac_tautva,
			code_tva.ctva_statvails 'ctva_statvails',
         facture_cli.efac_mttdvi_ttc,   
         facture_cli.efac_mttero_ttc,   
         facture_cli.efac_codaff,
			facture_cli.efac_typdoc,
			facture_cli.evol_idtcreenr,   
         lig_facture_cli.lfac_numligfaccli,   
         lig_facture_cli.lfac_codpre,   
         lig_facture_cli.lfac_typpre,   
         lig_facture_cli.lfac_prxpre,   
         lig_facture_cli.lfac_coduntfac,   
         lig_facture_cli.lfac_coddvi,   
         lig_facture_cli.lfac_tauchg,   
			lig_facture_cli.lfac_mttdvi_ht,   
			unt_dossier.udos_numintuntdos, 
			voyage.voya_indipoxpo,
			voyage.voya_codporebq,
			ISNULL(dossier.doss_codpordch,lig_facture_cli.lfac_codpordch),
			ISNULL(dossier.doss_numvyg,facture_cli.efac_numvyg),
		   pfac_cli_ele.pfce_codoricde,   
		   pfac_cli_ele.pfce_numcde,
			commande.cmde_codict,
			lig_facture_cli.lfac_tonfac,
			pre_facturation_cli.evol_dhecreenr, 
			ISNULL(dossier.doss_numdos,lig_facture_cli.fac_numdos),
			ISNULL
			(
				ISNULL
				(  (SELECT Max(tpsf_modpai)
				 	 FROM 	tiers_param_specifique
				 	 WHERE tiers_param_specifique.tpsf_codsit = facture_cli.efac_codsitref
					 AND	tiers_param_specifique.tpsf_codtie = facture_cli.efac_codclifac
					)
				,
					(SELECT Max(tpsf_modpai)
				 	 FROM 	tiers_param_specifique
				 	 WHERE tiers_param_specifique.tpsf_codsit = facture_cli.efac_codsitref
					 AND	tiers_param_specifique.tpsf_codtie = NULL
					)	
				)
			,
				tiers.tier_modpai 
			) 'modpai',
 			facture_cli.efac_codsitref
FROM		facture_cli,lig_facture_cli, pre_facturation_cli, pfac_cli_ele, unt_dossier , dossier, commande, voyage, tiers, code_tva, paramsite_regle_action
// jointure TABLE facture_cli <-> lig_facture_cli
WHERE		facture_cli.efac_numfaccli = lig_facture_cli.lfac_numfaccli
AND		facture_cli.efac_codsitref = lig_facture_cli.lfac_codsitref
AND		facture_cli.efac_codsit = lig_facture_cli.lfac_codsit
// jointure TABLE lig_facture_cli <-> pre_facturation_cli
AND		lig_facture_cli.lfac_codsit *= pre_facturation_cli.pfac_codsit 
AND		lig_facture_cli.lfac_codsitref *= pre_facturation_cli.pfac_codsitref
AND	   lig_facture_cli.lfac_numfaccli *= pre_facturation_cli.pfac_numfaccli
AND	   lig_facture_cli.lfac_numligfaccli *= pre_facturation_cli.pfac_numligfaccli
 
//TD SOPRA 16/06/06 - Les 5 jointures suivantes font planter l'execution de la proc sous SQL SERVER
// jointure table pre_facturation_cli <-> pfac_cli_ele
AND		pre_facturation_cli.pfac_numpeefacint *= pfac_cli_ele.pfce_numpeefacint
// jointure table pfac_cli_ele <-> unt_dossier
AND 		pfac_cli_ele.pfce_numintuntdos *= unt_dossier.udos_numintuntdos
// jointure table unt_dossier <-> dossier
AND 		unt_dossier.udos_codsit *= dossier.doss_codsit
AND		unt_dossier.udos_numdos *= dossier.doss_numdos
AND		unt_dossier.udos_tgm *= dossier.doss_tgm
//TD SOPRA 16/06/06
 
// jointure table dossier <-> voyage
AND 		dossier.doss_codsit *= voyage.voya_codsit
AND		dossier.doss_numvyg *= voyage.voya_numvyg
// jointure table unt_dossier <-> commande
AND 		unt_dossier.udos_codoricde *= commande.cmde_codoricde
AND		unt_dossier.udos_numcde *= commande.cmde_numcde
// jointure table facture_cli <-> tiers
AND		facture_cli.efac_codclifac = tiers.tier_codtie
// jointure table facture_cli <-> code_tva
AND 		facture_cli.efac_codtva = code_tva.ctva_codtva
// critères de sélection
AND 		efac_codstafac = '1'
AND		lig_facture_cli.lfac_codsit= paramsite_regle_action.psra_codsit
AND		paramsite_regle_action.psra_codact = 'ils_pec_sit'
ORDER BY	facture_cli.efac_codsit,
			facture_cli.efac_codsitref,   
         facture_cli.efac_numfaccli,
			lig_facture_cli.lfac_numligfaccli			
 
 
END;

J'obtiens le message d'erreur suivant :

Could not allocate space for object '(System table id: -3674826)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.


Y aurait-il un moyen d'optimiser la requête?

Merci d'avance

[fadace]

1. Agrandir tempdb
2. Ajouter des indexes adéquats à vos tables de manière à éviter d'énormes tables temporaires