[SQL] Petit "cours" sur l'optimisation

Petit "cours" sur l'optimisation [SQL] - SQL/NoSQL - Programmation

Marsh Posté le 30-03-2005 à 21:03:17    

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 :
  1. SELECT SOCIETE.NOM, EMPLOYE.NOM, sum(datediff(d, CONGES.DATE_DEB, CONGES.DATE_FIN))
  2. FROM SOCIETE, EMPLOYE, CONGES
  3. WHERE EMPLOYE.SOC_ID = SOCIETE.SOC_ID
  4. AND CONGES.EMP_ID = EMPLOYE.EMP_ID
  5. AND year(CONGES.DATE_DEB) = 2004
  6. 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 :D)
La requête optimisée aura alors pour syntaxe :
 

Code :
  1. SELECT SOCIETE.NOM, EMPLOYE.NOM, sum(datediff(d, CONGES.DATE_DEB, CONGES.DATE_FIN))
  2. FROM CONGES, EMPLOYE, SOCIETE
  3. WHERE EMPLOYE.SOC_ID = SOCIETE.SOC_ID
  4. AND CONGES.EMP_ID = EMPLOYE.EMP_ID
  5. AND year(CONGES.DATE_DEB) = 2004
  6. 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 :
  1. SELECT EMPLOYE.NOM, TYPE_DROITS.LIBELLE
  2. FROM SOCIETE, EMPOYE, DROITS, TYPE_DROITS, PROGRAMME
  3. WHERE SOCIETE.NOM = 'Microsoft'
  4. AND EMPLOYE.SOC_ID = SOCIETE.SOC_ID
  5. AND DROITS.EMP_ID = EMPLOYE.EMP_ID
  6. AND TYPE_DROITS.TYPE_DROIT = DROITS.TYPE_DROIT
  7. AND PROGRAMME.PROG_ID = DROITS.PROG_ID
  8. 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 :
  1. SELECT EMPLOYE.NOM, TYPE_DROITS.LIBELLE
  2. FROM TYPE_DROITS, SOCIETE, EMPOYE, DROITS, PROGRAMME
  3. WHERE SOCIETE.NOM = 'Microsoft'
  4. AND EMPLOYE.SOC_ID = SOCIETE.SOC_ID
  5. AND DROITS.EMP_ID = EMPLOYE.EMP_ID
  6. AND TYPE_DROITS.TYPE_DROIT = DROITS.TYPE_DROIT
  7. AND PROGRAMME.PROG_ID = DROITS.PROG_ID
  8. 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 :
  1. SELECT EMPLOYE.NOM, TYPE_DROITS.LIBELLE
  2. FROM TYPE_DROITS, PROGRAMME, DROITS, EMPOYE, SOCIETE
  3. WHERE SOCIETE.NOM = 'Microsoft'
  4. AND EMPLOYE.SOC_ID = SOCIETE.SOC_ID
  5. AND DROITS.EMP_ID = EMPLOYE.EMP_ID
  6. AND TYPE_DROITS.TYPE_DROIT = DROITS.TYPE_DROIT
  7. AND PROGRAMME.PROG_ID = DROITS.PROG_ID
  8. 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 !

Reply

Marsh Posté le 30-03-2005 à 21:03:17   

Reply

Marsh Posté le 30-03-2005 à 22:53:15    

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


---------------
As the plane took off, the pilot turned to the co-pilot and said, “Have you ever flown solo?” Co-pilot: No. Typically I fly much higher than this.
Reply

Marsh Posté le 30-03-2005 à 23:00:25    

Flemmard :o

Reply

Marsh Posté le 30-03-2005 à 23:02:29    

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 :)

Reply

Marsh Posté le 31-03-2005 à 00:59:49    

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 :D) qui fait plus d'une cinquantaine de lignes (jointures sur une douzaine de tables, avec un joli union au milieu :D)
 
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
Reply

Marsh Posté le 31-03-2005 à 08:05:44    

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 ?


---------------
As the plane took off, the pilot turned to the co-pilot and said, “Have you ever flown solo?” Co-pilot: No. Typically I fly much higher than this.
Reply

Marsh Posté le 31-03-2005 à 09:34:49    

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 :
  1. SELECT /*+ ordered */ *
  2. FROM CUSTOMER, ACCOUNT
  3. WHERE ...;


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

Reply

Marsh Posté le 31-03-2005 à 10:35:15    

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 ;)

Reply

Marsh Posté le 31-03-2005 à 10:56:16    

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 :D 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 :D)
 

Code :
  1. declare @i as numeric
  2. declare @j as numeric
  4. set @i = 1
  5. while @i <= 1000
  6. begin
  7. insert into t1 (t1_id, nom) values (@i, 'test t1 ' + cast(@i as varchar))
  8. insert into t3 (t3_id, nom) values (@i, 'test t3 ' + cast(@i as varchar))
  10. set @j = 1
  11. while @j <= 1000
  12. begin
  13.  insert into t2 (t1_id, t2_id, nom) values (@i, @j, 'test t2 ' + cast(@i as varchar) + ' ' + cast(@j as varchar))
  14.  insert into t23 (t1_id, t2_id, t3_id, libelle) values (@i, @j, @i, 'test t23 ' + cast(@i as varchar) + ' ' + cast(@j as varchar))
  15.  set @j = @j + 1
  16. end
  17. set @i = @i + 1
  18. end


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

Marsh Posté le 31-03-2005 à 11:00:09    

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

Reply

Marsh Posté le 31-03-2005 à 11:00:09   

Reply

Marsh Posté le 31-03-2005 à 11:06:49    

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
Reply

Marsh Posté le 31-03-2005 à 11:10:34    

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 :D
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.

Reply

Marsh Posté le 31-03-2005 à 11:13:43    

joce a écrit :

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 :))


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 :D), 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 :o ;)


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

Marsh Posté le 31-03-2005 à 11:19:26    

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
Reply

Marsh Posté le 31-03-2005 à 11:26:29    

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 :
  1. CREATE procedure icsd_ARTAssetList
  2. (
  3. @orgid   int,
  4. @userid   int,
  5. @date1   datetime,
  6. @date2   datetime,
  7. @invdate1  datetime,
  8. @invdate2  datetime,
  9. @leasescheduleid int = 0,
  10. @leasecontractid int = 0,
  11. @duration  int = 0,
  12. @brand   varchar(255) = '%',
  13. @lineOfProduct  varchar(255) = '%',
  14. @manufacturerRef varchar(255) = '',
  15. @description  varchar(255) = '%',
  16. @category  varchar(255) = '',
  17. @cat1   varchar(255) = '',
  18. @cat2   varchar(255) = '',
  19. @cat3   varchar(255) = '',
  20. @serialnumber  varchar(255) = '%',
  21. @supplier  varchar(255) = '%',
  22. @invoice  varchar(255) = '%',
  23. @ref1   varchar(255) = '%',
  24. @ref2   varchar(255) = '%',
  25. @ref3   varchar(255) = '%',
  26. @ref4   varchar(255) = '%',
  27. @ref5   varchar(255) = '%',
  28. @assetid  int = 0,
  29. @orderby  varchar(255) = 'org'
  30. )
  31. AS
  32. BEGIN
  33. select orgid,
  34. name,
  35. leasescheduleid,
  36. schedule,
  37. -- inventory row
  38. -- inventory autoincrement
  39. manufacturerRef,
  40. SerialNumber,
  41. description,
  42. brand,
  43. lineOfProduct,
  44. category,
  45. cat1,
  46. cat2,
  47. cat3,
  48. supplier,
  49. invoice,
  50. invcreation,
  51. ref1,
  52. ref2,
  53. ref3,
  54. ref4,
  55. ref5,
  56. leasecontractid,
  57. contract,
  58. assetid
  59. from (
  60.  select o.orgid,
  61.   o.name,
  62.   ls.leasescheduleid,
  63.   ls.schedule,
  64. -- inventory row
  65. -- inventory autoincrement
  66.   a.ProductCode manufacturerRef,
  67.   a.SerialNumber,
  68.   a.description,
  69.   a.manufacture brand,
  70.   a.brand lineOfProduct,
  71.   zg.category,
  72.   a.catalogType cat1,
  73.   at1.value cat2,
  74.   at2.value cat3,
  75.   v.name supplier,
  76.   zf.numero invoice,
  77.   zf.datecreation invcreation,
  78.   a.reference1 ref1,
  79.   a.reference2 ref2,
  80.   a.reference3 ref3,
  81.   a.reference4 ref4,
  82.   a.reference5 ref5,
  83.   lc.leasecontractid,
  84.   lc.contract,
  85.   a.assetid
  86.  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
  87.  where (uo.orgid = @orgid or @orgid = 0)
  88.  and uo.userid = @userid
  89.  and o.type = 'C'
  90.  and o.active = 'A'
  91.  and o.orgid = uo.orgid
  92.  and zu.userid = uo.userid
  93.  and zu.orgid = uo.orgid
  94.  and (zu.scheduleid = @leasescheduleid or @leasescheduleid = 0)
  95.  and ls.orgid = o.orgid
  96.  and ls.leasescheduleid = zu.scheduleid
  97.  and ls.active = 'A'
  98.  and ls.userights = 1
  99.  and lc.orgid = ls.orgid
  100.  and lc.leasescheduleid = ls.leasescheduleid
  101.  and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
  102.  and (lc.leasecontractid = zu.contractid or zu.contractid = -1)
  103.  and lc.active = 'A'
  104.  and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
  105.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  106.  and cl.contractlineid = (select max(cl2.contractlineid)
  107.      from contractline cl2
  108.      where cl2.leasecontractid = lc.leasecontractid
  109.      and cl2.active = 'A')
  110.  and cl.active = 'A'
  111.  and zl.leasecontractid = cl.leasecontractid
  112.  and zl.contractlineid = cl.ContractLineID
  113.  and a.orgid = o.orgid
  114.  and (a.assetid = @assetid or @assetid = 0)
  115.  and a.linetype in ('B', 'S')
  116.  and a.assetid = zl.assetid
  117.  and a.manufacture like '%' + @brand + '%'
  118.  and a.brand like  '%' + @lineOfProduct + '%'
  119.  and a.description like  '%' + @description + '%'
  120.  and (a.catalogtype = @cat1 or @cat1 = '')
  121.  and (a.productCode = @manufacturerRef or @manufacturerRef = '')
  122.  and zg.catalogType = a.catalogType
  123.  and zg.orgid = a.orgid
  124.  and (zg.category = @category or @category = '')
  125.  and at1.assetid =* a.assetid
  126.  and at1.attributename =* zg.subcat1
  127.  and (at1.value = @cat2 or @cat2 = '')
  128.  and at2.assetid =* a.assetid
  129.  and at2.attributename =* zg.subcat2
  130.  and (at2.value = @cat3 or @cat3 = '')
  131.  and zfa.assetid = a.assetid
  132.  and zfa.leasescheduleid = ls.leasescheduleid
  133.  and zfa.leasecontractid = lc.leasecontractid
  134.  and zfa.contractlineid = cl.contractlineid
  135.  and zf.factureid = zfa.factureid
  136.  and zf.type = 'F'
  137.  and a.serialNumber like  '%' + @serialNumber + '%'
  138.  and v.orgid = a.VendorOrgID
  139.  and v.type = 'V'
  140.  and zf.numero like  '%' + @invoice + '%'
  141.  and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
  142.  and a.reference1 like  '%' + @ref1 + '%'
  143.  and a.reference2 like  '%' + @ref2 + '%'
  144.  and a.reference3 like  '%' + @ref3 + '%'
  145.  and a.reference4 like  '%' + @ref4 + '%'
  146.  and a.reference5 like  '%' + @ref5 + '%'
  147.  union all
  148.  select o.orgid,
  149.   o.name,
  150.   ls.leasescheduleid,
  151.   ls.schedule,
  152. -- inventory row
  153. -- inventory autoincrement
  154.   a.ProductCode manufacturerRef,
  155.   a.SerialNumber,
  156.   a.description,
  157.   a.manufacture brand,
  158.   a.brand lineOfProduct,
  159.   zg.category,
  160.   a.catalogType cat1,
  161.   at1.value cat2,
  162.   at2.value cat3,
  163.   v.name supplier,
  164.   zf.numero invoice,
  165.   zf.datecreation invcreation,
  166.   a.reference1 ref1,
  167.   a.reference2 ref2,
  168.   a.reference3 ref3,
  169.   a.reference4 ref4,
  170.   a.reference5 ref5,
  171.   lc.leasecontractid,
  172.   lc.contract,
  173.   a.assetid
  174.  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
  175.  where (uo.orgid = @orgid or @orgid = 0)
  176.  and uo.userid = @userid
  177.  and o.type = 'C'
  178.  and o.active = 'A'
  179.  and o.orgid = uo.orgid
  180.  and ls.orgid = o.orgid
  181.  and (ls.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
  182.  and ls.active = 'A'
  183.  and ls.userights = 0
  184.  and lc.orgid = ls.orgid
  185.  and lc.leasescheduleid = ls.leasescheduleid
  186.  and lc.active = 'A'
  187.  and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
  188.  and (lc.leasestopdate between @date1 and @date2 or @date1 is null or @date2 is null or @date1 > @date2)
  189.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  190.  and cl.contractlineid = (select max(cl2.contractlineid)
  191.      from contractline cl2
  192.      where cl2.leasecontractid = lc.leasecontractid
  193.      and cl2.active = 'A')
  194.  and cl.active = 'A'
  195.  and zl.leasecontractid = cl.leasecontractid
  196.  and zl.contractlineid = cl.ContractLineID
  197.  and a.orgid = o.orgid
  198.  and (a.assetid = @assetid or @assetid = 0)
  199.  and a.linetype in ('B', 'S')
  200.  and a.assetid = zl.assetid
  201.  and a.manufacture like  '%' + @brand + '%'
  202.  and a.brand like  '%' + @lineOfProduct + '%'
  203.  and a.description like  '%' + @description + '%'
  204.  and (a.catalogtype = @cat1 or @cat1 = '')
  205.  and (a.productCode = @manufacturerRef or @manufacturerRef = '')
  206.  and zg.catalogType = a.catalogType
  207.  and zg.orgid = a.orgid
  208.  and (zg.category = @category or @category = '')
  209.  and at1.assetid =* a.assetid
  210.  and at1.attributename =* zg.subcat1
  211.  and (at1.value = @cat2 or @cat2 = '')
  212.  and at2.assetid =* a.assetid
  213.  and at2.attributename =* zg.subcat2
  214.  and (at2.value = @cat3 or @cat3 = '')
  215.  and zfa.assetid = a.assetid
  216.  and zfa.leasescheduleid = ls.leasescheduleid
  217.  and zfa.leasecontractid = lc.leasecontractid
  218.  and zfa.contractlineid = cl.contractlineid
  219.  and zf.factureid = zfa.factureid
  220.  and zf.type = 'F'
  221.  and a.serialNumber like  '%' + @serialNumber + '%'
  222.  and v.orgid = a.VendorOrgID
  223.  and v.type = 'V'
  224.  and zf.numero like  '%' + @invoice + '%'
  225.  and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
  226.  and a.reference1 like  '%' + @ref1 + '%'
  227.  and a.reference2 like  '%' + @ref2 + '%'
  228.  and a.reference3 like  '%' + @ref3 + '%'
  229.  and a.reference4 like  '%' + @ref4 + '%'
  230.  and a.reference5 like  '%' + @ref5 + '%'
  231. ) tmp
  232. order by case @orderby
  233.  when 'org'  then name
  234.  when 'manufacturerRef' then manufacturerRef
  235.  when 'SerialNumber' then SerialNumber
  236.  when 'description' then description
  237.  when 'brand'  then brand
  238.  when 'lineOfProduct' then lineOfProduct
  239.  when 'category'  then category
  240.  when 'cat1'  then cat1
  241.  when 'cat2'  then cat2
  242.  when 'cat3'  then cat3
  243.  when 'supplier'  then supplier
  244.  when 'numero'  then invoice
  245.  when 'datecreation' then convert(varchar(8), invcreation, 112)
  246.  when 'reference1' then ref1
  247.  when 'reference2' then ref2
  248.  when 'reference3' then ref3
  249.  when 'reference4' then ref4
  250.  when 'reference5' then ref5
  251. end, case @orderby
  252.  when 'org'  then schedule
  253.  when 'manufacturerRef' then manufacturerRef
  254.  when 'SerialNumber' then SerialNumber
  255.  when 'description' then description
  256.  when 'brand'  then brand
  257.  when 'lineOfProduct' then lineOfProduct
  258.  when 'category'  then category
  259.  when 'cat1'  then cat1
  260.  when 'cat2'  then cat2
  261.  when 'cat3'  then cat3
  262.  when 'supplier'  then supplier
  263.  when 'numero'  then invoice
  264.  when 'datecreation' then convert(varchar(8), invcreation, 112)
  265.  when 'reference1' then ref1
  266.  when 'reference2' then ref2
  267.  when 'reference3' then ref3
  268.  when 'reference4' then ref4
  269.  when 'reference5' then ref5
  270. end
  271. END
  272. GO


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

Reply

Marsh Posté le 31-03-2005 à 11:37:33    

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 :o ;)

