[SQL] un coup de main pour rouler mon join?

Recherche :

Mot : Pseudo : Filtrer
Page : 1 2 3 Page Suivante Page Précédente Bas de page
Auteur	Sujet : [SQL] un coup de main pour rouler mon join?

joce

Architecte / Développeur principal
"BugHunter"

Reprise du message précédent :
Requète 1 :

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'p.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | select `test`.`a`.`nom` AS `nom`,`test`.`a`.`adresse` AS `adresse`,`test`.`p`.`prix` AS `prix` from `test`.`Adresses` `a` join `test`.`Prix` `p` where ((`test`.`p`.`id` = `test`.`a`.`id`) and (`test`.`p`.`datetime` = (select max(`test`.`p1`.`datetime`) AS `max(p1.datetime)` from `test`.`prix` `p1` where (`test`.`p1`.`id` = `test`.`p`.`id`)))) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Requete 2 :

| Note | 1276 | Field or reference 'p.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | select `test`.`a`.`id` AS `id`,`test`.`a`.`nom` AS `nom`,`test`.`a`.`adresse` AS `adresse`,`test`.`p`.`id` AS `id`,`test`.`p`.`datetime` AS `datetime`,`test`.`p`.`prix` AS `prix` from `test`.`Adresses` `a` join `test`.`Prix` `p` where ((`test`.`p`.`id` = `test`.`a`.`id`) and (`test`.`p`.`datetime` = (select `test`.`p1`.`datetime` AS `datetime` from `test`.`Prix` `p1` where (`test`.`p1`.`id` = `test`.`p`.`id`) order by `test`.`p1`.`datetime` desc limit 1))) |

Requète 3 :

| Note | 1003 | select `test`.`Adresses`.`id` AS `id`,`test`.`Adresses`.`nom` AS `nom`,`test`.`Adresses`.`adresse` AS `adresse`,`test`.`Prix`.`prix` AS `prix` from `test`.`Adresses` join `test`.`Prix` where ((`test`.`Prix`.`id` = `test`.`Adresses`.`id`) and <in_optimizer>((`test`.`Adresses`.`id`,`test`.`Prix`.`datetime`),<exists>(select `test`.`Prix`.`id` AS `id`,max(`test`.`Prix`.`datetime`) AS `dt_max` from `test`.`Prix` group by `test`.`Prix`.`id` having (((<cache>(`test`.`Prix`.`id`) = `test`.`Prix`.`id`) or isnull(`test`.`Prix`.`id`)) and ((<cache>(`test`.`Prix`.`datetime`) = max(`test`.`Prix`.`datetime`)) or isnull(max(`test`.`Prix`.`datetime`))) and <is_not_null_test>(`test`.`Prix`.`id`) and <is_not_null_test>(max(`test`.`Prix`.`datetime`)))))) |

J'ai été mauvaise langue, y a que la 3 qui est réécrite en type EXISTS

Message cité 1 fois

Publicité

joce

Architecte / Développeur principal
"BugHunter"

la façon dont la 3 est réécrite, ca tire bien la gueule quand même [:ddr555]

mrbebert

C'est quoi ces informations ? Comment on les obtient :??:

cinocks

Punaise elle fait mal la 3.

Pour la 1, il me semblerait logique que l'outils execute la sous-requete une seule fois pour definir les differents max puisqu'il sait que la contrainte se fait sur l'id.

---------------
MZP est de retour

avander

C'est mon premier topic qui fait 3 pages :whistle: , je vois qu'on s'éclate ici!

joce

Architecte / Développeur principal
"BugHunter"

oui d'ailleurs il specifie bien Field or reference 'p.id' of SELECT #2 was resolved in SELECT #1.

Ca m'etonne que recuperer un MAX prenne plus de temps qu'un ORDER BY desc. Ou alors c'est que l'index est tres mal positionne

Arjuna

Aircraft Ident.: F-MBSD

En tout cas, sous Oracle, les trucs bien plus gore sont plus rapide que des prises de tête pas croyable

Code :

