<?xml version='1.0'?>\r\n<Host>\r\n <Instance>.<\/Instance>\r\n <Database>AdventureWorks<\/Database>\r\n<\/Host><\/span><\/pre>\nLe point repr\u00e9sente l’instance locale, si vous avez une instance nomm\u00e9e il faudra renseigner son nom complet. L’authentification est suppos\u00e9e en mode int\u00e9gr\u00e9e, la raison pour laquelle on ne passe pas de user, de mot de passe, etc…<\/p>\n
Un appel au programme s’effectue donc comme ceci:<\/p>\n
DOS>getMsDDLmt -I myConfigFile.xml > maBase.sql<\/span>\r\n<\/pre>\nLes\u00a0 \u00e9tapes critiques \u00e9tant la constitution de la liste d’objets\u00a0 et la partie multithreading, je vais surtout m’attarder sur ces deux points principaux.<\/p>\n
1\u00e8re \u00e9tape: prise de l’environnement et des variables principales ( Main()<\/em> ):<\/h3>\nC’est le v\u00e9ritable d\u00e9but, une fois les param\u00e8tres r\u00e9cup\u00e9r\u00e9s depuis le fichier de config XML, il faut tenter d’initier la connexion \u00e0 l’instance et \u00e0 la base. Quelques tests basiques sont effectu\u00e9s pour trapper une instance qui n’existe pas ou qui ne serait pas d\u00e9marr\u00e9e, par exemple. Tout cela a lieu dans le point d’entr\u00e9e principal du programme:<\/p>\n
\/\/ Validation de la connexion et de la base <\/span>\r\n try\r\n {\r\n Srv = new Server(_srv);\r\n if (Srv.Databases.Contains(_dbname))\r\n {\r\n Db = Srv.Databases[_dbname];\r\n }\r\n else\r\n {\r\n Console.WriteLine(\"La base \" + _dbname + \" n'a pas \u00e9t\u00e9 trouv\u00e9e\");\r\n return;\r\n }\r\n }\r\n catch(Exception exsql)\r\n {\r\n Console.WriteLine(\"Une erreur est survenue dans le module: \"+exsql.Source+\" : \"+exsql.Message);\r\n Console.WriteLine(\"V\u00e9rifiez le nom de l'instance dans le fichier \"+ _xmlcfg +\" et v\u00e9rifiez que celle-ci est bien d\u00e9marr\u00e9e\");\r\n return;\r\n }<\/span>\r\n\r\n \/\/ Test de la version de SQL Server. Support\u00e9 9+<\/span>\r\n if (Srv.Information.Version.Major < 9)\r\n {\r\n Console.WriteLine(\"Version de SQL Server support\u00e9e \u00e0 partir de SQL Server 2005 et plus\");\r\n Usage();\r\n return;\r\n }<\/span>\r\n\r\n \/\/ Revision 1.1: l'appel \u00e0 la m\u00e9thode Script() sur un objet encrypt\u00e9 retourne une exception non g\u00e9r\u00e9e de type PropertyCannotBeRetrievedException.\r\n \/\/ On fetche donc la propri\u00e9t\u00e9 d\u00e8s le d\u00e9part pour pouvoir la tester plus rapidement.<\/span>\r\n Srv.SetDefaultInitFields(typeof(StoredProcedure), \"IsEncrypted\");\r\n Srv.SetDefaultInitFields(typeof(View), \"IsEncrypted\");\r\n Srv.SetDefaultInitFields(typeof(UserDefinedFunction), \"IsEncrypted\");<\/span>\r\n\r\n \/\/ R\u00e9cup\u00e9ration de la liste des objet et alimentation du DataTable.<\/span>\r\n _DT_ObjList = getUsrObjList();<\/span>\r\n<\/pre>\nLa partie int\u00e9ressante est celle qui concerne la Revision 1.1. C’est la mise en place du workaround de Bill Graziano, pour permettre de pr\u00e9-fetcher la propri\u00e9t\u00e9 isEncrypted<\/em> pour les proc\u00e9dures, fonctions et vues, et d’y acc\u00e9der plus rapidement ensuite. La derni\u00e8re ligne affecte au DataTable global _DT_ObjList<\/em> la liste des objets \u00e0 scripter, en utilisant la fonction getUsrObjList()<\/em>.<\/p>\n2i\u00e8me \u00e9tape: lister les objets \u00e0 scripter par les threads ( getUsrObjList() )<\/em>:<\/h3>\nLa liste va constituer le panier dans lequel les worker threads vont venir piocher les objets \u00e0 scripter. Elle est constitu\u00e9e\u00a0 d’une requ\u00eate qui va placer l’object_id et le type d’objet dans un objet DataTable:<\/p>\n
\/* ================================================================================================================\r\n *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Peuplement de La liste des objets \u00e0 scripter par le Main Thread.\r\n *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n * Le plus efficace revient \u00e0 r\u00e9cup\u00e9rer directement la liste des objets user dans un DataTable,\r\n * plut\u00f4t que de boucler sur chaque collection.On ne fait qu'un seul appel \u00e0 la base pour tout\r\n * r\u00e9cup\u00e9rer d'un coup.On ordonne par type asc pour que les vues (V) soient cr\u00e9\u00e9es apr\u00e8s les\r\n * tables (U)\r\n * *\/<\/span>\r\n static DataTable getUsrObjList()\r\n {\r\n String _sqlText = @\"select object_id, type from <\/span>\" + _dbname + @\".sys.objects\r\n where isnull(OBJECTPROPERTY(object_id,'IsMSShipped'),0) = 0\r\n and type in ('P','FN','R','SN','U','V')\r\n and name not in ('sysdiagrams','sp_upgraddiagrams','sp_helpdiagrams',\r\n 'sp_helpdiagramdefinition','sp_creatediagram','sp_renamediagram',\r\n 'sp_alterdiagram','sp_dropdiagram','fn_diagramobjects')\r\n order by type asc<\/span>\";\r\n\r\n DataTable _DT_ObjList = new DataTable();\r\n String _cnStr = \"server=\" + _srv + \";trusted_connection=yes\";\r\n SqlConnection _cnx = new SqlConnection(_cnStr);\r\n SqlDataAdapter _sqlDataAdapt = new SqlDataAdapter(_sqlText, _cnx);\r\n\r\n try\r\n {\r\n _cnx.Open();\r\n }\r\n catch (Exception ex)\r\n {\r\n Console.WriteLine(ex.ToString());\r\n }\r\n\r\n try\r\n {\r\n _sqlDataAdapt.Fill(_DT_ObjList);\r\n }\r\n catch (Exception ex)\r\n {\r\n Console.WriteLine(ex.ToString());\r\n }\r\n\r\n return _DT_ObjList;\r\n }<\/span><\/pre>\nOn effectue d\u00e9j\u00e0 pas mal de filtres, en ne retenant pas les objets li\u00e9s aux diagrammes dans SSMS, et les objets syst\u00e8mes. Malheureusement il n’existe pas de propri\u00e9t\u00e9 pour identifier les fonctions ou proc\u00e9dures qui auraient \u00e9t\u00e9 encrypt\u00e9es, donc ont sera oblig\u00e9 de le faire au moment de la g\u00e9n\u00e9ration. On ne recherche que les objets de type Proc\u00e9dures stock\u00e9es (P), UDF (FN), r\u00e8gles (R), synonymes (SN), tables (U) et vues (V). Les indexes, triggers, et contraintes check seront g\u00e9n\u00e9r\u00e9es lors du passage sur la table donc ils ne sont pas mentionn\u00e9s explicitement dans la liste.<\/p>\n
Les autres objets de la base (la base elle-m\u00eame, les users, roles, sch\u00e9mas, fonctions et sch\u00e9mas de partition, etc…) seront sortis par le thread principal car ils ne pr\u00e9sentent pas de difficult\u00e9 particuli\u00e8re. On choisit \u00e9galement d’extraire les contraintes de cl\u00e9s \u00e9trang\u00e8res apr\u00e8s la g\u00e9n\u00e9ration des tables pour \u00e9viter les probl\u00e9matiques d’ordonnancement des tables dans le script (pour \u00e9viter qu’une contrainte ne soit \u00e9crite par un thread avant que la table \u00e0 laquelle elle fait r\u00e9f\u00e9rence ne soit pr\u00e9sente).<\/p>\n
Le tout est plac\u00e9 dans un objet DataTable _DT_ObjList<\/em>, qui est retourn\u00e9 au Main(), pour \u00eatre ensuite parcouru par chaque worker thread.<\/p>\n3i\u00e8me \u00e9tape: cr\u00e9ation des worker threads et d\u00e9but de script des objets ( initWorker()<\/em> et scriptThis()<\/em> ).<\/h3>\nAvant de rentrer dans le code, il faut peut \u00eatre expliquer un peu la notion de programmation avec plusieurs threads. L’avantage d’une telle philosophie est de pouvoir tirer pleinement parti des ressources de la machine. Le probl\u00e8me, c’est qu’\u00e0 un moment ou \u00e0 un autre, les threads vont entrer en concurrence sur l’aquisition d’une ressource partag\u00e9e. Dans notre cas, il s’agit de _DT_ObjList<\/em>, la liste des objets \u00e0 scripter. Il n’est pas question que deux threads parcourent la liste\u00a0 en m\u00eame temps et d\u00e9cident de scripter le m\u00eame objet. Il va donc falloir trouver un moyen de les synchroniser.<\/p>\nIl existe de nombreuses m\u00e9thodes pour synchroniser des threads entre eux. On distingue en gros deux grandes familles, les constructions utilisateur (comme les spinlocks ou les sections critiques en C++) et les kernel objects, qui sont des objets sp\u00e9cialement con\u00e7us pour \u00e7a, mais qui ont l’inconv\u00e9nient de ne pas \u00eatre manipulables directement dans l’espace d’adressage du programme utilisateur , et donc de n\u00e9cessiter une transition vers le mode kernel \u00e0 chaque fois qu’il sont invoqu\u00e9s.\u00a0 Toutefois, la m\u00e9thode la plus simple\u00a0 pour synchroniser des threads en C# est d’utiliser la m\u00e9thode lock()<\/em> sur un kernel object, c’est donc celle que nous allons utiliser.<\/p>\nConcernant le nombre de threads \u00e0 cr\u00e9er, j’ai d\u00e9cid\u00e9 d’observer la r\u00e8gle suivante:\u00a0 on cr\u00e9\u00e9 1 worker thread\u00a0 pour deux CPU, pour ne pas saturer compl\u00e8tement la machine sur laquelle on effectue l’extraction.\u00a0 La valeur est arrondie au sup\u00e9rieur. Donc si on a 1 ou 2 CPU, on ne cr\u00e9era qu’un seul worker thread, si on a 3 ou 4 CPU, on en cr\u00e9era 2, 5 ou 6 on en cr\u00e9era 3, etc…<\/p>\n
int _wtcnt = 1;\r\nint _corecnt = System.Environment.ProcessorCount;\r\n\r\n if (_corecnt == 1)\r\n {\r\n _wtcnt = 1;\r\n }\r\n else\r\n {\r\n if ((_corecnt % 2) != 0)\r\n {\r\n _wtcnt = (_corecnt \/ 2) + 1;\r\n }\r\n else\r\n {\r\n _wtcnt = (_corecnt \/ 2);\r\n }\r\n }<\/span><\/pre>\nEnsuite on dispatche les worker threads toutes les 1 ms. Le worker va passer par son point d’entr\u00e9e ( initWorker() ) et imm\u00e9diatement bloquer sur le kernel object. En appelant Join()<\/em>, le main thread va attendre que les worker threads lui signalent la fin de la g\u00e9n\u00e9ration des objets, pour qu’il puisse continuer avec les autres objets r\u00e9siduels (foreign keys, fonctions et sch\u00e9mas de partition, etc…) .<\/p>\n\/\/ Dispatch des wt toutes les 1 ms<\/span>\r\n Thread[] _T_wt = new Thread[_wtcnt];\r\n\r\n for (int i = 0; i < _wtcnt; i++)\r\n {\r\n _T_wt[i] = new Thread(new ThreadStart(initWorker));\r\n _T_wt[i].Start();\r\n Thread.Sleep(1);\r\n }<\/span>\r\n\r\n \/\/ Le main Thread attend la fin de l'ex\u00e9cution de chaque wt<\/span>\r\n for (int i = 0; i < _wtcnt; i++)\r\n {\r\n if (_T_wt[i] != null)\r\n {\r\n _T_wt[i].Join();\r\n }\r\n }<\/span>\r\n<\/pre>\nChaque worker thread va dans le m\u00eame temps bloquer sur le kernel object, et le premier qui obtient la ressource peut commencer \u00e0 d\u00e9piler le DataTable pendant que les autres worker threads attendent:<\/p>\n
\/* =================================================================================================================\r\n *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Point d'entr\u00e9e des wt.\r\n *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n * Chaque wt bloque sur le kernel object et d\u00e9pile une ligne du DataTable \u00e0 chaque fois qu'il obtient la ressource.\r\n * Chaque wt op\u00e9re tant qu'il reste des lignes dans le DataTable.\r\n * *\/<\/span>\r\n static void initWorker()\r\n {\r\n Server srv = new Server(_srv);\r\n Database _curdb = srv.Databases[_dbname];\r\n DataRow _DR;\r\n int _objid = 0;\r\n string _type;\r\n\r\n while (_DT_ObjList.Rows.Count > 0)\r\n {<\/span>\r\n \/\/ le wt courant bloque sur le kernel object pour obtenir la ligne courante<\/span>\r\n lock (_DTlocker)\r\n {<\/span>\r\n \/\/ On acc\u00e8de \u00e0 chaque fois \u00e0 la derni\u00e8re ligne, on la stocke et on la supprime pour\r\n \/\/ qu'elle ne soit plus visible aux autres wt.<\/span>\r\n _DR = _DT_ObjList.Rows[0];\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n _objid = ((int)_DR[\"object_id\"]);\r\n _type = ((string)_DR[\"type\"]).Trim();\r\n _DR.Delete();\r\n _DR.AcceptChanges();\r\n }<\/span>\r\n\r\n \/\/ execution du script de l'objet courant<\/span>\r\n try\r\n {\r\n scriptThis(_objid, _type, _curdb);\r\n }\r\n catch (Exception ex)\r\n {\r\n Console.WriteLine(ex.ToString());\r\n }\r\n }\r\n }<\/span>\r\n<\/pre>\nIl acc\u00e8de toujours \u00e0 la derni\u00e8re ligne car \u00e0 chaque fois qu’il en fetche une, il la supprime imm\u00e9diatement apr\u00e8s pour que le worker suivant puisse lire la ligne suivante (qui devient \u00e0 son tour la premi\u00e8re dans la liste). Une fois le kernel object rel\u00e2ch\u00e9, il a stock\u00e9 les informations n\u00e9cessaires (object_id et type d’objet) et peut appeler scriptThis() pour scripter l’objet courant. Pendant ce temps, un autre worker a obtenu la ressource et peut d\u00e9piler son objet, etc… jusqu’\u00e0 ce qu’il n’y ait plus d’\u00e9l\u00e9ments dans le DataTable. Allons voir maintenant ce que fait scriptThis():<\/p>\n
\/* =========================================================================================================\r\n *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Script de l'objet par appel de sa m\u00e9thode SMO Script()\r\n *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n * Types remont\u00e9s par le DataTable:\r\n * 'P','FN','R','SN', 'U','V'\r\n * Revision 1.1: Attention aux objets encrypt\u00e9s, la m\u00e9thode Script() n'est pas invocable.\r\n *\/<\/span>\r\n static bool scriptThis(int _objid, string _type, Database _curdb)\r\n {\r\n switch (_type)\r\n {\r\n\r\n case \"P\":\u00a0\u00a0 \/\/ PROCEDURES STOCKEES -----------------------------------------------------<\/span>\r\n StoredProcedure _sp = _curdb.StoredProcedures.ItemById(_objid);\r\n\r\n if (_sp.IsEncrypted == false)\r\n {\r\n StringCollection _spscr = _curdb.StoredProcedures.ItemById(_objid).Script();\r\n foreach (string _line in _spscr)\r\n Console.WriteLine(_line);\r\n }\r\n Console.WriteLine(\"GO\");\r\n break;\r\n\r\n case \"FN\":\u00a0 \/\/ UDF ----------------------------------------------------------------------<\/span>\r\n UserDefinedFunction _f = _curdb.UserDefinedFunctions.ItemById(_objid);\r\n if (_f.IsEncrypted == false)\r\n {\r\n StringCollection _fscr = _f.Script();\r\n foreach (string _line in _fscr)\r\n Console.WriteLine(_line);\r\n }\r\n Console.WriteLine(\"GO\");\r\n break;\r\n\r\n case \"R\":\u00a0\u00a0 \/\/ RULES -------------------------------------------------------------------<\/span>\r\n StringCollection _ruscr = _curdb.Rules.ItemById(_objid).Script();\r\n foreach (string _line in _ruscr)\r\n Console.WriteLine(_line);\r\n Console.WriteLine(\"GO\");\r\n break;\r\n\r\n case \"SN\":\u00a0 \/\/ SYNONYMS -----------------------------------------------------------------<\/span>\r\n StringCollection _synscr = _curdb.Synonyms.ItemById(_objid).Script();\r\n foreach (string _line in _synscr)\r\n Console.WriteLine(_line);\r\n Console.WriteLine(\"GO\");\r\n break;\r\n\r\n case \"U\":\u00a0\u00a0 \/\/ TABLES UTILISATEUR + CONTRAINTES + TRIGGERS + INDEXES -------------------<\/span>\r\n Table _t = _curdb.Tables.ItemById(_objid);\r\n StringCollection _tablescr = _curdb.Tables.ItemById(_objid).Script();\r\n\r\n foreach (string _line in _tablescr)\r\n Console.WriteLine(_line);\r\n\r\n IndexCollection _indexes = _t.Indexes;\r\n foreach (Index _i in _indexes)\r\n {\r\n StringCollection _iscr = _i.Script();\r\n foreach (string _line in _iscr)\r\n Console.WriteLine(_line);\r\n }\r\n\r\n CheckCollection _checks = _t.Checks;\r\n foreach (Check _c in _checks)\r\n {\r\n StringCollection _cscr = _c.Script();\r\n foreach (string _line in _cscr)\r\n Console.WriteLine(_line);\r\n }\r\n\r\n TriggerCollection _triggers = _t.Triggers;\r\n foreach (Trigger _trg in _triggers)\r\n {\r\n StringCollection _trgscr = _trg.Script();\r\n foreach (string _line in _trgscr)\r\n Console.WriteLine(_line);\r\n }\r\n\r\n \/\/ Pour les contraintes par d\u00e9faut c'est un peu diff\u00e9rent, il faut\r\n \/\/ passer par chaque colonne de la table, et faire attention si la\r\n \/\/ colonne ne contient pas de contrainte par d\u00e9faut ( != null)<\/span>\r\n foreach (Column _col in _t.Columns)\r\n {\r\n if (_col.DefaultConstraint != null)\r\n {\r\n StringCollection _def = _col.DefaultConstraint.Script();\r\n foreach (string _line in _def)\r\n Console.WriteLine(_line);\r\n }\r\n }\r\n\r\n Console.WriteLine(\"GO\");\r\n break;\r\n\r\n case \"V\":\u00a0\u00a0 \/\/ VUES UTILISATEUR -----------------------------------------------------<\/span>\r\n View _v = _curdb.Views.ItemById(_objid);\r\n if (_v.IsEncrypted == false)\r\n {\r\n StringCollection _vscr = _v.Script();\r\n foreach (string _line in _vscr)\r\n Console.WriteLine(_line);\r\n }\r\n Console.WriteLine(\"GO\");\r\n break;\r\n\r\n default:\r\n Console.WriteLine(\"Objet {0} non support\u00e9.\", _type);\r\n break;\r\n }\r\n\r\n return true;\r\n\r\n }<\/span>\r\n<\/pre>\nL’objet est r\u00e9cup\u00e9r\u00e9 en passant son object_id \u00e0 la m\u00e9thode ItemById()<\/em> \u00e0 chaque fois. Et en fonction du type, on applique la m\u00e9thode correspondante avec le principe expliqu\u00e9 au d\u00e9but de cet article. Lorsque le thread a termin\u00e9 la g\u00e9n\u00e9ration, il retourne bloquer \u00e0 nouveau sur le kernel object pour lire la ligne suivante. On remarquera le filtrage avec isEncrypted <\/em>pour les proc\u00e9dures, les fonctions et les vues.<\/p>\nConclusion:<\/h2>\n
Au final, le gain de passer en multithread est de plus de 30% rien que sur mon laptop avec 2 threads au lieu d’un seul. En plus en activant PARAMETERIZATION = FORCED, le temps de g\u00e9n\u00e9ration pour AdventureWorks tombe de 1 minute 30 \u00e0 20 secondes. A la prochaine !<\/p>\n
David B.<\/p>\n
![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>