Methodologie pour jointure entre table sur le serveur et table cliente
5 réponses
Gilles Vollant \(MVP\)
Bonjour
J'aurais besoin de conseil pour la situation suivante.
On a un serveur SQL Server 2000, qui contient un grand nombre de table de
taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes
joignant les tables sur le serveur (dont certain sont très grosse -
plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables
provenant du client, assez courte (de l'ordre de la centaine
d'enregistrement).
Le resultat étant lui même un ensemble d'enregistrement assez court (de
l'ordre de la centaine de ligne) qui sera récupérée côté client.
La solution que j'imagine est la suivante:
lorsque l'utilisateur a besoin des récupérer ses données, le logiciel client
crée sur le serveur la ou les tables temporaires, en recopiant la centaine
d'enregistrement (ce qui ne doit pas être dramatique en terme de
performance), executer les requetes côté serveur, puis recuperer les données
et détruire la ou les tables temporaires.
Plusieurs exemplaire du client pouvant tourner simultanément sur plusieurs
postes différents.
Le concept de table temporaire local de SQL Server (table commençant par #)
me paraissait une bonne idée, mais parait avoir un certain nombre de
containte. En particulier, ces tables ne peuvent être référencée dans les
vues, et je n'arrive pas à faire "voir" une telle table créer préalablement
via commande passer par ADO par des procédures stockées.
Peut être fais-je fausse route, toute piste ou réflexion est bienvenue...
Set cmd = CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn cmd.CommandType = adCmdStoredProc cmd.CommandText = "gvStorProcTstXml3"
Dim strparam As String
strparam = "<ROOT>" For i = 1 To 3000 * 5 strparam = strparam & " " & vbCrLf Next For i = 1 To 1500 strparam = strparam & "<Cp CodePostal=""69000"" Personne=""Jim" & Str(i) & "Jam""/>" & vbCrLf Next
strparam = strparam & "<Cp CodePostal=""78000"" Personne=""John""/><Cp CodePostal=""92000"" Personne=""Jack""/></ROOT>" Debug.Print "nombre d'octets de la chaine xml : " ;Len(strparam)
With cmd .Parameters.Append .CreateParameter("OrderList", adVarChar, _ adParamInput, 200000, _ strparam) End With
Set rs = CreateObject("ADODB.Recordset") rs.Open cmd, , adOpenStatic, adLockReadOnly
While Not rs.EOF Debug.Print rs.Fields(0).Value & " "; rs.Fields(1).Value; ", "; rs.Fields(2).Value rs.MoveNext Wend
rs.Close cn.Close Set rs = Nothing Set cmd = Nothing Set cn = Nothing
End Sub
CREATE PROCEDURE gvStorProcTstXml3 ( @OrderList text ) AS BEGIN SET NOCOUNT ON
SELECT o.CP,O.Ville,Personne FROM gvDemoVille AS o JOIN OPENXML (@DocHandle, '/ROOT/Cp',1) WITH (CodePostal int,Personne varchar(50)) AS x ON o.CP = x.CodePostal
EXEC sp_xml_removedocument @DocHandle END GO
J'ai une table gvDemoVille avec un champ CP entier et un champ texte Ville dans laquelle j'ai mis au moins ces 3 enregistrement: CPi000, Ville="Lyon" CPx000, Ville="Versailles" CP000, Ville="Nanterres"
et ça marche
"Gilles Vollant (MVP)" <info@winimage.com> wrote in message
news:eBNAu9QEHHA.3836@TK2MSFTNGP02.phx.gbl...
J'ai vu http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/share_data.html
à creuser
Le XML semble la solution
cf methode 4 de
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
j'utilise le type text au lieu de varchar(1000) comme type du parametre pour
pouvoir passer un bloc XML de taille adaptée
exemple d'appel en ADO avec AccessBasic
Sub tstprocstocxml3()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConnOLEDB As String
Dim rs As ADODB.Recordset
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "gvStorProcTstXml3"
Dim strparam As String
strparam = "<ROOT>"
For i = 1 To 3000 * 5
strparam = strparam & " " & vbCrLf
Next
For i = 1 To 1500
strparam = strparam & "<Cp CodePostal=""69000"" Personne=""Jim" & Str(i) &
"Jam""/>" & vbCrLf
Next
strparam = strparam & "<Cp CodePostal=""78000"" Personne=""John""/><Cp
CodePostal=""92000"" Personne=""Jack""/></ROOT>"
Debug.Print "nombre d'octets de la chaine xml : " ;Len(strparam)
With cmd
.Parameters.Append .CreateParameter("OrderList", adVarChar, _
adParamInput, 200000, _
strparam)
End With
Set rs = CreateObject("ADODB.Recordset")
rs.Open cmd, , adOpenStatic, adLockReadOnly
While Not rs.EOF
Debug.Print rs.Fields(0).Value & " "; rs.Fields(1).Value; ", ";
rs.Fields(2).Value
rs.MoveNext
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
CREATE PROCEDURE gvStorProcTstXml3
(
@OrderList text
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.CP,O.Ville,Personne
FROM gvDemoVille AS o
JOIN
OPENXML (@DocHandle, '/ROOT/Cp',1) WITH (CodePostal int,Personne
varchar(50)) AS x
ON o.CP = x.CodePostal
EXEC sp_xml_removedocument @DocHandle
END
GO
J'ai une table gvDemoVille avec un champ CP entier et un champ texte Ville
dans laquelle j'ai mis au moins ces 3 enregistrement:
CPi000, Ville="Lyon"
CPx000, Ville="Versailles"
CP000, Ville="Nanterres"
Set cmd = CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn cmd.CommandType = adCmdStoredProc cmd.CommandText = "gvStorProcTstXml3"
Dim strparam As String
strparam = "<ROOT>" For i = 1 To 3000 * 5 strparam = strparam & " " & vbCrLf Next For i = 1 To 1500 strparam = strparam & "<Cp CodePostal=""69000"" Personne=""Jim" & Str(i) & "Jam""/>" & vbCrLf Next
strparam = strparam & "<Cp CodePostal=""78000"" Personne=""John""/><Cp CodePostal=""92000"" Personne=""Jack""/></ROOT>" Debug.Print "nombre d'octets de la chaine xml : " ;Len(strparam)
With cmd .Parameters.Append .CreateParameter("OrderList", adVarChar, _ adParamInput, 200000, _ strparam) End With
Set rs = CreateObject("ADODB.Recordset") rs.Open cmd, , adOpenStatic, adLockReadOnly
While Not rs.EOF Debug.Print rs.Fields(0).Value & " "; rs.Fields(1).Value; ", "; rs.Fields(2).Value rs.MoveNext Wend
rs.Close cn.Close Set rs = Nothing Set cmd = Nothing Set cn = Nothing
End Sub
CREATE PROCEDURE gvStorProcTstXml3 ( @OrderList text ) AS BEGIN SET NOCOUNT ON
SELECT o.CP,O.Ville,Personne FROM gvDemoVille AS o JOIN OPENXML (@DocHandle, '/ROOT/Cp',1) WITH (CodePostal int,Personne varchar(50)) AS x ON o.CP = x.CodePostal
EXEC sp_xml_removedocument @DocHandle END GO
J'ai une table gvDemoVille avec un champ CP entier et un champ texte Ville dans laquelle j'ai mis au moins ces 3 enregistrement: CPi000, Ville="Lyon" CPx000, Ville="Versailles" CP000, Ville="Nanterres"
et ça marche
Pierre Goiffon
Gilles Vollant (MVP) wrote:
On a un serveur SQL Server 2000, qui contient un grand nombre de table de taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes joignant les tables sur le serveur (dont certain sont très grosse - plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables provenant du client, assez courte (de l'ordre de la centaine d'enregistrement). Le resultat étant lui même un ensemble d'enregistrement assez court (de l'ordre de la centaine de ligne) qui sera récupérée côté client.
Il est compliqué de vous suivre : ces "tables clients", où sont-elles situées ? Sur des SQL Server tournant sur les postes des applications ?
Vous pouvez regarder openrowset (possibilité d'ouvrir une source de données distance de manière dynamique, plus souple que les serveurs liés dans votre cas)
Gilles Vollant (MVP) wrote:
On a un serveur SQL Server 2000, qui contient un grand nombre de table de
taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes
joignant les tables sur le serveur (dont certain sont très grosse -
plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables
provenant du client, assez courte (de l'ordre de la centaine
d'enregistrement).
Le resultat étant lui même un ensemble d'enregistrement assez court (de
l'ordre de la centaine de ligne) qui sera récupérée côté client.
Il est compliqué de vous suivre : ces "tables clients", où sont-elles
situées ? Sur des SQL Server tournant sur les postes des applications ?
Vous pouvez regarder openrowset (possibilité d'ouvrir une source de
données distance de manière dynamique, plus souple que les serveurs liés
dans votre cas)
On a un serveur SQL Server 2000, qui contient un grand nombre de table de taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes joignant les tables sur le serveur (dont certain sont très grosse - plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables provenant du client, assez courte (de l'ordre de la centaine d'enregistrement). Le resultat étant lui même un ensemble d'enregistrement assez court (de l'ordre de la centaine de ligne) qui sera récupérée côté client.
Il est compliqué de vous suivre : ces "tables clients", où sont-elles situées ? Sur des SQL Server tournant sur les postes des applications ?
Vous pouvez regarder openrowset (possibilité d'ouvrir une source de données distance de manière dynamique, plus souple que les serveurs liés dans votre cas)
Fred BROUARD
Gilles Vollant (MVP) a écrit :
Bonjour
J'aurais besoin de conseil pour la situation suivante.
On a un serveur SQL Server 2000, qui contient un grand nombre de table de taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes joignant les tables sur le serveur (dont certain sont très grosse - plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables provenant du client, assez courte (de l'ordre de la centaine d'enregistrement). Le resultat étant lui même un ensemble d'enregistrement assez court (de l'ordre de la centaine de ligne) qui sera récupérée côté client.
La solution que j'imagine est la suivante: lorsque l'utilisateur a besoin des récupérer ses données, le logiciel client crée sur le serveur la ou les tables temporaires, en recopiant la centaine d'enregistrement (ce qui ne doit pas être dramatique en terme de performance), executer les requetes côté serveur, puis recuperer les données et détruire la ou les tables temporaires.
Plusieurs exemplaire du client pouvant tourner simultanément sur plusieurs postes différents.
Le concept de table temporaire local de SQL Server (table commençant par #) me paraissait une bonne idée, mais parait avoir un certain nombre de containte. En particulier, ces tables ne peuvent être référencée dans les vues, et je n'arrive pas à faire "voir" une telle table créer préalablement via commande passer par ADO par des procédures stockées.
Peut être fais-je fausse route, toute piste ou réflexion est bienvenue...
utilisez le concept de serveur liés et faites des requêtes avoir des jointures remote !
Oubliez openrowset qui : 1) est nul au point de vue perf 2) difficile à porter en 2005 (trou de sécurité)
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
Gilles Vollant (MVP) a écrit :
Bonjour
J'aurais besoin de conseil pour la situation suivante.
On a un serveur SQL Server 2000, qui contient un grand nombre de table de
taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes
joignant les tables sur le serveur (dont certain sont très grosse -
plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables
provenant du client, assez courte (de l'ordre de la centaine
d'enregistrement).
Le resultat étant lui même un ensemble d'enregistrement assez court (de
l'ordre de la centaine de ligne) qui sera récupérée côté client.
La solution que j'imagine est la suivante:
lorsque l'utilisateur a besoin des récupérer ses données, le logiciel client
crée sur le serveur la ou les tables temporaires, en recopiant la centaine
d'enregistrement (ce qui ne doit pas être dramatique en terme de
performance), executer les requetes côté serveur, puis recuperer les données
et détruire la ou les tables temporaires.
Plusieurs exemplaire du client pouvant tourner simultanément sur plusieurs
postes différents.
Le concept de table temporaire local de SQL Server (table commençant par #)
me paraissait une bonne idée, mais parait avoir un certain nombre de
containte. En particulier, ces tables ne peuvent être référencée dans les
vues, et je n'arrive pas à faire "voir" une telle table créer préalablement
via commande passer par ADO par des procédures stockées.
Peut être fais-je fausse route, toute piste ou réflexion est bienvenue...
utilisez le concept de serveur liés et faites des requêtes avoir des
jointures remote !
Oubliez openrowset qui :
1) est nul au point de vue perf
2) difficile à porter en 2005 (trou de sécurité)
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
J'aurais besoin de conseil pour la situation suivante.
On a un serveur SQL Server 2000, qui contient un grand nombre de table de taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes joignant les tables sur le serveur (dont certain sont très grosse - plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables provenant du client, assez courte (de l'ordre de la centaine d'enregistrement). Le resultat étant lui même un ensemble d'enregistrement assez court (de l'ordre de la centaine de ligne) qui sera récupérée côté client.
La solution que j'imagine est la suivante: lorsque l'utilisateur a besoin des récupérer ses données, le logiciel client crée sur le serveur la ou les tables temporaires, en recopiant la centaine d'enregistrement (ce qui ne doit pas être dramatique en terme de performance), executer les requetes côté serveur, puis recuperer les données et détruire la ou les tables temporaires.
Plusieurs exemplaire du client pouvant tourner simultanément sur plusieurs postes différents.
Le concept de table temporaire local de SQL Server (table commençant par #) me paraissait une bonne idée, mais parait avoir un certain nombre de containte. En particulier, ces tables ne peuvent être référencée dans les vues, et je n'arrive pas à faire "voir" une telle table créer préalablement via commande passer par ADO par des procédures stockées.
Peut être fais-je fausse route, toute piste ou réflexion est bienvenue...
utilisez le concept de serveur liés et faites des requêtes avoir des jointures remote !
Oubliez openrowset qui : 1) est nul au point de vue perf 2) difficile à porter en 2005 (trou de sécurité)
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
Gilles Vollant \(MVP\)
"Pierre Goiffon" wrote in message news:456ab8bc$0$4508$
Gilles Vollant (MVP) wrote:
On a un serveur SQL Server 2000, qui contient un grand nombre de table de taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes joignant les tables sur le serveur (dont certain sont très grosse - plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables provenant du client, assez courte (de l'ordre de la centaine d'enregistrement). Le resultat étant lui même un ensemble d'enregistrement assez court (de l'ordre de la centaine de ligne) qui sera récupérée côté client.
Il est compliqué de vous suivre : ces "tables clients", où sont-elles situées ? Sur des SQL Server tournant sur les postes des applications ?
Vous pouvez regarder openrowset (possibilité d'ouvrir une source de données distance de manière dynamique, plus souple que les serveurs liés dans votre cas)
Non, ces tables clients sont sur une application simple coté client (pour l'instant sur Access, à terme sans doute dans un autre langage). En ecrivant une fonction qui génère la chaine de caractère XML avec tous les enregistrements, puis la méthode 4 de http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
j'ai résolu parfaitement mon problème.
Je n'ai qu'un seul serveur SQL serveur dans la configuration
"Pierre Goiffon" <pgoiffon@free.fr.invalid> wrote in message
news:456ab8bc$0$4508$426a74cc@news.free.fr...
Gilles Vollant (MVP) wrote:
On a un serveur SQL Server 2000, qui contient un grand nombre de table de
taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des
requetes joignant les tables sur le serveur (dont certain sont très
grosse - plusieurs dizaine de milliers d'enregistrement) avec une ou deux
tables provenant du client, assez courte (de l'ordre de la centaine
d'enregistrement).
Le resultat étant lui même un ensemble d'enregistrement assez court (de
l'ordre de la centaine de ligne) qui sera récupérée côté client.
Il est compliqué de vous suivre : ces "tables clients", où sont-elles
situées ? Sur des SQL Server tournant sur les postes des applications ?
Vous pouvez regarder openrowset (possibilité d'ouvrir une source de
données distance de manière dynamique, plus souple que les serveurs liés
dans votre cas)
Non, ces tables clients sont sur une application simple coté client (pour
l'instant sur Access, à terme sans doute dans un autre langage).
En ecrivant une fonction qui génère la chaine de caractère XML avec tous les
enregistrements, puis la méthode 4 de
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
j'ai résolu parfaitement mon problème.
Je n'ai qu'un seul serveur SQL serveur dans la configuration
"Pierre Goiffon" wrote in message news:456ab8bc$0$4508$
Gilles Vollant (MVP) wrote:
On a un serveur SQL Server 2000, qui contient un grand nombre de table de taille variable.
Un logiciel client (utilisant ADO ou ADO.net) va avoir la faire des requetes joignant les tables sur le serveur (dont certain sont très grosse - plusieurs dizaine de milliers d'enregistrement) avec une ou deux tables provenant du client, assez courte (de l'ordre de la centaine d'enregistrement). Le resultat étant lui même un ensemble d'enregistrement assez court (de l'ordre de la centaine de ligne) qui sera récupérée côté client.
Il est compliqué de vous suivre : ces "tables clients", où sont-elles situées ? Sur des SQL Server tournant sur les postes des applications ?
Vous pouvez regarder openrowset (possibilité d'ouvrir une source de données distance de manière dynamique, plus souple que les serveurs liés dans votre cas)
Non, ces tables clients sont sur une application simple coté client (pour l'instant sur Access, à terme sans doute dans un autre langage). En ecrivant une fonction qui génère la chaine de caractère XML avec tous les enregistrements, puis la méthode 4 de http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
j'ai résolu parfaitement mon problème.
Je n'ai qu'un seul serveur SQL serveur dans la configuration