CREATE OR REPLACE function CONVDEV2(p_codsoc in number,
p_dateve in varchar2,
p_dev1 in varchar2,
p_dev2 in varchar2,
p_natdev in varchar2,
p_montant in number)
return number is
/*
Fonction stockee de conversion de devises monetaires (arrondi a 2 decimales)
Créée par ROA le 11-10-1999
Modifiée par DUJ le 27-08-2002 :
- ne gère plus la conversion des devises in via l'Euro
- recherche le cours inverse si le cours demandé n'est pas trouvé
- ajout du paramètre Nature de devise plutôt que de l'écrire en dur dans la requête
UTILISATION : CONVDEV(codsoc,date,dev1,dev2,montant) => montant en devise dev2
codsoc = code societe
date = conversion par rapport a une date donnee
dev1 = devise du montant à convertir
dev2 = devise de conversion
natdev = nature de devise
montant = montant a convertir
*/
mont_con number;
begin
if (p_dev1<>p_dev2) then -- Les 2 devises sont différentes, conversion
begin
select p_montant*cours
into mont_con
from dem dem2
where dem2.codsoc = p_codsoc
and dem2.coddev = p_dev1
and dem2.coddev2 = p_dev2
and dem2.natdev = p_natdev
and dem2.datdeb = (select max(datdeb)
from dem
where dem.codsoc = dem2.codsoc
and dem.coddev = dem2.coddev
and dem.coddev2 = dem2.coddev2
and dem.natdev = dem2.natdev
and dem.datdeb <= p_dateve);
exception
when no_data_found then
-- Pas de cours trouvé, on recherche si le cours inverse existe
select decode(cours,0,0,p_montant/cours)
into mont_con
from dem dem2
where dem2.codsoc = p_codsoc
and dem2.coddev = p_dev2
and dem2.coddev2 = p_dev1
and dem2.natdev = p_natdev
and dem2.datdeb = (select max(datdeb)
from dem
where dem.codsoc = dem2.codsoc
and dem.coddev = dem2.coddev
and dem.coddev2 = dem2.coddev2
and dem.natdev = dem2.natdev
and dem.datdeb <= p_dateve);
end;
return round(mont_con,2);
else -- Les 2 devises sont les memes, pas de conversion
return p_montant;
end if;
exception
when others then
return 0;
end;
/
CREATE OR REPLACE function getTSC(p_codsoc tsc.codsoc%type,
p_achvte tsc.achvte%type,
p_sigtie tsc.sigtie%type,
p_codpro tsc.codpro%type,
p_coddev tsc.coddev%type,
p_datval tsc.datdeb%type)
return number is
-- SD : 20051221
-- Fonction retournant le tarif du type choisi applicable en fonction des paramètres
-- Membres de la clé primaire qui ne sont pas passés en paramètre.
-- Ils sont gérés en Variables, afin de pouvoir simplement
-- les rajouter en paramètre si besoin.
v_codreg tsc.codreg%type;
v_codetb tsc.codetb%type;
v_codbar tsc.codbar%type;
v_codvar tsc.codvar%type;
-- Variable de retour
r_tscprx tsc.prxtar%type;
-- Variables locales
v_coddev tsc.coddev%type;
begin
v_codreg := ' ';
v_codetb := ' ';
v_codbar := ' ';
v_codvar := ' ';
if p_achvte = 'R' then
select para1
into v_coddev
from parav
where codsoc = p_codsoc
and codpar = 'DEVNAT'
and codfct = ' ';
else
v_coddev := p_coddev;
end if;
select decode(p_achvte, 'R', prxrev, prxtar)
into r_tscprx
from tsc
where
codsoc = p_codsoc
and achvte = p_achvte
and sigtie = p_sigtie
and codpro = p_codpro
and codreg = v_codreg
and codetb = v_codetb
and coddev = v_coddev
and codbar = v_codbar
and codvar = v_codvar
and datdeb = (
select min(datdeb)
from tsc tsc2
where tsc2.codsoc = tsc.codsoc
and tsc2.achvte = tsc.achvte
and tsc2.sigtie = tsc.sigtie
and tsc2.codpro = tsc.codpro
and tsc2.codreg = tsc.codreg
and tsc2.codetb = tsc.codetb
and tsc2.coddev = tsc.coddev
and tsc2.codbar = tsc.codbar
and tsc2.codvar = tsc.codvar
and p_datval between tsc2.datdeb and decode(tsc2.datfin, ' ', to_char(sysdate, 'YYYYMMDD'), tsc2.datfin));
return r_tscprx;
exception
when no_data_found then return -1;
end;
/
CREATE OR REPLACE FORCE VIEW SOC1.WV_STKCPT
(CODSOC, LIB1, LIB2, LIB3, LIB4,
LIB5, LIB6, LIB7, LIB8, LIB9,
LIB10, LIB11, LIB12, LIB13, LIB14,
LIB15, LIB16, LIB17, LIB18, LIB19,
LIB20, DAT1, DAT2, DAT3, DAT4,
DAT5, NUM01, NUM02, NUM03, NUM04,
NUM05, NUM06, NUM07, NUM08, NUM09,
NUM10, VAL01, VAL02, VAL03, VAL04,
VAL05, VAL06, VAL07, VAL08, VAL09,
VAL10, VAL11, VAL12, VAL13, VAL14,
VAL15, VAL16, VAL17, VAL18, VAL19,
VAL20)
AS
select
dsk.codsoc,
dsk.sigdep, dsk.codpro, dsk.coduni, to_char(sysdate,'YYYYMMDD'), ' ',
' ', ' ', ' ', ' ', ' ',
' ', ' ', ' ', ' ', ' ',
' ', ' ', ' ', ' ', ' ',
' ', ' ', ' ', ' ', ' ',
round(decode(
getTSC(
dsk.codsoc,
'R',
' ',
dsk.codpro,
' ',
to_char(sysdate, 'YYYYMMDD')
),
-1,
convdev2(
dsk.codsoc,
to_char(sysdate, 'YYYYMMDD'),
(select tie.coddev
from tie, pro
where pro.codsoc = dsk.codsoc
and pro.codpro = dsk.codpro
and tie.codsoc = pro.codsoc
and tie.typtie = 'FOU'
and tie.sigtie = pro.sigfou),
(select para1
from parav
where codsoc = dsk.codsoc
and codpar = 'DEVNAT'
and codfct = ' '),
'STD',
getTSC(
dsk.codsoc,
'A',
(select pro.sigfou
from pro
where pro.codsoc = dsk.codsoc
and pro.codpro = dsk.codpro),
dsk.codpro,
(select tie.coddev
from tie, pro
where pro.codsoc = dsk.codsoc
and pro.codpro = dsk.codpro
and tie.codsoc = pro.codsoc
and tie.typtie = 'FOU'
and tie.sigtie = pro.sigfou),
to_char(sysdate, 'YYYYMMDD'))
),
getTSC(
dsk.codsoc,
'R',
' ',
dsk.codpro,
' ',
to_char(sysdate, 'YYYYMMDD'))
), 2), 0, 0, 0, 0,
0, 0, 0, 0, 0,
dsk.c01, dsk.c02, dsk.c03, dsk.c04, dsk.c01,
0, 0, 0, 0, 0,
0, 0, 0, 0, 0,
0, 0, 0, 0, 0
from dsk
where c01 > 0;
select *
from wv_stkcpt
where codsoc = 2
and num01 < 0
-> 5 lignes en 2 secondes sur un serveur NT et des tables bien remplies.
Pourtant ce serveur ramme particulièrement lors de traîtements relativement simples par moment. Preuve que Oracle se sort remarquablement bien des trucs qui marchent sur la tête :D
PS: Oracle 8.1.7

Berceker United

PSN : berceker_united

le join n'a pas finit d'etre fumé ?

Arjuna

Aircraft Ident.: F-MBSD

t'as fini de changer de pseudo toutes les 30 secondes toi ?

après je comprends jamais plus qui est qui

Berceker United

PSN : berceker_united

non j'ai toujours gardé celui-ci

Publicité

joce

Architecte / Développeur principal
"BugHunter"

il est passé où notre expert SQL leflos5 :??:

cinocks

il nous pond une requete simple

---------------
MZP est de retour

joce

Architecte / Développeur principal
"BugHunter"

il a l'air constipé

mrbebert

Comment on obtient toutes ces infos sur une requête :??:

joce a écrit :

Requète 1 :

...

avander

J'étais tout content avec la requete de joce mais quand j'ai voulu la mettre en production je suis tombé sur un os...
en effet point de mySQL 5 en production mais un archaique mySQL 4.0... qui est complètement hermétique au subqueries :whistle:

En consultant la doc de mySQL j'ai quand même pondu un truc qui semble tenir la route et ce sans subquery :ange: grâce aux examples fourni ici

http://dev.mysql.com/doc/refman/4. [...] p-row.html

et la requete finale:

select s1.id, s3.nom, s1.date, s1.prix, count(*) as records
from prix s1