t'inquietes le forum est deja ecrit de facon optimale au niveau des requetes :D
 
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
 
 

Reply

Marsh Posté le 31-03-2005 à 11:39:12    

Je lirai ça plus tard :D Je bosse pas du tout avec MySQL :D
 
Bon, voilà ma base de test :

Code :
  1. /****** Objet :  Base de données testsd    Date du script : 31/03/2005 11:31:24 ******/
  2. IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'testsd')
  3. DROP DATABASE [testsd]
  4. GO
  6. 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%)
  7. GO
  9. exec sp_dboption N'testsd', N'autoclose', N'false'
  10. GO
  12. exec sp_dboption N'testsd', N'bulkcopy', N'false'
  13. GO
  15. exec sp_dboption N'testsd', N'trunc. log', N'false'
  16. GO
  18. exec sp_dboption N'testsd', N'torn page detection', N'true'
  19. GO
  21. exec sp_dboption N'testsd', N'read only', N'false'
  22. GO
  24. exec sp_dboption N'testsd', N'dbo use', N'false'
  25. GO
  27. exec sp_dboption N'testsd', N'single', N'false'
  28. GO
  30. exec sp_dboption N'testsd', N'autoshrink', N'false'
  31. GO
  33. exec sp_dboption N'testsd', N'ANSI null default', N'false'
  34. GO
  36. exec sp_dboption N'testsd', N'recursive triggers', N'false'
  37. GO
  39. exec sp_dboption N'testsd', N'ANSI nulls', N'false'
  40. GO
  42. exec sp_dboption N'testsd', N'concat null yields null', N'false'
  43. GO
  45. exec sp_dboption N'testsd', N'cursor close on commit', N'false'
  46. GO
  48. exec sp_dboption N'testsd', N'default to local cursor', N'false'
  49. GO
  51. exec sp_dboption N'testsd', N'quoted identifier', N'false'
  52. GO
  54. exec sp_dboption N'testsd', N'ANSI warnings', N'false'
  55. GO
  57. exec sp_dboption N'testsd', N'auto create statistics', N'true'
  58. GO
  60. exec sp_dboption N'testsd', N'auto update statistics', N'true'
  61. GO
  63. if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
  64. exec sp_dboption N'testsd', N'db chaining', N'false'
  65. GO
  67. use [testsd]
  68. GO
  70. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t2_t1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
  71. ALTER TABLE [dbo].[t2] DROP CONSTRAINT FK_t2_t1
  72. GO
  74. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t23_t3]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
  75. ALTER TABLE [dbo].[t23] DROP CONSTRAINT FK_t23_t3
  76. GO
  78. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_t23_t2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
  79. ALTER TABLE [dbo].[t23] DROP CONSTRAINT FK_t23_t2
  80. GO
  82. /****** Objet :  Table [dbo].[t23]    Date du script : 31/03/2005 11:31:30 ******/
  83. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t23]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  84. drop table [dbo].[t23]
  85. GO
  87. /****** Objet :  Table [dbo].[t2]    Date du script : 31/03/2005 11:31:30 ******/
  88. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  89. drop table [dbo].[t2]
  90. GO
  92. /****** Objet :  Table [dbo].[t1]    Date du script : 31/03/2005 11:31:30 ******/
  93. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  94. drop table [dbo].[t1]
  95. GO
  97. /****** Objet :  Table [dbo].[t3]    Date du script : 31/03/2005 11:31:30 ******/
  98. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  99. drop table [dbo].[t3]
  100. GO
  102. /****** Objet :  Nom d'accès interclass_user    Date du script : 31/03/2005 11:31:24 ******/
  103. if not exists (select * from master.dbo.syslogins where loginname = N'interclass_user')
  104. BEGIN
  105. declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = null, @loginlang = N'Français'
  106. if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
  107.  select @logindb = N'master'
  108. if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
  109.  select @loginlang = @@language
  110. exec sp_addlogin N'interclass_user', null, @logindb, @loginlang
  111. END
  112. GO
  114. /****** Objet :  Nom d'accès BUILTIN\Administrateurs    Date du script : 31/03/2005 11:31:24 ******/
  115. exec sp_addsrvrolemember N'BUILTIN\Administrateurs', sysadmin
  116. GO
  118. /****** Objet :  Utilisateur dbo    Date du script : 31/03/2005 11:31:24 ******/
  119. /****** Objet :  Table [dbo].[t1]    Date du script : 31/03/2005 11:31:32 ******/
  120. CREATE TABLE [dbo].[t1] (
  121. [t1_id] [numeric](18, 0) NOT NULL ,
  122. [nom] [varchar] (50) NOT NULL
  123. ) ON [PRIMARY]
  124. GO
  126. /****** Objet :  Table [dbo].[t3]    Date du script : 31/03/2005 11:31:33 ******/
  127. CREATE TABLE [dbo].[t3] (
  128. [t3_id] [numeric](18, 0) NOT NULL ,
  129. [nom] [varchar] (50) NOT NULL
  130. ) ON [PRIMARY]
  131. GO
  133. /****** Objet :  Table [dbo].[t2]    Date du script : 31/03/2005 11:31:34 ******/
  134. CREATE TABLE [dbo].[t2] (
  135. [t1_id] [numeric](18, 0) NOT NULL ,
  136. [t2_id] [numeric](18, 0) NOT NULL ,
  137. [nom] [varchar] (50) NOT NULL
  138. ) ON [PRIMARY]
  139. GO
  141. /****** Objet :  Table [dbo].[t23]    Date du script : 31/03/2005 11:31:34 ******/
  142. CREATE TABLE [dbo].[t23] (
  143. [t1_id] [numeric](18, 0) NOT NULL ,
  144. [t2_id] [numeric](18, 0) NOT NULL ,
  145. [t3_id] [numeric](18, 0) NOT NULL ,
  146. [libelle] [varchar] (50) NOT NULL
  147. ) ON [PRIMARY]
  148. GO
  150. ALTER TABLE [dbo].[t1] WITH NOCHECK ADD
  151. CONSTRAINT [PK_t1] PRIMARY KEY  CLUSTERED
  152. (
  153.  [t1_id]
  154. )  ON [PRIMARY]
  155. GO
  157. ALTER TABLE [dbo].[t3] WITH NOCHECK ADD
  158. CONSTRAINT [PK_t3] PRIMARY KEY  CLUSTERED
  159. (
  160.  [t3_id]
  161. )  ON [PRIMARY]
  162. GO
  164. ALTER TABLE [dbo].[t2] WITH NOCHECK ADD
  165. CONSTRAINT [PK_t2] PRIMARY KEY  CLUSTERED
  166. (
  167.  [t1_id],
  168.  [t2_id]
  169. )  ON [PRIMARY]
  170. GO
  172. ALTER TABLE [dbo].[t23] WITH NOCHECK ADD
  173. CONSTRAINT [PK_t23] PRIMARY KEY  CLUSTERED
  174. (
  175.  [t1_id],
  176.  [t2_id],
  177.  [t3_id]
  178. )  ON [PRIMARY]
  179. GO
  181. ALTER TABLE [dbo].[t2] ADD
  182. CONSTRAINT [FK_t2_t1] FOREIGN KEY
  183. (
  184.  [t1_id]
  185. ) REFERENCES [dbo].[t1] (
  186.  [t1_id]
  187. )
  188. GO
  190. ALTER TABLE [dbo].[t23] ADD
  191. CONSTRAINT [FK_t23_t2] FOREIGN KEY
  192. (
  193.  [t1_id],
  194.  [t2_id]
  195. ) REFERENCES [dbo].[t2] (
  196.  [t1_id],
  197.  [t2_id]
  198. ),
  199. CONSTRAINT [FK_t23_t3] FOREIGN KEY
  200. (
  201.  [t3_id]
  202. ) REFERENCES [dbo].[t3] (
  203.  [t3_id]
  204. )
  205. 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:

