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

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Tentative de mini-bench comparant MySQL et SQL Server

 
 


 Mot :   Pseudo :  
 
 Page :   1  2
Page Suivante
Page Précédente
Bas de page 
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.
  16. # The following options will be passed to all MySQL clients
  17. [client]
  18. #password = pass
  19. port  = 3306
  20. socket  = /tmp/mysql.sock
  22. # Here follows entries for some specific programs
  24. # The MySQL server
  25. [mysqld]
  26. port  = 3306
  27. socket  = /tmp/mysql.sock
  28. skip-locking
  29. key_buffer = 384M
  30. max_allowed_packet = 1M
  31. table_cache = 512
  32. sort_buffer_size = 2M
  33. read_buffer_size = 2M
  34. read_rnd_buffer_size = 8M
  35. myisam_sort_buffer_size = 64M
  36. thread_cache = 8
  37. query_cache_size = 32M
  38. # Try number of CPU's*2 for thread_concurrency
  39. thread_concurrency = 4
  40. basedir=d:/mysql-4.1.13-win32
  41. datadir=e:/mysqldata
  42. innodb_log_group_home_dir=f:/mysqllog
  45. # Don't listen on a TCP/IP port at all. This can be a security enhancement,
  46. # if all processes that need to connect to mysqld run on the same host.
  47. # All interaction with mysqld must be made via Unix sockets or named pipes.
  48. # Note that using this option without enabling named pipes on Windows
  49. # (via the "enable-named-pipe" option) will render mysqld useless!
  50. #
  51. #skip-networking
  53. # Replication Master Server (default)
  54. # binary logging is required for replication
  55. log-bin
  57. # required unique id between 1 and 2^32 - 1
  58. # defaults to 1 if master-host is not set
  59. # but will not function as a master if omitted
  60. server-id = 1
  62. # Replication Slave (comment out master section to use this)
  63. #
  64. # To configure this host as a replication slave, you can choose between
  65. # two methods :
  66. #
  67. # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
  68. #    the syntax is:
  69. #
  70. #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
  71. #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
  72. #
  73. #    where you replace <host>, <user>, <password> by quoted strings and
  74. #    <port> by the master's port number (3306 by default).
  75. #
  76. #    Example:
  77. #
  78. #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
  79. #    MASTER_USER='joe', MASTER_PASSWORD='secret';
  80. #
  81. # OR
  82. #
  83. # 2) Set the variables below. However, in case you choose this method, then
  84. #    start replication for the first time (even unsuccessfully, for example
  85. #    if you mistyped the password in master-password and the slave fails to
  86. #    connect), the slave will create a master.info file, and any later
  87. #    change in this file to the variables' values below will be ignored and
  88. #    overridden by the content of the master.info file, unless you shutdown
  89. #    the slave server, delete master.info and restart the slaver server.
  90. #    For that reason, you may want to leave the lines below untouched
  91. #    (commented) and instead use CHANGE MASTER TO (see above)
  92. #
  93. # required unique id between 2 and 2^32 - 1
  94. # (and different from the master)
  95. # defaults to 2 if master-host is set
  96. # but will not function as a slave if omitted
  97. #server-id       = 2
  98. #
  99. # The replication master for this slave - required
  100. #master-host     =   <hostname>
  101. #
  102. # The username the slave will use for authentication when connecting
  103. # to the master - required
  104. #master-user     =   <username>
  105. #
  106. # The password the slave will authenticate with when connecting to
  107. # the master - required
  108. #master-password =   <password>
  109. #
  110. # The port the master is listening on.
  111. # optional - defaults to 3306
  112. #master-port     =  <port>
  113. #
  114. # binary logging - not required for slaves, but recommended
  115. #log-bin
  117. # Point the following paths to different dedicated disks
  118. #tmpdir  = /tmp/
  119. #log-update  = /path-to-dedicated-directory/hostname
  121. # Uncomment the following if you are using BDB tables
  122. #bdb_cache_size = 384M
  123. #bdb_max_lock = 100000
  125. # Uncomment the following if you are using InnoDB tables
  126. #innodb_data_home_dir = /usr/local/var/
  127. #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
  128. #innodb_log_group_home_dir = /usr/local/var/
  129. #innodb_log_arch_dir = /usr/local/var/
  130. # You can set .._buffer_pool_size up to 50 - 80 %
  131. # of RAM but beware of setting memory usage too high
  132. #innodb_buffer_pool_size = 384M
  133. #innodb_additional_mem_pool_size = 20M
  134. # Set .._log_file_size to 25 % of buffer pool size
  135. #innodb_log_file_size = 100M
  136. #innodb_log_buffer_size = 8M
  137. #innodb_flush_log_at_trx_commit = 1
  138. #innodb_lock_wait_timeout = 50
  140. [mysqldump]
  141. quick
  142. max_allowed_packet = 16M
  144. [mysql]
  145. no-auto-rehash
  146. # Remove the next comment character if you are not familiar with SQL
  147. #safe-updates
  149. [isamchk]
  150. key_buffer = 256M
  151. sort_buffer_size = 256M
  152. read_buffer = 2M
  153. write_buffer = 2M
  155. [myisamchk]
  156. key_buffer = 256M
  157. sort_buffer_size = 256M
  158. read_buffer = 2M
  159. write_buffer = 2M
  161. [mysqlhotcopy]
  162. 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
  9. use [bench]
  10. GO
  12. CREATE TABLE [dbo].[address] (
  13. [user_id] [numeric](18, 0) NOT NULL ,
  14. [type] [char] (3) COLLATE French_CI_AS NOT NULL ,
  15. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  16. [street] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  17. [city] [numeric](18, 0) NOT NULL
  18. ) ON [PRIMARY]
  19. GO
  21. CREATE TABLE [dbo].[award] (
  22. [total_order] [decimal](18, 0) NOT NULL ,
  23. [rate] [decimal](18, 0) NOT NULL
  24. ) ON [PRIMARY]
  25. GO
  27. CREATE TABLE [dbo].[bank_account] (
  28. [warehouse_id] [numeric](18, 0) NOT NULL ,
  29. [bank_amount] [numeric](18, 0) NOT NULL
  30. ) ON [PRIMARY]
  31. GO
  33. CREATE TABLE [dbo].[city] (
  34. [id] [numeric](18, 0) NOT NULL ,
  35. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  36. [country_id] [numeric](18, 0) NOT NULL ,
  37. [zipcode] [nvarchar] (50) COLLATE French_CI_AS NOT NULL ,
  38. [warehouse_id] [numeric](18, 0) NOT NULL
  39. ) ON [PRIMARY]
  40. GO
  42. CREATE TABLE [dbo].[country] (
  43. [id] [numeric](18, 0) NOT NULL ,
  44. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  45. [currency_id] [numeric](18, 0) NOT NULL
  46. ) ON [PRIMARY]
  47. GO
  49. CREATE TABLE [dbo].[currency] (
  50. [id] [numeric](18, 0) NOT NULL ,
  51. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL
  52. ) ON [PRIMARY]
  53. GO
  55. CREATE TABLE [dbo].[currencyrate] (
  56. [currency1_id] [numeric](18, 0) NOT NULL ,
  57. [currency2_id] [numeric](18, 0) NOT NULL ,
  58. [rate] [decimal](18, 0) NOT NULL
  59. ) ON [PRIMARY]
  60. GO
  62. CREATE TABLE [dbo].[event] (
  63. [id] [numeric](18, 0) NOT NULL ,
  64. [user_id] [numeric](18, 0) NULL ,
  65. [type] [char] (3) COLLATE French_CI_AS NOT NULL ,
  66. [status] [char] (1) COLLATE French_CI_AS NOT NULL ,
  67. [vendor_id] [numeric](18, 0) NULL ,
  68. [warehouse_id] [numeric](18, 0) NULL ,
  69. [supplier_id] [numeric](18, 0) NULL ,
  70. [date_event] [int] NOT NULL
  71. ) ON [PRIMARY]
  72. GO
  74. CREATE TABLE [dbo].[event_line] (
  75. [event_id] [numeric](18, 0) NOT NULL ,
  76. [id] [numeric](18, 0) NOT NULL ,
  77. [product_id] [numeric](18, 0) NOT NULL ,
  78. [quantity] [numeric](18, 0) NOT NULL ,
  79. [date_event] [int] NOT NULL ,
  80. [status] [char] (1) COLLATE French_CI_AS NOT NULL
  81. ) ON [PRIMARY]
  82. GO
  84. CREATE TABLE [dbo].[internalmove] (
  85. [warehouse1_id] [numeric](18, 0) NOT NULL ,
  86. [warehouse2_id] [numeric](18, 0) NOT NULL ,
  87. [weight] [numeric](18, 0) NOT NULL ,
  88. [price] [decimal](18, 0) NOT NULL
  89. ) ON [PRIMARY]
  90. GO
  92. CREATE TABLE [dbo].[price] (
  93. [country_id] [numeric](18, 0) NOT NULL ,
  94. [product_id] [numeric](18, 0) NOT NULL ,
  95. [amount] [decimal](18, 0) NOT NULL
  96. ) ON [PRIMARY]
  97. GO
  99. CREATE TABLE [dbo].[product] (
  100. [id] [numeric](18, 0) NOT NULL ,
  101. [code] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  102. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  103. [weight] [numeric](18, 0) NOT NULL ,
  104. [active] [char] (1) COLLATE French_CI_AS NOT NULL
  105. ) ON [PRIMARY]
  106. GO
  108. CREATE TABLE [dbo].[productreplacement] (
  109. [product1_id] [numeric](18, 0) NOT NULL ,
  110. [product2_id] [numeric](18, 0) NOT NULL
  111. ) ON [PRIMARY]
  112. GO
  114. CREATE TABLE [dbo].[stock] (
  115. [warehouse_id] [numeric](18, 0) NOT NULL ,
  116. [product_id] [numeric](18, 0) NOT NULL ,
  117. [stock] [numeric](18, 0) NOT NULL ,
  118. [reserved] [numeric](18, 0) NOT NULL ,
  119. [qtysupply] [numeric](18, 0) NOT NULL
  120. ) ON [PRIMARY]
  121. GO
  123. CREATE TABLE [dbo].[suppliement] (
  124. [product_id] [numeric](18, 0) NOT NULL ,
  125. [supplier_id] [numeric](18, 0) NOT NULL ,
  126. [warehouse_id] [numeric](18, 0) NOT NULL ,
  127. [price] [decimal](18, 0) NOT NULL ,
  128. [delay] [numeric](18, 0) NOT NULL
  129. ) ON [PRIMARY]
  130. GO
  132. CREATE TABLE [dbo].[supplier] (
  133. [id] [numeric](18, 0) NOT NULL ,
  134. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  135. [warehouse_id] [numeric](18, 0) NOT NULL
  136. ) ON [PRIMARY]
  137. GO
  139. CREATE TABLE [dbo].[users] (
  140. [id] [numeric](18, 0) NOT NULL ,
  141. [vat_type] [numeric](18, 0) NOT NULL ,
  142. [firstname] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  143. [lastname] [varchar] (50) COLLATE French_CI_AS NOT NULL
  144. ) ON [PRIMARY]
  145. GO
  147. CREATE TABLE [dbo].[vat] (
  148. [country_id] [numeric](18, 0) NOT NULL ,
  149. [vat_id] [numeric](18, 0) NOT NULL ,
  150. [rate] [decimal](18, 0) NOT NULL
  151. ) ON [PRIMARY]
  152. GO
  154. CREATE TABLE [dbo].[vendor] (
  155. [id] [numeric](18, 0) NOT NULL ,
  156. [name] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
  157. [warehouse_id] [numeric](18, 0) NOT NULL
  158. ) ON [PRIMARY]
  159. GO
  161. CREATE TABLE [dbo].[vendor_award] (
  162. [vendor_id] [numeric](18, 0) NOT NULL ,
  163. [award_date] [int] NOT NULL ,
  164. [amount] [int] NOT NULL
  165. ) ON [PRIMARY]
  166. GO
  168. CREATE TABLE [dbo].[warehouse] (
  169. [id] [numeric](18, 0) NOT NULL ,
  170. [country_id] [numeric](18, 0) NOT NULL
  171. ) ON [PRIMARY]
  172. GO
  174. ALTER TABLE [dbo].[address] WITH NOCHECK ADD
  175. CONSTRAINT [PK_address] PRIMARY KEY  CLUSTERED
  176. (
  177.  [user_id],
  178.  [type]
  179. )  ON [PRIMARY]
  180. GO
  182. ALTER TABLE [dbo].[award] WITH NOCHECK ADD
  183. CONSTRAINT [PK_award] PRIMARY KEY  CLUSTERED
  184. (
  185.  [total_order]
  186. )  ON [PRIMARY]
  187. GO
  189. ALTER TABLE [dbo].[bank_account] WITH NOCHECK ADD
  190. CONSTRAINT [PK_bank_account] PRIMARY KEY  CLUSTERED
  191. (
  192.  [warehouse_id]
  193. )  ON [PRIMARY]
  194. GO
  196. ALTER TABLE [dbo].[city] WITH NOCHECK ADD
  197. CONSTRAINT [PK_city] PRIMARY KEY  CLUSTERED
  198. (
  199.  [id]
  200. )  ON [PRIMARY]
  201. GO
  203. ALTER TABLE [dbo].[country] WITH NOCHECK ADD
  204. CONSTRAINT [PK_country] PRIMARY KEY  CLUSTERED
  205. (
  206.  [id]
  207. )  ON [PRIMARY]
  208. GO
  210. ALTER TABLE [dbo].[currency] WITH NOCHECK ADD
  211. CONSTRAINT [PK_currency] PRIMARY KEY  CLUSTERED
  212. (
  213.  [id]
  214. )  ON [PRIMARY]
  215. GO
  217. ALTER TABLE [dbo].[currencyrate] WITH NOCHECK ADD
  218. CONSTRAINT [PK_currencyrate] PRIMARY KEY  CLUSTERED
  219. (
  220.  [currency1_id],
  221.  [currency2_id]
  222. )  ON [PRIMARY]
  223. GO
  225. ALTER TABLE [dbo].[event] WITH NOCHECK ADD
  226. CONSTRAINT [PK_event] PRIMARY KEY  CLUSTERED
  227. (
  228.  [id]
  229. )  ON [PRIMARY]
  230. GO
  232. ALTER TABLE [dbo].[event_line] WITH NOCHECK ADD
  233. CONSTRAINT [PK_event_line] PRIMARY KEY  CLUSTERED
  234. (
  235.  [event_id],
  236.  [id]
  237. )  ON [PRIMARY]
  238. GO
  240. ALTER TABLE [dbo].[internalmove] WITH NOCHECK ADD
  241. CONSTRAINT [PK_internalmove] PRIMARY KEY  CLUSTERED
  242. (
  243.  [warehouse1_id],
  244.  [warehouse2_id],
  245.  [weight]
  246. )  ON [PRIMARY]
  247. GO
  249. ALTER TABLE [dbo].[price] WITH NOCHECK ADD
  250. CONSTRAINT [PK_price] PRIMARY KEY  CLUSTERED
  251. (
  252.  [country_id],
  253.  [product_id]
  254. )  ON [PRIMARY]
  255. GO
  257. ALTER TABLE [dbo].[product] WITH NOCHECK ADD
  258. CONSTRAINT [PK_product] PRIMARY KEY  CLUSTERED
  259. (
  260.  [id]
  261. )  ON [PRIMARY]
  262. GO
  264. ALTER TABLE [dbo].[productreplacement] WITH NOCHECK ADD
  265. CONSTRAINT [PK_productreplacement] PRIMARY KEY  CLUSTERED
  266. (
  267.  [product1_id]
  268. )  ON [PRIMARY]
  269. GO
  271. ALTER TABLE [dbo].[stock] WITH NOCHECK ADD
  272. CONSTRAINT [PK_stock] PRIMARY KEY  CLUSTERED
  273. (
  274.  [warehouse_id],
  275.  [product_id]
  276. )  ON [PRIMARY]
  277. GO
  279. ALTER TABLE [dbo].[suppliement] WITH NOCHECK ADD
  280. CONSTRAINT [PK_suppliement] PRIMARY KEY  CLUSTERED
  281. (
  282.  [product_id],
  283.  [supplier_id]
  284. )  ON [PRIMARY]
  285. GO
  287. ALTER TABLE [dbo].[supplier] WITH NOCHECK ADD
  288. CONSTRAINT [PK_supplier] PRIMARY KEY  CLUSTERED
  289. (
  290.  [id]
  291. )  ON [PRIMARY]
  292. GO
  294. ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  295. CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED
  296. (
  297.  [id]
  298. )  ON [PRIMARY]
  299. GO
  301. ALTER TABLE [dbo].[vat] WITH NOCHECK ADD
  302. CONSTRAINT [PK_vat] PRIMARY KEY  CLUSTERED
  303. (
  304.  [vat_id],
  305.  [country_id]
  306. )  ON [PRIMARY]
  307. GO
  309. ALTER TABLE [dbo].[vendor] WITH NOCHECK ADD
  310. CONSTRAINT [PK_vendor] PRIMARY KEY  CLUSTERED
  311. (
  312.  [id]
  313. )  ON [PRIMARY]
  314. GO
  316. ALTER TABLE [dbo].[vendor_award] WITH NOCHECK ADD
  317. CONSTRAINT [PK_vendor_award] PRIMARY KEY  CLUSTERED
  318. (
  319.  [vendor_id],
  320.  [award_date]
  321. )  ON [PRIMARY]
  322. GO
  324. ALTER TABLE [dbo].[warehouse] WITH NOCHECK ADD
  325. CONSTRAINT [PK_warehouse] PRIMARY KEY  CLUSTERED
  326. (
  327.  [id]
  328. )  ON [PRIMARY]
  329. GO
  331. CREATE  INDEX [ix_address_user] ON [dbo].[address]([user_id], [type]) ON [SECONDARY]
  332. GO
  334. CREATE  INDEX [ix_event_type_status_user] ON [dbo].[event]([type], [status], [user_id] DESC ) ON [SECONDARY]
  335. GO
  337. CREATE  INDEX [ix_event_type_status_vendor] ON [dbo].[event]([type], [status], [vendor_id] DESC ) ON [SECONDARY]
  338. GO
  340. CREATE  INDEX [ix_event_type_status_warehouse] ON [dbo].[event]([type], [status], [warehouse_id] DESC ) ON [SECONDARY]
  341. GO
  343. CREATE  INDEX [ix_event_type_status_supplier] ON [dbo].[event]([type], [status], [supplier_id] DESC ) ON [SECONDARY]
  344. GO
  346. CREATE  INDEX [ix_event_date_status] ON [dbo].[event]([date_event] DESC , [status]) ON [SECONDARY]
  347. GO
  349. CREATE  INDEX [ix_eventline_event_status] ON [dbo].[event_line]([event_id] DESC , [status]) ON [SECONDARY]
  350. GO
  352. CREATE  INDEX [ix_product_active] ON [dbo].[product]([active]) ON [SECONDARY]
  353. GO
  355. CREATE  INDEX [ix_suppliement_delay] ON [dbo].[suppliement]([delay]) ON [SECONDARY]
  356. GO
  358. CREATE  INDEX [ix_suppliement_supply] ON [dbo].[suppliement]([warehouse_id], [product_id]) ON [SECONDARY]
  359. GO
  361. CREATE  INDEX [ix_supplier_warehouse] ON [dbo].[supplier]([warehouse_id]) ON [SECONDARY]
  362. GO
  364. ALTER TABLE [dbo].[address] ADD
  365. CONSTRAINT [FK_address_city] FOREIGN KEY
  366. (
  367.  [city]
  368. ) REFERENCES [dbo].[city] (
  369.  [id]
  370. ),
  371. CONSTRAINT [FK_address_users] FOREIGN KEY
  372. (
  373.  [user_id]
  374. ) REFERENCES [dbo].[users] (
  375.  [id]
  376. )
  377. GO
  379. ALTER TABLE [dbo].[bank_account] ADD
  380. CONSTRAINT [FK_bank_account_warehouse] FOREIGN KEY
  381. (
  382.  [warehouse_id]
  383. ) REFERENCES [dbo].[warehouse] (
  384.  [id]
  385. )
  386. GO
  388. ALTER TABLE [dbo].[city] ADD
  389. CONSTRAINT [FK_city_country] FOREIGN KEY
  390. (
  391.  [country_id]
  392. ) REFERENCES [dbo].[country] (
  393.  [id]
  394. )
  395. GO
  397. ALTER TABLE [dbo].[country] ADD
  398. CONSTRAINT [FK_country_currency] FOREIGN KEY
  399. (
  400.  [currency_id]
  401. ) REFERENCES [dbo].[currency] (
  402.  [id]
  403. )
  404. GO
  406. ALTER TABLE [dbo].[currencyrate] ADD
  407. CONSTRAINT [FK_currencyrate_currency] FOREIGN KEY
  408. (
  409.  [currency1_id]
  410. ) REFERENCES [dbo].[currency] (
  411.  [id]
  412. ),
  413. CONSTRAINT [FK_currencyrate_currency1] FOREIGN KEY
  414. (
  415.  [currency2_id]
  416. ) REFERENCES [dbo].[currency] (
  417.  [id]
  418. )
  419. GO
  421. ALTER TABLE [dbo].[event] ADD
  422. CONSTRAINT [FK_event_supplier] FOREIGN KEY
  423. (
  424.  [supplier_id]
  425. ) REFERENCES [dbo].[supplier] (
  426.  [id]
  427. ),
  428. CONSTRAINT [FK_event_users] FOREIGN KEY
  429. (
  430.  [user_id]
  431. ) REFERENCES [dbo].[users] (
  432.  [id]
  433. ),
  434. CONSTRAINT [FK_event_vendor] FOREIGN KEY
  435. (
  436.  [vendor_id]
  437. ) REFERENCES [dbo].[vendor] (
  438.  [id]
  439. ),
  440. CONSTRAINT [FK_event_warehouse] FOREIGN KEY
  441. (
  442.  [warehouse_id]
  443. ) REFERENCES [dbo].[warehouse] (
  444.  [id]
  445. )
  446. GO
  448. ALTER TABLE [dbo].[event_line] ADD
  449. CONSTRAINT [FK_event_line_event] FOREIGN KEY
  450. (
  451.  [event_id]
  452. ) REFERENCES [dbo].[event] (
  453.  [id]
  454. ),
  455. CONSTRAINT [FK_event_line_product] FOREIGN KEY
  456. (
  457.  [product_id]
  458. ) REFERENCES [dbo].[product] (
  459.  [id]
  460. )
  461. GO
  463. ALTER TABLE [dbo].[internalmove] ADD
  464. CONSTRAINT [FK_internalmove_warehouse] FOREIGN KEY
  465. (
  466.  [warehouse1_id]
  467. ) REFERENCES [dbo].[warehouse] (
  468.  [id]
  469. ),
  470. CONSTRAINT [FK_internalmove_warehouse1] FOREIGN KEY
  471. (
  472.  [warehouse2_id]
  473. ) REFERENCES [dbo].[warehouse] (
  474.  [id]
  475. )
  476. GO
  478. ALTER TABLE [dbo].[price] ADD
  479. CONSTRAINT [FK_price_country] FOREIGN KEY
  480. (
  481.  [country_id]
  482. ) REFERENCES [dbo].[country] (
  483.  [id]
  484. ),
  485. CONSTRAINT [FK_price_product] FOREIGN KEY
  486. (
  487.  [product_id]
  488. ) REFERENCES [dbo].[product] (
  489.  [id]
  490. )
  491. GO
  493. ALTER TABLE [dbo].[productreplacement] ADD
  494. CONSTRAINT [FK_productreplacement_product] FOREIGN KEY
  495. (
  496.  [product1_id]
  497. ) REFERENCES [dbo].[product] (
  498.  [id]
  499. ),
  500. CONSTRAINT [FK_productreplacement_product2] FOREIGN KEY
  501. (
  502.  [product2_id]
  503. ) REFERENCES [dbo].[product] (
  504.  [id]
  505. )
  506. GO
  508. ALTER TABLE [dbo].[stock] ADD
  509. CONSTRAINT [FK_stock_product] FOREIGN KEY
  510. (
  511.  [product_id]
  512. ) REFERENCES [dbo].[product] (
  513.  [id]
  514. ),
  515. CONSTRAINT [FK_stock_warehouse] FOREIGN KEY
  516. (
  517.  [warehouse_id]
  518. ) REFERENCES [dbo].[warehouse] (
  519.  [id]
  520. )
  521. GO
  523. ALTER TABLE [dbo].[suppliement] ADD
  524. CONSTRAINT [FK_suppliement_product] FOREIGN KEY
  525. (
  526.  [product_id]
  527. ) REFERENCES [dbo].[product] (
  528.  [id]
  529. ),
  530. CONSTRAINT [FK_suppliement_supplier] FOREIGN KEY
  531. (
  532.  [supplier_id]
  533. ) REFERENCES [dbo].[supplier] (
  534.  [id]
  535. ),
  536. CONSTRAINT [FK_suppliement_warehouse] FOREIGN KEY
  537. (
  538.  [warehouse_id]
  539. ) REFERENCES [dbo].[warehouse] (
  540.  [id]
  541. )
  542. GO
  544. ALTER TABLE [dbo].[vat] ADD
  545. CONSTRAINT [FK_vat_country1] FOREIGN KEY
  546. (
  547.  [country_id]
  548. ) REFERENCES [dbo].[country] (
  549.  [id]
  550. )
  551. GO
  553. ALTER TABLE [dbo].[vendor] ADD
  554. CONSTRAINT [FK_vendor_warehouse] FOREIGN KEY
  555. (
  556.  [warehouse_id]
  557. ) REFERENCES [dbo].[warehouse] (
  558.  [id]
  559. )
  560. GO
  562. ALTER TABLE [dbo].[warehouse] ADD
  563. CONSTRAINT [FK_warehouse_country] FOREIGN KEY
  564. (
  565.  [country_id]
  566. ) REFERENCES [dbo].[country] (
  567.  [id]
  568. )
  569. GO


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

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


 
Script du bench :