inner join prix s2
on s1.id = s2.id

inner join adresses s3
on s1.id = s3.id

group by s1.id, s1.date, s1.prix

having s1.date = max(s2.date);

Ne me dites pas que c'est pas terrible niveau perfs... je m'en doute un peu!

Message cité 1 fois

joce

Architecte / Développeur principal
"BugHunter"

avander a écrit :

Ne me dites pas que c'est pas terrible niveau perfs... je m'en doute un peu!

tu pourrais passer à MySQL 4.1 au moins

avander

lol, j'ai pas le choix c'est mon hébergeur qui décide...

joce

Architecte / Développeur principal
"BugHunter"

change d'hebergeur
parce que 4.1 est déjà considéré comme deprecated, alors 4.0 voila quoi

avander

je vais toujours lui ( www.b-one.net) en parler, on ne sait jamais... maintenant pour 1,25 par mois c'est peut-être abuser aussi. :ange:

avander

Voila une réponse encourageante, qui demande rien n'a rien...

Thank you for contacting B-one!

The current version of MySQL is Client API
version 4.0.24 . We do intend to make MySQL
5.0 available in the near furture but I cannot
give you the exact time frame when it will
be available.

joce

Architecte / Développeur principal
"BugHunter"

ah ouais ils passent directement de 4.0 a 5.0 sans passer par la case 4.1

joce

Architecte / Développeur principal
"BugHunter"

autre méthode marrante qui a priori fonctionne :

SELECT a.*,p.* FROM adresse as a, prix as p, prix as p1 WHERE a.id=p.id AND p.id=p1.id AND p.date<p1.date AND p1.id IS NULL

Message cité 1 fois
Message édité par joce le 18-01-2006 à 01:46:06

avander

joce a écrit :

autre méthode marrante qui a priori fonctionne :

SELECT a.*,p.* FROM adresse as a, prix as p, prix as p1 WHERE a.id=p.id AND p.id=p1.id AND p.date<p1.date AND p1.id IS NULL

:gratgrat: je comprends pas trop l'entourloupe avec p1.id IS NULL mais je vais tester...

si ça marche en 4.0 se serait cool parce que pour l'instant je dois le faire en deux temps ( sélectionner les prix dans une table temporaire puis lecture de la temporaire avec order by sur les codes postaux). Avec ceci je pourrais rajouter l'order by direct, nan?

Message cité 1 fois
Message édité par avander le 18-01-2006 à 10:06:01

Arjuna

Aircraft Ident.: F-MBSD

joce, quand t'auras 5 minutes à perdre...
http://forum.hardware.fr/forum2.ph [...] w=0&nojs=0

joce

Architecte / Développeur principal
"BugHunter"

avander a écrit :

Seulement l'enregistrement qui a la date la plus recente ne peut pas avoir la condition de jointure p.date<p1.date vrai, donc il est associe a un p1 NULL, d'ou la condition sur p1.id IS NULL pour ne recuperer que cette ligne qui nous interesse.

oups d'ailleurs c'est :

SELECT a.*,p.* FROM adresse as a, prix as p LEFT JOIN prix as p1 ON (p.id=p1.id AND p.date<p1.date) WHERE a.id=p.id AND p1.id IS NULL

Message cité 1 fois

Arjuna

Aircraft Ident.: F-MBSD

je préfère avec le left join, je comprenais pas très bien non plus la requête sans lui.
me disais "encore un délire de MySQL"

avander

joce a écrit :

...
oups d'ailleurs c'est :

SELECT a.*,p.* FROM adresse as a, prix as p LEFT JOIN prix as p1 ON (p.id=p1.id AND p.date<p1.date) WHERE a.id=p.id AND p1.id IS NULL

J'espère que ça marche mieux que le précedent... :whistle:

C'est effectivement le cas après avoir testé: magnifique !! :jap: :jap: :jap:

Message édité par avander le 18-01-2006 à 14:36:03

joce

Architecte / Développeur principal
"BugHunter"

:sol:
par contre je serais curieux de voir le resultat du test de vitesse

(enfin dans l'esprit ca ressemble à

select s1.id, s3.nom, s1.date, s1.prix, count(*) as records
from prix s1

inner join prix s2
on s1.id = s2.id

inner join adresses s3
on s1.id = s3.id

group by s1.id, s1.date, s1.prix

having s1.date = max(s2.date); )