Reply

Marsh Posté le 31-03-2005 à 12:01:56    

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 :D une honte :o

Reply

Marsh Posté le 31-03-2005 à 12:15:42    

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 :
  1. select t3.t3_id, t1.nom, t23.libelle, t3.nom
  2. from t3, t23, t2, t1
  3. where t1.t1_id between 200 and 210
  4. and t2.t1_id = t1.t1_id
  5. and t23.t1_id = t2.t1_id
  6. and t23.t2_id = t2.t2_id
  7. and t3.t3_id = t23.t3_id
  8. -- 2 secondes
  10. select t3.t3_id, t1.nom, t23.libelle, t3.nom
  11. from t3, t23, t1
  12. where t1.t1_id between 200 and 210
  13. and t23.t1_id = t1.t1_id
  14. and t3.t3_id = t23.t3_id
  15. -- 0 secondes


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

Reply

Marsh Posté le 31-03-2005 à 13:15:13    

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

---------------
As the plane took off, the pilot turned to the co-pilot and said, “Have you ever flown solo?” Co-pilot: No. Typically I fly much higher than this.
Reply

Marsh Posté le 31-03-2005 à 17:46:57    

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

Reply

Marsh Posté le 05-04-2005 à 17:39:50    

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
Reply

Marsh Posté le 05-04-2005 à 17:41:32    

Pour résumer le bordel :
 

