SQL(server) optimisation d'une requête

SQL(server) optimisation d'une requête - SQL/NoSQL - Programmation

Marsh Posté le 13-01-2005 à 10:02:01    

Voilà j'ai une table qui contient des taux de change entre devises dont voici les colonnes :
-date
-fournisseur du taux
-devise1
-devise2
-taux
 
J'ai une autre table qui contient des transactions dont voici les colonnes :  
-date
-devise de la transaction
-devise du portefeuille
-quantite
 
J'ai une troisieme table qui contient le prix des actifs ;
-date
-fournisseur du prix
-prix
-id de l'actif
 
 
J'ai une requete SQL qui calcul la formule suivante Prix * Quantité * Taux de change à une date donnée :
Mon problème est que je ne suis pas certains que j'aurai un taux de change à cette date là, si ce n'est pas le cas je dois prendre le taux de change le plus proche de la date (par exemple le 31/10/2004).
 
SELECT Prix * quantite *  
(SELECT Taux From Change WHERE RateDate <= (SELECT MAX(ratedate) WHERE ratedate <= '10/31/2004') AND Change.Devise1 = Transactions.Devise1 AND Change.Devise2 = Transactions.Devise2)  
FROM Transactions,Prix AS B  
WHERE  
Transactions.Date <= '10/31/2004' AND B.PriceDate =  
(SELECT MAX(PriceDate) From Prix AS H WHERE H.Actif = Transactions.Actif AND H.ratedate <= '10/31/2004')  
AND B.FournisseurID = (SELECT TOP 1 From Prix where H.Actif = Transactions.Actif AND H.ratedate <= (SELECT MAX(PriceDate) From Prix AS H WHERE H.Actif = Transactions.Actif and  
H.ratedate <= '10/31/2004'))
 
 
Il manque surement des liens entre les tables mais la n'est pas le plus important. (J'ai pas fait un copier coller direct de la requete pour plus de clareté).
Mon problème est que cette requete est assez lente meme avec des index avez vous une idée pour l'optimiser. (Si vous avez des questions n'hesitez pas. Je vous remercie deja d'avoir lu le message)
Merci

Reply

Marsh Posté le 13-01-2005 à 10:02:01   

Reply

Marsh Posté le 13-01-2005 à 11:11:22    

Si personne t'as répondu d'ici là, je me pencherai sur ton problème plus tard dans la journée, là ça va prendre trop de temps ;)

Reply

Marsh Posté le 13-01-2005 à 11:57:05    

T'ain, elle est compliquée ta requête, j'y comprends rien :D

Reply

Marsh Posté le 13-01-2005 à 11:59:51    

Oui c'est bien pour ca que j'ai besoin d'un coup de main. Si tu veux je fais Prix * Quantite * Taux de change.
Le prix est dans une table, la quantite dans une autre et le taux de change dans la derniere. Le probleme c'est que à la date de ma transaction(donc la date de la quantite) je n'ai peut etre pas de taux de change ou de prix donc je fais des sous select max pour trouver les taux les plus proches

Reply

Marsh Posté le 13-01-2005 à 12:01:04    

Bon, déjé, je suis en train de refaire ton modèle des données, et j'ai un problème : les transactions ne portent sur aucun actif.
 
Est-ce que tu peux poster le script de création de test tables ? (et si possible un script contenant les insert pour mettre quelques données, ça m'évitera de me créer un jeu de test ;))

Reply

Marsh Posté le 13-01-2005 à 12:01:52    

mystereetbouledegomme a écrit :

Oui c'est bien pour ca que j'ai besoin d'un coup de main. Si tu veux je fais Prix * Quantite * Taux de change.
Le prix est dans une table, la quantite dans une autre et le taux de change dans la derniere. Le probleme c'est que à la date de ma transaction(donc la date de la quantite) je n'ai peut etre pas de taux de change ou de prix donc je fais des sous select max pour trouver les taux les plus proches


ça, j'avais compris, mais c'est écrit bizarrement je trouve ;)

Reply

Marsh Posté le 13-01-2005 à 12:03:00    

Bon, dans ta requête, y'a un transaction.actif, donc j'en déduis que tu l'as simplement oublié dans la description des tables
 
 
Groumpf. Il y a aussi un problème entre les actifs et les fournisseurs. Tu peux expliquer comment ça marche ?