Message édité par joce le 19-01-2006 à 00:08:53

avander

Je ferais la comparaison en lancant les deux requêtes à partir de phpMyAdmin...

avander

Choses promises, choses dues...

Les requêtes sont lancées à partir de phpMyAdmin, j'ignore si il y a un méchanisme de cache qui joue, j'obtiens le même résultat si je me déconnecte d'abord...

Code :

SELECT s1.id, s1.date, s3.nom, s3.adresse, s3.cp, s3.localite, s1.prix
FROM prix s1
INNER JOIN prix s2
ON s1.id = s2.id
INNER JOIN adresses s3
ON s1.id = s3.id
GROUP BY s1.id, s1.date, s1.prix
HAVING s1.date = MAX( s2.date)

L'ancienne requête: 0,0042 sec pour 16 résultats

Code :

SELECT p.id, p.date, a.nom, a.adresse, a.cp, a.localite, p.prix
FROM adresses as a, prix as p
LEFT JOIN prix as p1
ON ( p.id = p1.id AND p.date < p1.date)
WHERE a.id = p.id AND p1.id IS NULL

La nouvelle version: 0,0019 sec toujours pour 16 résultats

Code :

SELECT p.id, p.date, a.nom, a.adresse, a.cp, a.localite, p.prix
FROM adresses as a, prix as p
LEFT JOIN prix as p1
ON ( p.id = p1.id AND p.date < p1.date)
WHERE a.id = p.id AND p1.id IS NULL
ORDER BY a.cp

La nouvelle avec order by: 0,0002 sec toujours pour 16 résultats :pt1cable:

Une idée pourquoi l'order by dope le résultat de façon spectaculaire? :gratgrat:

Arjuna

Aircraft Ident.: F-MBSD

Une idée pourquoi l'order by dope le résultat de façon spectaculaire?
-> Parceque c'est la même requête que précédement, et qu'elle est déjà en cache.

Sinon, tes résultats ne sont pas très parlants... Met au moins 10000 lignes et refait les requêtes, parceque sur 16 résultats, c'est trop juste... Notamment la première requête c'est tapé toutes les IO pour charger les données en mémoire, et les autres se sont basées sur les données déjà en mémoire pour travailler, donc les résultats sont fortement biaisés.

Message cité 1 fois

avander

Arjuna a écrit :

Une idée pourquoi l'order by dope le résultat de façon spectaculaire?
-> Parceque c'est la même requête que précédement, et qu'elle est déjà en cache.

Oui je m'en doute un peu, mais comment faire pour lancer les requêtes dans des conditions de départ identiques alors...

Arjuna a écrit :

Sinon, tes résultats ne sont pas très parlants... Met au moins 10000 lignes et refait les requêtes, parceque sur 16 résultats, c'est trop juste...
....

Tu fais quoi ce weekend? :ange:

Message cité 2 fois

joce

Architecte / Développeur principal
"BugHunter"

avander a écrit :

Oui je m'en doute un peu, mais comment faire pour lancer les requêtes dans des conditions de départ identiques alors...

Tu fais quoi ce weekend? :ange:

key_buffer_size à 0 et query_cache à 0 ca devrait pas mal aider

Arjuna

Aircraft Ident.: F-MBSD

avander a écrit :

Tu fais quoi ce weekend? :ange:

bah tu peux faire un script genre :

for i = 1 to 100000
insert dans ma table des conneries aléatoires;
next

Publicité

Page : 1 2 3

Page Suivante

Page Précédente

Haut de page

FORUM HardWare.fr

Programmation

SQL/NoSQL

[SQL] un coup de main pour rouler mon join?

Sujets relatifs
export de phpmyadmin vers SQL Server	ordonner les resultats d'une requete SQL !
[SQL] Sauvegarder un fichier dans un blob ?	Pb de PL SQL
PROMPT dans une procédure PL SQL	Hibernate Commande SQL direct
SQl\excel	Import Export de données dans une BDD SQL et fichier texte
SQL Server 2005 Express et Full-text index ?	[SQL Server]Reporting Services : Pages blanches à l'export des reports
Plus de sujets relatifs à : [SQL] un coup de main pour rouler mon join?

Page générée en 0.133 secondes