Discussion :

[Jephte]

Bonjour à tous.

Je suis confronté à un problème urgent

Une procédure stockée (non paramétrée) sur SQL-Server met à jour une table à partie d'une autre table.

Si je lance la procédure à partir de l'analyseur de requêtes SQL, elle s'exécute en une seconde.

La même procédure appelée à partir de Delphi (Delphi 5, ADOConnection et ADOCommand) met 7 minutes au bas mot.

Quelle est la bonne configuration ADO, où ais-je faux ?

Merci d'avance.

[Fabien Celaia]

Lors de l'exécution via Delphi, quels sont l'état des processus (sp_who) et des verrous (sp_lock) ?

[Jephte]

Voici les 2 états pendant l'exécution:

le processus Delphi appellant la procédure stockée est le 106

sp_who
1 0 sleeping sa 0 NULL LOG WRITER
2 0 background sa 0 master SIGNAL HANDLER
3 0 background sa 0 NULL LOCK MONITOR
4 0 background sa 0 NULL LAZY WRITER
5 0 background sa 0 master TASK MANAGER
6 0 sleeping sa 0 NULL CHECKPOINT SLEEP
7 0 background sa 0 master TASK MANAGER
8 0 background sa 0 master TASK MANAGER
10 0 background sa 0 master TASK MANAGER
11 0 background sa 0 master TASK MANAGER
12 0 background sa 0 master TASK MANAGER
13 0 background sa 0 master TASK MANAGER
14 0 background sa 0 master TASK MANAGER
15 0 background sa 0 master TASK MANAGER
17 0 background sa 0 master TASK MANAGER
51 0 sleeping SACLA\Administrateur SERVEUR 0 msdb AWAITING COMMAND
52 0 sleeping SACLA\Administrateur SERVEUR 0 master AWAITING COMMAND
53 0 sleeping SACLA\Administrateur SERVEUR 0 msdb AWAITING COMMAND
54 0 sleeping SACLA\Administrateur SERVEUR 0 EUROPROTECTION AWAITING COMMAND
55 0 sleeping SACLA\Administrateur SERVEUR 0 EUROPROTECTION AWAITING COMMAND
56 0 sleeping SACLA\Administrateur SERVEUR 0 EUROPROTECTION AWAITING COMMAND
57 0 sleeping SACLA\muriel POSTE10 0 EUROPROTECTION AWAITING COMMAND
58 0 sleeping SACLA\muriel POSTE10 0 EUROPROTECTION AWAITING COMMAND
59 0 sleeping SACLA\asanchis STATION25 0 EUROPROTECTION AWAITING COMMAND
60 0 sleeping SACLA\asanchis STATION25 0 EUROPROTECTION AWAITING COMMAND
61 0 sleeping SACLA\vsancho PC-SANCHO 0 EUROPROTECTION AWAITING COMMAND
62 0 sleeping SACLA\vsancho PC-SANCHO 0 EUROPROTECTION AWAITING COMMAND
63 0 sleeping SACLA\mtolosane MARTINE 0 EUROPROTECTION AWAITING COMMAND
64 0 sleeping SACLA\bpillot PC-BERRYL 0 EUROPROTECTION AWAITING COMMAND
65 0 sleeping SACLA\mtolosane MARTINE 0 EUROPROTECTION AWAITING COMMAND
66 0 sleeping SACLA\dianne PC-CATHERINE 0 EUROPROTECTION AWAITING COMMAND
67 0 sleeping SACLA\dianne PC-CATHERINE 0 EUROPROTECTION AWAITING COMMAND
68 0 sleeping SACLA\agnes 104 0 EUROPROTECTION AWAITING COMMAND
69 0 sleeping SACLA\agnes 104 0 EUROPROTECTION AWAITING COMMAND
70 0 sleeping SACLA\dianne NADINE 0 SACLA AWAITING COMMAND
71 0 sleeping SACLA\joelle JOELLE2 0 EUROPROTECTION AWAITING COMMAND
72 0 sleeping SACLA\cdecloedt PC-DECLOEDT 0 EUROPROTECTION AWAITING COMMAND
73 0 sleeping SACLA\cdecloedt PC-DECLOEDT 0 EUROPROTECTION AWAITING COMMAND
74 0 sleeping SACLA\hugues VERONIQUE2 0 EUROPROTECTION AWAITING COMMAND
75 0 sleeping SACLA\Administrateur SERVEUR 0 EUROPROTECTION AWAITING COMMAND
76 0 sleeping SACLA\hugues VERONIQUE2 0 EUROPROTECTION AWAITING COMMAND
77 0 sleeping SACLA\vsculfort PC-VERONIQUE 0 EUROPROTECTION AWAITING COMMAND
78 0 sleeping SACLA\vsculfort PC-VERONIQUE 0 EUROPROTECTION AWAITING COMMAND
79 0 sleeping SACLA\andeole POSTE12 0 EUROPROTECTION AWAITING COMMAND
80 0 sleeping SACLA\andeole POSTE12 0 EUROPROTECTION AWAITING COMMAND
81 0 sleeping SACLA\joelle JOELLE2 0 EUROPROTECTION AWAITING COMMAND
82 0 sleeping SACLA\nbourdin STATION30 0 EUROPROTECTION AWAITING COMMAND
83 0 sleeping SACLA\Administrateur SERVEUR 0 EUROPROTECTION AWAITING COMMAND
84 0 sleeping SACLA\nbourdin STATION30 0 EUROPROTECTION AWAITING COMMAND
85 0 sleeping SACLA\EXPORT1 EXPORT1 0 EUROPROTECTION AWAITING COMMAND
86 0 sleeping SACLA\EXPORT1 EXPORT1 0 EUROPROTECTION AWAITING COMMAND
87 0 sleeping SACLA\dianne POSTE11 0 EUROPROTECTION AWAITING COMMAND
88 0 sleeping SACLA\dianne POSTE11 0 EUROPROTECTION AWAITING COMMAND
89 0 sleeping SACLA\cborghero STATION28 0 EUROPROTECTION AWAITING COMMAND
90 0 sleeping SACLA\cborghero STATION28 0 EUROPROTECTION AWAITING COMMAND
91 0 sleeping SACLA\etexier ESPAGNE 0 EUROPROTECTION AWAITING COMMAND
92 0 sleeping SACLA\etexier ESPAGNE 0 EUROPROTECTION AWAITING COMMAND
93 0 sleeping SACLA\isabelle ISABELLE 0 EUROPROTECTION AWAITING COMMAND
94 0 sleeping SACLA\isabelle ISABELLE 0 EUROPROTECTION AWAITING COMMAND
95 0 sleeping SACLA\cgros PC-CHRISTELE 0 EUROPROTECTION AWAITING COMMAND
96 0 runnable SACLA\cgros PC-CHRISTELE 0 EUROPROTECTION SELECT
97 0 sleeping SACLA\bpillot PC-BERRYL 0 EUROPROTECTION AWAITING COMMAND
98 0 sleeping sa SERV-SQL 0 master AWAITING COMMAND
99 0 runnable sa SERV-SQL 0 EUROPROTECTION SELECT
100 0 sleeping SACLA\hugues ESPAGNOL 0 EUROPROTECTION AWAITING COMMAND
101 0 sleeping SACLA\hugues ESPAGNOL 0 EUROPROTECTION AWAITING COMMAND
102 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
103 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
104 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
105 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
106 0 runnable sa SERV-SQL 0 master EXECUTE
107 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
108 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
109 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
111 0 sleeping sa SERV-SQL 0 EUROPROTECTION AWAITING COMMAND
114 0 sleeping SACLA\agnes 1182 0 SACLA AWAITING COMMAND