Message édité par Arjuna le 13-01-2005 à 12:11:50
Reply

Marsh Posté le 13-01-2005 à 12:11:39    

Oui je voulais dire id de l'actif :D sorry Mais je suis presque certain qu'on peut mieux faire. Dis moi peut etre comment toi tu l'aurtais ecrit


Message édité par mystereetbouledegomme le 13-01-2005 à 12:12:20
Reply

Marsh Posté le 13-01-2005 à 12:20:09    

Bon, moi je t'ai pondu ça :

Code :
  1. select transactions.[date], transactions.devisetrans, transactions.deviseport, transactions.actif, transactions.quantite *
  2.        (select top 1 change.taux
  3. from change
  4. where change.[date] <= transactions.[date]
  5. and change.devise1 = transactions.devisetrans
  6. and change.devise2 = transactions.deviseport
  7. order by change.[date] desc) *
  8.        (select top 1 prix.prix
  9. from prix
  10. where prix.[date] <= transactions.[date]
  11. -- and prix.fournisseur = transactions.fournisseur
  12. and prix.actif = transactions.actif
  13. order by prix.[date] desc) prix_calcule
  14. from transactions
  15. where transactions.[date] <= '10/01/2005'


 
Mais ton modèle me semble bizarre, je ne suis pas certain que pour les devises ça soit correct :
Le prix exprimé dans la table des prix, il est exprimé en quelle monnaie ? Lui aussi il faut le convertir je suppose, et non seulement le prix au final qu'il faut passer dans la monnaie du portefeuil. Non ?

Reply

Marsh Posté le 13-01-2005 à 12:23:10    

Ma base :
 