Code :
  1. Option Explicit
  2. Randomize
  4. ' Manque : dans la gestion des claim, avant le produit de remplacement, chercher si un InternalMove est possible !
  6. Dim nbDay
  7. nbDay = 360
  9. Dim cnxString
  10. Dim nbOrders
  11. Dim nbProductsPerOrder
  13. cnxString = "dsn=SQLBench;UID=sa;PWD=pdta"
  14. nbOrders = 100
  15. nbProductsPerOrder = 20
  17. Function CreateLog(name) 
  18.     Dim fso, fil 
  19.     Set fso = CreateObject("Scripting.FileSystemObject" ) 
  20.     Set fil = fso.CreateTextFile(name) 
  21.     Set CreateLog = fil 
  22. End Function 
  24. Sub LogEntry(fil, str) 
  25.     fil.WriteLine str
  26. End Sub 
  28. Sub CloseLog(fil) 
  29.     fil.Close 
  30.     Set fil = Nothing 
  31. End Sub 
  33. Dim logFile 
  34. Set logFile = CreateLog("c:\bench.log" ) 
  35. 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 
  44. Dim nbCustomers
  45. Dim nbProducts
  47. Dim rs
  48. Set rs = CreateObject("ADODB.RecordSet" )
  49. Set rs.ActiveConnection = cnx
  51. rs.Open "select count(*) nbProducts from product"
  52. nbProducts = rs("nbProducts" )
  53. rs.Close
  55. rs.Open "select count(*) nbCustomers from users"
  56. nbCustomers = rs("nbCustomers" )
  57. rs.Close
  59. Dim dDay
  60. For dDay = 1 To nbDay
  61. Call LogEntry(logFile, "Start day " & CStr(dDay) & " " & CStr(Now()))
  62. Call LogEntry(logFile, "Start process claims" & " " & CStr(Now()))
  63.  Call processClaims(dDay, cnx, rs)
  64. Call LogEntry(logFile, "End process claims" & " " & CStr(Now()))
  65. Call LogEntry(logFile, "Start make orders" & " " & CStr(Now()))
  66.     Call makeOrders(dDay, cnx, rs)
  67. Call LogEntry(logFile, "End make orders" & " " & CStr(Now()))
  68. Call LogEntry(logFile, "Start control stock" & " " & CStr(Now()))
  69.     Call controlStock(dDay, cnx, rs)
  70. Call LogEntry(logFile, "End control stock" & " " & CStr(Now()))
  71. Call LogEntry(logFile, "Start recieve stock" & " " & CStr(Now()))
  72.     Call RecieveStock(dDay, cnx, rs)
  73. Call LogEntry(logFile, "End recieve stock" & " " & CStr(Now()))
  74. Call LogEntry(logFile, "Start reserve stock" & " " & CStr(Now()))
  75.     Call ReserveStock(dDay, cnx, rs)
  76. Call LogEntry(logFile, "End reserve stock" & " " & CStr(Now()))
  77. Call LogEntry(logFile, "Start close orders" & " " & CStr(Now()))
  78.     Call CloseOrder(dDay, cnx, rs)
  79. Call LogEntry(logFile, "End close orders" & " " & CStr(Now()))
  80. Call LogEntry(logFile, "End day " & CStr(dDay) & " " & CStr(Now()))
  81. Next
  83. Set rs = Nothing
  84. cnx.Close 
  85. Set cnx = Nothing 
  87. Call LogEntry(logFile, "End on " & CStr(Now())) 
  88. Call CloseLog(logFile) 
  89. MsgBox "Bench end"
  91. Sub makeOrders(dDay, cnx, rs)
  92.     Dim i, j, k
  93.     Dim user_id
  94.     Dim vendor_id
  95.     Dim event_id
  96.     Dim event_line_id
  97.     Dim product_id
  98.     Dim quantity
  99.     cnx.BeginTrans
  100.     Dim rs2
  101.     Set rs2 = CreateObject("ADODB.RecordSet" )
  102.     Set rs2.ActiveConnection = cnx
  103.     For i = 1 To nbOrders
  104.         user_id = Int(Rnd(9) * nbCustomers) + 1
  105.         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"
  106.    j = Int(Rnd(9) * rs("cpt" ))
  107.    rs.Close       
  108.    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"
  109.    rs2.Move j
  110.    vendor_id = rs2("id" )
  111.    rs2.Close
  112.         rs.Open "select max(id) event_id from event"
  113.         event_id = CInt("0" & rs("event_id" )) + 1
  114.         rs.Close
  115.         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) & " )"
  116.         For j = 1 to nbProductsPerOrder
  117.          rs.Open "select count(id) cpt from product where active = 'A'"
  118.      k = Int(Rnd(9) * rs("cpt" ))
  119.      rs.Close       
  120.           rs2.Open "select id from product where active = 'A'"
  121.           rs2.Move k
  122.             product_id = rs2("id" )
  123.             rs2.Close
  124.             quantity = Int(Rnd(9) * 100) + 1
  125.             rs.Open "select max(id) event_line_id from event_line where event_id = " & CStr(event_id)
  126.             event_line_id = CInt("0" & rs("event_line_id" )) +1
  127.             rs.Close
  128.             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')"
  129.         Next
  130.     Next
  131.     Set rs2 = Nothing
  132.     cnx.CommitTrans
  133. End Sub
  135. Sub controlStock(dDay, cnx, rs)
  136.     cnx.BeginTrans
  137.     Dim rs2
  138.     Set rs2 = CreateObject("ADODB.RecordSet" )
  139.     Set rs2.ActiveConnection = cnx
  140.     Dim rs3
  141.     Set rs3 = CreateObject("ADODB.RecordSet" )
  142.     Set rs3.ActiveConnection = cnx
  143.     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
  144.     Dim prevSupplier_id
  145.     Dim event_id
  146.     Dim event_line_id
  147.     Dim remain
  148.     prevSupplier_id = 0
  149.     Do While Not rs.EOF
  150.      If Not IsNull(rs("supplier_id" )) Then
  151.         If prevSupplier_id <> CInt(rs("supplier_id" )) Then
  152.             event_id = purchaseOrder(dDay, cnx, rs2, rs("supplier_id" ), rs("warehouse_id" ))
  153.             event_line_id = 1
  154.             prevSupplier_id = CInt(rs("supplier_id" ))
  155.         End If
  156.         Call purchaseOrderLine(dDay, cnx, event_id, event_line_id, rs("warehouse_id" ), rs("product_id" ), rs("quantitypurchase" ))
  157.         event_line_id = event_line_id + 1
  158.       Else
  159.        ' 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
  160.        remain = CLng(rs("quantityPurchase" ))
  161.    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
  162.         Do While Not rs3.EOF
  163.         event_id = purchaseOrder(dDay, cnx, rs2, rs3("supplier_id" ), rs("warehouse_id" ))
  164.         Call purchaseOrderLine(dDay, cnx, event_id, 1, rs("warehouse_id" ), rs("product_id" ), Min(remain, CLng(rs3("remain" ))))
  165.         remain = remain - Min(remain, CLng(rs3("remain" )))
  166.         If remain = 0 Then
  167.          rs3.MoveLast
  168.        End If
  169.         rs3.MoveNext
  170.       Loop
  171.       rs3.Close
  172.       End If
  173.       rs.MoveNext
  174.     Loop
  175.     rs.Close
  176.     Set rs2 = Nothing
  177.     Set rs3 = Nothing
  178.     cnx.CommitTrans
  179. End Sub
  181. Function purchaseOrder(dDay, cnx, rs, supplier_id, warehouse_id)
  182.     Dim event_id
  183.     rs.Open "select max(id) event_id from event", , 2
  184.     event_id = CInt("0" & rs("event_id" )) + 1
  185.     rs.Close
  186.     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) & " )"
  187.     purchaseOrder = event_id
  188. End Function
  190. Sub purchaseOrderLine(dDay, cnx, event_id, event_line_id, warehouse_id, product_id, quantityPurchase)
  191.     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')"
  192.     cnx.Execute "update stock set qtysupply = (qtysupply + " & CStr(quantityPurchase) & " ) * 1.1 / 2 where warehouse_id = " & CStr(warehouse_id) & " and product_id = " & CStr(product_id)
  193. End Sub
  195. Sub RecieveStock(dDay, cnx, rs)
  196.     cnx.BeginTrans
  197.     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"
  198.     Do While Not rs.EOF
  199.         cnx.Execute "update stock set stock = " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" ))
  200.         cnx.Execute "update event_line set status = 'C' where event_id = " & CStr(rs("id" ))
  201.         cnx.Execute "update event set status = 'C' where id = " & CStr(rs("id" ))
  202.         rs.MoveNext
  203.     Loop
  204.     rs.Close
  205.     cnx.CommitTrans
  206. End Sub
  208. Sub ReserveStock(dDay, cnx, rs)
  209.     cnx.BeginTrans
  210.     Dim rs2
  211.     Set rs2 = CreateObject("ADODB.RecordSet" )
  212.     Set rs2.ActiveConnection = cnx
  214.     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
  215.     Do While Not rs.EOF
  216.         rs2.Open "select stock - reserved available from stock where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" )), , 0
  217.         If CLng(rs2("available" )) > CLng(rs("quantity" )) Then
  218.             cnx.Execute "update stock set reserved = reserved + " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product_id" ))
  219.             cnx.Execute "update event_line set status = 'R' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs("id" ))
  220.         End If
  221.         rs2.Close
  222.         rs.MoveNext
  223.     Loop
  224.     rs.Close
  225.    
  226.     Set rs2 = Nothing
  227.     cnx.CommitTrans
  228. End Sub
  230. Sub CloseOrder(dDay, cnx, rs)
  231.     cnx.BeginTrans
  232.    
  233.     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'"
  234.     Do While Not rs.EOF
  235.         cnx.Execute "update event_line set status = 'C' where event_id = " & CStr(rs("id" )) & " and status = 'R'"
  236.         cnx.Execute "update event set status = 'P' where event_id = " & CStr(rs("id" )) & " and type = 'ORD'"
  237.         rs.MoveNext
  238.     Loop
  239.     rs.Close
  240.  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"
  241.  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)"
  242.  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"
  243.  cnx.Execute "update event set status = 'C' where status = 'P'"
  245.  cnx.CommitTrans
  246. End Sub
  248. Sub ProcessClaims(dDay, cnx, rs)
  249.  Dim event_id
  250.  Dim killOrder
  251.  event_id = 0
  252.  killOrder = False
  253.  cnx.BeginTrans
  254.  Dim rs2
  255.  Set rs2 = CreateObject("ADODB.RecordSet" )
  256.  Set rs2.ActiveConnection = cnx
  257.  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
  258.  Do While Not rs.EOF
  259.   If Not IsNull(rs("product2_id" )) And Rnd(9) > .2 And (Not killOrder Or event_id <> CLng(rs("event_id" ))) Then
  260.    killOrder = False
  261.    cnx.Execute "update event_line set status = 'D' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs("id" ))
  262.    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')"
  263.    cnx.Execute "update stock set stock = stock - " & CStr(rs("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs("product2_id" ))
  264.   Else
  265.    killOrder = True
  266.    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
  267.    Do While Not rs2.EOF
  268.     cnx.Execute "update event_line set status = 'D' where event_id = " & CStr(rs("event_id" )) & " and id = " & CStr(rs2("id" ))
  269.     cnx.Execute "update stock set reserved = reserved - " & CStr(rs2("quantity" )) & " where warehouse_id = " & CStr(rs("warehouse_id" )) & " and product_id = " & CStr(rs2("product_id" ))
  270.     rs2.MoveNext
  271.    Loop
  272.    rs2.Close
  273.    cnx.Execute "update event set status = 'D' where id = " & CStr(rs("event_id" ))
  274.   End If
  275.   event_id = CLng(rs("event_id" ))
  276.   rs.MoveNext
  277.  Loop
  278.  Set rs2 = Nothing
  279.  rs.Close
  280.  cnx.CommitTrans
  281. End Sub
  283. Function Min(a, b)
  284. If a <= b Then
  285.  Min = a
  286. Else
  287.  Min = b
  288. End If
  289. 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
  43. /*
  44. -- Run from here for "bench.vbs" data clean-up
  45. delete vendor_award
  46. delete event_line
  47. delete event
  48. update bank_account set bank_amount = 0
  49. update stock set stock = 0, reserved = 0, qtysupply = 0
  50. -- Stop here for "bench.vbs" data clean-up
  52. -- Run from here for "filldata.vbs" data clean-up (beware, fillind again database is very slow!)
  53. delete award
  54. delete internalmove
  55. delete address
  56. delete users
  57. delete city
  58. delete suppliement
  59. delete supplier
  60. delete vendor
  61. delete stock
  62. delete bank_account
  63. delete warehouse
  64. delete price
  65. delete vat
  66. delete currencyrate
  67. delete country
  68. delete currency
  69. delete productreplacement
  70. delete product
  71. -- Stop here for "filldata.vbs" data clean-up
  72. */


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 Suivante
Page Précédente
Haut de page 

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

Forum MesDiscussions.Net, Version 2010.2
(c) 2000-2011 Doctissimo
Page générée en 0.401 secondes

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