Clever Geek Handbook

異種のランダムデータまたは17時間の待機でMS SQLデータベースを埋める方法

良い一日

開発者は、多くの場合、大量のデータに対してデータベーステストを実施するタスクを持っていますが、このまさにデータをどこで取得するのでしょうか？ 結局のところ、データベースの構造が50を超えるテーブルに到達する可能性があることは誰もが知っているので、実際には手で記入する必要はありません。 また、値が他のテーブルに関連付けられている外部キーと複合主キーについて考えると、冷却をオフにすると、古いAMDに比例してヘッドが加熱し始めます。

インターネットでは、.NET、C ++、Javaなどを使用してデータベースにランダムな値を入力するための多くのソリューションがあります。 この記事では、MS SQL Serverの制御下でT-SQLツールを使用してデータベースにランダムな値を入力するトピックについて説明します。



はじめに



数日前、MS SQL Serverを実行しているデータベースにランダムデータを入力するタスクがありました。 さらに、実装全体はT-SQLによってのみ実行する必要があります。 リソースに関するこのようなソリューションを長時間検索した後、私は自分でやらなければならず、仕事に取りかかる必要があるという結論に達しました。 （最近まで） T-SQLの専門家ではなく、大学の「データベース」コースの知識しか持っていなかったため、実装は非常に「松葉杖」で遅く（主な問題）、動作していました。



この記事の主な目的は、ソリューションを最適化する可能性、または既製の実装へのリンクを含むCtrl + AとShift + DelをHabrコミュニティと議論することです。



そして、入り口にあったもの：



あなたはこれらすべてに関係していました：



実装



実装全体が、他の各プロシージャを呼び出すように見えました。



各手順について詳しく説明することを提案します。 （読者とは、著者とは異なり、 SQLの基本に精通していることを前提としています）



randomString 
CREATE PROCEDURE [dbo].[randomString] @inputSize int, @outputRandomString nvarchar(max) output AS BEGIN --        . END;




MS SQLフォーラムで出会った最初の実装オプションの1つを使用しました。 このプロシージャは、入力として文字列の長さを受け取り、目的のサイズのタイプNVARCHAR（MAX）のランダム文字の文字列を出力します。 この場合、大量のデータに対する深刻な時間コストがないため、実装は重要ではありません。 さらに進んでいます。



randomInt 
 CREATE PROCEDURE [dbo].[randomInt] @inputSize int, @outputInteger int output AS BEGIN DECLARE @TEMP bigint SET @TEMP = SUBSTRING('999999999999999999',1,@inputSize) SET @outputInteger = (ABS(CHECKSUM(NewId())) % @TEMP) END




機能は小さく、あまり美しくありません（特にSUBSTRINGがある場所）が、その速度には私に合っていたので、今のところはそのままにして先に進みます。



generateDataByType 
 CREATE PROCEDURE [dbo].[generateDataByType] @tableName nvarchar(40), --  ,      @inputColumName nvarchar(40), --  ,      @inputType nvarchar(10), @inputSize int, @outputString nvarchar(max) output --  AS BEGIN DECLARE @isFK bit = 0; DECLARE @FKName NVARCHAR(MAX); DECLARE @ParentTable NVARCHAR(MAX); -- @tableName     FK (ccu.table_name)     (references_table)           DECLARE columnsCursor1 CURSOR FOR SELECT kcu.column_name, ccu.table_name AS references_table FROM information_schema.table_constraints tc INNER JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name INNER JOIN information_schema.referential_constraints rc ON tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = rc.constraint_schema AND tc.constraint_name = rc.constraint_name AND tc.constraint_type = 'FOREIGN KEY' INNER JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name WHERE tc.table_name = @tableName OPEN columnsCursor1; FETCH NEXT FROM columnsCursor1 INTO @FKName,@ParentTable --     WHILE @@FETCH_STATUS = 0 BEGIN -- ,        @inputColumName       IF (@inputColumName = @FKName) BEGIN SET @isFK = 1; --   true -   FK         . DECLARE @selectedPK NVARCHAR(MAX); DECLARE @params NVARCHAR(MAX); --           ,         SET @selectedPK = N'SELECT TOP 1 @outputString =' + @FKName + ' FROM ' + @ParentTable + ' ORDER BY NEWID(); '; SET @params = N'@FKName NVARCHAR(MAX), @ParentTable NVARCHAR(MAX), @outputString NVARCHAR(MAX) OUTPUT'; EXEC sp_executesql @selectedPK , @params, @FKName = @FKName, @ParentTable = @ParentTable, @outputString = @outputString OUTPUT; END FETCH NEXT FROM columnsCursor1 INTO @FKName,@ParentTable END; CLOSE columnsCursor1; DEALLOCATE columnsCursor1; --          . IF (@isFK <> 1) BEGIN IF (@inputType = 'nvarchar') BEGIN EXECUTE randomString @inputSize, @outputRandomString = @outputString OUTPUT ; END ELSE --          . END




したがって、「メイン」手順に到達する前に、見つかった親テーブルのデータをテーブルの外部キーに入力する際に膨大な時間コストがかかります。 与えられた範囲の乱数を置き換えることでこの検索に気付くと、パフォーマンスが大幅に向上します。 おそらく、問題はシステムテーブルとランダムソートからのSELECT'eにあります。 比較のため：FKなしで100万行をテーブルに書き込むには約20分かかり、FKありで100万行をテーブルに書き込むには17時間以上かかります。 参考までに、1つのフィールドにクリーンなINSERTを使用して100万行を書き込むには6〜10秒かかります。

現時点では、この記事を書くきっかけになった最適なものを見つけることはできませんでしたが、それについては結論を出しました。



insertRandomData 
 CREATE PROCEDURE [dbo].[insertRandomData] @childTableName nvarchar(MAX), @insertRowCount int AS BEGIN DECLARE @i int = 0 /*  */ DECLARE @columnName NVARCHAR(30); DECLARE @columnType NVARCHAR(10); DECLARE @columnLenght INT; DECLARE @columnUniq INT; /*      */ DECLARE @insertQuery NVARCHAR(MAX); DECLARE @insertColumnsQuery NVARCHAR(MAX); DECLARE @insertValuesQuery VARCHAR(MAX); DECLARE @params NVARCHAR(MAX); SET @insertColumnsQuery = ''; SET @insertValuesQuery = ''; begin transaction WHILE (@i < @insertRowCount) BEGIN DECLARE columnsCursor CURSOR FOR ----------        @childTableName    ----- SELECT all_columns.column_id, all_columns.name, systypes.name, all_columns.max_length FROM SYS.all_objects join SYS.all_columns on all_columns.object_id = all_objects.object_id join SYS.systypes on all_columns.system_type_id = systypes.xtype WHERE all_objects.name like @childTableName and all_objects.type = 'U' AND systypes.name <> 'sysname'/*-     2   ( )*/ ORDER BY all_columns.column_id; OPEN columnsCursor; --  ,   ID- (   ) FETCH NEXT FROM columnsCursor INTO @columnUniq, @columnName, @columnType, @columnLenght; FETCH NEXT FROM columnsCursor INTO @columnUniq, @columnName, @columnType, @columnLenght; DECLARE @tempLenght INT = 0; WHILE @@FETCH_STATUS = 0 BEGIN /*   (   -1   30) , -1   MAX  ,         30 . */ IF(@columnLenght >= 0) BEGIN SET @tempLenght = @columnLenght; END ELSE BEGIN SET @tempLenght = 30; END --    INSERT,   . SET @insertColumnsQuery = @insertColumnsQuery + @columnName + ', '; DECLARE @TEMPValues nvarchar(MAX) = '' ---  generateStringByType,    TEMPValues--- EXECUTE generateDataByType @childTableName, @columnName, @columnType, @tempLenght, @outputString = @TEMPValues OUTPUT --     INSERT,  . SET @insertValuesQuery = @insertValuesQuery +'''' + @TEMPValues + ''',' FETCH NEXT FROM columnsCursor INTO @columnUniq, @columnName, @columnType, @columnLenght; END; --         INSERT SET @insertColumnsQuery = SUBSTRING(@insertColumnsQuery, 1, LEN(@insertColumnsQuery)-1); SET @insertValuesQuery = SUBSTRING(@insertValuesQuery,1, LEN(@insertValuesQuery)-1); --       . SET @insertQuery = N'INSERT INTO ' + @childTableName + N' (' + @insertColumnsQuery + N') VALUES (' + @insertValuesQuery + ') ;'; EXEC(@insertQuery); --




この手順は「比較的」時間がかかりますが、システムテーブルに忍び込んで入力に来たテーブルの構造を取得しますが、いくつかの明らかな弱点があります。 たとえば、テーブルの最初の要素をPKであると期待してジャンプします。



おわりに



上記は、そのような解決策を見つけることができなかったので、作者が心から望んでいる誰かにとって有用です。 しかし、裁判所にコミュニティに提出された決定は、時間の面で最適ではなく、重大な変更が必要です。 興味のある人が皆、私にそれを思い起こさせる（それが理にかなっているなら）か、別の道筋を示すのを助けることを願っています。


More articles:


All Articles