Code :
  1. CREATE TABLE [dbo].[change] (
  2. [date] [datetime] NOT NULL ,
  3. [fournisseur] [numeric](18, 0) NOT NULL ,
  4. [devise1] [char] (3) COLLATE French_CI_AS NOT NULL ,
  5. [devise2] [char] (3) COLLATE French_CI_AS NOT NULL ,
  6. [taux] [float] NOT NULL
  7. ) ON [PRIMARY]
  8. GO
  9. CREATE TABLE [dbo].[prix] (
  10. [date] [datetime] NOT NULL ,
  11. [fournisseur] [numeric](18, 0) NOT NULL ,
  12. [prix] [float] NOT NULL ,
  13. [actif] [numeric](18, 0) NOT NULL
  14. ) ON [PRIMARY]
  15. GO
  16. CREATE TABLE [dbo].[tree] (
  17. [id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
  18. [nom] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  19. [parent] [numeric](18, 0) NULL
  20. ) ON [PRIMARY]
  21. GO


 
Mes données :

Code :
  1. select * from transactions
  2. date                                                   devisetrans deviseport quantite             actif               
  3. ------------------------------------------------------ ----------- ---------- -------------------- --------------------
  4. 2005-01-08 00:00:00.000                                EUR         USD        5                    1
  5. (1 ligne(s) affectée(s))
  6. select * from change
  7. date                                                   fournisseur          devise1 devise2 taux                                                 
  8. ------------------------------------------------------ -------------------- ------- ------- -----------------------------------------------------
  9. 2005-01-01 00:00:00.000                                1                    EUR     USD     1.2
  10. 2005-01-05 00:00:00.000                                1                    EUR     USD     1.3
  11. (2 ligne(s) affectée(s))
  12. select * from prix
  13. date                                                   fournisseur          prix                                                  actif               
  14. ------------------------------------------------------ -------------------- ----------------------------------------------------- --------------------
  15. 2005-01-05 00:00:00.000                                1                    5.0                                                   1
  16. 2005-01-04 00:00:00.000                                1                    6.0                                                   1
  17. 2005-01-05 00:00:00.000                                2                    10.0                                                  1
  18. 2005-01-04 00:00:00.000                                1                    12.0                                                  2
  19. (4 ligne(s) affectée(s))

Reply

Marsh Posté le 13-01-2005 à 12:23:10   

Reply

Marsh Posté le 13-01-2005 à 12:29:33    

Alors, ça donné quoi ?
 
Je parie qu'il est parti bouffer pendant que je jeûnais pour l'aider :o

Reply

Marsh Posté le 13-01-2005 à 12:45:02    

je pose juste une question : y'a t'il absolument besoin de déporter le calcul sur le SGBD ? non parce que je connais rien de plus crade que ce genre de manip, lourde pour le SGBD. les traitements post extraction doivent se faire à mon avis dans une procédure stockée ou directement dans l'application cliente, mais pas dans la requete

Reply

Marsh Posté le 13-01-2005 à 13:10:23    

Harkonnen : si tu pouvais faire comprendre ca à mes patrons :D

Reply

Marsh Posté le 13-01-2005 à 13:33:17    

Bon alors en tripatouillant la requete pour avoir les bons noms de champs, j'obtiens 81.9% pour la requete avant modif et 18.11 pour la tienne. A creuser mais je pense que tu es dans le bon.  
Merci bcp pour ton temps. :D

Reply

Marsh Posté le 13-01-2005 à 14:10:41    

Harkonnen a écrit :

je pose juste une question : y'a t'il absolument besoin de déporter le calcul sur le SGBD ? non parce que je connais rien de plus crade que ce genre de manip, lourde pour le SGBD. les traitements post extraction doivent se faire à mon avis dans une procédure stockée ou directement dans l'application cliente, mais pas dans la requete


Rien n'empêche de reprendre cette requête et en faire une fonction retournant une table (une vue paramètrée quoi).
 
Sinon, au contraire, moi je suis pour l'intégration le plus possible dans les PS (ou requêtes quand on ne peut pas utiliser de PS), dans la mesure ou les traîtements/calculs font partie de l'intégrité des données.
 
Dans ce cas concret par exemple, si on veut faire ça dans le programme, on risque rapidement de faire des centaines de requêtes (au moins deux par ligne de "transactions" ) alors que les données récupérées par ces requêtes ne sont pas directement exploitables (une transaction sans prix, ça n'a pas de sens, tout comme récupérer le prix d'un actif à un instant T sans le rattacher à une transaction n'a pas d'intérêt).
 
Actuellement, je suis en train de faire un CMS pour mon boulot. Par exemple, lorsque je modifie un article "validé", au lieu de le modifier, je dois le dupliquer, laisser l'original tel quel, et rattacher le nouveau à l'ancien, afin de gérer du versionning, et permettre côté front la consultation des données avant modification, tant que les nouvelles valeurs n'ont pas été validées.
Ensuite, lorsque je valide la nouvelle version, je flag l'ancienne version comme supprimée, et je recopie tous ses fils dans la nouvelle version.
 
Ces traîtements ont beau être compliqué, ils font partie intégrante des règles de gestion de mes données, et ne sont donc pas des traîtements en tant que tel. Résultat, toutes ces actions sont intégralement gérées en PS, de façon à ce que dans le programme lui-même, je n'ait pas à me soucier du status de l'article avant d'y apporter des modifications : je fais un update, create, delete ou validate sans me soucier du traîtement qui est derrière.
De la même façon, si je demande la suppression d'un article qui a des fils non supprimé, c'est ma PS qui lève une exception et me permet de rollbacker les modifs et afficher un message d'erreur. A nouveau, parceque si demain je décide de changer le fonctionnement de l'appli (suppression en cascade, ou gestion d'une nouvelle erreur), je n'aurai pas à retoucher l'appli, mais uniquement la PS qui s'occupe de faire le traîtement (ou même simplement modifier les règles d'intégrité au sein des tables concernées).
 
Par contre, le SGBD ne doit pas gérer des éléments comme décider des champs à rammener selon le type d'article. C'est totalement dépendant du programme et ça ne change rien dans la cohérence des données qu'un article de type "news" aie un titre, une date et un corps, tandis qu'un mode d'emploi ait des chapitres et des images : dans tous les cas ma PS "getObj" me ramène l'article entier, et c'est le soft qui choisi lesquels afficher ou non.
 
Chais pas si je suis clair dans ma façon de découper les traîtements "fonctionnels" et la gestion de cohérence des données.


Message édité par Arjuna le 13-01-2005 à 14:13:27
Reply

Marsh Posté le 13-01-2005 à 14:22:59    

Enfin, sinon, pour moi, le mieu c'est de faire de cette requête une vue, sans filtre sur la date ni autre (mais retourner ces champs).
 
Ensuite un simple select dans cette vue en filtrant sur les éléments voulus permettra de retourner les données calculées, avec de bonnes performances.

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed