[SQL] Petit "cours" sur l'optimisation

Recherche :

Mot : Pseudo : Filtrer
Page : 1 2 Page Suivante Page Précédente Bas de page
Auteur	Sujet : [SQL] Petit "cours" sur l'optimisation

Arjuna

Aircraft Ident.: F-MBSD

Salut.

Y'a un moment déjà, j'avais indiqué que ce qui a le plus d'importance après les index, c'est l'ordre des tables dans la clause FROM.

Je reviens à la charge avec une petite modification : dans certains cas (j'ai pu tester), c'est bien plus important que les index, y compris pour les tables insignifiantes (celles qu'on ne se soucie généralement pas trop d'optimiser).

Comment ça marche ?

Les SGBD (tous ?), du moins, Oracle et SQL Server sont TRES sensibles à l'ordre des tables dans la clause FROM. Avec ou sans clés étrangères explicites, ces deux SGBD en tout cas, font confiance à l'ordre des tables dans la clause FROM pour optimiser leur plan d'éxécution.

Première chose qu'on remarque : les SGBD lisent cette clause "à l'envers". En effet, alors qu'on serait tenté de mettre "le point d'entrée" de la requête en premier, c'est au contraire en dernier qu'il faut le mettre !

Exemple basic :
J'ai une table "SOCIETE", avec les champs "SOC_ID" et "NOM". J'ai une table "EMPLOYE" avec les champs "SOC_ID", "EMP_ID" et "NOM". J'ai ensuite une table "CONGES" avec les champs "EMP_ID", "DATE_DEB" et "DATE_FIN".

Je veux retouver la durée totale des congés de tous les employés toutes sociétés confondues, pour l'année 2004.
Voici ce qu'on serait tenté d'écrire, ici avec la syntaxe SQL Server.
Vous noterez que je n'utilise pas la syntaxe "... INNER JOIN ... ON ...", pour deux raisons :
- Le parseur SQL se moque éperduement de cette syntaxe, ça ne l'aide en rien à faire les requêtes.
- Je trouve cette syntaxe lourde, et particulièrement difficile à lire, notamment quand on a un grand nombre de tables ou/et des jointures entre plusieurs tables à la fois.
- Pour moi, le critère de jointure est un filtre comme un autre. Je préfère donc le spécifier dans la clause WHERE dédiée aux filtres.
- Contrôler l'ordre des tables dans la clause FROM quand on utilise une telle syntaxe est quasi-impossible à faire.

Code :

SELECT SOCIETE.NOM, EMPLOYE.NOM, sum(datediff(d, CONGES.DATE_DEB, CONGES.DATE_FIN))
FROM SOCIETE, EMPLOYE, CONGES
WHERE EMPLOYE.SOC_ID = SOCIETE.SOC_ID
AND CONGES.EMP_ID = EMPLOYE.EMP_ID
AND year(CONGES.DATE_DEB) = 2004
GROUP BY SOCIETE.NOM, EMPLOYE.NOM

Cette requête marche très bien. Etant donné la simplicité de cette dernière, l'odre des tables dans la clause FROM n'impactera que très peu la rapidité globale. Cependant, c'est une véritable catastrophe niveau optimisation !

Rappelez-vous, j'ai dit que le point d'entrée de la requête devait se trouver en dernier. Penser que "CONGES" avec le filtre sur l'année est le point d'entrée est une hérésie. Un point d'entrée est par définition la table la plus restrictive. Il y a normalement moins de sociétés que d'employés et moins d'employés que de congés (ou alors y'a un problème )
La requête optimisée aura alors pour syntaxe :

Code :

SELECT SOCIETE.NOM, EMPLOYE.NOM, sum(datediff(d, CONGES.DATE_DEB, CONGES.DATE_FIN))
FROM CONGES, EMPLOYE, SOCIETE
WHERE EMPLOYE.SOC_ID = SOCIETE.SOC_ID
AND CONGES.EMP_ID = EMPLOYE.EMP_ID
AND year(CONGES.DATE_DEB) = 2004
GROUP BY SOCIETE.NOM, EMPLOYE.NOM

Le changement n'est pas flagrant, mais il est suffisant pour améliorer considérablement les performances.
Vous noterez que l'odre des éléments de la clause SELECT et de la clause WHERE n'ont strictement aucun impact sur les performances.

Maintenant, un cas moins évident.
J'ai une table "EMPLOYE", avec la même structure que précédement. J'ai une table "DROITS" avec les champs "EMP_ID", "PROG_ID" et "TYPE_DROIT". Une autre table "PROGRAMME", avec les champs "PROG_ID" et "NOM", et enfin une table "TYPE_DROITS" avec les champs "TYPE_DROIT" et "LIBELLE".

Je veux retrouver tous les droits qu'on les utilisateurs de la société "Microsoft" sur le programme "Excel".

Premier jet :

Code :

SELECT EMPLOYE.NOM, TYPE_DROITS.LIBELLE
FROM SOCIETE, EMPOYE, DROITS, TYPE_DROITS, PROGRAMME
WHERE SOCIETE.NOM = 'Microsoft'
AND EMPLOYE.SOC_ID = SOCIETE.SOC_ID
AND DROITS.EMP_ID = EMPLOYE.EMP_ID
AND TYPE_DROITS.TYPE_DROIT = DROITS.TYPE_DROIT
AND PROGRAMME.PROG_ID = DROITS.PROG_ID
AND PROGRAMME.NOM = 'Excel'

Maintenant, on va réfléchir un peu.

J'ai 50 programmes différents.
J'ai 10 sociétés
J'ai 50 000 employés
J'ai 2 types de droits
J'ai 250 000 droits

Quel est le point d'entrée ? (donc qu'on va mettre en dernier, puis précéder des tables les plus restrictives suivantes)
Du tac au tac, on se dit "table la plus restrictive = table où y'a le moins de lignes, donc c'est type_droits".
MIIIP ! Mauvaise réponse.
La table la plus restrictive est la table qui, AVANT filtre contient un maximum de données, mais APRES filtre en contient le moins.
C'est donc soit PROGRAMME, soit SOCIETE. Pour programme, on grade seulement 2% des lignes (1/50) alors que pour la table société, on en garde 10% (1/10).
On garde donc la table "PROGRAMME", qui est la plus restrictive.
Ensuite, on est tenté de mettre la table "SOCIETE" en second. En effet, c'est la seconde la plus restrictive.
Ratté. En effet, les SGBD n'aiment pas trop devoir boucler sur plusieurs tables à la fois sans savoir qui est rattaché avec qui. La seconde sera donc "DROITS". Entre "EMPLOYE" et "TYPE_DROITS", qui est la plus restrictive ensuite ? Type_droits ? Non. Pourquoi ? Parcequ'on ne va pas la filter du tout. A partir de là, elle n'apporte rien à l'optimisation, mise à par ajouter un varchar en mémoire pour chaque ligne de droits répondant à l'application "Excel". "EMPLOYE", par contre, va nous premettre de filtrer énormément dès qu'on va le lier à "SOCIETE". Allez, zou, on ajoute donc "EMPLOYE" et "SOCIETE". Et enfin, on rajoute "TYPE_DROITS". C'est pas vraiment ce qu'on avait imaginé au début !

Ca donne donc, après optimisation :

Code :

SELECT EMPLOYE.NOM, TYPE_DROITS.LIBELLE
FROM TYPE_DROITS, SOCIETE, EMPOYE, DROITS, PROGRAMME
WHERE SOCIETE.NOM = 'Microsoft'
AND EMPLOYE.SOC_ID = SOCIETE.SOC_ID
AND DROITS.EMP_ID = EMPLOYE.EMP_ID
AND TYPE_DROITS.TYPE_DROIT = DROITS.TYPE_DROIT
AND PROGRAMME.PROG_ID = DROITS.PROG_ID
AND PROGRAMME.NOM = 'Excel'

Vous noterez que "par chance", la version "optimisée" est quasi identique à la requête qui était venue littéralement. Ne vous y fiez pas, c'est uniquement parcequ'il y avait plus de programmes de que sociétés. Si les nombres de lignes de ces deux tables avait été inversés, ça aurait été totalement différent :

Code :

SELECT EMPLOYE.NOM, TYPE_DROITS.LIBELLE
FROM TYPE_DROITS, PROGRAMME, DROITS, EMPOYE, SOCIETE
WHERE SOCIETE.NOM = 'Microsoft'
AND EMPLOYE.SOC_ID = SOCIETE.SOC_ID
AND DROITS.EMP_ID = EMPLOYE.EMP_ID
AND TYPE_DROITS.TYPE_DROIT = DROITS.TYPE_DROIT
AND PROGRAMME.PROG_ID = DROITS.PROG_ID
AND PROGRAMME.NOM = 'Excel'