Code :
  1. CREATE procedure icsd_ARTAssetList
  2. (
  3. @orgid   int,
  4. @userid   int,
  5. @date1   datetime,
  6. @date2   datetime,
  7. @invdate1  datetime,
  8. @invdate2  datetime,
  9. @leasescheduleid int = 0,
  10. @leasecontractid int = 0,
  11. @duration  int = 0,
  12. @brand   varchar(255) = '%',
  13. @lineOfProduct  varchar(255) = '%',
  14. @manufacturerRef varchar(255) = '',
  15. @description  varchar(255) = '%',
  16. @category  varchar(255) = '',
  17. @cat1   varchar(255) = '',
  18. @cat2   varchar(255) = '',
  19. @cat3   varchar(255) = '',
  20. @serialnumber  varchar(255) = '%',
  21. @supplier  varchar(255) = '%',
  22. @invoice  varchar(255) = '%',
  23. @ref1   varchar(255) = '%',
  24. @ref2   varchar(255) = '%',
  25. @ref3   varchar(255) = '%',
  26. @ref4   varchar(255) = '%',
  27. @ref5   varchar(255) = '%',
  28. @assetid  int = 0,
  29. @orderby  varchar(255) = 'org'
  30. )
  31. AS
  32. BEGIN
  33. select orgid,
  34. name,
  35. leasescheduleid,
  36. schedule,
  37. -- inventory row
  38. -- inventory autoincrement
  39. manufacturerRef,
  40. SerialNumber,
  41. description,
  42. brand,
  43. lineOfProduct,
  44. category,
  45. cat1,
  46. cat2,
  47. cat3,
  48. supplier,
  49. invoice,
  50. invcreation,
  51. ref1,
  52. ref2,
  53. ref3,
  54. ref4,
  55. ref5,
  56. leasecontractid,
  57. contract,
  58. assetid
  59. from (
  60.  select o.orgid,
  61.   o.name,
  62.   ls.leasescheduleid,
  63.   ls.schedule,
  64. -- inventory row
  65. -- inventory autoincrement
  66.   a.ProductCode manufacturerRef,
  67.   a.SerialNumber,
  68.   a.description,
  69.   a.manufacture brand,
  70.   a.brand lineOfProduct,
  71.   zg.category,
  72.   a.catalogType cat1,
  73.   at1.value cat2,
  74.   at2.value cat3,
  75.   v.name supplier,
  76.   zf.numero invoice,
  77.   zf.datecreation invcreation,
  78.   a.reference1 ref1,
  79.   a.reference2 ref2,
  80.   a.reference3 ref3,
  81.   a.reference4 ref4,
  82.   a.reference5 ref5,
  83.   lc.leasecontractid,
  84.   lc.contract,
  85.   a.assetid
  86.  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
  87.  where (uo.orgid = @orgid or @orgid = 0)
  88.  and uo.userid = @userid
  89.  and o.type = 'C'
  90.  and o.active = 'A'
  91.  and o.orgid = uo.orgid
  92.  and zu.userid = uo.userid
  93.  and zu.orgid = uo.orgid
  94.  and (zu.scheduleid = @leasescheduleid or @leasescheduleid = 0)
  95.  and ls.orgid = o.orgid
  96.  and ls.leasescheduleid = zu.scheduleid
  97.  and ls.active = 'A'
  98.  and ls.userights = 1
  99.  and lc.orgid = ls.orgid
  100.  and lc.leasescheduleid = ls.leasescheduleid
  101.  and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
  102.  and (lc.leasecontractid = zu.contractid or zu.contractid = -1)
  103.  and lc.active = 'A'
  104.  and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
  105.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  106.  and cl.contractlineid = (select max(cl2.contractlineid)
  107.      from contractline cl2
  108.      where cl2.leasecontractid = lc.leasecontractid
  109.      and cl2.active = 'A')
  110.  and cl.active = 'A'
  111.  and zl.leasecontractid = cl.leasecontractid
  112.  and zl.contractlineid = cl.ContractLineID
  113.  and a.orgid = o.orgid
  114.  and (a.assetid = @assetid or @assetid = 0)
  115.  and a.linetype in ('B', 'S')
  116.  and a.assetid = zl.assetid
  117.  and a.manufacture like '%' + @brand + '%'
  118.  and a.brand like  '%' + @lineOfProduct + '%'
  119.  and a.description like  '%' + @description + '%'
  120.  and (a.catalogtype = @cat1 or @cat1 = '')
  121.  and (a.productCode = @manufacturerRef or @manufacturerRef = '')
  122.  and zg.catalogType = a.catalogType
  123.  and zg.orgid = a.orgid
  124.  and (zg.category = @category or @category = '')
  125.  and at1.assetid =* a.assetid
  126.  and at1.attributename =* zg.subcat1
  127.  and (at1.value = @cat2 or @cat2 = '')
  128.  and at2.assetid =* a.assetid
  129.  and at2.attributename =* zg.subcat2
  130.  and (at2.value = @cat3 or @cat3 = '')
  131.  and zfa.assetid = a.assetid
  132.  and zfa.leasescheduleid = ls.leasescheduleid
  133.  and zfa.leasecontractid = lc.leasecontractid
  134.  and zfa.contractlineid = cl.contractlineid
  135.  and zf.factureid = zfa.factureid
  136.  and zf.type = 'F'
  137.  and a.serialNumber like  '%' + @serialNumber + '%'
  138.  and v.orgid = a.VendorOrgID
  139.  and v.type = 'V'
  140.  and zf.numero like  '%' + @invoice + '%'
  141.  and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
  142.  and a.reference1 like  '%' + @ref1 + '%'
  143.  and a.reference2 like  '%' + @ref2 + '%'
  144.  and a.reference3 like  '%' + @ref3 + '%'
  145.  and a.reference4 like  '%' + @ref4 + '%'
  146.  and a.reference5 like  '%' + @ref5 + '%'
  147.  union all
  148.  select o.orgid,
  149.   o.name,
  150.   ls.leasescheduleid,
  151.   ls.schedule,
  152. -- inventory row
  153. -- inventory autoincrement
  154.   a.ProductCode manufacturerRef,
  155.   a.SerialNumber,
  156.   a.description,
  157.   a.manufacture brand,
  158.   a.brand lineOfProduct,
  159.   zg.category,
  160.   a.catalogType cat1,
  161.   at1.value cat2,
  162.   at2.value cat3,
  163.   v.name supplier,
  164.   zf.numero invoice,
  165.   zf.datecreation invcreation,
  166.   a.reference1 ref1,
  167.   a.reference2 ref2,
  168.   a.reference3 ref3,
  169.   a.reference4 ref4,
  170.   a.reference5 ref5,
  171.   lc.leasecontractid,
  172.   lc.contract,
  173.   a.assetid
  174.  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
  175.  where (uo.orgid = @orgid or @orgid = 0)
  176.  and uo.userid = @userid
  177.  and o.type = 'C'
  178.  and o.active = 'A'
  179.  and o.orgid = uo.orgid
  180.  and ls.orgid = o.orgid
  181.  and (ls.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
  182.  and ls.active = 'A'
  183.  and ls.userights = 0
  184.  and lc.orgid = ls.orgid
  185.  and lc.leasescheduleid = ls.leasescheduleid
  186.  and lc.active = 'A'
  187.  and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
  188.  and (lc.leasestopdate between @date1 and @date2 or @date1 is null or @date2 is null or @date1 > @date2)
  189.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  190.  and cl.contractlineid = (select max(cl2.contractlineid)
  191.      from contractline cl2
  192.      where cl2.leasecontractid = lc.leasecontractid
  193.      and cl2.active = 'A')
  194.  and cl.active = 'A'
  195.  and zl.leasecontractid = cl.leasecontractid
  196.  and zl.contractlineid = cl.ContractLineID
  197.  and a.orgid = o.orgid
  198.  and (a.assetid = @assetid or @assetid = 0)
  199.  and a.linetype in ('B', 'S')
  200.  and a.assetid = zl.assetid
  201.  and a.manufacture like  '%' + @brand + '%'
  202.  and a.brand like  '%' + @lineOfProduct + '%'
  203.  and a.description like  '%' + @description + '%'
  204.  and (a.catalogtype = @cat1 or @cat1 = '')
  205.  and (a.productCode = @manufacturerRef or @manufacturerRef = '')
  206.  and zg.catalogType = a.catalogType
  207.  and zg.orgid = a.orgid
  208.  and (zg.category = @category or @category = '')
  209.  and at1.assetid =* a.assetid
  210.  and at1.attributename =* zg.subcat1
  211.  and (at1.value = @cat2 or @cat2 = '')
  212.  and at2.assetid =* a.assetid
  213.  and at2.attributename =* zg.subcat2
  214.  and (at2.value = @cat3 or @cat3 = '')
  215.  and zfa.assetid = a.assetid
  216.  and zfa.leasescheduleid = ls.leasescheduleid
  217.  and zfa.leasecontractid = lc.leasecontractid
  218.  and zfa.contractlineid = cl.contractlineid
  219.  and zf.factureid = zfa.factureid
  220.  and zf.type = 'F'
  221.  and a.serialNumber like  '%' + @serialNumber + '%'
  222.  and v.orgid = a.VendorOrgID
  223.  and v.type = 'V'
  224.  and zf.numero like  '%' + @invoice + '%'
  225.  and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
  226.  and a.reference1 like  '%' + @ref1 + '%'
  227.  and a.reference2 like  '%' + @ref2 + '%'
  228.  and a.reference3 like  '%' + @ref3 + '%'
  229.  and a.reference4 like  '%' + @ref4 + '%'
  230.  and a.reference5 like  '%' + @ref5 + '%'
  231. ) tmp
  232. order by case @orderby
  233.  when 'org'  then name
  234.  when 'manufacturerRef' then manufacturerRef
  235.  when 'SerialNumber' then SerialNumber
  236.  when 'description' then description
  237.  when 'brand'  then brand
  238.  when 'lineOfProduct' then lineOfProduct
  239.  when 'category'  then category
  240.  when 'cat1'  then cat1
  241.  when 'cat2'  then cat2
  242.  when 'cat3'  then cat3
  243.  when 'supplier'  then supplier
  244.  when 'numero'  then invoice
  245.  when 'datecreation' then convert(varchar(8), invcreation, 112)
  246.  when 'reference1' then ref1
  247.  when 'reference2' then ref2
  248.  when 'reference3' then ref3
  249.  when 'reference4' then ref4
  250.  when 'reference5' then ref5
  251. end, case @orderby
  252.  when 'org'  then schedule
  253.  when 'manufacturerRef' then manufacturerRef
  254.  when 'SerialNumber' then SerialNumber
  255.  when 'description' then description
  256.  when 'brand'  then brand
  257.  when 'lineOfProduct' then lineOfProduct
  258.  when 'category'  then category
  259.  when 'cat1'  then cat1
  260.  when 'cat2'  then cat2
  261.  when 'cat3'  then cat3
  262.  when 'supplier'  then supplier
  263.  when 'numero'  then invoice
  264.  when 'datecreation' then convert(varchar(8), invcreation, 112)
  265.  when 'reference1' then ref1
  266.  when 'reference2' then ref2
  267.  when 'reference3' then ref3
  268.  when 'reference4' then ref4
  269.  when 'reference5' then ref5
  270. end
  271. END
  272. GO
  273. -- 19 SECONDES
  279. NEW
  288. CREATE procedure icsd_ARTAssetList
  289. (
  290. @orgid   int,
  291. @userid   int,
  292. @date1   datetime,
  293. @date2   datetime,
  294. @invdate1  datetime,
  295. @invdate2  datetime,
  296. @leasescheduleid int = 0,
  297. @leasecontractid int = 0,
  298. @duration  int = 0,
  299. @brand   varchar(255) = '%',
  300. @lineOfProduct  varchar(255) = '%',
  301. @manufacturerRef varchar(255) = '',
  302. @description  varchar(255) = '%',
  303. @category  varchar(255) = '',
  304. @cat1   varchar(255) = '',
  305. @cat2   varchar(255) = '',
  306. @cat3   varchar(255) = '',
  307. @serialnumber  varchar(255) = '%',
  308. @supplier  varchar(255) = '%',
  309. @invoice  varchar(255) = '%',
  310. @ref1   varchar(255) = '%',
  311. @ref2   varchar(255) = '%',
  312. @ref3   varchar(255) = '%',
  313. @ref4   varchar(255) = '%',
  314. @ref5   varchar(255) = '%',
  315. @assetid  int = 0,
  316. @orderby  varchar(255) = 'org'
  317. )
  318. AS
  319. BEGIN
  320. select orgid,
  321. name,
  322. leasescheduleid,
  323. schedule,
  324. -- inventory row
  325. -- inventory autoincrement
  326. manufacturerRef,
  327. SerialNumber,
  328. description,
  329. brand,
  330. lineOfProduct,
  331. category,
  332. cat1,
  333. cat2,
  334. cat3,
  335. supplier,
  336. invoice,
  337. invcreation,
  338. ref1,
  339. ref2,
  340. ref3,
  341. ref4,
  342. ref5,
  343. leasecontractid,
  344. contract,
  345. assetid
  346. from (
  347.  select o.orgid,
  348.   o.name,
  349.   ls.leasescheduleid,
  350.   ls.schedule,
  351. -- inventory row
  352. -- inventory autoincrement
  353.   a.ProductCode manufacturerRef,
  354.   a.SerialNumber,
  355.   a.description,
  356.   a.manufacture brand,
  357.   a.brand lineOfProduct,
  358.   zg.category,
  359.   a.catalogType cat1,
  360.   at1.value cat2,
  361.   at2.value cat3,
  362.   v.name supplier,
  363.   zf.numero invoice,
  364.   zf.datecreation invcreation,
  365.   a.reference1 ref1,
  366.   a.reference2 ref2,
  367.   a.reference3 ref3,
  368.   a.reference4 ref4,
  369.   a.reference5 ref5,
  370.   lc.leasecontractid,
  371.   lc.contract,
  372.   a.assetid
  373.  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
  374.  where 1 = 1
  375.  and (a.orgid = @orgid or @orgid = 0)
  376.  and (a.assetid = @assetid or @assetid = 0)
  377.  and a.linetype in ('B', 'S')
  378.  and a.manufacture like '%' + @brand + '%'
  379.  and a.brand like  '%' + @lineOfProduct + '%'
  380.  and a.description like  '%' + @description + '%'
  381.  and (a.catalogtype = @cat1 or @cat1 = '')
  382.  and (a.productCode = @manufacturerRef or @manufacturerRef = '')
  383.  and a.serialNumber like  '%' + @serialNumber + '%'
  384.  and a.reference1 like  '%' + @ref1 + '%'
  385.  and a.reference2 like  '%' + @ref2 + '%'
  386.  and a.reference3 like  '%' + @ref3 + '%'
  387.  and a.reference4 like  '%' + @ref4 + '%'
  388.  and a.reference5 like  '%' + @ref5 + '%'
  389.  and (a.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
  390.  and (a.leasecontractid = @leasecontractid or @leasecontractid = 0)
  391.  and a.contractlineid = (select max(cl2.contractlineid)
  392.      from contractline cl2
  393.      where cl2.leasecontractid = a.leasecontractid
  394.      and cl2.active = 'A')
  395.  and zl.leasecontractid = a.leasecontractid
  396.  and zl.contractlineid = a.ContractLineID
  397.  and zl.assetid = a.assetid
  398.  and cl.contractlineid = zl.contractlineid
  399.  and cl.leasecontractid = zl.leasecontractid
  400.  and cl.active = 'A'
  401.  and lc.orgid = a.orgid
  402.  and lc.leasescheduleid = a.leasescheduleid
  403.  and lc.leasecontractid = zl.leasecontractid
  404.  and lc.active = 'A'
  405.  and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
  406.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  407.  and ls.orgid = lc.orgid
  408.  and ls.leasescheduleid = lc.leasescheduleid
  409.  and ls.active = 'A'
  410.  and ls.userights = 1
  411.  and uo.orgid = ls.orgid
  412.  and uo.userid = @userid
  413.  and o.type = 'C'
  414.  and o.active = 'A'
  415.  and o.orgid = uo.orgid
  416.  and zu.userid = uo.userid
  417.  and zu.orgid = uo.orgid
  418.  and zu.scheduleid = ls.leasescheduleid
  419.  and (zu.contractid = lc.leasecontractid or zu.contractid = -1)
  420.  and zg.catalogType = a.catalogType
  421.  and zg.orgid = a.orgid
  422.  and (zg.category = @category or @category = '')
  423.  and at1.assetid =* a.assetid
  424.  and at1.attributename =* zg.subcat1
  425.  and (at1.value = @cat2 or @cat2 = '')
  426.  and at2.assetid =* a.assetid
  427.  and at2.attributename =* zg.subcat2
  428.  and (at2.value = @cat3 or @cat3 = '')
  429.  and zfa.assetid = a.assetid
  430.  and zfa.leasescheduleid = ls.leasescheduleid
  431.  and zfa.leasecontractid = lc.leasecontractid
  432.  and zfa.contractlineid = cl.contractlineid
  433.  and zf.factureid = zfa.factureid
  434.  and zf.type = 'F'
  435.  and v.orgid = a.VendorOrgID
  436.  and v.type = 'V'
  437.  and zf.numero like  '%' + @invoice + '%'
  438.  and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
  439.  union all
  440.  select o.orgid,
  441.   o.name,
  442.   ls.leasescheduleid,
  443.   ls.schedule,
  444. -- inventory row
  445. -- inventory autoincrement
  446.   a.ProductCode manufacturerRef,
  447.   a.SerialNumber,
  448.   a.description,
  449.   a.manufacture brand,
  450.   a.brand lineOfProduct,
  451.   zg.category,
  452.   a.catalogType cat1,
  453.   at1.value cat2,
  454.   at2.value cat3,
  455.   v.name supplier,
  456.   zf.numero invoice,
  457.   zf.datecreation invcreation,
  458.   a.reference1 ref1,
  459.   a.reference2 ref2,
  460.   a.reference3 ref3,
  461.   a.reference4 ref4,
  462.   a.reference5 ref5,
  463.   lc.leasecontractid,
  464.   lc.contract,
  465.   a.assetid
  466.  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
  467.  where 1 = 1
  468.  and (a.orgid = @orgid or @orgid = 0)
  469.  and (a.assetid = @assetid or @assetid = 0)
  470.  and a.linetype in ('B', 'S')
  471.  and a.manufacture like '%' + @brand + '%'
  472.  and a.brand like  '%' + @lineOfProduct + '%'
  473.  and a.description like  '%' + @description + '%'
  474.  and (a.catalogtype = @cat1 or @cat1 = '')
  475.  and (a.productCode = @manufacturerRef or @manufacturerRef = '')
  476.  and a.serialNumber like  '%' + @serialNumber + '%'
  477.  and a.reference1 like  '%' + @ref1 + '%'
  478.  and a.reference2 like  '%' + @ref2 + '%'
  479.  and a.reference3 like  '%' + @ref3 + '%'
  480.  and a.reference4 like  '%' + @ref4 + '%'
  481.  and a.reference5 like  '%' + @ref5 + '%'
  482.  and (a.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
  483.  and (a.leasecontractid = @leasecontractid or @leasecontractid = 0)
  484.  and a.contractlineid = (select max(cl2.contractlineid)
  485.      from contractline cl2
  486.      where cl2.leasecontractid = a.leasecontractid
  487.      and cl2.active = 'A')
  488.  and zl.leasecontractid = a.leasecontractid
  489.  and zl.contractlineid = a.ContractLineID
  490.  and zl.assetid = a.assetid
  491.  and cl.contractlineid = zl.contractlineid
  492.  and cl.leasecontractid = zl.leasecontractid
  493.  and cl.active = 'A'
  494.  and lc.orgid = a.orgid
  495.  and lc.leasescheduleid = a.leasescheduleid
  496.  and lc.leasecontractid = zl.leasecontractid
  497.  and lc.active = 'A'
  498.  and (lc.leasestopdate between @date1 and @date2 or @date1 > @date2 or @date1 is null or @date2 is null)
  499.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  500.  and ls.orgid = lc.orgid
  501.  and ls.leasescheduleid = lc.leasescheduleid
  502.  and ls.active = 'A'
  503.  and ls.userights = 0
  504.  and uo.orgid = ls.orgid
  505.  and uo.userid = @userid
  506.  and o.type = 'C'
  507.  and o.active = 'A'
  508.  and o.orgid = uo.orgid
  509.  and zg.catalogType = a.catalogType
  510.  and zg.orgid = a.orgid
  511.  and (zg.category = @category or @category = '')
  512.  and at1.assetid =* a.assetid
  513.  and at1.attributename =* zg.subcat1
  514.  and (at1.value = @cat2 or @cat2 = '')
  515.  and at2.assetid =* a.assetid
  516.  and at2.attributename =* zg.subcat2
  517.  and (at2.value = @cat3 or @cat3 = '')
  518.  and zfa.assetid = a.assetid
  519.  and zfa.leasescheduleid = ls.leasescheduleid
  520.  and zfa.leasecontractid = lc.leasecontractid
  521.  and zfa.contractlineid = cl.contractlineid
  522.  and zf.factureid = zfa.factureid
  523.  and zf.type = 'F'
  524.  and v.orgid = a.VendorOrgID
  525.  and v.type = 'V'
  526.  and zf.numero like  '%' + @invoice + '%'
  527.  and (zf.datecreation between @invdate1 and @invdate2 or @invdate1 > @invdate2 or @invdate1 is null or @invdate2 is null)
  528. ) tmp
  529. order by case @orderby
  530.  when 'org'  then name
  531.  when 'manufacturerRef' then manufacturerRef
  532.  when 'SerialNumber' then SerialNumber
  533.  when 'description' then description
  534.  when 'brand'  then brand
  535.  when 'lineOfProduct' then lineOfProduct
  536.  when 'category'  then category
  537.  when 'cat1'  then cat1
  538.  when 'cat2'  then cat2
  539.  when 'cat3'  then cat3
  540.  when 'supplier'  then supplier
  541.  when 'numero'  then invoice
  542.  when 'datecreation' then convert(varchar(8), invcreation, 112)
  543.  when 'reference1' then ref1
  544.  when 'reference2' then ref2
  545.  when 'reference3' then ref3
  546.  when 'reference4' then ref4
  547.  when 'reference5' then ref5
  548. end, case @orderby
  549.  when 'org'  then schedule
  550.  when 'manufacturerRef' then manufacturerRef
  551.  when 'SerialNumber' then SerialNumber
  552.  when 'description' then description
  553.  when 'brand'  then brand
  554.  when 'lineOfProduct' then lineOfProduct
  555.  when 'category'  then category
  556.  when 'cat1'  then cat1
  557.  when 'cat2'  then cat2
  558.  when 'cat3'  then cat3
  559.  when 'supplier'  then supplier
  560.  when 'numero'  then invoice
  561.  when 'datecreation' then convert(varchar(8), invcreation, 112)
  562.  when 'reference1' then ref1
  563.  when 'reference2' then ref2
  564.  when 'reference3' then ref3
  565.  when 'reference4' then ref4
  566.  when 'reference5' then ref5
  567. end
  568. END
  569. GO
  571. -- 2 SECONDES !!!

Reply

Marsh Posté le 05-04-2005 à 18:36:47    

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.

Reply

Marsh Posté le 05-04-2005 à 19:22:55    

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 :D) 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.

Reply

Marsh Posté le 06-04-2005 à 10:45:49    

Arjuna a écrit :

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 :D) 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.


 
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...

Reply

Marsh Posté le 06-04-2005 à 11:17:39    

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.

Reply

Marsh Posté le 06-04-2005 à 12:22:05    

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
Reply

Marsh Posté le 06-04-2005 à 12:22:24    

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:

Reply

Marsh Posté le 06-04-2005 à 14:26:02    

cinocks:

Code :
  1. select count(*) from organization
  2. -----------
  3. 69
  5. select count(*) from zfactures
  6. -----------
  7. 242
  9. select count(*) from zfactureasset
  10. -----------
  11. 93
  13. select count(*) from sdViewAttributesValues -- vue
  14. -----------
  15. 44660
  17. select count(*) from zCatalogTypeGenre
  18. -----------
  19. 400
  21. select count(*) from zUserContractPerimeter
  22. -----------
  23. 0
  25. select count(*) from UserOrg
  26. -----------
  27. 46
  29. select count(*) from leaseschedule
  30. -----------
  31. 5
  33. select count(*) from leasecontract
  34. -----------
  35. 26
  37. select count(*) from contractline
  38. -----------
  39. 46
  41. select count(*) from zloyer
  42. -----------
  43. 9363
  45. select count(*) from asset
  46. -----------
  47. 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 :D

Reply

Marsh Posté le 06-04-2005 à 14:26:35    

rufo a écrit :

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:


J'ai rien compris du tout :D
 
Tu veux quoi ?

Reply

Marsh Posté le 06-04-2005 à 14:27:27    

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 :D)

