Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
2294 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Tentative de mini-bench comparant MySQL et SQL Server

 



 Mot :   Pseudo :  
 
 Page :   1  2
Page Précédente
Auteur Sujet :

Tentative de mini-bench comparant MySQL et SQL Server

n°1160981
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 01:34:16  profilanswer
 

ATTENTION:
 
Je suis actuellement en train de réécrire de A à Z un nouveau bench bien plus complexe, et qui tente de répondre des critères d'utilisation plus classiques d'une base de données.
Les résultats de ce test sont donc à prendre avec parcimonie, et ne permettent très certainement pas de faire de réelles conclusions (test trop imparfait, et trop limité).

 
Voilà, je viens de passer ma soirée à le faire.
Je suis totalement ouvert pour apporter d'autres éléments de tests (probants si possible, pas des trucs où on sait que MySQL éclate tous les autres SGBD, car ils sont rarement représentatifs)
 
Voici le test :
 
 

Alors, vu qu'on n'arrive pas à se mettre d'accord sur les capacités de MySQL, j'ai décidé de faire un petit bench moi-même.
 
J'ai déjà une base sur SQL Server, écrite à l'origine pour faire un petit jeu en ligne.
Le projet est plus ou moins à l'abandon faute de temps, mais la base de données, en partie remplie, constitue une bonne base pour faire quelques tests de montée en charge.
 
Je tiens à préciser deux choses :
- La base sur SQL Server étant totalement en cours de développement, je n'ai pas eu le temps de faire le moindre tuning de performance.
- J'ai repris les mêmes index, même structure (type, etc.) pour faire mes tests.
- Je n'ai pas trouvé de drivers officiels OLEDB pour MySQL, donc tous les tests sont donc effectués avec des drivers ODBC (MySQL tout comme SQL Server)
- Versions testées :
 MySQL Database 4.1.13 (version "Generally Available" sur le site http://dev.mysql.com/ au 26/07/2005)
 Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
 
Plateforme de test
------------------
 
 Configuration système :
  CPU : 2xPiii 933 MHz EB  
  MB : Asus CUV4X-DLS (Chipset Via 694XDP)  
  CG : ATI Radon All-In-Wonder 7200  
  RAM : 2 Go SD-RAM PC133  
  HD1 : 18.6 Go U3W IBM  
  HD2 : 18.6 Go U3W IBM  
  HD3 : 18.6 Go U3W IBM  
  HD4 : 18.6 Go U3W IBM  
  HD5 : 9.1 Go U3W Seagate  
  HD6 : 9.1 Go U2W Western Digital
 
  Comme vous pouvez le constater, même s'il n'est plus de première jeunesse, il s'agit d'une configuration serveur, et non d'une machine de gamer.
  La grande quantité de mémoire, l'architecture SMP et les disques interfacés en SCSI garantissent un environnement adapté pour une base de données.
 
 Configuration logicièle :
  Windows 2003 Server SP1
  Aucun autre produit mise à part FireFox et le service IIS, qui n'est pas solicité durant le test (site offline)
 
  A nouveau, la configuration est la même que celle d'un serveur dédié.
 
 Configuration des disques :
  C: (9 Go U2W NTFS) : Système (Windows + SWAP)
  D: (9 Go U2W NTFS) : Données systèmes (binaires de SQL Server et MySQL)
  E: (18 Go U3W NTFS) : Fichiers de données des SGBD
  F: (18 Go U3W NTFS) : Fichiers du journal des transactions des SGBD
  G: (18 Go U3W NTFS) : Fichiers d'index des SGBD
  H: (18 Go U3W NTFS) : Fichiers de IIS (non utilisés pour le bench)
 
Installation
------------
 
 N'ayant pas refait l'installation de SQL Server qui date de quelques mois, je ne vous détaille pas écran par écran la procédure.
 Installez n'importe quel produit Microsoft pour avoir un apperçu, on est guidé écran par écran.
 A noter que j'ai configuré SQL Server en mode sécurité élevé (droits explicites sur tous les objets, et mots de passe de plus de 50 caractères). Je ne sais pas si ça influe sur les résultats, je mentionne juste.
 
 Réutilisation du fichier "my-huge.ini" pour le "my.cnf" avec les modifications suivantes :
  # Try number of CPU's*2 for thread_concurrency
  thread_concurrency = 4
  (passé de 8 à 4 comme préconisé)
 Ajout de la ligne :
  datadir=e:/mysqldata
  (séparation des données du binaire d'un point de vue physique)
 Ajout de la ligne :
  innodb_log_group_home_dir=f:/mysqllog
  (séparation des logs du binaire et des données d'un point de vue physique)
 Ajout de la ligne :
  basedir=d:/mysql-4.1.13-win32
  Histoire de lui dire où il est
 
 A noter que je n'ai pas trouvé d'option permettant de séparer les index des données.
 
 Premier lancement :
 
  D:\mysql-4.1.13-win32\bin>mysqld-max-nt --console
  InnoDB: The first specified data file .\ibdata1 did not exist:
  InnoDB: a new database to be created!
  050726 21:45:29  InnoDB: Setting file .\ibdata1 size to 10 MB
  InnoDB: Database physically writes the file full: wait...
  050726 21:45:29  InnoDB: Log file f:\mysqllog\ib_logfile0 did not exist: new to be created
  InnoDB: Setting log file f:\mysqllog\ib_logfile0 size to 5 MB
  InnoDB: Database physically writes the file full: wait...
  050726 21:45:29  InnoDB: Log file f:\mysqllog\ib_logfile1 did not exist: new to be created
  InnoDB: Setting log file f:\mysqllog\ib_logfile1 size to 5 MB
  InnoDB: Database physically writes the file full: wait...
  InnoDB: Doublewrite buffer not found: creating new
  InnoDB: Doublewrite buffer created
  InnoDB: Creating foreign key constraint system tables
  InnoDB: Foreign key constraint system tables created
  050726 21:45:32  InnoDB: Started; log sequence number 0 0
  mysqld-max-nt: ready for connections.
  Version: '4.1.13-nt-max-log'  socket: ''  port: 3306  Official MySQL binary
 
 Ca semble correspondre à ce qui est dans la doc, j'en déduis que je ne me suis pas planté.
 
 Installation en tant que service :
 
  D:\mysql-4.1.13-win32\bin>mysqld-max-nt --install
  Service successfully installed.
 
 Premier test :
 
  D:\mysql-4.1.13-win32\bin>net start "mysql"
  Le service MySQL a démarré.
  D:\mysql-4.1.13-win32\bin>mysql
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 1 to server version: 4.1.13-nt-max-log
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
  mysql>
 
 Alimentation de la base MySQL à partir de SQL Server.
 => "Exporter des données", choix de ma base SQL Server, choix d'un DSN vers une base vide de MySQL, sélection de toutes les tables "suivant" et zou ! Roule ma poule.
 Après maintes tentatives et un destroyage total de ma base sql server, j'ai renoncé à copier les tables contenant des champs texte.
 En soit ce n'est pas très grave, ce n'était que des tables de référence.
 
 Après avoir installé un outils d'administration pour MySQL, j'ai passé les tables en InnoDB (par défaut MyIsam).
 
Avant le bench
--------------
 
 Copie des index de SQL Server sur MySQL.
 Une chose étonnante que j'ai pu constater avec MySQL, c'est que pour une table relativement volumineuse (10800 lignes), la création d'un index unique est... lente :
 
  mysql> CREATE UNIQUE INDEX PK_Terrain ON Terrain (idSolarSystem, idPlanet, xTerrain, yTerrain);
  Query OK, 10800 rows affected (1 min 46.47 sec)
  Records: 10800  Duplicates: 0  Warnings: 0
 
 Sous SQL Server, le même index fût créé en moins d'une dizaine de secondes.
 Ceci dit, cela ne doit pas intervenir dans les résultats du bench, étant donné que la création d'un index fait partie de la maintenance, et n'impacte donc en rien la rapidité d'une application utilisant la base.
 
Résultat du bench
-----------------
 
SQL Server :
 
 Démarrage du bench à 27/07/2005 00:50:05
 Création de 10 connections
 => 0,16
 Execution de 10 requêtes relativement simples sur chacune des 100 connections
 => 5,91
 Execution de 10 requêtes
 => 76,55
 Clôture des 100 connections
 => 0,02
 Durée totale du bench : 82,63
 Fin du bench à 27/07/2005 00:51:27
 
 
MySQL :
 
 Démarrage du bench à 27/07/2005 01:00:09
 Création de 10 connections
 => 0,09
 Execution de 10 requêtes relativement simples sur chacune des 100 connections
 => 0,95
 Execution de 10 requêtes
 => 837,8
 Clôture des 100 connections
 => 0,02
 Durée totale du bench : 838,86
 Fin du bench à 27/07/2005 01:14:08
 
IMPORTANT !
 Alors que la transaction a bien été prise en compte sous SQL Server, MySQL m'a laissé comme un goret les données mises à jour malgré le rollback !
 Le test est donc à refaire sans transaction (pour avoir testé "en live" lors de l'écriture de la requête, SQL Server aurait dû êre près de 10 fois plus rapide !
 
Remarques :
 
 Durant l'exécution du bench sous SQL Server, la mémoire occupée est resté à la valeur fixe de 681 Mo.
 Durant l'exécution du bench sous SQL Server, la part de CPU occupée par le noyaux s'élevait environ à 2/3% de charge CPU.
 Durant l'ecécution du bench sous SQL Server, le CPU occupé par le bench a toujours été le second, sans jamais de changement.
 Durant l'exécution du bench sous MySQL, la mémoire occupée est restée longtemps à 681 Mo avant de faire un légère pointe à 687 Mo (pis c'est resté à ce niveau jusqu'à la fin.
 Durant l'écécution du bench sous MySQL, la part de CPU occupée par le noyau s'élevait environ à 25%.
 Durant l'exécution du bench sous MySQL, à une ou deux reprises, la charge est passée du CPU2 au CPU1 puis est revenur sur le CPU2.
 Durant l'exécution des deux benchs, la charge CPU globale s'élevait exactement à 51% (1 processur alloué pour le bench, et 1 processur alloué pour le système).
 Durant l'exécution des deux benchs, aucune activité disque intensive n'était à noter.
 
Conclusions :
 
 MySQL est significativement plus rapide en ce qui concerne l'ouverture de connections. En effet, il est presque deux fois plus rapide.*
 En ce qui concerne les select "relativement simple", MySQL affiche encore un résultat assez extraordinaire. Cela mérite d'être creusé avec des requête vraiment complexes afin de voir si le moteur de SQL Server tire vraiment profit des cas complexes ou non.
 Pour ce qui est de la gestion des transactions, là, je sors un carton rouge à MySQL. Les tests sont complètement baisés à cause de ça. Les tables sont pourtant en InnoDB et j'ai bien explicité une transaction. En refaisant le test "à la main" directement dans l'analyseur de requêtes de MySQL, même constat, la transaction n'est pas effective !
 Je remets ça sur mon inexpérience de MySQL (c'est la première fois qu'il reste aussi longtemps sur un de mes PC). A creuser, je suis ouvert aux suggestions afin de corriger ce problème et refaire le test dans de bonne conditions.
 Par contre, là où SQL Server surpasse très largement (plus de 10 fois plus rapide, en comptant qu'il a géré la transaction !), c'est sur une requête de mise à jour relativement complexe.
 J'entends déjà certains s'écrier que c'est la recopie dans "terrain2" qui bouffe tout, et non. Lorsque je la fais à la main, la recopie dans la table dire 0.27 secondes, l'indexaction dure 0.17 secondes (c'est plus rapide de faire une PK qu'un index unique, allez comprendre pourquoi), et le drop 0.03 secondes. Donc même en ajoutant le tout et en multipliant par les 10 passages dans la bougle, on ne peut sourtraire que 5 secondes à ce résultat catastrophique.
 
*: A noter que l'absence de transaction pourrait se traduire aussi par une absence de cette couche dans l'objet ODBC. Sans compter les bugs que j'ai eu lors de la réplication, montrant que ce drivers ne supporte pas la norme correctement. On peut en déduire qu'il est bien plus léger que celui de SQL Server qui supporte toute la norme ODBC. Je ne cherche pas une fausse excuse, mais ceci me semble être une raison possible de cet écart de performance quand à la connection.
 
PS: si vous voulez trôler, ouver un autre topic dans lequel je veux bien participer, mais en attendant, je préfère laisser se topic propre en préférant une discussion constructive et une amélioration du test.


Message édité par Arjuna le 29-07-2005 à 00:42:04
mood
Publicité
Posté le 27-07-2005 à 01:34:16  profilanswer
 

n°1160982
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 01:35:13  profilanswer
 

Script de test :
 
Supprimé en attendant la version finale du nouveau bench


Message édité par Arjuna le 29-07-2005 à 00:42:58
n°1160983
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 01:35:42  profilanswer
 

Le fichier my.cnf
 

Code :
  1. # Example MySQL config file for very large systems.
  2. #
  3. # This is for a large system with memory of 1G-2G where the system runs mainly
  4. # MySQL.
  5. #
  6. # You can copy this file to
  7. # /etc/my.cnf to set global options,
  8. # mysql-data-dir/my.cnf to set server-specific options (in this
  9. # installation this directory is /usr/local/var) or
  10. # ~/.my.cnf to set user-specific options.
  11. #
  12. # In this file, you can use all long options that a program supports.
  13. # If you want to know which options a program supports, run the program
  14. # with the "--help" option.
  15. # The following options will be passed to all MySQL clients
  16. [client]
  17. #password = pass
  18. port  = 3306
  19. socket  = /tmp/mysql.sock
  20. # Here follows entries for some specific programs
  21. # The MySQL server
  22. [mysqld]
  23. port  = 3306
  24. socket  = /tmp/mysql.sock
  25. skip-locking
  26. key_buffer = 384M
  27. max_allowed_packet = 1M
  28. table_cache = 512
  29. sort_buffer_size = 2M
  30. read_buffer_size = 2M
  31. read_rnd_buffer_size = 8M
  32. myisam_sort_buffer_size = 64M
  33. thread_cache = 8
  34. query_cache_size = 32M
  35. # Try number of CPU's*2 for thread_concurrency
  36. thread_concurrency = 4
  37. basedir=d:/mysql-4.1.13-win32
  38. datadir=e:/mysqldata
  39. innodb_log_group_home_dir=f:/mysqllog
  40. # Don't listen on a TCP/IP port at all. This can be a security enhancement,
  41. # if all processes that need to connect to mysqld run on the same host.
  42. # All interaction with mysqld must be made via Unix sockets or named pipes.
  43. # Note that using this option without enabling named pipes on Windows
  44. # (via the "enable-named-pipe" option) will render mysqld useless!
  45. #
  46. #skip-networking
  47. # Replication Master Server (default)
  48. # binary logging is required for replication
  49. log-bin
  50. # required unique id between 1 and 2^32 - 1
  51. # defaults to 1 if master-host is not set
  52. # but will not function as a master if omitted
  53. server-id = 1
  54. # Replication Slave (comment out master section to use this)
  55. #
  56. # To configure this host as a replication slave, you can choose between
  57. # two methods :
  58. #
  59. # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
  60. #    the syntax is:
  61. #
  62. #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
  63. #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
  64. #
  65. #    where you replace <host>, <user>, <password> by quoted strings and
  66. #    <port> by the master's port number (3306 by default).
  67. #
  68. #    Example:
  69. #
  70. #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
  71. #    MASTER_USER='joe', MASTER_PASSWORD='secret';
  72. #
  73. # OR
  74. #
  75. # 2) Set the variables below. However, in case you choose this method, then
  76. #    start replication for the first time (even unsuccessfully, for example
  77. #    if you mistyped the password in master-password and the slave fails to
  78. #    connect), the slave will create a master.info file, and any later
  79. #    change in this file to the variables' values below will be ignored and
  80. #    overridden by the content of the master.info file, unless you shutdown
  81. #    the slave server, delete master.info and restart the slaver server.
  82. #    For that reason, you may want to leave the lines below untouched
  83. #    (commented) and instead use CHANGE MASTER TO (see above)
  84. #
  85. # required unique id between 2 and 2^32 - 1
  86. # (and different from the master)
  87. # defaults to 2 if master-host is set
  88. # but will not function as a slave if omitted
  89. #server-id       = 2
  90. #
  91. # The replication master for this slave - required
  92. #master-host     =   <hostname>
  93. #
  94. # The username the slave will use for authentication when connecting
  95. # to the master - required
  96. #master-user     =   <username>
  97. #
  98. # The password the slave will authenticate with when connecting to
  99. # the master - required
  100. #master-password =   <password>
  101. #
  102. # The port the master is listening on.
  103. # optional - defaults to 3306
  104. #master-port     =  <port>
  105. #
  106. # binary logging - not required for slaves, but recommended
  107. #log-bin
  108. # Point the following paths to different dedicated disks
  109. #tmpdir  = /tmp/
  110. #log-update  = /path-to-dedicated-directory/hostname
  111. # Uncomment the following if you are using BDB tables
  112. #bdb_cache_size = 384M
  113. #bdb_max_lock = 100000
  114. # Uncomment the following if you are using InnoDB tables
  115. #innodb_data_home_dir = /usr/local/var/
  116. #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
  117. #innodb_log_group_home_dir = /usr/local/var/
  118. #innodb_log_arch_dir = /usr/local/var/
  119. # You can set .._buffer_pool_size up to 50 - 80 %
  120. # of RAM but beware of setting memory usage too high
  121. #innodb_buffer_pool_size = 384M
  122. #innodb_additional_mem_pool_size = 20M
  123. # Set .._log_file_size to 25 % of buffer pool size
  124. #innodb_log_file_size = 100M
  125. #innodb_log_buffer_size = 8M
  126. #innodb_flush_log_at_trx_commit = 1
  127. #innodb_lock_wait_timeout = 50
  128. [mysqldump]
  129. quick
  130. max_allowed_packet = 16M
  131. [mysql]
  132. no-auto-rehash
  133. # Remove the next comment character if you are not familiar with SQL
  134. #safe-updates
  135. [isamchk]
  136. key_buffer = 256M
  137. sort_buffer_size = 256M
  138. read_buffer = 2M
  139. write_buffer = 2M
  140. [myisamchk]
  141. key_buffer = 256M
  142. sort_buffer_size = 256M
  143. read_buffer = 2M
  144. write_buffer = 2M
  145. [mysqlhotcopy]
  146. interactive-timeout


 
Je posterai demain dans la soirée un script de génération de la base et des données testées. Là je suis trop naze pour faire ça.


Message édité par Arjuna le 27-07-2005 à 01:39:29
n°1160986
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 01:51:54  profilanswer
 

Autre point avant de me coucher...
 
La requête SELECT exécutée une seule fois était plus rapide avec SQL Server qu'avec MySQL. Le résultat me semble donc assez étrange. Il semblerait que MySQL conserve en cache l'intégralité du jeu de requête, et n'a donc effectué qu'une seule fois la requête alors que SQL Server l'a réellement exécutée.
Le test pourrait donc être refait soit en trouvant un moyen de forcer MySQL à refaire la requête, soit en permettant à SQL Server de ne pas la refaire, histoire de les tester dans les mêmes conditions.

n°1161031
docmaboul
Posté le 27-07-2005 à 07:22:42  profilanswer
 

C'est tout à fait ça. Pour les conditions, à mon sens ça revient à obliger mysql à se battre avec une main attachée dans le dos mais pour désactiver son cache, c'est facile:
query_cache_size = 0
 
Pour ce qui est de la transaction, j'ai été lire vite fait le code du driver odbc, et c'est supporté (enfin, pas si on fait un rollback sur un ensemble de connexions mais ce n'est pas le cas ici). Pas le temps d'investiguer plus que ça.

n°1161046
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 08:26:12  profilanswer
 

De toute façon, en effet, le problème de la transaction se produit aussi lorsque je suis directement connecté à la base via l'outils "mysql".
J'en déduis que j'ai dû me planter quelquepart dans ma config.
 
PS: la raison vient peut-être du fait que lorsque j'ai tranféré les données, SQL Server a créé les tables dans MySQL avec le format MyIsam, qui est celui par défaut.
Ce n'est qu'une fois les tables remplies que j'ai changé le format. Peut-être faut-il faire une action supplémentaire.

n°1161047
cesarr89
Posté le 27-07-2005 à 08:28:17  profilanswer
 

SET AUTOCOMMIT = 0;  
Tu as fais ca avant ?

n°1161091
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 09:33:00  profilanswer
 

Je l'avais fait un coup en ligne de commande, et ça n'avait rien changé.
 
Au fait, lorsque j'ai fais mes transactions, il m'a toujours dit "1 warning", mais sans l'afficher, je fais comment pour l'afficher, ça me donnera certainement une piste pour résoudre ce problème.
 
Sinon, pour en revenir au problème de cache, je suis pas tout à fait d'accord avec ta réaction DocMaboul, par contre, je tiens a éviter de faire le teste en pénalisant volontairement MySQL.
Je vais voir si je peux trouver un paramètre permettant à SQL Server de se comporter comme MySQL plutôt, et si je ne trouve pas, je vais faire un bench qui sera bien plus long, mais aussi plus représentatif d'une base de données d'un gros SI.
 
Sur un ERP par exemple, alors qu'un bon millier de personnes travaillent dans la même base, un grand nombre font évidement les mêmes requêtes.
Cependant, vu l'étendue fonctionnel et la complexité des traîtements effectués par un ERP, on sait aussi qu'un même utilisateur, avant de refaire une deuxième fois la même requête, va en faire pas loin d'une vingtaine différentes.
Et sur le millier d'utilisateurs, seule une vingtaine à chaque fois vont faire des traîtements similaires, le SI gérant à la fois les tarifs, les commandes, les stocks, la compta, les CRM, etc., le nombre de requêtes différentes exécutées dans un laps de temps assez court est assez phénoménal. Difficile donc de conserver en cache toutes les requêtes, d'autant plus qu'il est rare que deux personnes demandent exactement la même chose. Je pense d'ailleurs que SQL Server étant principalement prévu pour ce type de base, c'est une raison pour laquelle il réeffectue les requêtes qu'il vient de faire : en production, ça n'arrive grossomodo jamais.
Hors, c'est justement ce cas que je veux tester, car c'est en comparant MySQL sur le terrain de SQL Server et Oracle, et non l'inverse, qu'on peut, selon moi, avoir une réponse claire quant à la capacité de MySQL à rivaliser avec ces deux derniers.
 
Donc je pense que ce soir, je vais tenter de me faire une autre base, plus représentative d'un ERP (si vous avez des script de créations de base/données d'une base type "catalogue produit, commande, stocks, compta, etc.", je suis totalement preneur, parceque ça va me prendre des heures à monter une base comme ça, et elle risque de ne pas être significative.
 
Sinon, je prochain bench sera plutôt orienté vers :
-> Au moins une dizaine de requêtes différentes, éxécutées dans un ordre aléatoire une série de fois, avec des paramètres différents à chaque fois.
-> Je vais aussi essayer de voir si je peux lancer plusieurs instances du même bench en //, histoire de simuler au moins 4 ou 5 connections concurrentes. En effet, tester un SGBD en environnement mono-utilisateur, c'est pas très représentatif...
 
Par contre, je tiens à faire des tests avec des transactions, ça me semble très important comme point à tester (notamment la façon de gérer les transactions concurrentes sur un même ensemble de données)

n°1162129
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 19:22:14  profilanswer
 

Je réitère ma demande : afin de faire un bench plus représentatif de la réalité, je cherche un exemple de base de données (si possible, scripts de création plus données) relativement volumineuse, et représentant une utilisation courrante :
- Catalogue / commandes
- Gestion de stocks
- CRM
- Etc.
 
Le summum serait une base avec un peu de tout, histoire par exemple de faire le lien entre une demande CRM et une commande, afin de consulter les réapprovisionnements des stocks des produits en relica (chose très courrante dans les centres d'appel, et qui demande souvent des requêtes relativement complexes.)

n°1162130
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 19:23:13  profilanswer
 

Je pourrais tenter d'en créer une via un script qui allimente la base de façon aléatoire, mais des données cohérentes c'est quand même mieu.

mood
Publicité
Posté le 27-07-2005 à 19:23:13  profilanswer
 

n°1162143
sircam
I Like Trains
Posté le 27-07-2005 à 19:38:15  profilanswer
 

Arjuna a écrit :

Je pourrais tenter d'en créer une via un script qui allimente la base de façon aléatoire, mais des données cohérentes c'est quand même mieu.


Ca risque d'être difficile : en général, les données réelles sont protégées.


---------------
Now Playing: {SYNTAX ERROR AT LINE 1210}
n°1162178
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 20:26:27  profilanswer
 

C'est sûr. Mais disons par exemple qu'avec SQL Server, il y plusieurs bases de test livrées en exemple, sur lesquelles s'appuient l'aide.
Seul hic, c'est qu'elle ne sont à la fois pas assez volumineuses (une base de bouquin, et une base de gestion RH), mais surtout, après le problèmes que j'ai eu hier avec la transformation des données de SQL Server vers MySQL, je préferarais partir d'un fichier SQL, ou d'une base MySQL. Dans ce sens ça marchera peut-être mieu.
 
Enfin bref, je suppose que Microsoft n'est pas le seul a fournir des données de test/formation avec leurs produits...

n°1162179
Arjuna
Aircraft Ident.: F-MBSD
Posté le 27-07-2005 à 20:27:25  profilanswer
 

Parceque sinon, j'ai bien accès à des bases de mes clients mais bon, s'en servir pour faire un bench, c'est mal, c'est tout protégé en effet.

n°1162197
sircam
I Like Trains
Posté le 27-07-2005 à 20:46:13  profilanswer
 

Arjuna a écrit :

Parceque sinon, j'ai bien accès à des bases de mes clients mais bon, s'en servir pour faire un bench, c'est mal, c'est tout protégé en effet.


Disons que ça limite fortement la publicité de tes résultats, ou à tout le moins leur reproduction par autrui.
 
Tu sais, quand un de mes collègues devait faire des benchs sur des datawarehouse, il se tapait des données bidons via des scripts. T'as pas toujours un tera de données sous la main.
 
Ca reste malgré tout plus précis quant à la quantité de données à injecter, même si c'est moins réaliste.
 
Tu peux aussi plus facilement paramétrer tes tests : des champs plus ou moins longs, avec plus ou moins de variance dans la longueur des données, plus ou moins de records...  
 
Il se peut que les perf s'inversent ou que les écarts se creusent en augmentant/diminuant un volume.
 
Bref, un snapshot de DB, pour "réaliste" que ce soit, c'est aussi fort limité.


---------------
Now Playing: {SYNTAX ERROR AT LINE 1210}
n°1162434
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 00:45:41  profilanswer
 

Bon, j'ai pas attendu cette réponse pour commencer ;)
 
Je suis en train d'allimenter ma base SQL Server avec des données bidons.
 
En fait, j'ai carrément décidé de réécrire un mini-erp, car ce qu'il faut bencher, ce n'est pas seulement le process de passage d'une commande, mais aussi tout ce qui est réapprovisionnement des stocks et tout ça, qui se font par batch la nuit, et par expérience, je sais qu'une nuit c'est court (la preuve, il est déjà presque 1h du matin, et j'ai toujours pas fini de débugger l'allimentation des tables...)

n°1162438
matthieu_p​hpmv
Posté le 28-07-2005 à 00:48:38  profilanswer
 

si tu codais pas en asp aussi, le debug irait plus vite... :-D
 
bon j'arrête de charier...  ;)


Message édité par matthieu_phpmv le 28-07-2005 à 00:49:01
n°1162447
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 00:55:40  profilanswer
 

T'ain, quand ça veut pas, ça veut pas :o
 
Pis là j'en suis vers la fin du script évidement... quand il faut régénérer des mégas de données avant d'y arriver :o

n°1162450
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 00:56:44  profilanswer
 

C'est pas de l'ASP, c'est du VBScript :o
 
Pis c'est tout ce que j'ai sous la main :spamafote:
 
En tout cas, je ne sais pas par quoi je suis limité, parceque le script prend seulement 25% des ressources quand il tourne, du coup il pourrait aller plus vite ce con :/

n°1162452
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 00:59:05  profilanswer
 

Voilà la liste des tables en attendant :
 

Code :
  1. address
  2. users
  3. city
  4. suppliement
  5. supplier
  6. vendor
  7. stock
  8. warehouse
  9. price
  10. vat
  11. currencyrate
  12. country
  13. currency
  14. product


 
Il reste la gestion des claims CRM, mais je ne sais pas si je vais les stocker. Je vais plutôt faire les traîtements "en live" (c'est à dire que c'est au moment des "batchs de nuit" que je vais décider si un client mécontent a appelé et si je dois envoyer un produit de substitution, ou le rembourser...)


Message édité par Arjuna le 28-07-2005 à 00:59:55
n°1162464
matthieu_p​hpmv
Posté le 28-07-2005 à 01:10:57  profilanswer
 

VBScript ????? des gens compétents bossent avec ce truc ???  
 
bon je vais pas troller dans chacun de mes posts mais la c'est gros ! :-D

n°1162467
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 01:16:22  profilanswer
 

Mon Dieu que c'est chiant... Ca a planté sur l'avant-dernière table :o

n°1162468
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 01:17:29  profilanswer
 

C'est bien plus puissant qu'il n'y paraît, et surtout, t'as besoin de rien d'autre que Notepad pour en faire. Vu que je suis sur un serveur, je vais pas m'amuser à installer Visual Studio ou autre connerie pour coder dans un autre langage...

n°1162471
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 01:22:31  profilanswer
 

T'ain, chuis fatigué moi... Ca fait trois fois que je corrige (mal) le même bug :o

n°1162478
matthieu_p​hpmv
Posté le 28-07-2005 à 01:57:28  profilanswer
 

tiens c'est marrant, le langage à la mode là, le php, et ben pareil : faut qu'un éditeur de texte ! :-)
 
sauf que là avec rien qu'un éditeur, tu as accès à une puissante phénoménale..
 
par exemple, je cherchais une fonction miracle, je pensais pas qu'elle était built-in, et ben si : http://fr.php.net/manual/en/functi [...] ursive.php
j'adore !  :love:


Message édité par matthieu_phpmv le 28-07-2005 à 01:57:58
n°1162479
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 02:02:02  profilanswer
 

un éditeur de texte plus un interpréteur... VBS, l'interpréteur est intégré à l'OS...
 
sinon, franchement, quitter le VBScript au profit du PHP, non merci, je ne vois pas trop l'intérêt...
 
Sinon, ça m'a l'air bien parti, j'ai plus de nom de champ foireux, plus d'ID doublé, plus de FK voilée, c'est la fête !
 
Sauf que là j'ai pas envie d'attendre la fin du script pour attendre de voir ce que fait la dernière table... là il est parti en tout cas, y'en a pour un moment maintenant !


Message édité par Arjuna le 29-07-2005 à 00:43:45
n°1162487
docmaboul
Posté le 28-07-2005 à 02:37:32  profilanswer
 

Arjuna a écrit :

Je réitère ma demande : afin de faire un bench plus représentatif de la réalité, je cherche un exemple de base de données (si possible, scripts de création plus données) relativement volumineuse, et représentant une utilisation courrante :
- Catalogue / commandes
- Gestion de stocks
- CRM
- Etc.
 
Le summum serait une base avec un peu de tout, histoire par exemple de faire le lien entre une demande CRM et une commande, afin de consulter les réapprovisionnements des stocks des produits en relica (chose très courrante dans les centres d'appel, et qui demande souvent des requêtes relativement complexes.)


 
en cherchant un peu sur www.tpc.org, vous devriez trouver votre bonheur. Typiquement, en lisant bien ce doc, vous trouverez tout ce qu'il vous faut pour faire péter votre serveur: http://www.tpc.org/results/FDR/TPC [...] 64_fdr.pdf :D (par contre, ils utilisent du C et du bulk-copy à ce niveau (pour bourrer une archi à 5M de dollars, faut bien ça [:ddr555])


Message édité par docmaboul le 28-07-2005 à 09:38:49
n°1162565
sircam
I Like Trains
Posté le 28-07-2005 à 09:28:53  profilanswer
 

Oh, au fait, quand tu lanceras tes billions de requêtes depuis ton client de tests, fais gaffe à ce que celui-ci ne constitue pas un bottleneck.
 
C'est pas évident à mesurer et à mettre en évidence et ça fausse sauvagement tes résultats.


---------------
Now Playing: {SYNTAX ERROR AT LINE 1210}
n°1162594
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 09:44:40  profilanswer
 

Ben vu que ce sera le même soft pour les deux benchs, au pire, ça permettra de mesurer aussi l'impact de la connection ODBC.
 
Mais sinon, je suis confiant : autant le VBS n'est pas un langage rapide, autant les requêtes seront très certainement plus lentes que le script, sans parler du fait que lent ou rapide, le VBS n'occupe jamais 100% du CPU, donc pour ce qui est de l'éxécution elle-même, elle n'impactera pas les temps de traîtement d'un SGBD à l'autre.

n°1162620
sircam
I Like Trains
Posté le 28-07-2005 à 09:56:17  profilanswer
 

Je suis un jour parti de ce genre de supposition... et ça c'est avéré faux.
 
Ce que je peux te conseiller, c'est d'avoir plusieurs postes clients pour commencer. Tu peux te fixer p.e. 8 requêtes simultanées sur le serveur. Compare :
 
- 4 postes x 2 requêtes (tous ensemble bien sûr) à
- 1 poste x 8 requêtes simultanées.
 
Ou 2x4... Ca peut te donner une idée. Dans le cas qui me revient, le bottleneck client apparaissait àpd 5 threads seulement.
 
En matière de benchmarks, les bottlenecks et les améliorations ne sont pas toujours là où on le bon sens nous mène.


---------------
Now Playing: {SYNTAX ERROR AT LINE 1210}
n°1162803
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 11:17:40  profilanswer
 

Je suis en train de pourrir ma machine au boulot en continuant a faire ça...
Je viens de finir le script de passage de commandes...
Ben ça ramme pas mal sur mon PC de merde :D
 
Pii@300 MHz / 128 Mo RAM / DD tout pourri / NT4 :D

n°1162811
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 11:26:08  profilanswer
 

sircam a écrit :

Je suis un jour parti de ce genre de supposition... et ça c'est avéré faux.
 
Ce que je peux te conseiller, c'est d'avoir plusieurs postes clients pour commencer. Tu peux te fixer p.e. 8 requêtes simultanées sur le serveur. Compare :
 
- 4 postes x 2 requêtes (tous ensemble bien sûr) à
- 1 poste x 8 requêtes simultanées.
 
Ou 2x4... Ca peut te donner une idée. Dans le cas qui me revient, le bottleneck client apparaissait àpd 5 threads seulement.
 
En matière de benchmarks, les bottlenecks et les améliorations ne sont pas toujours là où on le bon sens nous mène.


 
Le seul souci, c'est pour le test que je suis en train de mettre au point, je ne peux pas faire de requêtes concurrentes, ou sinon je vais être obligé de séparer mes benchs en "lots" étallés de le temps.
 
En fait, le principe est le suivant :
 
Pour un nombre "X" de jours, "Y" clients passent des commandes.
Ensuite, je fais les traîtements de vérification des stocks, et lance des commandes fournisseurs selon les dépôts aux endroits où les commandes on été passées.
A réception des colis, j'affecte les produits en stock aux commandes relicats.
Si passé un délais, aucun colis n'est encore arrivé, je procède à un mouvement inter-dépôts, en vérifiant que le coût n'est pas trop important, et que je ne met pas en pénurie le dépôt source.
Si une commande est en relicat depuis plus de "Z" jours, alors j'ai une chance sur deux que le client logue un appel.
Je dois lui annoncer un délais d'attente, qui va impacter sa décision "oui ok j'attends encore un peu" ou "nan vous faites chier".
Dans ce cas, je cherche un produit de remplacement en stock, et sinon, je procède à au remboursement.
Enfin, je calcule les primes des commerciaux basées sur les bénéfices moyens de chaque commande.
Et pour terminer, je mets à jour la balance en banque pour chaque dépôt.
 
En fait, on peut dire que je reproduis le fonctionnement général d'une chaîne de franchises de magasin (où le dépôt est le magasin), qui ne disposerait pas d'un stock central.
 
Y'a du boulot. En tout cas, quand j'aurai terminé, je pourrai me venté d'avoir fait le premier ERP "complet" totalement en VBScript :lol:


Message édité par Arjuna le 28-07-2005 à 11:42:25
n°1162815
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 11:29:22  profilanswer
 

Oui, pour en revenir aux requêtes concommitantes, le problème, c'est ma notion de temps (pour gérer les claims et les réapprovisionnement).
 
Ici, je gère un entier qui s'incrémente pour chaque jour.
 
Mais si je sérialise mes requêtes, ça ne marchera plus, et je vais me retrouver avec mon "batch de nuit" qui fait n'importe quoi.
 
Ou alors, il faudrait que le log la date, et que j'interprète chaque 5 minutes comme un jour, et que je schedule mon "batch de nuit" toutes les 5 minutes... Mais à ce moment, on n'aura plus de chiffres sur "le temps nécessaire", mais "le nombre de lignes mises à jour en X heures".

n°1162926
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 12:39:30  profilanswer
 

Bon, les jolies petites requêtes commencent :)
 
Calcul de la quantité des produits à commander par fournisseur :)
 

Code :
  1. select su.supplier_id, st.warehouse_id, el.product_id, -1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.status = 'V' and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply quantityPuchase
  2. from (select su.supplier_id, su.warehouse_id, su.product_id from suppliement su where su.delay = (select min(su2.delay) from suppliement su2 where su2.product_id = su.product_id and su2.warehouse_id = su.warehouse_id)) su, stock st, country co, city ci, address a, users u, event_line el, event e
  3. where e.status = 'V'
  4. and el.event_id = e.id
  5. and u.id = e.user_id
  6. and a.user_id = u.id
  7. and a.type = 'LIV'
  8. and ci.id = a.city
  9. and st.warehouse_id = ci.warehouse_id
  10. and st.product_id = el.product_id
  11. and su.product_id = st.product_id
  12. and su.warehouse_id = st.warehouse_id
  13. group by su.supplier_id, st.warehouse_id, el.product_id, st.reserved, st.stock, st.qtysupply


 
Je confirme donc, c'est bien le "batch de nuit" qui va se taper toutes les requêtes qui auront une véritable valeur pour les benchs :)

n°1163523
Arjuna
Aircraft Ident.: F-MBSD
Posté le 28-07-2005 à 16:37:10  profilanswer
 

Finalement, pour la gestion des stocks, ça sera :

Code :
  1. select su.supplier_id, st.warehouse_id, el.product_id, -1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.status = 'V' and e2.warehouse_id = st.warehouse_id and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply quantityPuchase, st.qtysupply
  2. from (select su.supplier_id, su.warehouse_id, su.product_id from suppliement su where su.delay = (select min(su2.delay) from suppliement su2 where su2.product_id = su.product_id and su2.warehouse_id = su.warehouse_id)) su, stock st, country co, city ci, address a, users u, event_line el, event e
  3. where e.status = 'V'
  4. and el.event_id = e.id
  5. and u.id = e.user_id
  6. and a.user_id = u.id
  7. and a.type = 'LIV'
  8. and ci.id = a.city
  9. and st.warehouse_id = ci.warehouse_id
  10. and st.product_id = el.product_id
  11. and su.product_id = st.product_id
  12. and su.warehouse_id = st.warehouse_id
  13. group by su.supplier_id, st.warehouse_id, el.product_id, st.reserved, st.stock, st.qtysupply
  14. having -1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.status = 'V' and e2.warehouse_id = st.warehouse_id and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply > 0
  15. order by su.supplier_id, st.warehouse_id


 
:pt1cable:
 
Heuresement que MySQL sait faire des sous-requêtes, parceque sinon c'était pas gagné :D


Message édité par Arjuna le 28-07-2005 à 16:38:24
n°1164007
matthieu_p​hpmv
Posté le 28-07-2005 à 22:12:06  profilanswer
 

tu ne fais pas tes jointures avec les clauses JOIN, etc. ? mais avec le classique WHERE a.id = b.id  
Est ce pour des raisons historiques, esthétiques, performances ?

n°1164165
Arjuna
Aircraft Ident.: F-MBSD
Posté le 29-07-2005 à 00:25:57  profilanswer
 

Pour plusieurs raisons en fait :)
- Pratique : je hais tout bonnement les jointures avec la clause "join", j'y panne jamais rien, je trouve ça illisible
- Esthétique : En plus d'être à mon goût illisible, en cas de jointure "non évidente", le fait d'isoler ce point le plus important de la requête dans la partie la moins importante (généralement, on regarde ce qu'on sélectionne en premier, puis les filtres, pensant que les jointures sont bonnes), on risque de ne pas comprendre la requête
- Logique : le fait de faire une jointure entre deux tables, c'est selon moins simplement filtrer une table selon les lignes d'une autre, c'est donc à mes yeux un filtre comme un autre, et doit être traîté comme tel
- Historique : Même si j'ai vu en cours pendant mes études les jointures avec des "join", j'ai toujours fait (et ce, depuis l'école) avec des "="
- Compatibilité : Tous les SGBD supportent cette syntaxe depuis toujours, alors que la syntaxe SQL92, avec les "join", n'est pas toujours supporté (sur les versions pas trop vieilles d'Oracle, ça ne marchait toujours pas)
 
Point de vue performance, les optimiseurs de toutes les bases savent interpréter les jointures dans la clause where de la même façon que si elles étaient explicites, ainsi, il n'y a pas de différence.
 
Je trouve notamment la syntaxe avec "join" dangereuse lorsque le critère de jointure n'est pas un "=" mais un "!=" ou ">=" par exemple, dans ce cas, c'est vraiment plus un filtre qu'une jointure, hors ça ne saute pas du tout aux yeux.


Message édité par Arjuna le 29-07-2005 à 00:28:48
n°1164169
Arjuna
Aircraft Ident.: F-MBSD
Posté le 29-07-2005 à 00:33:36  profilanswer
 

Sinon, ça y est, le script du bench est presque terminé.
 
Il me reste à rajouter une fonctionnalité que j'ai complètement zappé (c'est ça de couvrir un périmètre fonctionnel trop grand d'un coup :D), et débugger.
Je vais commencer par ce dernier point d'ailleurs :)
 
PS: sinon, mise à part demain où je devrais avoir un peu de temps dans la journée, je ne pourrai re-travailler dessus qu'à partir de lundi soir. Par conséquent, pensez-bien à faire remonter le topic histoire que je me motive pour terminer :)
 
Je colle ici l'intégralité de mon travail, histoire de pouvoir continuer à bosser demain.
Dans le script "bench.vbs", je vous invite à rechercher les requêtes contenant des jointures externes à la SQL Server "=*" et me proposer leur réécriture avec des jointures de type "join".
En effet, cette syntaxe n'est pas supportée par MySQL, donc ça va pas aller pour faire le bench :D
Il ne faut pas se contenter de faire seulement ces jointures avec cette syntaxe, mais réécrire toute la requête avec des "join". En effet, SQL Server doit utiliser deux parseurs/optimiseurs différents selon les syntaxes, et par conséquent on ne peux pas mélanger les deux types de jointures.

n°1164173
Arjuna
Aircraft Ident.: F-MBSD
Posté le 29-07-2005 à 00:37:15  profilanswer
 

Script de la base de données :

Code :
  1. CREATE DATABASE [bench]  ON (NAME = N'bench_Data', FILENAME = N'E:\MagicLand\bench_Data.MDF' , SIZE = 1000, FILEGROWTH = 10%) LOG ON (NAME = N'bench_Log', FILENAME = N'F:\MagicLand\bench_Log.LDF' , SIZE = 176, FILEGROWTH = 10%)
  2. COLLATE French_CI_AS
  3. GO
  4. ALTER DATABASE [bench] ADD FILEGROUP [SECONDARY]
  5. GO
  6. ALTER DATABASE [bench] ADD FILE(NAME = N'bench_index', FILENAME = N'G:\MagicLand\bench_index_Data.NDF' , SIZE = 1000, FILEGROWTH = 10%) TO FILEGROUP [SECONDARY]
  7. GO
  8. use [bench]
  9. GO
  10. CREATE TABLE [dbo].[address] (
  11. [user_id] [numeric](18, 0) NOT NULL ,
  12. [type] [char] (3) COLLATE French_CI_AS NOT NULL ,
  13. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  14. [street] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  15. [city] [numeric](18, 0) NOT NULL
  16. ) ON [PRIMARY]
  17. GO
  18. CREATE TABLE [dbo].[award] (
  19. [total_order] [decimal](18, 0) NOT NULL ,
  20. [rate] [decimal](18, 0) NOT NULL
  21. ) ON [PRIMARY]
  22. GO
  23. CREATE TABLE [dbo].[bank_account] (
  24. [warehouse_id] [numeric](18, 0) NOT NULL ,
  25. [bank_amount] [numeric](18, 0) NOT NULL
  26. ) ON [PRIMARY]
  27. GO
  28. CREATE TABLE [dbo].[city] (
  29. [id] [numeric](18, 0) NOT NULL ,
  30. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  31. [country_id] [numeric](18, 0) NOT NULL ,
  32. [zipcode] [nvarchar] (50) COLLATE French_CI_AS NOT NULL ,
  33. [warehouse_id] [numeric](18, 0) NOT NULL
  34. ) ON [PRIMARY]
  35. GO
  36. CREATE TABLE [dbo].[country] (
  37. [id] [numeric](18, 0) NOT NULL ,
  38. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  39. [currency_id] [numeric](18, 0) NOT NULL
  40. ) ON [PRIMARY]
  41. GO
  42. CREATE TABLE [dbo].[currency] (
  43. [id] [numeric](18, 0) NOT NULL ,
  44. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL
  45. ) ON [PRIMARY]
  46. GO
  47. CREATE TABLE [dbo].[currencyrate] (
  48. [currency1_id] [numeric](18, 0) NOT NULL ,
  49. [currency2_id] [numeric](18, 0) NOT NULL ,
  50. [rate] [decimal](18, 0) NOT NULL
  51. ) ON [PRIMARY]
  52. GO
  53. CREATE TABLE [dbo].[event] (
  54. [id] [numeric](18, 0) NOT NULL ,
  55. [user_id] [numeric](18, 0) NULL ,
  56. [type] [char] (3) COLLATE French_CI_AS NOT NULL ,
  57. [status] [char] (1) COLLATE French_CI_AS NOT NULL ,
  58. [vendor_id] [numeric](18, 0) NULL ,
  59. [warehouse_id] [numeric](18, 0) NULL ,
  60. [supplier_id] [numeric](18, 0) NULL ,
  61. [date_event] [int] NOT NULL
  62. ) ON [PRIMARY]
  63. GO
  64. CREATE TABLE [dbo].[event_line] (
  65. [event_id] [numeric](18, 0) NOT NULL ,
  66. [id] [numeric](18, 0) NOT NULL ,
  67. [product_id] [numeric](18, 0) NOT NULL ,
  68. [quantity] [numeric](18, 0) NOT NULL ,
  69. [date_event] [int] NOT NULL ,
  70. [status] [char] (1) COLLATE French_CI_AS NOT NULL
  71. ) ON [PRIMARY]
  72. GO
  73. CREATE TABLE [dbo].[internalmove] (
  74. [warehouse1_id] [numeric](18, 0) NOT NULL ,
  75. [warehouse2_id] [numeric](18, 0) NOT NULL ,
  76. [weight] [numeric](18, 0) NOT NULL ,
  77. [price] [decimal](18, 0) NOT NULL
  78. ) ON [PRIMARY]
  79. GO
  80. CREATE TABLE [dbo].[price] (
  81. [country_id] [numeric](18, 0) NOT NULL ,
  82. [product_id] [numeric](18, 0) NOT NULL ,
  83. [amount] [decimal](18, 0) NOT NULL
  84. ) ON [PRIMARY]
  85. GO
  86. CREATE TABLE [dbo].[product] (
  87. [id] [numeric](18, 0) NOT NULL ,
  88. [code] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  89. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  90. [weight] [numeric](18, 0) NOT NULL ,
  91. [active] [char] (1) COLLATE French_CI_AS NOT NULL
  92. ) ON [PRIMARY]
  93. GO
  94. CREATE TABLE [dbo].[productreplacement] (
  95. [product1_id] [numeric](18, 0) NOT NULL ,
  96. [product2_id] [numeric](18, 0) NOT NULL
  97. ) ON [PRIMARY]
  98. GO
  99. CREATE TABLE [dbo].[stock] (
  100. [warehouse_id] [numeric](18, 0) NOT NULL ,
  101. [product_id] [numeric](18, 0) NOT NULL ,
  102. [stock] [numeric](18, 0) NOT NULL ,
  103. [reserved] [numeric](18, 0) NOT NULL ,
  104. [qtysupply] [numeric](18, 0) NOT NULL
  105. ) ON [PRIMARY]
  106. GO
  107. CREATE TABLE [dbo].[suppliement] (
  108. [product_id] [numeric](18, 0) NOT NULL ,
  109. [supplier_id] [numeric](18, 0) NOT NULL ,
  110. [warehouse_id] [numeric](18, 0) NOT NULL ,
  111. [price] [decimal](18, 0) NOT NULL ,
  112. [delay] [numeric](18, 0) NOT NULL
  113. ) ON [PRIMARY]
  114. GO
  115. CREATE TABLE [dbo].[supplier] (
  116. [id] [numeric](18, 0) NOT NULL ,
  117. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  118. [warehouse_id] [numeric](18, 0) NOT NULL
  119. ) ON [PRIMARY]
  120. GO
  121. CREATE TABLE [dbo].[users] (
  122. [id] [numeric](18, 0) NOT NULL ,
  123. [vat_type] [numeric](18, 0) NOT NULL ,
  124. [firstname] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  125. [lastname] [varchar] (50) COLLATE French_CI_AS NOT NULL
  126. ) ON [PRIMARY]
  127. GO
  128. CREATE TABLE [dbo].[vat] (
  129. [country_id] [numeric](18, 0) NOT NULL ,
  130. [vat_id] [numeric](18, 0) NOT NULL ,
  131. [rate] [decimal](18, 0) NOT NULL
  132. ) ON [PRIMARY]
  133. GO
  134. CREATE TABLE [dbo].[vendor] (
  135. [id] [numeric](18, 0) NOT NULL ,
  136. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  137. [warehouse_id] [numeric](18, 0) NOT NULL
  138. ) ON [PRIMARY]
  139. GO
  140. CREATE TABLE [dbo].[vendor_award] (
  141. [vendor_id] [numeric](18, 0) NOT NULL ,
  142. [award_date] [int] NOT NULL ,
  143. [amount] [int] NOT NULL
  144. ) ON [PRIMARY]
  145. GO
  146. CREATE TABLE [dbo].[warehouse] (
  147. [id] [numeric](18, 0) NOT NULL ,
  148. [country_id] [numeric](18, 0) NOT NULL
  149. ) ON [PRIMARY]
  150. GO
  151. ALTER TABLE [dbo].[address] WITH NOCHECK ADD
  152. CONSTRAINT [PK_address] PRIMARY KEY  CLUSTERED
  153. (
  154.  [user_id],
  155.  [type]
  156. )  ON [PRIMARY]
  157. GO
  158. ALTER TABLE [dbo].[award] WITH NOCHECK ADD
  159. CONSTRAINT [PK_award] PRIMARY KEY  CLUSTERED
  160. (
  161.  [total_order]
  162. )  ON [PRIMARY]
  163. GO
  164. ALTER TABLE [dbo].[bank_account] WITH NOCHECK ADD
  165. CONSTRAINT [PK_bank_account] PRIMARY KEY  CLUSTERED
  166. (
  167.  [warehouse_id]
  168. )  ON [PRIMARY]
  169. GO
  170. ALTER TABLE [dbo].[city] WITH NOCHECK ADD
  171. CONSTRAINT [PK_city] PRIMARY KEY  CLUSTERED
  172. (
  173.  [id]
  174. )  ON [PRIMARY]
  175. GO
  176. ALTER TABLE [dbo].[country] WITH NOCHECK ADD
  177. CONSTRAINT [PK_country] PRIMARY KEY  CLUSTERED
  178. (
  179.  [id]
  180. )  ON [PRIMARY]
  181. GO
  182. ALTER TABLE [dbo].[currency] WITH NOCHECK ADD
  183. CONSTRAINT [PK_currency] PRIMARY KEY  CLUSTERED
  184. (
  185.  [id]
  186. )  ON [PRIMARY]
  187. GO
  188. ALTER TABLE [dbo].[currencyrate] WITH NOCHECK ADD
  189. CONSTRAINT [PK_currencyrate] PRIMARY KEY  CLUSTERED
  190. (
  191.  [currency1_id],
  192.  [currency2_id]
  193. )  ON [PRIMARY]
  194. GO
  195. ALTER TABLE [dbo].[event] WITH NOCHECK ADD
  196. CONSTRAINT [PK_event] PRIMARY KEY  CLUSTERED
  197. (
  198.  [id]
  199. )  ON [PRIMARY]
  200. GO
  201. ALTER TABLE [dbo].[event_line] WITH NOCHECK ADD
  202. CONSTRAINT [PK_event_line] PRIMARY KEY  CLUSTERED
  203. (
  204.  [event_id],
  205.  [id]
  206. )  ON [PRIMARY]
  207. GO
  208. ALTER TABLE [dbo].[internalmove] WITH NOCHECK ADD
  209. CONSTRAINT [PK_internalmove] PRIMARY KEY  CLUSTERED
  210. (
  211.  [warehouse1_id],
  212.  [warehouse2_id],
  213.  [weight]
  214. )  ON [PRIMARY]
  215. GO
  216. ALTER TABLE [dbo].[price] WITH NOCHECK ADD
  217. CONSTRAINT [PK_price] PRIMARY KEY  CLUSTERED
  218. (
  219.  [country_id],
  220.  [product_id]
  221. )  ON [PRIMARY]
  222. GO
  223. ALTER TABLE [dbo].[product] WITH NOCHECK ADD
  224. CONSTRAINT [PK_product] PRIMARY KEY  CLUSTERED
  225. (
  226.  [id]
  227. )  ON [PRIMARY]
  228. GO
  229. ALTER TABLE [dbo].[productreplacement] WITH NOCHECK ADD
  230. CONSTRAINT [PK_productreplacement] PRIMARY KEY  CLUSTERED
  231. (
  232.  [product1_id]
  233. )  ON [PRIMARY]
  234. GO
  235. ALTER TABLE [dbo].[stock] WITH NOCHECK ADD
  236. CONSTRAINT [PK_stock] PRIMARY KEY  CLUSTERED
  237. (
  238.  [warehouse_id],
  239.  [product_id]
  240. )  ON [PRIMARY]
  241. GO
  242. ALTER TABLE [dbo].[suppliement] WITH NOCHECK ADD
  243. CONSTRAINT [PK_suppliement] PRIMARY KEY  CLUSTERED
  244. (
  245.  [product_id],
  246.  [supplier_id]
  247. )  ON [PRIMARY]
  248. GO
  249. ALTER TABLE [dbo].[supplier] WITH NOCHECK ADD
  250. CONSTRAINT [PK_supplier] PRIMARY KEY  CLUSTERED
  251. (
  252.  [id]
  253. )  ON [PRIMARY]
  254. GO
  255. ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  256. CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED
  257. (
  258.  [id]
  259. )  ON [PRIMARY]
  260. GO
  261. ALTER TABLE [dbo].[vat] WITH NOCHECK ADD
  262. CONSTRAINT [PK_vat] PRIMARY KEY  CLUSTERED
  263. (
  264.  [vat_id],
  265.  [country_id]
  266. )  ON [PRIMARY]
  267. GO
  268. ALTER TABLE [dbo].[vendor] WITH NOCHECK ADD
  269. CONSTRAINT [PK_vendor] PRIMARY KEY  CLUSTERED
  270. (
  271.  [id]
  272. )  ON [PRIMARY]
  273. GO
  274. ALTER TABLE [dbo].[vendor_award] WITH NOCHECK ADD
  275. CONSTRAINT [PK_vendor_award] PRIMARY KEY  CLUSTERED
  276. (
  277.  [vendor_id],
  278.  [award_date]
  279. )  ON [PRIMARY]
  280. GO
  281. ALTER TABLE [dbo].[warehouse] WITH NOCHECK ADD
  282. CONSTRAINT [PK_warehouse] PRIMARY KEY  CLUSTERED
  283. (
  284.  [id]
  285. )  ON [PRIMARY]
  286. GO
  287. CREATE  INDEX [ix_address_user] ON [dbo].[address]([user_id], [type]) ON [SECONDARY]
  288. GO
  289. CREATE  INDEX [ix_event_type_status_user] ON [dbo].[event]([type], [status], [user_id] DESC ) ON [SECONDARY]
  290. GO
  291. CREATE  INDEX [ix_event_type_status_vendor] ON [dbo].[event]([type], [status], [vendor_id] DESC ) ON [SECONDARY]
  292. GO
  293. CREATE  INDEX [ix_event_type_status_warehouse] ON [dbo].[event]([type], [status], [warehouse_id] DESC ) ON [SECONDARY]
  294. GO
  295. CREATE  INDEX [ix_event_type_status_supplier] ON [dbo].[event]([type], [status], [supplier_id] DESC ) ON [SECONDARY]
  296. GO
  297. CREATE  INDEX [ix_event_date_status] ON [dbo].[event]([date_event] DESC , [status]) ON [SECONDARY]
  298. GO
  299. CREATE  INDEX [ix_eventline_event_status] ON [dbo].[event_line]([event_id] DESC , [status]) ON [SECONDARY]
  300. GO
  301. CREATE  INDEX [ix_product_active] ON [dbo].[product]([active]) ON [SECONDARY]
  302. GO
  303. CREATE  INDEX [ix_suppliement_delay] ON [dbo].[suppliement]([delay]) ON [SECONDARY]
  304. GO
  305. CREATE  INDEX [ix_suppliement_supply] ON [dbo].[suppliement]([warehouse_id], [product_id]) ON [SECONDARY]
  306. GO
  307. CREATE  INDEX [ix_supplier_warehouse] ON [dbo].[supplier]([warehouse_id]) ON [SECONDARY]
  308. GO
  309. ALTER TABLE [dbo].[address] ADD
  310. CONSTRAINT [FK_address_city] FOREIGN KEY
  311. (
  312.  [city]
  313. ) REFERENCES [dbo].[city] (
  314.  [id]
  315. ),
  316. CONSTRAINT [FK_address_users] FOREIGN KEY
  317. (
  318.  [user_id]
  319. ) REFERENCES [dbo].[users] (
  320.  [id]
  321. )
  322. GO
  323. ALTER TABLE [dbo].[bank_account] ADD
  324. CONSTRAINT [FK_bank_account_warehouse] FOREIGN KEY
  325. (
  326.  [warehouse_id]
  327. ) REFERENCES [dbo].[warehouse] (
  328.  [id]
  329. )
  330. GO
  331. ALTER TABLE [dbo].[city] ADD
  332. CONSTRAINT [FK_city_country] FOREIGN KEY
  333. (
  334.  [country_id]
  335. ) REFERENCES [dbo].[country] (
  336.  [id]
  337. )
  338. GO
  339. ALTER TABLE [dbo].[country] ADD
  340. CONSTRAINT [FK_country_currency] FOREIGN KEY
  341. (
  342.  [currency_id]
  343. ) REFERENCES [dbo].[currency] (
  344.  [id]
  345. )
  346. GO
  347. ALTER TABLE [dbo].[currencyrate] ADD
  348. CONSTRAINT [FK_currencyrate_currency] FOREIGN KEY
  349. (
  350.  [currency1_id]
  351. ) REFERENCES [dbo].[currency] (
  352.  [id]
  353. ),
  354. CONSTRAINT [FK_currencyrate_currency1] FOREIGN KEY
  355. (
  356.  [currency2_id]
  357. ) REFERENCES [dbo].[currency] (
  358.  [id]
  359. )
  360. GO
  361. ALTER TABLE [dbo].[event] ADD
  362. CONSTRAINT [FK_event_supplier] FOREIGN KEY
  363. (
  364.  [supplier_id]
  365. ) REFERENCES [dbo].[supplier] (
  366.  [id]
  367. ),
  368. CONSTRAINT [FK_event_users] FOREIGN KEY
  369. (
  370.  [user_id]
  371. ) REFERENCES [dbo].[users] (
  372.  [id]
  373. ),
  374. CONSTRAINT [FK_event_vendor] FOREIGN KEY
  375. (
  376.  [vendor_id]
  377. ) REFERENCES [dbo].[vendor] (
  378.  [id]
  379. ),
  380. CONSTRAINT [FK_event_warehouse] FOREIGN KEY
  381. (
  382.  [warehouse_id]
  383. ) REFERENCES [dbo].[warehouse] (
  384.  [id]
  385. )
  386. GO
  387. ALTER TABLE [dbo].[event_line] ADD
  388. CONSTRAINT [FK_event_line_event] FOREIGN KEY
  389. (
  390.  [event_id]
  391. ) REFERENCES [dbo].[event] (
  392.  [id]
  393. ),
  394. CONSTRAINT [FK_event_line_product] FOREIGN KEY
  395. (
  396.  [product_id]
  397. ) REFERENCES [dbo].[product] (
  398.  [id]
  399. )
  400. GO
  401. ALTER TABLE [dbo].[internalmove] ADD
  402. CONSTRAINT [FK_internalmove_warehouse] FOREIGN KEY
  403. (
  404.  [warehouse1_id]
  405. ) REFERENCES [dbo].[warehouse] (
  406.  [id]
  407. ),
  408. CONSTRAINT [FK_internalmove_warehouse1] FOREIGN KEY
  409. (
  410.  [warehouse2_id]
  411. ) REFERENCES [dbo].[warehouse] (
  412.  [id]
  413. )
  414. GO
  415. ALTER TABLE [dbo].[price] ADD
  416. CONSTRAINT [FK_price_country] FOREIGN KEY
  417. (
  418.  [country_id]
  419. ) REFERENCES [dbo].[country] (
  420.  [id]
  421. ),
  422. CONSTRAINT [FK_price_product] FOREIGN KEY
  423. (
  424.  [product_id]
  425. ) REFERENCES [dbo].[product] (
  426.  [id]
  427. )
  428. GO
  429. ALTER TABLE [dbo].[productreplacement] ADD
  430. CONSTRAINT [FK_productreplacement_product] FOREIGN KEY
  431. (
  432.  [product1_id]
  433. ) REFERENCES [dbo].[product] (
  434.  [id]
  435. ),
  436. CONSTRAINT [FK_productreplacement_product2] FOREIGN KEY
  437. (
  438.  [product2_id]
  439. ) REFERENCES [dbo].[product] (
  440.  [id]
  441. )
  442. GO
  443. ALTER TABLE [dbo].[stock] ADD
  444. CONSTRAINT [FK_stock_product] FOREIGN KEY
  445. (
  446.  [product_id]
  447. ) REFERENCES [dbo].[product] (
  448.  [id]
  449. ),
  450. CONSTRAINT [FK_stock_warehouse] FOREIGN KEY
  451. (
  452.  [warehouse_id]
  453. ) REFERENCES [dbo].[warehouse] (
  454.  [id]
  455. )
  456. GO
  457. ALTER TABLE [dbo].[suppliement] ADD
  458. CONSTRAINT [FK_suppliement_product] FOREIGN KEY
  459. (
  460.  [product_id]
  461. ) REFERENCES [dbo].[product] (
  462.  [id]
  463. ),
  464. CONSTRAINT [FK_suppliement_supplier] FOREIGN KEY
  465. (
  466.  [supplier_id]
  467. ) REFERENCES [dbo].[supplier] (
  468.  [id]
  469. ),
  470. CONSTRAINT [FK_suppliement_warehouse] FOREIGN KEY
  471. (
  472.  [warehouse_id]
  473. ) REFERENCES [dbo].[warehouse] (
  474.  [id]
  475. )
  476. GO
  477. ALTER TABLE [dbo].[vat] ADD
  478. CONSTRAINT [FK_vat_country1] FOREIGN KEY
  479. (
  480.  [country_id]
  481. ) REFERENCES [dbo].[country] (
  482.  [id]
  483. )
  484. GO
  485. ALTER TABLE [dbo].[vendor] ADD
  486. CONSTRAINT [FK_vendor_warehouse] FOREIGN KEY
  487. (
  488.  [warehouse_id]
  489. ) REFERENCES [dbo].[warehouse] (
  490.  [id]
  491. )
  492. GO
  493. ALTER TABLE [dbo].[warehouse] ADD
  494. CONSTRAINT [FK_warehouse_country] FOREIGN KEY
  495. (
  496.  [country_id]
  497. ) REFERENCES [dbo].[country] (
  498.  [id]
  499. )
  500. GO


 
Script d'initialisation des données de la base :

Code :
  1. Option Explicit
  2. Dim cnxString
  3. Dim nbProducts
  4. Dim nbCountries
  5. Dim nbWarehousePerCountry
  6. Dim nbCitiesPerWarehouse
  7. Dim nbSuppliersPerWarehouse
  8. Dim nbCustomersPerCity
  9. Dim nbVendorPerWarehouse
  10. Dim nbVatPerCountry
  11. cnxString = "dsn=SQLBench"
  12. nbProducts = 10000
  13. nbCountries = 10
  14. nbWarehousePerCountry = 5
  15. nbCitiesPerWareHouse = 10
  16. nbSuppliersPerWarehouse = 3
  17. nbCustomersPerCity = 1000
  18. nbVendorPerWarehouse = 3
  19. nbVatPerCountry = 2
  20. Randomize
  21. Function CreateLog(name)
  22. Dim fso, fil
  23. Set fso = CreateObject("Scripting.FileSystemObject" )
  24. Set fil = fso.CreateTextFile(name)
  25. Set CreateLog = fil
  26. End Function
  27. Sub LogEntry(fil, str)
  28. fil.WriteLine str
  29. End Sub
  30. Sub CloseLog(fil)
  31. fil.Close
  32. Set fil = Nothing
  33. End Sub
  34. Dim logFile
  35. Set logFile = CreateLog("c:\create_database.log" )
  36. Call LogEntry(logFile, "Start on " & CStr(Now()))
  37. Dim cnx
  38. Set cnx = CreateObject("ADODB.Connection" )
  39. cnx.ConnectionTimeOut = 0
  40. cnx.CommandTimeOut = 0
  41. cnx.ConnectionString = cnxString
  42. cnx.Open
  43. Dim i, j, k, l
  44. Dim productWeight
  45. Dim productActive
  46. For i = 1 to nbProducts
  47. productWeight = Int(Rnd(10) * 20)
  48. If Rnd(10) > .1 Then productActive = "A" Else productActive = "I"
  49. cnx.Execute "insert into product (id, code, name, weight, active) values (" & CStr(i) & ", 'PCODE" & CStr(i) & "', 'Product " & CStr(i) & "', " & productWeight & ", '" & productActive & "')"
  50. Next
  51. For i = 1 to Int(nbProducts / 2)
  52. If Rnd(10) > .1 Then productActive = "A" Else productActive = "I"
  53. cnx.Execute "insert into productreplacement (product1_id, product2_id) values (" & CStr(i) & ", " & CStr(i + Int(nbProducts / 2)) & " )"
  54. Next
  55. Dim currencyRate
  56. Dim productPurchasePrice
  57. Dim productPurchaseDelay
  58. Dim productPrice
  59. Dim vatRate
  60. Dim vatType
  61. For i = 1 to nbCountries
  62. If i = 1 Then currencyRate = 1 Else currencyRate = Rnd(10)
  63. cnx.Execute "insert into currency (id, name) values (" & CStr(i) & ", 'Currency " & CStr(i) & "')"
  64. cnx.Execute "insert into country (id, name, currency_id) values (" & CStr(i) & ", 'Country " & CStr(i) & "', " & CStr(i) & " )"
  65. cnx.Execute "insert into currencyrate (currency1_id, currency2_id, rate) values (" & CStr(i) & ", 1, " & CStr(currencyRate) & " )"
  66. For j = 1 to nbVatPerCountry
  67.  vatRate = Round(Rnd(9) / 4, 2)
  68.  cnx.Execute "insert into vat (country_id, vat_id, rate) values (" & CStr(i) & ", " & CStr(j) & ", " & vatRate & " )"
  69. Next
  70. For j = 1 to nbProducts
  71.  productPrice = Round(Rnd(9) * 300, 2) + 330
  72.  cnx.Execute "insert into price (country_id, product_id, amount) values (" & CStr(i) & ", " & CStr(j) & ", " & productPrice & " )"
  73. Next
  74. For j = 1 to nbWarehousePerCountry
  75.  cnx.Execute "insert into warehouse (id, country_id) values (" & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", " & CStr(i) & " )"
  76.  cnx.Execute "insert into bank_account (warehouse_id, bank_amount) values (" & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", 0)"
  77.  For k = 1 to nbProducts
  78.   cnx.Execute "insert into stock (product_id, warehouse_id, stock, reserved, qtysupply) values (" & CStr(k) & ", " & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", 0, 0, 0)"
  79.  Next
  80.  For k = 1 to nbVendorPerWarehouse
  81.   cnx.Execute "insert into vendor (id, name, warehouse_id) values (" & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbVendorPerWarehouse) + k) & ", 'Vendor " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbVendorPerWarehouse) + k) & "', " & CStr(((i - 1) * nbWarehousePerCountry) + j) & " )"
  82.  Next
  83.  For k = 1 to nbSuppliersPerWarehouse
  84.   cnx.Execute "insert into supplier (id, name, warehouse_id) values (" & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbSuppliersPerWarehouse) + k) & ", 'Supplier " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbSuppliersPerWarehouse) + k) & "', " & CStr(((i - 1) * nbWarehousePerCountry) + j) & " )"
  85.   For l = 1 to nbProducts
  86.    productPurchasePrice = Round(Rnd(9) * 300, 2)
  87.    productPurchaseDelay = Int(Rnd(9) * 10) + 1
  88.    cnx.Execute "insert into suppliement (product_id, supplier_id, warehouse_id, price, delay) values (" & CStr(l) & ", " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbSuppliersPerWarehouse) + k) & ", " & CStr(((i - 1) * nbWarehousePerCountry) + j) & ", " & productPurchasePrice & ", " & productPurchaseDelay & " )"
  89.   Next
  90.  Next
  91.  For k = 1 to nbCitiesPerWareHouse
  92.   cnx.Execute "insert into city (id, name, country_id, zipcode, warehouse_id) values (" & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & ", 'City " & CStr(k) & "', " & CStr(i) & ", 'Zip " & CStr(k) & "', " & CStr(((i - 1) * nbWarehousePerCountry) + j) & " )"
  93.   For l = 1 to nbCustomersPerCity
  94.    vatType = Int(Rnd(9) * nbVatPerCountry) + 1
  95.    cnx.Execute "insert into users (id, firstname, lastname, vat_type) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'Mister', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', " & CStr(vatType) & " )"
  96.    cnx.Execute "insert into address (user_id, type, name, street, city) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'COM', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', 'Street', " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & " )"
  97.    cnx.Execute "insert into address (user_id, type, name, street, city) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'LIV', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', 'Street', " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & " )"
  98.    cnx.Execute "insert into address (user_id, type, name, street, city) values (" & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & ", 'FAC', 'Customer " & CStr(((((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) - 1) * nbCustomersPerCity) + l) & "', 'Street', " & CStr((((((i - 1) * nbWarehousePerCountry) + j) - 1) * nbCitiesPerWarehouse) + k) & " )"
  99.   Next
  100.  Next
  101. Next
  102. Next
  103. Dim rate1, rate2, rate3
  104. For i = 1 to nbCountries * nbWarehousePerCountry
  105. For j = 1 to nbCountries * nbWarehousePerCountry
  106.  rate1 = Round(Rnd(9) * 10, 2)
  107.  rate2 = Round(Rnd(9) * 10, 2) + rate1
  108.  rate3 = Round(Rnd(9) * 10, 2) + rate2
  109.  cnx.Execute "insert into internalmove (warehouse1_id, warehouse2_id, weight, price) values (" & CStr(i) & ", " & CStr(j) & ", 5, " & CStr(rate1) & " )"
  110.  cnx.Execute "insert into internalmove (warehouse1_id, warehouse2_id, weight, price) values (" & CStr(i) & ", " & CStr(j) & ", 10, " & CStr(rate2) & " )"
  111.  cnx.Execute "insert into internalmove (warehouse1_id, warehouse2_id, weight, price) values (" & CStr(i) & ", " & CStr(j) & ", 15, " & CStr(rate3) & " )"
  112. Next
  113. Next
  114. cnx.Execute "insert into award (total_order, rate) values (10000, .01)"
  115. cnx.Execute "insert into award (total_order, rate) values (20000, .015)"
  116. cnx.Execute "insert into award (total_order, rate) values (50000, .02)"
  117. cnx.Execute "insert into award (total_order, rate) values (100000, .05)"
  118. cnx.Close
  119. Set cnx = Nothing
  120. Call LogEntry(logFile, "End on " & CStr(Now()))
  121. Call CloseLog(logFile)
  122. MsgBox("Databases are ready to bench!" )


 
Script du bench :

Code :
  1. Option Explicit
  2. Randomize
  3. ' Manque : dans la gestion des claim, avant le produit de remplacement, chercher si un InternalMove est possible !
  4. Dim nbDay
  5. nbDay = 360
  6. Dim cnxString
  7. Dim nbOrders
  8. Dim nbProductsPerOrder
  9. cnxString = "dsn=SQLBench;UID=sa;PWD=pdta"
  10. nbOrders = 100
  11. nbProductsPerOrder = 20
  12. Function CreateLog(name) 
  13.     Dim fso, fil 
  14.     Set fso = CreateObject("Scripting.FileSystemObject" ) 
  15.     Set fil = fso.CreateTextFile(name) 
  16.     Set CreateLog = fil 
  17. End Function 
  18. Sub LogEntry(fil, str) 
  19.     fil.WriteLine str
  20. End Sub 
  21. Sub CloseLog(fil) 
  22.     fil.Close 
  23.     Set fil = Nothing 
  24. End Sub 
  25. Dim logFile 
  26. Set logFile = CreateLog("c:\bench.log" ) 
  27. Call LogEntry(logFile, "Start on " & CStr(Now())) 
  28. Dim cnx 
  29. Set cnx = CreateObject("ADODB.Connection" ) 
  30. cnx.ConnectionTimeOut = 0 
  31. cnx.CommandTimeOut = 0 
  32. cnx.ConnectionString = cnxString 
  33. cnx.Open 
  34. Dim nbCustomers
  35. Dim nbProducts
  36. Dim rs
  37. Set rs = CreateObject("ADODB.RecordSet" )
  38. Set rs.ActiveConnection = cnx
  39. rs.Open "select count(*) nbProducts from product"
  40. nbProducts = rs("nbProducts" )
  41. rs.Close
  42. rs.Open "select count(*) nbCustomers from users"
  43. nbCustomers = rs("nbCustomers" )
  44. rs.Close
  45. Dim dDay
  46. For dDay = 1 To nbDay
  47. Call LogEntry(logFile, "Start day " & CStr(dDay) & " " & CStr(Now()))
  48. Call LogEntry(logFile, "Start process claims" & " " & CStr(Now()))
  49.  Call processClaims(dDay, cnx, rs)
  50. Call LogEntry(logFile, "End process claims" & " " & CStr(Now()))
  51. Call LogEntry(logFile, "Start make orders" & " " & CStr(Now()))
  52.     Call makeOrders(dDay, cnx, rs)
  53. Call LogEntry(logFile, "End make orders" & " " & CStr(Now()))
  54. Call LogEntry(logFile, "Start control stock" & " " & CStr(Now()))
  55.     Call controlStock(dDay, cnx, rs)
  56. Call LogEntry(logFile, "End control stock" & " " & CStr(Now()))
  57. Call LogEntry(logFile, "Start recieve stock" & " " & CStr(Now()))
  58.     Call RecieveStock(dDay, cnx, rs)
  59. Call LogEntry(logFile, "End recieve stock" & " " & CStr(Now()))
  60. Call LogEntry(logFile, "Start reserve stock" & " " & CStr(Now()))
  61.     Call ReserveStock(dDay, cnx, rs)
  62. Call LogEntry(logFile, "End reserve stock" & " " & CStr(Now()))
  63. Call LogEntry(logFile, "Start close orders" & " " & CStr(Now()))
  64.     Call CloseOrder(dDay, cnx, rs)
  65. Call LogEntry(logFile, "End close orders" & " " & CStr(Now()))
  66. Call LogEntry(logFile, "End day " & CStr(dDay) & " " & CStr(Now()))
  67. Next
  68. Set rs = Nothing
  69. cnx.Close 
  70. Set cnx = Nothing 
  71. Call LogEntry(logFile, "End on " & CStr(Now())) 
  72. Call CloseLog(logFile) 
  73. MsgBox "Bench end"
  74. Sub makeOrders(dDay, cnx, rs)
  75.     Dim i, j, k
  76.     Dim user_id
  77.     Dim vendor_id
  78.     Dim event_id
  79.     Dim event_line_id
  80.     Dim product_id
  81.     Dim quantity
  82.     cnx.BeginTrans
  83.     Dim rs2
  84.     Set rs2 = CreateObject("ADODB.RecordSet" )
  85.     Set rs2.ActiveConnection = cnx
  86.     For i = 1 To nbOrders
  87.         user_id = Int(Rnd(9) * nbCustomers) + 1
  88.         rs.Open "select count(v.id) cpt from vendor v, city c, address a, users u where u.id = " & CStr(user_id) & " and a.user_id = u.id and a.type = 'LIV' and c.id = a.city and v.warehouse_id = c.warehouse_id"
  89.    j = Int(Rnd(9) * rs("cpt" ))
  90.    rs.Close       
  91.    rs2.Open "select v.id from vendor v, city c, address a, users u where u.id = " & CStr(user_id) & " and a.user_id = u.id and a.type = 'LIV' and c.id = a.city and v.warehouse_id = c.warehouse_id"
  92.    rs2.Move j
  93.    vendor_id = rs2("id" )
  94.    rs2.Close
  95.         rs.Open "select max(id) event_id from event"
  96.         event_id = CInt("0" & rs("event_id" )) + 1
  97.         rs.Close
  98.         cnx.Execute "insert into event (id, user_id, type, status, date_event, vendor_id) values (" & event_id & ", " & CStr(user_id) & ", 'ORD', 'V', " & CStr(dDay) & ", " & CStr(vendor_id) & " )"
  99.         For j = 1 to nbProductsPerOrder
  100.          rs.Open "select count(id) cpt from product where active = 'A'"
  101.      k = Int(Rnd(9) * rs("cpt" ))
  102.      rs.Close       
  103.           rs2.Open "select id from product where active = 'A'"
  104.           rs2.Move k
  105.             product_id = rs2("id" )
  106.             rs2.Close
  107.             quantity = Int(Rnd(9) * 100) + 1
  108.             rs.Open "select max(id) event_line_id from event_line where event_id = " & CStr(event_id)
  109.             event_line_id = CInt("0" & rs("event_line_id" )) +1
  110.             rs.Close
  111.             cnx.Execute "insert into event_line (event_id, id, product_id, quantity, date_event, status) values (" & CStr(event_id) & ", " & CStr(event_line_id) & ", " & CStr(product_id) & ", " & CStr(quantity) & ", " & CStr(dDay) & ", 'V')"
  112.         Next
  113.     Next
  114.     Set rs2 = Nothing
  115.     cnx.CommitTrans
  116. End Sub
  117. Sub controlStock(dDay, cnx, rs)
  118.     cnx.BeginTrans
  119.     Dim rs2
  120.     Set rs2 = CreateObject("ADODB.RecordSet" )
  121.     Set rs2.ActiveConnection = cnx
  122.     Dim rs3
  123.     Set rs3 = CreateObject("ADODB.RecordSet" )
  124.     Set rs3.ActiveConnection = cnx
  125.     rs.Open "select (select top 1 supplier_id from suppliement su where su.warehouse_id = st.warehouse_id and su.product_id = el.product_id and (price = (select min(price) from suppliement su2 where su2.warehouse_id = su.warehouse_id and su2.product_id = su.product_id and su2.delay <= (select min(e.date_event) from city c, address a, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status != 'R' and el.product_id = su2.product_id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and c.warehouse_id = su2.warehouse_id) + 10 and su2.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply)) and su.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply) or delay = (select min(delay) from suppliement su2 where su2.warehouse_id = su.warehouse_id and su2.product_id = su.product_id and su2.delay > (select min(e.date_event) from city c, address a, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status != 'R' and el.product_id = su2.product_id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and c.warehouse_id = su2.warehouse_id) + 10 and su2.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply) and not exists (select null from suppliement su3 where su3.warehouse_id = su2.warehouse_id and su3.product_id = su2.warehouse_id and su3.delay <= (select min(e.date_event) from city c, address a, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status != 'R' and el.product_id = su3.product_id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and c.warehouse_id = su3.warehouse_id) + 10)) and su.max_supply >= (-1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.warehouse_id = st.warehouse_id and e2.date_event >= " & CStr(dDay) & " - 30 and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply))) supplier_id, st.warehouse_id, el.product_id, -1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.status = 'V' and e2.warehouse_id = st.warehouse_id and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply quantityPurchase from stock st, city ci, address a, event_line el, event e where e.status = 'V' and e.type = 'ORD' and el.event_id = e.id and el.status = 'V' and a.user_id = e.user_id and a.type = 'LIV' and ci.id = a.city and st.warehouse_id = ci.warehouse_id and st.product_id = el.product_id group by st.warehouse_id, el.product_id, st.reserved, st.stock, st.qtysupply having -1 * (st.stock - (sum(el.quantity) + st.reserved) + isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.status = 'V' and e2.warehouse_id = st.warehouse_id and el2.event_id = e2.id and el2.product_id = el.product_id), 0)) - st.qtysupply > 0 order by supplier_id, st.warehouse_id", , 2
  126.     Dim prevSupplier_id
  127.     Dim event_id
  128.     Dim event_line_id
  129.     Dim remain
  130.     prevSupplier_id = 0
  131.     Do While Not rs.EOF
  132.      If Not IsNull(rs("supplier_id" )) Then
  133.         If prevSupplier_id <> CInt(rs("supplier_id" )) Then
  134.             event_id = purchaseOrder(dDay, cnx, rs2, rs("supplier_id" ), rs("warehouse_id" ))
  135.             event_line_id = 1
  136.             prevSupplier_id = CInt(rs("supplier_id" ))
  137.         End If
  138.         Call purchaseOrderLine(dDay, cnx, event_id, event_line_id, rs("warehouse_id" ), rs("product_id" ), rs("quantitypurchase" ))
  139.         event_line_id = event_line_id + 1
  140.       Else
  141.        ' Commandes spéciales pour les produits en rupture de stock : on commande à plusieurs fournisseurs, en privilégiant ceux qui offrent des délais le plus rapide possible
  142.        remain = CLng(rs("quantityPurchase" ))
  143.    rs3.Open "select su.supplier_id, su.max_supply - isNull((select sum(el2.quantity) from event_line el2, event e2 where e2.type = 'PUR' and e2.date_event > " & CStr(dDay) & " - 30 and e2.warehouse_id = su.warehouse_id and el2.event_id = e2.id and el2.product_id = su.product_id), 0) remain from suppliement su where su.warehouse_id = " & rs("warehouse_id" ) & " and su.product_id = " & rs("product_id" ) & " order by su.delay", , 2
  144.         Do While Not rs3.EOF
  145.         event_id = purchaseOrder(dDay, cnx, rs2, rs3("supplier_id" ), rs("warehouse_id" ))
  146.         Call purchaseOrderLine(dDay, cnx, event_id, 1, rs("warehouse_id" ), rs("product_id" ), Min(remain, CLng(rs3("remain" ))))
  147.         remain = remain - Min(remain, CLng(rs3("remain" )))
  148.         If remain = 0 Then
  149.          rs3.MoveLast
  150.        End If
  151.         rs3.MoveNext
  152.       Loop
  153.       rs3.Close
  154.       End If
  155.       rs.MoveNext
  156.     Loop
  157.     rs.Close
  158.     Set rs2 = Nothing
  159.     Set rs3 = Nothing
  160.     cnx.CommitTrans
  161. End Sub
  162. Function purchaseOrder(dDay, cnx, rs, supplier_id, warehouse_id)
  163.     Dim event_id
  164.     rs.Open "select max(id) event_id from event", , 2
  165.     event_id = CInt("0" & rs("event_id" )) + 1
  166.     rs.Close
  167.     cnx.Execute "insert into event (id, type, status, date_event, supplier_id, warehouse_id) values (" & CStr(event_id) & ", 'PUR', 'V', " & CStr(dDay) & ", " & CStr(supplier_id) & ", " & CStr(warehouse_id) & " )"
  168.     purchaseOrder = event_id
  169. End Function
  170. Sub purchaseOrderLine(dDay, cnx, event_id, event_line_id, warehouse_id, product_id, quantityPurchase)
  171.     cnx.Execute "insert into event_line (event_id, id, product_id, quantity, date_event, status) values (" & CStr(event_id) & ", " & CStr(event_line_id) & ", " & CStr(product_id) & ", " & CStr(quantityPurchase) & ", " & CStr(dDay) & ", 'V')"
  172.     cnx.Execute "update stock set qtysupply = (qtysupply + " & CStr(quantityPurchase) & " ) * 1.1 / 2 where warehouse_id = " & CStr(warehouse_id) & " and product_id = " & CStr(product_id)
  173. End Sub
  174. Sub RecieveStock(dDay, cnx, rs)
  175.     cnx.BeginTrans
  176.     rs.Open "select e.id, e.warehouse_id, el.product_id, el.quantity from suppliement s, event_line el, event e where e.type = 'PUR' and e.status = 'V' and el.event_id = e.id and el.date_event - 1 > s.delay and s.supplier_id = e.supplier_id and s.product_id = el.product_id"
  177.     Do While Not rs.EOF
  178.         cnx.Execute "update stock set stock = " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" ))
  179.         cnx.Execute "update event_line set status = 'C' where event_id = " & CStr(rs("id" ))
  180.         cnx.Execute "update event set status = 'C' where id = " & CStr(rs("id" ))
  181.         rs.MoveNext
  182.     Loop
  183.     rs.Close
  184.     cnx.CommitTrans
  185. End Sub
  186. Sub ReserveStock(dDay, cnx, rs)
  187.     cnx.BeginTrans
  188.     Dim rs2
  189.     Set rs2 = CreateObject("ADODB.RecordSet" )
  190.     Set rs2.ActiveConnection = cnx
  191.     rs.Open "select el.event_id, el.id, c.warehouse_id, el.product_id, el.quantity from city c, address a, users u, event_line el, event e where e.type = 'ORD' and e.status = 'V' and el.event_id = e.id and el.status = 'V' and u.id = e.user_id and a.user_id = u.id and a.type = 'LIV' and c.id = a.city order by e.date_event asc, el.quantity desc", , 2
  192.     Do While Not rs.EOF
  193.         rs2.Open "select stock - reserved available from stock where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" )), , 0
  194.         If CLng(rs2("available" )) > CLng(rs("quantity" )) Then
  195.             cnx.Execute "update stock set reserved = reserved + " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" ))
  196.             cnx.Execute "update event_line set status = 'R' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs("id" ))
  197.         End If
  198.         rs2.Close
  199.         rs.MoveNext
  200.     Loop
  201.     rs.Close
  202.    
  203.     Set rs2 = Nothing
  204.     cnx.CommitTrans
  205. End Sub
  206. Sub CloseOrder(dDay, cnx, rs)
  207.     cnx.BeginTrans
  208.    
  209.     rs.Open "select e.id from event e where not exists (select null from event_line el where el.event_id = e.id and el.status = 'V') and e.status = 'V' and e.type = 'ORD'"
  210.     Do While Not rs.EOF
  211.         cnx.Execute "update event_line set status = 'C' where event_id = " & CStr(rs("id" )) & " and status = 'R'"
  212.         cnx.Execute "update event set status = 'P' where event_id = " & CStr(rs("id" )) & " and type = 'ORD'"
  213.         rs.MoveNext
  214.     Loop
  215.     rs.Close
  216.  cnx.Execute "update bank_account set bank_amount = bank_amount + tmp.amount from (select sum(p.amount / cr.rate) amount, c.warehouse_id from currencyrate cr, price p, country co, city c, address a, event_line el, event e where e.status = 'P' and e.type = 'ORD' and el.status = 'C' and el.event_id = e.id and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and co.id = c.country_id and p.country_id = co.id and cr.currency1_id = co.currency_id and cr.currency2_id = 1 group by c.warehouse_id) tmp where bank_account.warehouse_id = tmp.warehouse_id"
  217.  cnx.Execute "insert into vendor_award (vendor_id, award_date, amount) select tmp.vendor_id, " & CStr(dDay) & ", tmp.amount * a.rate from award a, (select sum(p.amount / cr.rate) amount, e.vendor_id from award aw, currencyrate cr, price p, country co, city c, address a, event_line el, event e where e.status = 'P' and e.type = 'ORD' and el.event_id = e.id and el.status = 'C' and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city and co.id = c.country_id and p.country_id = co.id and cr.currency1_id = co.currency_id and cr.currency2_id = 1 group by e.vendor_id) tmp where a.total_order >= tmp.amount and a.total_order = (select max(a2.total_order) from award a2 where a2.total_order >= tmp.amount)"
  218.  cnx.Execute "update bank_account set bank_amount = bank_amount - tmp.amount from (Select Sum(va.amount) amount, v.warehouse_id from vendor_award va, vendor v where va.vendor_id = v.id group by v.warehouse_id) tmp where bank_account.warehouse_id = tmp.warehouse_id"
  219.  cnx.Execute "update event set status = 'C' where status = 'P'"
  220.  cnx.CommitTrans
  221. End Sub
  222. Sub ProcessClaims(dDay, cnx, rs)
  223.  Dim event_id
  224.  Dim killOrder
  225.  event_id = 0
  226.  killOrder = False
  227.  cnx.BeginTrans
  228.  Dim rs2
  229.  Set rs2 = CreateObject("ADODB.RecordSet" )
  230.  Set rs2.ActiveConnection = cnx
  231.  rs.Open "select el.event_id, el.id, el.product_id, (select pr.product2_id from product p2, stock s, productreplacement pr where pr.product1_id = el.product_id and s.warehouse_id = c.warehouse_id and s.product_id = pr.product2_id and s.stock - s.reserved >= el.quantity and p2.id = pr.product2_id and p2.active = 'A') product2_id, el.quantity, c.warehouse_id from city c, address a, event_line el, event e where e.type = 'ORD' and e.status not in ('C', 'D') and e.date_event < 8 - 5 and el.event_id = e.id and el.status not in ('R', 'C', 'D') and a.user_id = e.user_id and a.type = 'LIV' and c.id = a.city order by el.event_id, el.id, product2_id", , 2
  232.  Do While Not rs.EOF
  233.   If Not IsNull(rs("product2_id" )) And Rnd(9) > .2 And (Not killOrder Or event_id <> CLng(rs("event_id" ))) Then
  234.    killOrder = False
  235.    cnx.Execute "update event_line set status = 'D' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs("id" ))
  236.    cnx.Execute "insert into event_line (event_id, id, product_id, quantity, date_event, status) values (" & CStr(rs("event_id" )) & ", " & CStr(rs("id" )) & ", " & CStr(rs("product2_id" )) & ", " & CStr(rs("quantity" )) & ", " & CStr(dDay) & ", 'R')"
  237.    cnx.Execute "update stock set stock = stock - " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product2_id" ))
  238.   Else
  239.    killOrder = True
  240.    rs2.Open "select el.id, el.product_id, el.quantity from event_line el where el.event_id = " & CStr(rs("event_id" )) & " and el.status = 'R'", , 2
  241.    Do While Not rs2.EOF
  242.     cnx.Execute "update event_line set status = 'D' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs2("id" ))
  243.     cnx.Execute "update stock set reserved = reserved - " & CStr(rs2("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs2("product_id" ))
  244.     rs2.MoveNext
  245.    Loop
  246.    rs2.Close
  247.    cnx.Execute "update event set status = 'D' where id = " & CStr(rs("event_id" ))
  248.   End If
  249.   event_id = CLng(rs("event_id" ))
  250.   rs.MoveNext
  251.  Loop
  252.  Set rs2 = Nothing
  253.  rs.Close
  254.  cnx.CommitTrans
  255. End Sub
  256. Function Min(a, b)
  257. If a <= b Then
  258.  Min = a
  259. Else
  260.  Min = b
  261. End If
  262. End Function


 
Petit script SQL de contrôle/nettoyage de la base (listing de toutes les tables, et lots de reminse à 0 des données des deux scripts) :

Code :
  1. select 'products :             ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from product
  2. union all
  3. select 'replacement products : ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from productreplacement
  4. union all
  5. select 'currencies             ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from currency
  6. union all
  7. select 'countries              ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from country
  8. union all
  9. select 'currencies rates :     ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from currencyrate
  10. union all
  11. select 'vats :                 ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from vat
  12. union all
  13. select 'prices :               ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from price
  14. union all
  15. select 'warehouses :           ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from warehouse
  16. union all
  17. select 'bank accounts :        ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from bank_account
  18. union all
  19. select 'stocks :               ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from stock
  20. union all
  21. select 'vendors :              ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from vendor
  22. union all
  23. select 'suppliers :            ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from supplier
  24. union all
  25. select 'suppliements :         ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from suppliement
  26. union all
  27. select 'cities :               ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from city
  28. union all
  29. select 'users :                ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from users
  30. union all
  31. select 'addresses :            ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from address
  32. union all
  33. select 'internal moves :       ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from internalmove
  34. union all
  35. select 'awards :               ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from award
  36. union all
  37. select 'events :               ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from event
  38. union all
  39. select 'events lines :         ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from event_line
  40. union all
  41. select 'vendors awards :       ' + Cast(count(*) as varchar) as "Nombre de lignes par table" from vendor_award
  42. /*
  43. -- Run from here for "bench.vbs" data clean-up
  44. delete vendor_award
  45. delete event_line
  46. delete event
  47. update bank_account set bank_amount = 0
  48. update stock set stock = 0, reserved = 0, qtysupply = 0
  49. -- Stop here for "bench.vbs" data clean-up
  50. -- Run from here for "filldata.vbs" data clean-up (beware, fillind again database is very slow!)
  51. delete award
  52. delete internalmove
  53. delete address
  54. delete users
  55. delete city
  56. delete suppliement
  57. delete supplier
  58. delete vendor
  59. delete stock
  60. delete bank_account
  61. delete warehouse
  62. delete price
  63. delete vat
  64. delete currencyrate
  65. delete country
  66. delete currency
  67. delete productreplacement
  68. delete product
  69. -- Stop here for "filldata.vbs" data clean-up
  70. */


Message édité par Arjuna le 03-08-2005 à 01:01:31
n°1164179
Arjuna
Aircraft Ident.: F-MBSD
Posté le 29-07-2005 à 00:46:22  profilanswer
 

J'ai fait un peu de nettoyage dans le topic, il commençait à y avoir trop de versions non à jour de bouts de code :)

n°1164189
matthieu_p​hpmv
Posté le 29-07-2005 à 01:16:00  profilanswer
 

Je ne souhaite pas troller (loin de moi cette idée), mais qu'est ce que c'est moche le VBscript!!! hallucinant.
Tu sais arjuna ya un driver sql server pour php ;)

mood
Publicité
Posté le   profilanswer
 

 Page :   1  2
Page Précédente

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Tentative de mini-bench comparant MySQL et SQL Server

 

Sujets relatifs
[MySQL] Update multitablesvba access mysql
Amélioration de performances d'une base SQL Server 2000SQL Server: Select date qui ont minimum une heure d'ecart
SQL Server et le XML[RESOLU] [MySQL API C] Problème avec mysql_query
[SQL] Problème écriture requête 
Plus de sujets relatifs à : Tentative de mini-bench comparant MySQL et SQL Server


Copyright © 1997-2018 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR