Utiliser une transaction plutôt que des tables temportaires

**stolx_10** · 17/11/2008, 12h36

Bonjour,

Suite à des erreurs que j'obtiens après l'écriture de ma requête SQL sous ASP ( cf : http://www.developpez.net/forums/d64...s-temporaires/)

Je souhaite savoir si il faut que j'utilise des transactions plutôt que l'utilisation de tables temporaires de type table #t ??

Voici ma requête avec ASP :

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
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
sql =""
sql = sql & " drop table #b "
sql = sql & " drop table #t "
 
sql = sql & "select * "
sql = sql & "into #b "
sql = sql & "from "
sql = sql & "(select e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id as numero, "
sql = sql & "		e_compta_segments.periode, e_compta_segments.glinvoice as facture_numero, e_compta_segments.date_elbs as facture_periode "
sql = sql & "			, e_compta_segments.devise,e_compta_segments.origine,"
sql = sql & "		sum( credit_devise - debit_devise ) as montant_devise ,"
sql = sql & "		sum( credit_euro - debit_euro ) as montant_euro,"
sql = sql & "		("
sql = sql & "		 sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + "
sql = sql & "			(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) "
sql = sql & "			from e_compta_segments as cd "
sql = sql & "			where cd.origine = 'CD' "
sql = sql & " 			and cd.seg1 in ('401000', '404000', '425700') "
sql = sql & "			and cd.periode <= '" & fin & "' "
sql = sql & "			and cd.glinvoice = e_compta_segments.id  "
sql = sql & "			) "
sql = sql & "		) as restant_euro_euro "
sql = sql & "from e_compta_segments "
sql = sql & "inner join e_general_fournisseurs "
sql = sql & "on fournisseur_id = fournisseur_numero "
sql = sql & "where e_compta_segments.origine = 'ap' "
				if compte <> "*" then
					sql = sql & " and e_compta_segments.seg1 = '" & compte & "' "
				else
					sql = sql & " and e_compta_segments.seg1 in ('401000', '404000', '425700') "
				end if
				if bureau <> "*" then
											sql = sql & " and e_compta_segments.seg2 = '" & bureau & "' "
				end if
				if fournisseur_categorie <> "*" then
					sql = sql & " and e_general_fournisseurs.fournisseur_categorie = '" & fournisseur_categorie & "' "
				end if
				if fournisseur_id <> "*" then
					sql = sql & " and e_compta_segments.fournisseur_id = '" & fournisseur_id & "' "
				end if
				if fournisseur_nom <> "*" then
					sql = sql & " and e_general_fournisseurs.fournisseur_nom like '%" & fournisseur_nom & "%' "
				end if
sql = sql & "and e_compta_segments.periode <= '" & fin & "'		"
sql = sql & "group by e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id , "
sql = sql & "e_compta_segments.periode, e_compta_segments.glinvoice , e_compta_segments.date_elbs ,e_compta_segments.devise,e_compta_segments.origine "
sql = sql & "having  sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + "
sql = sql & "			(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) "
sql = sql & "			from e_compta_segments as cd "
sql = sql & "			where cd.origine = 'CD' "
sql = sql & " 			and cd.seg1 in ('401000', '404000', '425700') "
sql = sql & "			and cd.periode <= '" & fin & "' "
sql = sql & "			and cd.glinvoice = e_compta_segments.id "
sql = sql & "			) <> 0 "
sql = sql & "and sum(credit_devise - debit_devise) <> 0 and sum(credit_euro - debit_euro) <> 0 "
sql = sql & ") as t "
sql = sql & "where devise ='EUR' "
 
sql = sql & "select * "
sql = sql & "into #t "
sql = sql & "from "
sql = sql & "(select e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id as numero, "
sql = sql & "		e_compta_segments.periode, e_compta_segments.glinvoice as facture_numero, e_compta_segments.date_elbs as facture_periode "
sql = sql & "			, e_compta_segments.devise,e_compta_segments.origine, "
sql = sql & "		sum( credit_devise - debit_devise ) as montant_devise , "
sql = sql & "		sum( credit_euro - debit_euro ) as montant_euro, "
sql = sql & "		( "
sql = sql & "		 sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + "
sql = sql & "			(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) "
sql = sql & "			from e_compta_segments as cd "
sql = sql & "			where cd.origine = 'CD' "
sql = sql & "			and cd.seg1 in ('401000', '404000', '425700') "
sql = sql & "			and cd.periode <= '" & fin & "' "
sql = sql & "			and cd.glinvoice = e_compta_segments.id  "
sql = sql & "			) "
sql = sql & "		) as restant_euro_usd "
sql = sql & "from e_compta_segments "
sql = sql & "inner join e_general_fournisseurs "
sql = sql & "on fournisseur_id = fournisseur_numero "
sql = sql & "where e_compta_segments.origine = 'ap' "
				if compte <> "*" then
					sql = sql & " and e_compta_segments.seg1 = '" & compte & "' "
				else
					sql = sql & " and e_compta_segments.seg1 in ('401000', '404000', '425700') "
				end if
				if bureau <> "*" then
											sql = sql & " and e_compta_segments.seg2 = '" & bureau & "' "
				end if
				if fournisseur_categorie <> "*" then
					sql = sql & " and e_general_fournisseurs.fournisseur_categorie = '" & fournisseur_categorie & "' "
				end if
				if fournisseur_id <> "*" then
					sql = sql & " and e_compta_segments.fournisseur_id = '" & fournisseur_id & "' "
				end if
				if fournisseur_nom <> "*" then
					sql = sql & " and e_general_fournisseurs.fournisseur_nom like '%" & fournisseur_nom & "%' "
				end if
sql = sql & "and e_compta_segments.periode <= '" & fin & "'		"
sql = sql & "group by e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id , "
sql = sql & "e_compta_segments.periode, e_compta_segments.glinvoice , e_compta_segments.date_elbs ,e_compta_segments.devise,e_compta_segments.origine "
sql = sql & "having  sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + "
sql = sql & "			(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) "
sql = sql & "			from e_compta_segments as cd "
sql = sql & "			where cd.origine = 'CD' "
sql = sql & " 			and cd.seg1 in ('401000', '404000', '425700') "
sql = sql & "			and cd.periode <= '" & fin & "' "
sql = sql & "			and cd.glinvoice = e_compta_segments.id "
sql = sql & "			) <> 0 "
sql = sql & "and sum(credit_devise - debit_devise) <> 0 and sum(credit_euro - debit_euro) <> 0 "
sql = sql & ") as t "
sql = sql & "where devise ='USD' "
 
sql = sql & " drop table #a "
 
 
sql = sql & "select * "
sql = sql & "into #a "
sql = sql & "from ( "
sql = sql & "select "
sql = sql & "fournisseur_id, "
sql = sql & "fournisseur_nom, "
sql = sql & "restant_euro_usd, "
sql = sql & "0 as restant_euro_euro "
sql = sql & "from #t "
 
sql = sql & "union all "
 
sql = sql & "select fournisseur_id, "
sql = sql & "fournisseur_nom, "
sql = sql & "0 as restant_euro_usd, "
sql = sql & "restant_euro_euro "
sql = sql & "from #b) as a "
 
 
sql = sql & "select fournisseur_id, fournisseur_nom, sum(restant_euro_euro) as restant_euro_euro, sum(restant_euro_usd) as restant_euro_usd, "
sql = sql & "(sum(restant_euro_euro)+sum(restant_euro_usd)) as Montant_Du "
sql = sql & "from #a "
sql = sql & "where 1=1 "
sql = sql & "group by fournisseur_id, fournisseur_nom "
sql = sql & "having sum(restant_euro_euro) <>0 or sum(restant_euro_usd) <>0 "
sql = sql & "order by " & tri & " " & trisens

Si tel est le cas, merci de m'orienter sur une solution
Merci pour votre aide précieuse

**elsuket** · 17/11/2008, 13h20

Bonjour,

Ni l'un, ni l'autre.
Le mieux est que vous écriviez une procédure stockée à laquelle vous passez des paramètres.
Si vous avez besoin d'aide pour cela, postez votre requête originale ou un exemple de celle-ci.

@++

**stolx_10** · 17/11/2008, 13h39

merci !!

je reposte ma requête (longue) :

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
79
80
81
drop table #b; 
drop table #t; 
 
select * 
into #b
from 
	(select e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, 
			e_compta_segments.id as numero, e_compta_segments.periode, e_compta_segments.glinvoice as facture_numero, 
			e_compta_segments.date_elbs as facture_periode , e_compta_segments.devise,e_compta_segments.origine, 
			sum( credit_devise - debit_devise ) as montant_devise , sum( credit_euro - debit_euro ) as montant_euro, 
			( sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
				(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
				from e_compta_segments as cd 
				where cd.origine = 'CD' and cd.seg1 in ('401000', '404000', '425700') 
				and cd.periode <= '17/11/2008' and cd.glinvoice = e_compta_segments.id ) 
			) as restant_euro_euro 
	from e_compta_segments 
	inner join e_general_fournisseurs on fournisseur_id = fournisseur_numero 
	where e_compta_segments.origine = 'ap' and e_compta_segments.seg1 in ('401000', '404000', '425700') 
	and e_compta_segments.periode <= '17/11/2008' 
	group by e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id , 
				e_compta_segments.periode, e_compta_segments.glinvoice , e_compta_segments.date_elbs ,
				e_compta_segments.devise,e_compta_segments.origine 
	having sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
				(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
					from e_compta_segments as cd 
					where cd.origine = 'CD' and cd.seg1 in ('401000', '404000', '425700') and cd.periode <= '17/11/2008' 
					and cd.glinvoice = e_compta_segments.id ) <> 0 and sum(credit_devise - debit_devise) <> 0 
					and sum(credit_euro - debit_euro) <> 0 ) as t 
where devise ='EUR'; 
 
--*************************************************
 
select * 
into #t 
from 
	(select e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id as numero, 
			e_compta_segments.periode, e_compta_segments.glinvoice as facture_numero, 
			e_compta_segments.date_elbs as facture_periode , e_compta_segments.devise,e_compta_segments.origine, 
			sum( credit_devise - debit_devise ) as montant_devise , sum( credit_euro - debit_euro ) as montant_euro, 
			( sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
				(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
				from e_compta_segments as cd 
				where cd.origine = 'CD' and cd.seg1 in ('401000', '404000', '425700') and cd.periode <= '17/11/2008' 
				and cd.glinvoice = e_compta_segments.id ) 
			) as restant_euro_usd 
	from e_compta_segments 
	inner join e_general_fournisseurs on fournisseur_id = fournisseur_numero 
	where e_compta_segments.origine = 'ap' and e_compta_segments.seg1 in ('401000', '404000', '425700') 
	and e_compta_segments.periode <= '17/11/2008' 
	group by e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id , e_compta_segments.periode, 
				e_compta_segments.glinvoice , e_compta_segments.date_elbs ,e_compta_segments.devise,e_compta_segments.origine 
	having sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
			(select isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
			from e_compta_segments as cd 
			where cd.origine = 'CD' and cd.seg1 in ('401000', '404000', '425700') and cd.periode <= '17/11/2008' 
			and cd.glinvoice = e_compta_segments.id ) <> 0 and sum(credit_devise - debit_devise) <> 0 
			and sum(credit_euro - debit_euro) <> 0 ) as t 
where devise ='USD'; 
 
--*******************************************
drop table #a; 
select * 
into #a 
from 
	( select fournisseur_id, fournisseur_nom, restant_euro_usd, 0 as restant_euro_euro 
		from #t 
	union all 
		select fournisseur_id, fournisseur_nom, 0 as restant_euro_usd, restant_euro_euro 
		from #b
	) 
as a;
 
--********************************************
select fournisseur_id, fournisseur_nom, sum(restant_euro_euro) as restant_euro_euro, sum(restant_euro_usd) as restant_euro_usd,
		 (sum(restant_euro_euro)+sum(restant_euro_usd)) as Montant_Du 
from #a 
where 1=1 
group by fournisseur_id, fournisseur_nom 
having sum(restant_euro_euro) <>0 or sum(restant_euro_usd) <>0 
order by fournisseur_nom asc;

ATTENTION : la requête ci dessus est la requête que me renvoit ASP après un response.write.
Celle ci fonctionne sous SQL management.

Pour les paramètres, il faut reagrder mon premier post

Merci

**SQLpro** · 17/11/2008, 17h06

Pourquoi voulez-vous faire des tables temporaires alors que votre requête peut s'écrire en une seule requête ? Vous voulez sciemment pourrir les performances de votre SGBDR ?
Lisez ceci : http://blog.developpez.com/sqlpro?ti...et_persistance

Si vous voulez décortiquer votre requête utilisez une CTE...
Lisez ceci :
http://sqlpro.developpez.com/cours/s...te-recursives/

A +

**SQLpro** · 17/11/2008, 17h10

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
SELECT fournisseur_id, fournisseur_nom, sum(restant_euro_euro) AS restant_euro_euro, sum(restant_euro_usd) AS restant_euro_usd,
		 (sum(restant_euro_euro)+sum(restant_euro_usd)) AS Montant_Du 
FROM (SELECT * 
FROM 
	(SELECT e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, 
			e_compta_segments.id AS numero, e_compta_segments.periode, e_compta_segments.glinvoice AS facture_numero, 
			e_compta_segments.date_elbs AS facture_periode , e_compta_segments.devise,e_compta_segments.origine, 
			sum( credit_devise - debit_devise ) AS montant_devise , sum( credit_euro - debit_euro ) AS montant_euro, 
			( sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
				(SELECT isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
				FROM e_compta_segments AS cd 
				WHERE cd.origine = 'CD' AND cd.seg1 IN ('401000', '404000', '425700') 
				AND cd.periode <= '17/11/2008' AND cd.glinvoice = e_compta_segments.id ) 
			) AS restant_euro_euro 
	FROM e_compta_segments 
	INNER JOIN e_general_fournisseurs ON fournisseur_id = fournisseur_numero 
	WHERE e_compta_segments.origine = 'ap' AND e_compta_segments.seg1 IN ('401000', '404000', '425700') 
	AND e_compta_segments.periode <= '17/11/2008' 
	GROUP BY e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id , 
				e_compta_segments.periode, e_compta_segments.glinvoice , e_compta_segments.date_elbs ,
				e_compta_segments.devise,e_compta_segments.origine 
	HAVING sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
				(SELECT isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
					FROM e_compta_segments AS cd 
					WHERE cd.origine = 'CD' AND cd.seg1 IN ('401000', '404000', '425700') AND cd.periode <= '17/11/2008' 
					AND cd.glinvoice = e_compta_segments.id ) <> 0 AND sum(credit_devise - debit_devise) <> 0 
					AND sum(credit_euro - debit_euro) <> 0 ) AS t 
WHERE devise ='EUR' 
 	union ALL
SELECT * 
FROM 
	(SELECT e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id AS numero, 
			e_compta_segments.periode, e_compta_segments.glinvoice AS facture_numero, 
			e_compta_segments.date_elbs AS facture_periode , e_compta_segments.devise,e_compta_segments.origine, 
			sum( credit_devise - debit_devise ) AS montant_devise , sum( credit_euro - debit_euro ) AS montant_euro, 
			( sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
				(SELECT isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
				FROM e_compta_segments AS cd 
				WHERE cd.origine = 'CD' AND cd.seg1 IN ('401000', '404000', '425700') AND cd.periode <= '17/11/2008' 
				AND cd.glinvoice = e_compta_segments.id ) 
			) AS restant_euro_usd 
	FROM e_compta_segments 
	INNER JOIN e_general_fournisseurs ON fournisseur_id = fournisseur_numero 
	WHERE e_compta_segments.origine = 'ap' AND e_compta_segments.seg1 IN ('401000', '404000', '425700') 
	AND e_compta_segments.periode <= '17/11/2008' 
	GROUP BY e_compta_segments.fournisseur_id, e_general_fournisseurs.fournisseur_nom, e_compta_segments.id , e_compta_segments.periode, 
				e_compta_segments.glinvoice , e_compta_segments.date_elbs ,e_compta_segments.devise,e_compta_segments.origine 
	HAVING sum(e_compta_segments.credit_euro - e_compta_segments.debit_euro) + 
			(SELECT isnull (sum( isnull(cd.credit_euro,0) - isnull(cd.debit_euro,0) ),0) 
			FROM e_compta_segments AS cd 
			WHERE cd.origine = 'CD' AND cd.seg1 IN ('401000', '404000', '425700') AND cd.periode <= '17/11/2008' 
			AND cd.glinvoice = e_compta_segments.id ) <> 0 AND sum(credit_devise - debit_devise) <> 0 
			AND sum(credit_euro - debit_euro) <> 0 ) AS t 
WHERE devise ='USD') AS Z
WHERE 1=1 
GROUP BY fournisseur_id, fournisseur_nom 
HAVING sum(restant_euro_euro) <>0 OR sum(restant_euro_usd) <>0 
ORDER BY fournisseur_nom ASC;

Voici votre requête en une seule passe.

A +

**stolx_10** · 17/11/2008, 19h27

Merci beaucoup !!

J'ai modifié un peu la dernière requête de mon côté car quelques petites erreurs, mais tout fonctionne convenablement maintenant.

J'ai pas pensé à tout regrouper en une seule passe, je comprenais beaucoup mieux comme j'avais fait.

Merci beaucoup

Utiliser une transaction plutôt que des tables temportaires

MS SQL Server

Discussions similaires

Partager

Partager