Reply

Marsh Posté le 06-04-2005 à 14:45:50    

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 :)

Reply

Marsh Posté le 06-04-2005 à 14:46:32    

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 :D)


 
bah, même pas peur :D

Reply

Marsh Posté le 06-04-2005 à 16:27:04    

Arjuna a écrit :

cinocks:

Code :
  1. select count(*) from organization
  2. -----------
  3. 69
  5. select count(*) from zfactures
  6. -----------
  7. 242
  9. select count(*) from zfactureasset
  10. -----------
  11. 93
  13. select count(*) from sdViewAttributesValues -- vue
  14. -----------
  15. 44660
  17. select count(*) from zCatalogTypeGenre
  18. -----------
  19. 400
  21. select count(*) from zUserContractPerimeter
  22. -----------
  23. 0
  25. select count(*) from UserOrg
  26. -----------
  27. 46
  29. select count(*) from leaseschedule
  30. -----------
  31. 5
  33. select count(*) from leasecontract
  34. -----------
  35. 26
  37. select count(*) from contractline
  38. -----------
  39. 46
  41. select count(*) from zloyer
  42. -----------
  43. 9363
  45. select count(*) from asset
  46. -----------
  47. 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 :D


 
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
Reply

Marsh Posté le 06-04-2005 à 16:43:20    

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.

Reply

Marsh Posté le 06-04-2005 à 16:55:28    

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
Reply

Marsh Posté le 06-04-2005 à 17:45:19    

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 ........

Reply

Marsh Posté le 06-04-2005 à 17:52:14    

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.

Reply

Marsh Posté le 06-04-2005 à 17:52:52    

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.

Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

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