Voilà. Je sais pas si ça va être utile à certains, en tout cas, après avoir été confronté à une requête qui mettais plus de 2 minutes à s'éxécuter hier, j'ai juste interverti deux tables en appliquant cette logique, et le temps d'éxécution est tombé à 3 secondes ! Pourtant, les index utilisés notamment n'ont pas changés entre les deux versions, et les deux tables interchangées contenaient respectivement 68 et 12 lignes, sâchant que dans les deux cas, je ne gardais qu'une seule ligne, retrouvée en utilisant l'index unique de la primary key clusterée. Comme quoi, c'est pas de l'omptimisation en l'air !

Publicité

TBone

Pouet.

c'est plus long que je croyais, je lirai ça demain mating

---------------
A straight line is a special case of a curve. It's a curve which is uncurved. -- Susskind.

Arjuna

Aircraft Ident.: F-MBSD

Flemmard

glod 2

Votre trajet, notre projet.

C'est interressant mais t'as des chiffres ?
Si tu retestes avec des chiffres essaies de le faire sur une base dont t'as les scripts et file tes sql de test que jpuisse tenter sur mes bases (je bosse sur sql server, oracle et firebird régulièrement, et sur d'autres du style mysql et access de temps en temps...) j'aimerai bien vérifier tout ça

Arjuna

Aircraft Ident.: F-MBSD

Ben là, ça va être chaud, parceque la base en question fait quelques centaines de méga, et certaines tables font plusieurs centaintes de milliers de lignes...
Sans parler de la requête avec laquelle j'ai obtenu le résutlat (passer de 2 minutes à 3 secondes, c'est quand même pas mal ) qui fait plus d'une cinquantaine de lignes (jointures sur une douzaine de tables, avec un joli union au milieu )

Mais demain, si j'y pense, je vais me faire une petite base de test pour valider ces résultats, et pourquoi pas affiner la méthode. En effet, j'ai remarqué en écrivant le topic qu'il y a une légère incohérence par rapport à la requête que j'ai ainsi optimisé. M'enfin globalement, ça reste vrai tout de même, mais pas parfait.

Message édité par Arjuna le 31-03-2005 à 01:00:01

TBone

Pouet.

comme Glod2, je veux des chiffres !

mais si les chiffres sont probants, ce serait un bon plan que je m'y plonge

quid des Sybase, DB2/400 et mysql ?

---------------
A straight line is a special case of a curve. It's a curve which is uncurved. -- Susskind.

Beegee

L'idéal ce serait que tu essayes sous Oracle aussi, car il me semble que c'est un peu différent, et puis ça dépend du mode d'optimisation choisi (stats / rule).
Il me semblait que le SGBD ne prenait pas nécessairement les tables dans l'ordre inverse à celui indiqué ... mais j'ai fait quelques test simples et c'est apparemment le cas aussi sous Oracle

En tout cas, sous Oracle, on peut utiliser un "hint" pour s'assurer de l'ordre d'utilisation des tables :

Code :

SELECT /*+ ordered */ *
FROM CUSTOMER, ACCOUNT
WHERE ...;

Cette requête lira CUSTOMER d'abord, puis ACCOUNT ...

Arjuna

Aircraft Ident.: F-MBSD

TBone a écrit :

comme Glod2, je veux des chiffres !

mais si les chiffres sont probants, ce serait un bon plan que je m'y plonge

quid des Sybase, DB2/400 et mysql ?

Jamais bossé sur ces 3 bases, donc je ne peux rien dire

Cela dit, si Oracle et MS SQL Server, qui sont deux des ténors des SGBD courants, en profitent, je suppose que ce fonctionnement est applicable à la plupart des autres SGBD.

A vérifier tout de même.

Là, j'ai rien à faire au boulot, je ne sais pas pour combien de temps. Je vais commencer à me faire une base de test, et si j'ai le temps de faire des mesures, je vous préviens, sinon il faudra attendre ce soir

Arjuna

Aircraft Ident.: F-MBSD

Bon, le script d'initialisation d'une base simple est lancé.

Le volume de données ne va pas être énorme, mais vu que je tourne sur un Pii 400 avec 384 Mo de mémoire et un HD à 5400 trm

Arf, c'est marrant, au boulot il vient d'y avoir une micro-coupure de courant. Mon PC est tellement lent qu'il s'en est même pas rendu compte Tous les autres ont rebooté... Ironique, puisque je suis le seul à pas travailler :lol:

Le script d'allimentation de la base (SQL Server) :
J'espère que ça sera quand même représentatif d'une vraie base. (je mettrai le script de la base quand il aura fini, parceque là, ça ramme à mort, dans le forum ça écrit ce que j'écrit 20 secondes après, c'est pratique pour corriger les fautes de frappe )

Code :

declare @i as numeric
declare @j as numeric
set @i = 1
while @i <= 1000
begin
insert into t1 (t1_id, nom) values (@i, 'test t1 ' + cast(@i as varchar))
insert into t3 (t3_id, nom) values (@i, 'test t3 ' + cast(@i as varchar))
set @j = 1
while @j <= 1000
begin
insert into t2 (t1_id, t2_id, nom) values (@i, @j, 'test t2 ' + cast(@i as varchar) + ' ' + cast(@j as varchar))
insert into t23 (t1_id, t2_id, t3_id, libelle) values (@i, @j, @i, 'test t23 ' + cast(@i as varchar) + ' ' + cast(@j as varchar))
set @j = @j + 1
end
set @i = @i + 1
end

Message édité par Arjuna le 31-03-2005 à 10:58:39

Arjuna

Aircraft Ident.: F-MBSD

:heink:

En 6 minutes, il a fait 52 passages dans la boucle principale. C'est pas gagné

Publicité

joce

Architecte / Développeur principal
"BugHunter"

Ca ne s'applique pas a MySQL, c'est l'optimizer qui va decider dans quel sens doit se faire la jointure.
Si tu veux specifier explicitement l'ordre de la jointure et court-circuiter l'optimizer il faut utiliser la syntaxe A STRAIGHT_JOIN B au lieu de A,B. (donc la majorite des cas MySQL ordonne les tables de facon optimale, sauf cas assez rare et un peu tordu )

Message édité par joce le 31-03-2005 à 11:09:24

Arjuna

Aircraft Ident.: F-MBSD

ese-aSH a écrit :

hm je savais pas du tout ca, mais ca me semble gros quand meme, ils pourraient pas developper un pti truc qui s'arrange pour trouver l'ordre optimal des tables dans ces cas la ^^ (genre en essayant un peu toutes les combinaisons possibles et en gardant que la meilleure).
parceque bon les tables peuvent evoluer et du coup l'ordre optimal aussi non?

En effet, comme je l'ai souligné, l'ordre des tables est fortement influencé par leur contenu. Ainsi, les optimisations qui sont vraies aujourd'hui ne le sont pas forcément demain (d'où ma réaction aux questions du genre "donnez-moi vos règles principales pour optimiser un serveur SQL", phrase dénuée de sens, puisque ça dépend de "tout" ).

Comme l'a souligné Beegee, sous Oracle (peut-être sur SQL Server aussi, je ne maîtrise pas suffisament) on peut indiquer au parseur SQL différents modes d'optimisation. Tous ne sont pas aussi impactés par l'ordre des tables. Cela dit, cela reste le paramètre par défaut de ces deux SGBD, et pas conséquent, ce qu'on trouve en exploitation sur une très large majorité des serveurs.
Sinon, pour ce qui est de l'automatisation, c'est pas si évident à faire. Moi, je connais d'un point de vue logique ma base, et j'ai une idée précise du volume traîté et du volume attendu.
Le SGBD est incapable d'avoir une vision aussi globale. Donc pour optimiser, il faudrait qu'il fasse la requête par tous les moyens d'optimisation connus, puis qu'il garde la meilleur pour les utilisations ultérieures, ce qui est un peu lent
Deplus, la phase d'optimisation étant, dans la majorité des cas, la phase la plus lente de l'éxécution d'une requête, faciliter la tâche au parseur n'est pas plus mal, rien que là, on gagne en temps, sans compter sur l'exécution elle-même.

Arjuna

Aircraft Ident.: F-MBSD

joce a écrit :