et sp_lock

51 4 0 0 DB S GRANT
53 4 0 0 DB S GRANT
54 10 0 0 DB S GRANT
55 10 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 10 0 0 DB S GRANT
58 10 0 0 DB S GRANT
59 10 0 0 DB S GRANT
59 10 578153155 0 TAB Sch-S GRANT
59 2 567788855 0 TAB Sch-S GRANT
60 10 0 0 DB S GRANT
61 10 0 0 DB S GRANT
62 10 0 0 DB S GRANT
63 10 0 0 DB S GRANT
64 10 0 0 DB S GRANT
65 10 0 0 DB S GRANT
66 10 0 0 DB S GRANT
67 10 0 0 DB S GRANT
68 10 0 0 DB S GRANT
69 10 0 0 DB S GRANT
70 7 0 0 DB S GRANT
71 10 0 0 DB S GRANT
72 10 0 0 DB S GRANT
73 10 0 0 DB S GRANT
74 10 0 0 DB S GRANT
75 10 0 0 DB S GRANT
76 10 0 0 DB S GRANT
77 10 0 0 DB S GRANT
78 10 0 0 DB S GRANT
79 10 0 0 DB S GRANT
80 10 0 0 DB S GRANT
81 10 0 0 DB S GRANT
82 10 0 0 DB S GRANT
83 10 0 0 DB S GRANT
84 10 0 0 DB S GRANT
85 10 0 0 DB S GRANT
86 10 0 0 DB S GRANT
87 10 0 0 DB S GRANT
88 10 0 0 DB S GRANT
89 10 0 0 DB S GRANT
90 10 0 0 DB S GRANT
91 10 0 0 DB S GRANT
92 10 0 0 DB S GRANT
93 10 0 0 DB S GRANT
94 10 0 0 DB S GRANT
95 10 0 0 DB S GRANT
96 10 0 0 DB S GRANT
97 10 0 0 DB S GRANT
99 10 0 0 DB S GRANT
99 1 85575343 0 TAB IS GRANT
100 10 0 0 DB S GRANT
101 10 0 0 DB S GRANT
102 10 0 0 DB S GRANT
103 10 0 0 DB S GRANT
104 10 0 0 DB S GRANT
105 10 0 0 DB S GRANT
106 10 0 0 DB S GRANT
106 10 482152813 1 PAG 1:127269 S GRANT
106 10 578153155 1 KEY (f800ad3b110f) X GRANT
106 10 482152813 0 TAB IS GRANT
106 10 578153155 1 PAG 1:264270 IX GRANT
106 10 578153155 0 TAB IX GRANT
106 10 661577395 0 TAB Sch-S GRANT
107 10 0 0 DB S GRANT
108 10 0 0 DB S GRANT
109 10 0 0 DB S GRANT
111 10 0 0 DB S GRANT
114 7 0 0 DB S GRANT

[WOLO Laurent]

il serait plus facile de verifier tes procedures de verrouillage.
Utilises tu le verrouillage pessimiste ou bien optimiste ?



Veuillez poster le code ...