C'est le cas aussi d'Oracle et SQL Server normalement. Sur une requête "simple", on a rarement des problèmes de ce genre, l'optimisation montre des résultats très faibles.
Cela dit, comme tu dis, dans les cas "tordus" (et quand on bosse sur des applis comme celles sur lesquelles je bosse, y'a que ça ), je pense que MySQL va lui aussi s'emmêler les pinceaux, et à ce moment, l'optimiseur prend les tables dans l'ordre indiqué dans la clause FROM.
Je pense que dans tous les cas, étant donné que cette optimisation est parmis les plus simples possibles, il vaut mieu la faire systématiquement, au moins on est sûr d'avoir le meilleur résultat possible
Allez Joce, réécris tout de suis le forum

Message édité par Arjuna le 31-03-2005 à 11:14:08

Arjuna

Aircraft Ident.: F-MBSD

Joce, d'ailleurs, tu devrais pouvoir confirmer. Il me semble avoir lu (mais ça fait un bail) que pour l'optimiseur, en réalité, il tente de trouver le moyen d'exécuter la requête le plus rapidement possible (logique).

Mais si au bout d'un délais X il n'a pas encore trouvé d'optimisation satisfaisante, il annule tout, et lance la requête sans optimisation, afin d'éviter de perdre trop de temps à optimiser (en fin de compte, c'est con de passer 5 minutes à optimiser une jointure super complexe entre 100 tables, si chacune contient 2 lignes )

Je pense que si c'est bien le fonctionnement, c'est à ce moment que cette syntaxe devient vraiment utile.

Message édité par Arjuna le 31-03-2005 à 11:20:07

Arjuna

Aircraft Ident.: F-MBSD

Sinon, quand je parle de requêtes "complexes", c'est de ce type de truc que je parle

C'est cette requête qui est passée de 2 minutes à 3 secondes après modification

Code :

CREATE procedure icsd_ARTAssetList
(
@orgid int,
@userid int,
@date1 datetime,
@date2 datetime,
@invdate1 datetime,
@invdate2 datetime,
@leasescheduleid int = 0,
@leasecontractid int = 0,
@duration int = 0,
@brand varchar(255) = '%',
@lineOfProduct varchar(255) = '%',
@manufacturerRef varchar(255) = '',
@description varchar(255) = '%',
@category varchar(255) = '',
@cat1 varchar(255) = '',
@cat2 varchar(255) = '',
@cat3 varchar(255) = '',
@serialnumber varchar(255) = '%',
@supplier varchar(255) = '%',
@invoice varchar(255) = '%',
@ref1 varchar(255) = '%',
@ref2 varchar(255) = '%',
@ref3 varchar(255) = '%',
@ref4 varchar(255) = '%',
@ref5 varchar(255) = '%',
@assetid int = 0,
@orderby varchar(255) = 'org'
)
AS
BEGIN
select orgid,
name,
leasescheduleid,
schedule,
-- inventory row
-- inventory autoincrement
manufacturerRef,
SerialNumber,
description,
brand,
lineOfProduct,
category,
cat1,
cat2,
cat3,
supplier,
invoice,
invcreation,
ref1,
ref2,
ref3,
ref4,
ref5,
leasecontractid,
contract,
assetid
from (
select o.orgid,
o.name,
ls.leasescheduleid,
ls.schedule,
-- inventory row
-- inventory autoincrement
a.ProductCode manufacturerRef,
a.SerialNumber,
a.description,
a.manufacture brand,
a.brand lineOfProduct,
zg.category,
a.catalogType cat1,
at1.value cat2,
at2.value cat3,
v.name supplier,
zf.numero invoice,
zf.datecreation invcreation,
a.reference1 ref1,
a.reference2 ref2,
a.reference3 ref3,
a.reference4 ref4,
a.reference5 ref5,
lc.leasecontractid,
lc.contract,
a.assetid
from organization v, zfactures zf, zfactureasset zfa, sdViewAttributesValues at2, sdViewAttributesValues at1, zCatalogTypeGenre zg, asset a, zloyer zl, contractline cl, leasecontract lc, leaseschedule ls, zUserContractPerimeter zu, organization o, UserOrg uo
where (uo.orgid = @orgid or @orgid = 0)
and uo.userid = @userid
and o.type = 'C'
and o.active = 'A'
and o.orgid = uo.orgid
and zu.userid = uo.userid
and zu.orgid = uo.orgid
and (zu.scheduleid = @leasescheduleid or @leasescheduleid = 0)
and ls.orgid = o.orgid
and ls.leasescheduleid = zu.scheduleid
and ls.active = 'A'
and ls.userights = 1
and lc.orgid = ls.orgid
and lc.leasescheduleid = ls.leasescheduleid
and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
and (lc.leasecontractid = zu.contractid or zu.contractid = -1)
and lc.active = 'A'
and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
and cl.contractlineid = (select max(cl2.contractlineid)
from contractline cl2
where cl2.leasecontractid = lc.leasecontractid
and cl2.active = 'A')
and cl.active = 'A'
and zl.leasecontractid = cl.leasecontractid
and zl.contractlineid = cl.ContractLineID
and a.orgid = o.orgid
and (a.assetid = @assetid or @assetid = 0)
and a.linetype in ('B', 'S')
and a.assetid = zl.assetid
and a.manufacture like '%' + @brand + '%'
and a.brand like '%' + @lineOfProduct + '%'
and a.description like '%' + @description + '%'
and (a.catalogtype = @cat1 or @cat1 = '')
and (a.productCode = @manufacturerRef or @manufacturerRef = '')
and zg.catalogType = a.catalogType
and zg.orgid = a.orgid
and (zg.category = @category or @category = '')
and at1.assetid =* a.assetid
and at1.attributename =* zg.subcat1
and (at1.value = @cat2 or @cat2 = '')
and at2.assetid =* a.assetid
and at2.attributename =* zg.subcat2
and (at2.value = @cat3 or @cat3 = '')
and zfa.assetid = a.assetid
and zfa.leasescheduleid = ls.leasescheduleid
and zfa.leasecontractid = lc.leasecontractid
and zfa.contractlineid = cl.contractlineid
and zf.factureid = zfa.factureid
and zf.type = 'F'
and a.serialNumber like '%' + @serialNumber + '%'
and v.orgid = a.VendorOrgID
and v.type = 'V'
and zf.numero like '%' + @invoice + '%'
and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
and a.reference1 like '%' + @ref1 + '%'
and a.reference2 like '%' + @ref2 + '%'
and a.reference3 like '%' + @ref3 + '%'
and a.reference4 like '%' + @ref4 + '%'
and a.reference5 like '%' + @ref5 + '%'
union all
select o.orgid,
o.name,
ls.leasescheduleid,
ls.schedule,
-- inventory row
-- inventory autoincrement
a.ProductCode manufacturerRef,
a.SerialNumber,
a.description,
a.manufacture brand,
a.brand lineOfProduct,
zg.category,
a.catalogType cat1,
at1.value cat2,
at2.value cat3,
v.name supplier,
zf.numero invoice,
zf.datecreation invcreation,
a.reference1 ref1,
a.reference2 ref2,
a.reference3 ref3,
a.reference4 ref4,
a.reference5 ref5,
lc.leasecontractid,
lc.contract,
a.assetid
from organization v, zfactures zf, zfactureasset zfa, sdViewAttributesValues at2, sdViewAttributesValues at1, zCatalogTypeGenre zg, asset a, zloyer zl, contractline cl, leasecontract lc, leaseschedule ls, organization o, UserOrg uo
where (uo.orgid = @orgid or @orgid = 0)
and uo.userid = @userid
and o.type = 'C'
and o.active = 'A'
and o.orgid = uo.orgid
and ls.orgid = o.orgid
and (ls.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
and ls.active = 'A'
and ls.userights = 0
and lc.orgid = ls.orgid
and lc.leasescheduleid = ls.leasescheduleid
and lc.active = 'A'
and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
and (lc.leasestopdate between @date1 and @date2 or @date1 is null or @date2 is null or @date1 > @date2)
and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
and cl.contractlineid = (select max(cl2.contractlineid)
from contractline cl2
where cl2.leasecontractid = lc.leasecontractid
and cl2.active = 'A')
and cl.active = 'A'
and zl.leasecontractid = cl.leasecontractid
and zl.contractlineid = cl.ContractLineID
and a.orgid = o.orgid
and (a.assetid = @assetid or @assetid = 0)
and a.linetype in ('B', 'S')
and a.assetid = zl.assetid
and a.manufacture like '%' + @brand + '%'
and a.brand like '%' + @lineOfProduct + '%'
and a.description like '%' + @description + '%'
and (a.catalogtype = @cat1 or @cat1 = '')
and (a.productCode = @manufacturerRef or @manufacturerRef = '')
and zg.catalogType = a.catalogType
and zg.orgid = a.orgid
and (zg.category = @category or @category = '')
and at1.assetid =* a.assetid
and at1.attributename =* zg.subcat1
and (at1.value = @cat2 or @cat2 = '')
and at2.assetid =* a.assetid
and at2.attributename =* zg.subcat2
and (at2.value = @cat3 or @cat3 = '')
and zfa.assetid = a.assetid
and zfa.leasescheduleid = ls.leasescheduleid
and zfa.leasecontractid = lc.leasecontractid
and zfa.contractlineid = cl.contractlineid
and zf.factureid = zfa.factureid
and zf.type = 'F'
and a.serialNumber like '%' + @serialNumber + '%'
and v.orgid = a.VendorOrgID
and v.type = 'V'
and zf.numero like '%' + @invoice + '%'
and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
and a.reference1 like '%' + @ref1 + '%'
and a.reference2 like '%' + @ref2 + '%'
and a.reference3 like '%' + @ref3 + '%'
and a.reference4 like '%' + @ref4 + '%'
and a.reference5 like '%' + @ref5 + '%'
) tmp
order by case @orderby
when 'org' then name
when 'manufacturerRef' then manufacturerRef
when 'SerialNumber' then SerialNumber
when 'description' then description
when 'brand' then brand
when 'lineOfProduct' then lineOfProduct
when 'category' then category
when 'cat1' then cat1
when 'cat2' then cat2
when 'cat3' then cat3
when 'supplier' then supplier
when 'numero' then invoice
when 'datecreation' then convert(varchar(8), invcreation, 112)
when 'reference1' then ref1
when 'reference2' then ref2
when 'reference3' then ref3
when 'reference4' then ref4
when 'reference5' then ref5
end, case @orderby
when 'org' then schedule
when 'manufacturerRef' then manufacturerRef
when 'SerialNumber' then SerialNumber
when 'description' then description
when 'brand' then brand
when 'lineOfProduct' then lineOfProduct
when 'category' then category
when 'cat1' then cat1
when 'cat2' then cat2
when 'cat3' then cat3
when 'supplier' then supplier
when 'numero' then invoice
when 'datecreation' then convert(varchar(8), invcreation, 112)
when 'reference1' then ref1
when 'reference2' then ref2
when 'reference3' then ref3
when 'reference4' then ref4
when 'reference5' then ref5
end
END
GO

Vous comprendrez que je vais avoir du mal à vous fournir une base de test correspondant

joce

Architecte / Développeur principal
"BugHunter"

Arjuna a écrit :

Je pense que dans tous les cas, étant donné que cette optimisation est parmis les plus simples possibles, il vaut mieu la faire systématiquement, au moins on est sûr d'avoir le meilleur résultat possible
Allez Joce, réécris tout de suis le forum

t'inquietes le forum est deja ecrit de facon optimale au niveau des requetes

Pour MySQL, y a bcp de changement qui ont ete introduit dans MySQL 5 et qui permettent de controller le comportement de l'optimizer et l'heuristique qu'il va utiliser dans le cas des jointures.
Je te recommande de lire :

http://dev.mysql.com/doc/mysql/en/ [...] mizer.html

Arjuna

Aircraft Ident.: F-MBSD

Je lirai ça plus tard Je bosse pas du tout avec MySQL

Bon, voilà ma base de test :

Code :

/****** Objet : Base de données testsd Date du script : 31/03/2005 11:31:24 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'testsd')
DROP DATABASE [testsd]
GO
CREATE DATABASE [testsd] ON (NAME = N'testsd_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\testsd_Data.MDF' , SIZE = 42, FILEGROWTH = 10%) LOG ON (NAME = N'testsd_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\testsd_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
GO
exec sp_dboption N'testsd', N'autoclose', N'false'
GO
exec sp_dboption N'testsd', N'bulkcopy', N'false'
GO
exec sp_dboption N'testsd', N'trunc. log', N'false'
GO
exec sp_dboption N'testsd', N'torn page detection', N'true'
GO
exec sp_dboption N'testsd', N'read only', N'false'
GO
exec sp_dboption N'testsd', N'dbo use', N'false'
GO
exec sp_dboption N'testsd', N'single', N'false'
GO
exec sp_dboption N'testsd', N'autoshrink', N'false'
GO
exec sp_dboption N'testsd', N'ANSI null default', N'false'
GO
exec sp_dboption N'testsd', N'recursive triggers', N'false'
GO
exec sp_dboption N'testsd', N'ANSI nulls', N'false'
GO
exec sp_dboption N'testsd', N'concat null yields null', N'false'
GO
exec sp_dboption N'testsd', N'cursor close on commit', N'false'
GO
exec sp_dboption N'testsd', N'default to local cursor', N'false'
GO
exec sp_dboption N'testsd', N'quoted identifier', N'false'
GO
exec sp_dboption N'testsd', N'ANSI warnings', N'false'
GO
exec sp_dboption N'testsd', N'auto create statistics', N'true'
GO
exec sp_dboption N'testsd', N'auto update statistics', N'true'
GO
if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'testsd', N'db chaining', N'false'
GO
use [testsd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t2_t1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[t2] DROP CONSTRAINT FK_t2_t1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t23_t3]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[t23] DROP CONSTRAINT FK_t23_t3
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t23_t2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[t23] DROP CONSTRAINT FK_t23_t2
GO
/****** Objet : Table [dbo].[t23] Date du script : 31/03/2005 11:31:30 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t23]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t23]
GO
/****** Objet : Table [dbo].[t2] Date du script : 31/03/2005 11:31:30 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t2]
GO
/****** Objet : Table [dbo].[t1] Date du script : 31/03/2005 11:31:30 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t1]
GO
/****** Objet : Table [dbo].[t3] Date du script : 31/03/2005 11:31:30 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t3]
GO
/****** Objet : Nom d'accès interclass_user Date du script : 31/03/2005 11:31:24 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'interclass_user')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = null, @loginlang = N'Français'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'interclass_user', null, @logindb, @loginlang
END
GO
/****** Objet : Nom d'accès BUILTIN\Administrateurs Date du script : 31/03/2005 11:31:24 ******/
exec sp_addsrvrolemember N'BUILTIN\Administrateurs', sysadmin
GO
/****** Objet : Utilisateur dbo Date du script : 31/03/2005 11:31:24 ******/
/****** Objet : Table [dbo].[t1] Date du script : 31/03/2005 11:31:32 ******/
CREATE TABLE [dbo].[t1] (
[t1_id] [numeric](18, 0) NOT NULL ,
[nom] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
/****** Objet : Table [dbo].[t3] Date du script : 31/03/2005 11:31:33 ******/
CREATE TABLE [dbo].[t3] (
[t3_id] [numeric](18, 0) NOT NULL ,
[nom] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
/****** Objet : Table [dbo].[t2] Date du script : 31/03/2005 11:31:34 ******/
CREATE TABLE [dbo].[t2] (
[t1_id] [numeric](18, 0) NOT NULL ,
[t2_id] [numeric](18, 0) NOT NULL ,
[nom] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
/****** Objet : Table [dbo].[t23] Date du script : 31/03/2005 11:31:34 ******/
CREATE TABLE [dbo].[t23] (
[t1_id] [numeric](18, 0) NOT NULL ,
[t2_id] [numeric](18, 0) NOT NULL ,
[t3_id] [numeric](18, 0) NOT NULL ,
[libelle] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t1] WITH NOCHECK ADD
CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED
(
[t1_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t3] WITH NOCHECK ADD
CONSTRAINT [PK_t3] PRIMARY KEY CLUSTERED
(
[t3_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t2] WITH NOCHECK ADD
CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED
(
[t1_id],
[t2_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t23] WITH NOCHECK ADD
CONSTRAINT [PK_t23] PRIMARY KEY CLUSTERED
(
[t1_id],
[t2_id],
[t3_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t2] ADD
CONSTRAINT [FK_t2_t1] FOREIGN KEY
(
[t1_id]
) REFERENCES [dbo].[t1] (
[t1_id]
)
GO
ALTER TABLE [dbo].[t23] ADD
CONSTRAINT [FK_t23_t2] FOREIGN KEY
(
[t1_id],
[t2_id]
) REFERENCES [dbo].[t2] (
[t1_id],
[t2_id]
),
CONSTRAINT [FK_t23_t3] FOREIGN KEY
(
[t3_id]
) REFERENCES [dbo].[t3] (
[t3_id]
)
GO

Vais boire un café, et après je fais quelques tests, mais j'ai peur qu'il y ait trop peu de tables pour que ce soit significatif :spamafote:

joce

Architecte / Développeur principal
"BugHunter"

j'ai deja eu un cas ou sur deux tables l'optimizer se plantait dans l'ordre de la jointure a cause d'un mauvais calcul de la cardinalite par l'optimizer une honte

Arjuna

Aircraft Ident.: F-MBSD

Bon, c'est chiant, la base est trop petite. Vu que toutes les requêtes (sauf une) mettent 0 secondes à tourner, je ne peux rien mesurer.

Par contre, j'ai remarqué un point relativement important dans le domaine des optimisation.

T1
-----
t1_id PK / Culstered
nom

T2
-----
t1_id PK / Clustered / FK vers t1.t1_id
t2_id PK / Clustered
nom

T1
-----
t3_id PK / Culstered
nom

T23
-----
t1_id PK / Clustered / FK vers t2.t1_id et t2.t2_id
t2_id PK / Clustered / FK vers t2.t1_id et t2.t2_id
t3_id PK / Clustered / FK vers t3.t3_id
libelle

Il y a quelques temps, j'ai annoncé, sûr de moi, que les FK ne servaient à rien au niveau optimisation des requêtes de type SELECT. En réponse, on m'a volé dans les plumes en me disant que c'était totalement faux.
J'ai la preuve, chiffres à l'appui que si, ça ne sert ABSOLUMENT à rien. Même pire, ça peu, dans certains cas, ralentir les requêtes, à cause de l'ajout de tables de correspondances inutiles.

Si je suis mes FK, je dois passer par T2, puis vers T1 pour lier T23 à T1.
Mais vu que T23 contient t1_id, je peux aussi me passer du passage par T2, et faire fi des FK.

Requêtes :

Code :

select t3.t3_id, t1.nom, t23.libelle, t3.nom
from t3, t23, t2, t1
where t1.t1_id between 200 and 210
and t2.t1_id = t1.t1_id
and t23.t1_id = t2.t1_id
and t23.t2_id = t2.t2_id
and t3.t3_id = t23.t3_id
-- 2 secondes
select t3.t3_id, t1.nom, t23.libelle, t3.nom
from t3, t23, t1
where t1.t1_id between 200 and 210
and t23.t1_id = t1.t1_id
and t3.t3_id = t23.t3_id
-- 0 secondes

Les résultats sont sans appel : passer par la FK est énormément plus lent que de passer outre.

TBone

Pouet.

les FK servent surtout de garde-fou quand on fait joujou avec les datas (non ?)

Message édité par TBone le 31-03-2005 à 13:15:28

---------------
A straight line is a special case of a curve. It's a curve which is uncurved. -- Susskind.

Arjuna

Aircraft Ident.: F-MBSD

Ben oui. Mais les index générés par les FK n'apportent rien à l'optimisation.

Arjuna

Aircraft Ident.: F-MBSD

J'ai quelques nouveaux chiffres.

Je viens de changer une procédure du même type que celle que j'avais posté. Je suis passé de 19 secondes à 2 secondes, en prenant comme point d'entrée une table qui à première vue n'y avait rien à faire.

J'ai un user. Un user a accès à plusieurs organisations. Une organisation contient des schedule. Un schedule contient des contract. Un contract contient des contractline. Une contractline correspond à un loyer. Un loyer correspond à un asset. (plus quelques autres tables, mais ça c'est les principales).

Dans l'asset, j'ai toutes les FK correspondant aux autres tables (sauf loyer, qui est une table de correspondance).

Au départ, j'étais parti de l'utilisateur, puis des organisations, des schedules, etc.

Finalement, je suis parti d'Asset. Et c'est plus rapide !

En fait, plus ça va, et j'ai l'impression que la méthode est légèrement différente : il faut que la table contenant le plus de lignes soit la première. Ca expliquerait pourquoi MySQL peut optimiser de façon plus efficace que les autres SGBD, puisque le COUNT(*) est stocké dans la base pour chaque table, alors qu'aucun autre SGBD ne stocke cette infos, résultat, il ne peuvent que faire une approximation du nombre de ligne lors de l'optimisation.

Message édité par Arjuna le 05-04-2005 à 17:40:30

Arjuna

Aircraft Ident.: F-MBSD

Pour résumer le bordel :

Code :

CREATE procedure icsd_ARTAssetList
(
@orgid int,
@userid int,
@date1 datetime,
@date2 datetime,
@invdate1 datetime,
@invdate2 datetime,
@leasescheduleid int = 0,
@leasecontractid int = 0,
@duration int = 0,
@brand varchar(255) = '%',
@lineOfProduct varchar(255) = '%',
@manufacturerRef varchar(255) = '',
@description varchar(255) = '%',
@category varchar(255) = '',
@cat1 varchar(255) = '',
@cat2 varchar(255) = '',
@cat3 varchar(255) = '',
@serialnumber varchar(255) = '%',
@supplier varchar(255) = '%',
@invoice varchar(255) = '%',
@ref1 varchar(255) = '%',
@ref2 varchar(255) = '%',
@ref3 varchar(255) = '%',
@ref4 varchar(255) = '%',
@ref5 varchar(255) = '%',
@assetid int = 0,
@orderby varchar(255) = 'org'
)
AS
BEGIN
select orgid,
name,
leasescheduleid,
schedule,
-- inventory row
-- inventory autoincrement
manufacturerRef,
SerialNumber,
description,
brand,
lineOfProduct,
category,
cat1,
cat2,
cat3,
supplier,
invoice,
invcreation,
ref1,
ref2,
ref3,
ref4,
ref5,
leasecontractid,
contract,
assetid
from (
select o.orgid,
o.name,
ls.leasescheduleid,
ls.schedule,
-- inventory row
-- inventory autoincrement
a.ProductCode manufacturerRef,
a.SerialNumber,
a.description,
a.manufacture brand,
a.brand lineOfProduct,
zg.category,
a.catalogType cat1,
at1.value cat2,
at2.value cat3,
v.name supplier,
zf.numero invoice,
zf.datecreation invcreation,
a.reference1 ref1,
a.reference2 ref2,
a.reference3 ref3,
a.reference4 ref4,
a.reference5 ref5,
lc.leasecontractid,
lc.contract,
a.assetid
from organization v, zfactures zf, zfactureasset zfa, sdViewAttributesValues at2, sdViewAttributesValues at1, zCatalogTypeGenre zg, asset a, zloyer zl, contractline cl, leasecontract lc, leaseschedule ls, zUserContractPerimeter zu, organization o, UserOrg uo
where (uo.orgid = @orgid or @orgid = 0)
and uo.userid = @userid
and o.type = 'C'
and o.active = 'A'
and o.orgid = uo.orgid
and zu.userid = uo.userid
and zu.orgid = uo.orgid
and (zu.scheduleid = @leasescheduleid or @leasescheduleid = 0)
and ls.orgid = o.orgid
and ls.leasescheduleid = zu.scheduleid
and ls.active = 'A'
and ls.userights = 1
and lc.orgid = ls.orgid
and lc.leasescheduleid = ls.leasescheduleid
and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
and (lc.leasecontractid = zu.contractid or zu.contractid = -1)
and lc.active = 'A'
and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
and cl.contractlineid = (select max(cl2.contractlineid)
from contractline cl2
where cl2.leasecontractid = lc.leasecontractid
and cl2.active = 'A')
and cl.active = 'A'
and zl.leasecontractid = cl.leasecontractid
and zl.contractlineid = cl.ContractLineID
and a.orgid = o.orgid
and (a.assetid = @assetid or @assetid = 0)
and a.linetype in ('B', 'S')
and a.assetid = zl.assetid
and a.manufacture like '%' + @brand + '%'
and a.brand like '%' + @lineOfProduct + '%'
and a.description like '%' + @description + '%'
and (a.catalogtype = @cat1 or @cat1 = '')
and (a.productCode = @manufacturerRef or @manufacturerRef = '')
and zg.catalogType = a.catalogType
and zg.orgid = a.orgid
and (zg.category = @category or @category = '')
and at1.assetid =* a.assetid
and at1.attributename =* zg.subcat1
and (at1.value = @cat2 or @cat2 = '')
and at2.assetid =* a.assetid
and at2.attributename =* zg.subcat2
and (at2.value = @cat3 or @cat3 = '')
and zfa.assetid = a.assetid
and zfa.leasescheduleid = ls.leasescheduleid
and zfa.leasecontractid = lc.leasecontractid
and zfa.contractlineid = cl.contractlineid
and zf.factureid = zfa.factureid
and zf.type = 'F'
and a.serialNumber like '%' + @serialNumber + '%'
and v.orgid = a.VendorOrgID
and v.type = 'V'
and zf.numero like '%' + @invoice + '%'
and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
and a.reference1 like '%' + @ref1 + '%'
and a.reference2 like '%' + @ref2 + '%'
and a.reference3 like '%' + @ref3 + '%'
and a.reference4 like '%' + @ref4 + '%'
and a.reference5 like '%' + @ref5 + '%'
union all
select o.orgid,
o.name,
ls.leasescheduleid,
ls.schedule,
-- inventory row
-- inventory autoincrement
a.ProductCode manufacturerRef,
a.SerialNumber,
a.description,
a.manufacture brand,
a.brand lineOfProduct,
zg.category,
a.catalogType cat1,
at1.value cat2,
at2.value cat3,
v.name supplier,
zf.numero invoice,
zf.datecreation invcreation,
a.reference1 ref1,
a.reference2 ref2,
a.reference3 ref3,
a.reference4 ref4,
a.reference5 ref5,
lc.leasecontractid,
lc.contract,
a.assetid
from organization v, zfactures zf, zfactureasset zfa, sdViewAttributesValues at2, sdViewAttributesValues at1, zCatalogTypeGenre zg, asset a, zloyer zl, contractline cl, leasecontract lc, leaseschedule ls, organization o, UserOrg uo
where (uo.orgid = @orgid or @orgid = 0)
and uo.userid = @userid
and o.type = 'C'
and o.active = 'A'
and o.orgid = uo.orgid
and ls.orgid = o.orgid
and (ls.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
and ls.active = 'A'
and ls.userights = 0
and lc.orgid = ls.orgid
and lc.leasescheduleid = ls.leasescheduleid
and lc.active = 'A'
and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
and (lc.leasestopdate between @date1 and @date2 or @date1 is null or @date2 is null or @date1 > @date2)
and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
and cl.contractlineid = (select max(cl2.contractlineid)
from contractline cl2
where cl2.leasecontractid = lc.leasecontractid
and cl2.active = 'A')
and cl.active = 'A'
and zl.leasecontractid = cl.leasecontractid
and zl.contractlineid = cl.ContractLineID
and a.orgid = o.orgid
and (a.assetid = @assetid or @assetid = 0)
and a.linetype in ('B', 'S')
and a.assetid = zl.assetid
and a.manufacture like '%' + @brand + '%'
and a.brand like '%' + @lineOfProduct + '%'
and a.description like '%' + @description + '%'
and (a.catalogtype = @cat1 or @cat1 = '')
and (a.productCode = @manufacturerRef or @manufacturerRef = '')
and zg.catalogType = a.catalogType
and zg.orgid = a.orgid
and (zg.category = @category or @category = '')
and at1.assetid =* a.assetid
and at1.attributename =* zg.subcat1
and (at1.value = @cat2 or @cat2 = '')
and at2.assetid =* a.assetid
and at2.attributename =* zg.subcat2
and (at2.value = @cat3 or @cat3 = '')
and zfa.assetid = a.assetid
and zfa.leasescheduleid = ls.leasescheduleid
and zfa.leasecontractid = lc.leasecontractid
and zfa.contractlineid = cl.contractlineid
and zf.factureid = zfa.factureid
and zf.type = 'F'
and a.serialNumber like '%' + @serialNumber + '%'
and v.orgid = a.VendorOrgID
and v.type = 'V'
and zf.numero like '%' + @invoice + '%'
and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
and a.reference1 like '%' + @ref1 + '%'
and a.reference2 like '%' + @ref2 + '%'
and a.reference3 like '%' + @ref3 + '%'
and a.reference4 like '%' + @ref4 + '%'
and a.reference5 like '%' + @ref5 + '%'
) tmp
order by case @orderby
when 'org' then name
when 'manufacturerRef' then manufacturerRef
when 'SerialNumber' then SerialNumber
when 'description' then description
when 'brand' then brand
when 'lineOfProduct' then lineOfProduct
when 'category' then category
when 'cat1' then cat1
when 'cat2' then cat2
when 'cat3' then cat3
when 'supplier' then supplier
when 'numero' then invoice
when 'datecreation' then convert(varchar(8), invcreation, 112)
when 'reference1' then ref1
when 'reference2' then ref2
when 'reference3' then ref3
when 'reference4' then ref4
when 'reference5' then ref5
end, case @orderby
when 'org' then schedule
when 'manufacturerRef' then manufacturerRef
when 'SerialNumber' then SerialNumber
when 'description' then description
when 'brand' then brand
when 'lineOfProduct' then lineOfProduct
when 'category' then category
when 'cat1' then cat1
when 'cat2' then cat2
when 'cat3' then cat3
when 'supplier' then supplier
when 'numero' then invoice
when 'datecreation' then convert(varchar(8), invcreation, 112)
when 'reference1' then ref1
when 'reference2' then ref2
when 'reference3' then ref3
when 'reference4' then ref4
when 'reference5' then ref5
end
END
GO
-- 19 SECONDES
NEW
CREATE procedure icsd_ARTAssetList
(
@orgid int,
@userid int,
@date1 datetime,
@date2 datetime,
@invdate1 datetime,
@invdate2 datetime,
@leasescheduleid int = 0,
@leasecontractid int = 0,
@duration int = 0,
@brand varchar(255) = '%',
@lineOfProduct varchar(255) = '%',
@manufacturerRef varchar(255) = '',
@description varchar(255) = '%',
@category varchar(255) = '',
@cat1 varchar(255) = '',
@cat2 varchar(255) = '',
@cat3 varchar(255) = '',
@serialnumber varchar(255) = '%',
@supplier varchar(255) = '%',
@invoice varchar(255) = '%',
@ref1 varchar(255) = '%',
@ref2 varchar(255) = '%',
@ref3 varchar(255) = '%',
@ref4 varchar(255) = '%',
@ref5 varchar(255) = '%',
@assetid int = 0,
@orderby varchar(255) = 'org'
)
AS
BEGIN
select orgid,
name,
leasescheduleid,
schedule,
-- inventory row
-- inventory autoincrement
manufacturerRef,
SerialNumber,
description,
brand,
lineOfProduct,
category,
cat1,
cat2,
cat3,
supplier,
invoice,
invcreation,
ref1,
ref2,
ref3,
ref4,
ref5,
leasecontractid,
contract,
assetid
from (
select o.orgid,
o.name,
ls.leasescheduleid,
ls.schedule,
-- inventory row
-- inventory autoincrement
a.ProductCode manufacturerRef,
a.SerialNumber,
a.description,
a.manufacture brand,
a.brand lineOfProduct,
zg.category,
a.catalogType cat1,
at1.value cat2,
at2.value cat3,
v.name supplier,
zf.numero invoice,
zf.datecreation invcreation,
a.reference1 ref1,
a.reference2 ref2,
a.reference3 ref3,
a.reference4 ref4,
a.reference5 ref5,
lc.leasecontractid,
lc.contract,
a.assetid
from organization v, zfactures zf, zfactureasset zfa, sdViewAttributesValues at2, sdViewAttributesValues at1, zCatalogTypeGenre zg, zUserContractPerimeter zu, organization o, UserOrg uo, leaseschedule ls, leasecontract lc, contractline cl, zloyer zl, asset a
where 1 = 1
and (a.orgid = @orgid or @orgid = 0)
and (a.assetid = @assetid or @assetid = 0)
and a.linetype in ('B', 'S')
and a.manufacture like '%' + @brand + '%'
and a.brand like '%' + @lineOfProduct + '%'
and a.description like '%' + @description + '%'
and (a.catalogtype = @cat1 or @cat1 = '')
and (a.productCode = @manufacturerRef or @manufacturerRef = '')
and a.serialNumber like '%' + @serialNumber + '%'
and a.reference1 like '%' + @ref1 + '%'
and a.reference2 like '%' + @ref2 + '%'
and a.reference3 like '%' + @ref3 + '%'
and a.reference4 like '%' + @ref4 + '%'
and a.reference5 like '%' + @ref5 + '%'
and (a.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
and (a.leasecontractid = @leasecontractid or @leasecontractid = 0)
and a.contractlineid = (select max(cl2.contractlineid)
from contractline cl2
where cl2.leasecontractid = a.leasecontractid
and cl2.active = 'A')
and zl.leasecontractid = a.leasecontractid
and zl.contractlineid = a.ContractLineID
and zl.assetid = a.assetid
and cl.contractlineid = zl.contractlineid
and cl.leasecontractid = zl.leasecontractid
and cl.active = 'A'
and lc.orgid = a.orgid
and lc.leasescheduleid = a.leasescheduleid
and lc.leasecontractid = zl.leasecontractid
and lc.active = 'A'
and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
and ls.orgid = lc.orgid
and ls.leasescheduleid = lc.leasescheduleid
and ls.active = 'A'
and ls.userights = 1
and uo.orgid = ls.orgid
and uo.userid = @userid
and o.type = 'C'
and o.active = 'A'
and o.orgid = uo.orgid
and zu.userid = uo.userid
and zu.orgid = uo.orgid
and zu.scheduleid = ls.leasescheduleid
and (zu.contractid = lc.leasecontractid or zu.contractid = -1)
and zg.catalogType = a.catalogType
and zg.orgid = a.orgid
and (zg.category = @category or @category = '')
and at1.assetid =* a.assetid
and at1.attributename =* zg.subcat1
and (at1.value = @cat2 or @cat2 = '')
and at2.assetid =* a.assetid
and at2.attributename =* zg.subcat2
and (at2.value = @cat3 or @cat3 = '')
and zfa.assetid = a.assetid
and zfa.leasescheduleid = ls.leasescheduleid
and zfa.leasecontractid = lc.leasecontractid
and zfa.contractlineid = cl.contractlineid
and zf.factureid = zfa.factureid
and zf.type = 'F'
and v.orgid = a.VendorOrgID
and v.type = 'V'
and zf.numero like '%' + @invoice + '%'
and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
union all
select o.orgid,
o.name,
ls.leasescheduleid,
ls.schedule,
-- inventory row
-- inventory autoincrement
a.ProductCode manufacturerRef,
a.SerialNumber,
a.description,
a.manufacture brand,
a.brand lineOfProduct,
zg.category,
a.catalogType cat1,
at1.value cat2,
at2.value cat3,
v.name supplier,
zf.numero invoice,
zf.datecreation invcreation,
a.reference1 ref1,
a.reference2 ref2,
a.reference3 ref3,
a.reference4 ref4,
a.reference5 ref5,
lc.leasecontractid,
lc.contract,
a.assetid
from organization v, zfactures zf, zfactureasset zfa, sdViewAttributesValues at2, sdViewAttributesValues at1, zCatalogTypeGenre zg, organization o, UserOrg uo, leaseschedule ls, leasecontract lc, contractline cl, zloyer zl, asset a
where 1 = 1
and (a.orgid = @orgid or @orgid = 0)
and (a.assetid = @assetid or @assetid = 0)
and a.linetype in ('B', 'S')
and a.manufacture like '%' + @brand + '%'
and a.brand like '%' + @lineOfProduct + '%'
and a.description like '%' + @description + '%'
and (a.catalogtype = @cat1 or @cat1 = '')
and (a.productCode = @manufacturerRef or @manufacturerRef = '')
and a.serialNumber like '%' + @serialNumber + '%'
and a.reference1 like '%' + @ref1 + '%'
and a.reference2 like '%' + @ref2 + '%'
and a.reference3 like '%' + @ref3 + '%'
and a.reference4 like '%' + @ref4 + '%'
and a.reference5 like '%' + @ref5 + '%'
and (a.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
and (a.leasecontractid = @leasecontractid or @leasecontractid = 0)
and a.contractlineid = (select max(cl2.contractlineid)
from contractline cl2
where cl2.leasecontractid = a.leasecontractid
and cl2.active = 'A')
and zl.leasecontractid = a.leasecontractid
and zl.contractlineid = a.ContractLineID
and zl.assetid = a.assetid
and cl.contractlineid = zl.contractlineid
and cl.leasecontractid = zl.leasecontractid
and cl.active = 'A'
and lc.orgid = a.orgid
and lc.leasescheduleid = a.leasescheduleid
and lc.leasecontractid = zl.leasecontractid
and lc.active = 'A'
and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
and ls.orgid = lc.orgid
and ls.leasescheduleid = lc.leasescheduleid
and ls.active = 'A'
and ls.userights = 0
and uo.orgid = ls.orgid
and uo.userid = @userid
and o.type = 'C'
and o.active = 'A'
and o.orgid = uo.orgid
and zg.catalogType = a.catalogType
and zg.orgid = a.orgid
and (zg.category = @category or @category = '')
and at1.assetid =* a.assetid
and at1.attributename =* zg.subcat1
and (at1.value = @cat2 or @cat2 = '')
and at2.assetid =* a.assetid
and at2.attributename =* zg.subcat2
and (at2.value = @cat3 or @cat3 = '')
and zfa.assetid = a.assetid
and zfa.leasescheduleid = ls.leasescheduleid
and zfa.leasecontractid = lc.leasecontractid
and zfa.contractlineid = cl.contractlineid
and zf.factureid = zfa.factureid
and zf.type = 'F'
and v.orgid = a.VendorOrgID
and v.type = 'V'
and zf.numero like '%' + @invoice + '%'
and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
) tmp
order by case @orderby
when 'org' then name
when 'manufacturerRef' then manufacturerRef
when 'SerialNumber' then SerialNumber
when 'description' then description
when 'brand' then brand
when 'lineOfProduct' then lineOfProduct
when 'category' then category
when 'cat1' then cat1
when 'cat2' then cat2
when 'cat3' then cat3
when 'supplier' then supplier
when 'numero' then invoice
when 'datecreation' then convert(varchar(8), invcreation, 112)
when 'reference1' then ref1
when 'reference2' then ref2
when 'reference3' then ref3
when 'reference4' then ref4
when 'reference5' then ref5
end, case @orderby
when 'org' then schedule
when 'manufacturerRef' then manufacturerRef
when 'SerialNumber' then SerialNumber
when 'description' then description
when 'brand' then brand
when 'lineOfProduct' then lineOfProduct
when 'category' then category
when 'cat1' then cat1
when 'cat2' then cat2
when 'cat3' then cat3
when 'supplier' then supplier
when 'numero' then invoice
when 'datecreation' then convert(varchar(8), invcreation, 112)
when 'reference1' then ref1
when 'reference2' then ref2
when 'reference3' then ref3
when 'reference4' then ref4
when 'reference5' then ref5
end
END
GO
-- 2 SECONDES !!!

rufo

Pas me confondre avec Lycos!

j'ai aussi des pbs de lenteur avec MySql pour une requête issue d'un moteur de recherche multicritères. Pourtant, j'ai pas tant que ça d'enregistrements :
- 5000 demandes
- pour chaque demande, un historique de 8 enregistrements en moyenne

Ca doit bien prendre 7 ou 8 secondes pour trouver les demandes et encore, je pagine avec LIMIT, mais ça ne me fait pas gagner tant que ça de temps. Mon principal pb, c'est que je travaille avec un mysql 3.23.58 qui ne gère pas les requêtes imbriquées : bilan, je dois passer par une table temporaire J'ai tenté une optimisation, mais elle marche que dans certains cas (ça dépend des critères sélectionnés par l'utilisateur)... Ta piste est intéressant en tout cas et je vais regarder mysql 5.

Arjuna

Aircraft Ident.: F-MBSD

Question bête : t'as des LIKE ?
Si oui, alors c'est certainement eux qui plombent ta requête.
Essaie de voir si tu peux implémenter (et utiliser ) les fonctions d'indexation de texte, afin d'utiliser FREETEXT et CONTAINS notamment. C'est infiniment plus rapide, et ça permet de faire des recherhces plus naturelles.

rufo

Pas me confondre avec Lycos!

Arjuna a écrit :

Oui, j'ai du LIKE (plusieurs même). Le soucis, c'est que je ne veux pas faire du SQL trop spécifique à MySQl, l'un des principes de l'appli étant d'être indépendante du SGBD...

Arjuna

Aircraft Ident.: F-MBSD

Les fonctions de texte intégral sont relativement similaires d'un SGBD à l'autre.

Je te conseille de faire ce que j'ai fait pour mon site Manga-Torii.
Une fonction PHP qui génère le code SQL correspondant à l'utilisation des index de texte, que tu colles au reste de ta requête plus générique.
Ainsi, tu pourras aisément faire les adaptations selon les SGBD utilisés (voir une version avec like) sans casser la requête.

Pour information, SQL Server utilise les mêmes procédures que MySQL, plus d'autres supplémentaires (avec quelques déviances niveau syntaxe cependant)
Oracle, quant à lui, a une syntaxe plus spécifique.

cinocks

j'ai une petite question concernant ta requete au dessus. Y'a combien d'enregistrements à 'scanner'? Quelle est le volume de la plus grosse tables?

Bref, des details permettant de mettre en relation les 2 secondes d'execution et le volume.

:jap:

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

rufo

Pas me confondre avec Lycos!

Comme autre SGBD, je pensais entre autre à Oracle, mais surtout à PostgreSql.

T'aurais un ex de ta fonction de cote sql à me montrer? je pense avoir saisi le truc, mais je voudrais m'en assurer avant de recoder cette partie. Merci :jap:

Arjuna

Aircraft Ident.: F-MBSD

cinocks:

Code :

select count(*) from organization
-----------
69
select count(*) from zfactures
-----------
242
select count(*) from zfactureasset
-----------
93
select count(*) from sdViewAttributesValues -- vue
-----------
44660
select count(*) from zCatalogTypeGenre
-----------
400
select count(*) from zUserContractPerimeter
-----------
0
select count(*) from UserOrg
-----------
46
select count(*) from leaseschedule
-----------
5
select count(*) from leasecontract
-----------
26
select count(*) from contractline
-----------
46
select count(*) from zloyer
-----------
9363
select count(*) from asset
-----------
27699

Tiens, y'en a beaucoup moins que ce que j'avais prévu :heink:

Ca va faire mal, parcequ'on va pas tarder à avoir plus de 500 000 lignes dans la table asset, avec au minimum le double dans la vue sdViewAttributesValues :ouch:

Va falloir optimiser encore un peu je crois

Arjuna

Aircraft Ident.: F-MBSD

rufo a écrit :

J'ai rien compris du tout

Tu veux quoi ?

Arjuna

Aircraft Ident.: F-MBSD

Ah, ok, je viens de piger. Euh... Si j'arrive à me connecter au FTP de mon site ce soir, je te poste ma fonction (elle est fait un peur, et est améliorable )

rufo

Pas me confondre avec Lycos!

Arjuna a écrit :

Les fonctions de texte intégral sont relativement similaires d'un SGBD à l'autre.

Je te conseille de faire ce que j'ai fait pour mon site Manga-Torii.
Une fonction PHP qui génère le code SQL correspondant à l'utilisation des index de texte, que tu colles au reste de ta requête plus générique.
Ainsi, tu pourras aisément faire les adaptations selon les SGBD utilisés (voir une version avec like) sans casser la requête.

Pour information, SQL Server utilise les mêmes procédures que MySQL, plus d'autres supplémentaires (avec quelques déviances niveau syntaxe cependant)
Oracle, quant à lui, a une syntaxe plus spécifique.

je parle de cette fct

rufo

Pas me confondre avec Lycos!

Arjuna a écrit :

Ah, ok, je viens de piger. Euh... Si j'arrive à me connecter au FTP de mon site ce soir, je te poste ma fonction (elle est fait un peur, et est améliorable )

bah, même pas peur

cinocks

Arjuna a écrit :

cinocks:

Code :

select count(*) from organization
-----------
69
select count(*) from zfactures
-----------
242
select count(*) from zfactureasset
-----------
93
select count(*) from sdViewAttributesValues -- vue
-----------
44660
select count(*) from zCatalogTypeGenre
-----------
400
select count(*) from zUserContractPerimeter
-----------
0
select count(*) from UserOrg
-----------
46
select count(*) from leaseschedule
-----------
5
select count(*) from leasecontract
-----------
26
select count(*) from contractline
-----------
46
select count(*) from zloyer
-----------
9363
select count(*) from asset
-----------
27699

Tout depend du serveur et de sa puissance derriere. Mais ta requete ne tiendra pas longtemps une montée en charge. Car pour le moment, il n'y a pas bcp de lignes dans les tables. Etant donné que tu passes par une procedure stockée, autant en profiter pour decouper ta requete en plusieurs sous-requetes et tables de travail.

Avec ton union, tu as deux blocs requetes qui au debut recupere les memes données. Ce n'est qu'à partir du and ls.userights = 1 qu'elles se distinguent. Du coup, ce qui se fait avant est identique entre les 2 blocs.
Pourquoi ne pas mutualiser ce qui est identique par requetes intermediaires et tables de travail, puis partir de ce resultat pour construire le reste.

Personnellement, c'est ce que l'on utilise au boulot, et ce sont des volumes variant entre 20 000 000 et 30 000 000 de lignes dans les tables.

Message édité par cinocks le 06-04-2005 à 16:28:57

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

Arjuna

Aircraft Ident.: F-MBSD

Parceque potentiellement, la requête peut retourner TOUTES les lignes (tous les filtres sont facultatifs). Par conséquent, si je fait ça dans des tables temporaires, je fais passer 2 heures à recopier toutes les données dans la base tempdb, jusqu'à planter le serveur par manque d'espace.

Deplus, étant donné que les filtres se trouvent à tous les niveaux, et que la notion de droit impact les filtres sur plusieurs tables, stocker des infos selon un nombre limité de filtres va provoquer le stockage d'un nombre important de lignes qui ne seront très certainement pas retenues dans les filtres finaux.

cinocks

Tout depend du nombre de lignes retournée depuis asset quand les filtres sont inactifs. Quel est le % d'enregistrements retournés si tu executes:

# and a.contractlineid = (select max(cl2.contractlineid)
# from contractline cl2
# where cl2.leasecontractid = a.leasecontractid
# and cl2.active = 'A')

idem cette sous-requete peut se preparer pour devenir

select cl2.leasecontractid, max(cl2.contractlineid)
into #contractline
from contractline cl2
where cl2.active = 'A'
group by cl2.leasecontractid

Ca evitera de faire autant d'aggregats qu'il n'y a d'enregistrement dans a.

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

red faction

bon je profite de ce topic pour reposer une question auquel personne ne ma encore repondu : comment peut on faire pour implementer facilement dans sql server un systeme de gestion des droits des utilisateurs ??? ex: tel user a le droit de modifier tel colonne ou tel ligne ........

Arjuna

Aircraft Ident.: F-MBSD

cinocks > il y a très peu de "doublons" dans la table contractline. En fait, chaque nouvelle ligne correspond à un avenant à un contrat. Hors, un contrat qui arrive à échéance, dans le mode de fonctionnement de la bdd dans laquelle va tourner ma requête, un nouveau contrat est créé. Donc je dirais que ce "max" va rammener entre 90 et 100% des lignes. Je pense qu'il n'est donc pas utile de le compiler à l'avance.

Ensuite, il va y avoir environ 40 000 clients. Chaque client ayant 1 à 5 contrats actifs dans la base, donc ça fait pas mal de lignes à mettre dans la table temporaire.

Quand j'aurai les données réelles, je ferais peut-être le test, mais je pense que je vais perdre du temps plutôt qu'en gagner avec le sous-select. En effet, les tables étant bien indexées, le nombre de lignes n'influe que très peu sur la vitesse d'éxécution de la requête. Par contre, dès que le volume de données augmente, l'utilisation de tables temporaire plombe tous les traîtements.

Arjuna

Aircraft Ident.: F-MBSD

red faction > ben à partir de l'interface, ça marche tout seul. sinon, il y a des fonction systèmes qui permettent de gérer les droits dans sql server.

Publicité

Page : 1 2

Page Suivante

Page Précédente

Haut de page

FORUM HardWare.fr

Programmation

SQL/NoSQL

[SQL] Petit "cours" sur l'optimisation

Sujets relatifs
[Resolu] Probleme Sql mais comment ?	[SQL] recuperer que les chiffres d'un champ
SQL / Informix : Error 297	[SQL]Insérer une ligne automatiquement dans une autre table
Comparer date SQL/Date du jour	Formulaire html et SQL vers Excel
[SQL] Je sais pas comment formuler mon probleme !	apostrophe dans SQL
SQL: mysql_query()	Access SQL et la date
Plus de sujets relatifs à : [SQL] Petit "cours" sur l'optimisation

Page générée en 0.341 secondes