ストアドプロシージャを使用した挿入は非常に遅いです -- c# フィールド と sql フィールド 関連 問題

Insert using stored procedure is very slow












0
vote

問題

日本語

DataAdapter バッチを使用して多数のテーブルに挿入しています バッチサイズ= 1000

私は3つの表

を持っています
  1. 学校(ID、名前)
  2. 学生(ID、名前)
  3. 学校学校(学生、studreen_id)
  4. テーブルの学校への約700kの行を挿入しようとしていますが、とても遅いです 私は学校名と学生名をストアドプロシージャーに渡しています

    <事前> <コード> ( @schoolName varchar(100), @studentName varchar(50) ) AS BEGIN transaction declare @scoolId int,@studentId int set @scoolId = (select ID from SCHOOL where [SCHOOL_NAME] = @schoolName) set @studentId = (select ID from STUDENT where STUDENT_NAME = @studentName) INSERT INTO [dbo].SCHOOL_STUDENT (SCHOOL_ID,STUDENT_ID) VALUES (@scoolId,@studentId) commit transaction

    しかしこれは実行されるのに約1時間かかります。 どうやってこれをスピードアップできますか、 school_Id が事前に student_Id でもわからないので、私は常にストアドプロシージャ内でそれらを選択する必要があります。 (より良い方法がありますか)

    アプリケーションの流れはまずすべての学生を挿入してから、すべての学校を挿入してから、それらをテーブルの学校でリンクします。

英語

I'm using DataAdapter Batch to insert to many to many table the batch size = 1000

i have 3 tables

  1. SCHOOL (ID, Name)
  2. STUDENT (ID,Name)
  3. SCHOOL_STUDENT (SCHOOL_ID, STUDENT_ID)

I'm trying to insert around 700K rows to the table SCHOOL_STUDENT but it's very slow i'm passing the school name and the student name to the stored procedure

    ( @schoolName varchar(100), @studentName varchar(50) )  AS BEGIN transaction      declare @scoolId int,@studentId int      set @scoolId = (select ID from SCHOOL where [SCHOOL_NAME] = @schoolName)     set @studentId = (select ID from STUDENT where STUDENT_NAME = @studentName)      INSERT INTO [dbo].SCHOOL_STUDENT                    (SCHOOL_ID,STUDENT_ID)              VALUES                    (@scoolId,@studentId)  commit transaction 

but this takes around 1 hour to run. How can i speed this, as i don't know the school_Id neither the student_Id in advance, then i have to always select them inside the stored procedure. (is there a better way)

the flow of the application is first inserting all students, then insert all schools then link them in the table school_student.

</div
     
   
   

回答リスト

3
 
vote
vote
ベストアンサー
 

効率性/衝撃と労力の順に、おそらく試してみてください:

1-読んでいるテーブルのインデックス作成を確認してください。各テーブルのID列と名前列の索引を確認してください。

2-保存されたプロセッサをこのようにリファクタリングします。

<事前> <コード> INSERT INTO dbo.School_Student(School_ID, Student_ID) SELECT SC.ID, ST.ID FROM dbo.School AS SC JOIN dbo.Student AS ST ON ST.Student_Name = @studentName AND SC.School_Name = @schoolName;

3- Proc

からトランザクションを削除する

4-このPROCを呼び出す前に、すべての学校のIDと学生IDをすべて事前にロードしてください。ループスルーしてIDを渡します。

5- SQLバルクコピー操作を調査します。

 

In order of efficiency/impact and effort, perhaps try:

1- Check your indexing on the tables you're reading. Ensure indexes on the ID and name columns on each table.

2- Refactor your stored proc like this:

 INSERT INTO dbo.School_Student(School_ID, Student_ID)      SELECT SC.ID, ST.ID      FROM dbo.School AS SC      JOIN dbo.Student AS ST ON ST.Student_Name = @studentName                             AND SC.School_Name = @schoolName; 

3- Remove the transaction from the proc

4- Preload all the school ID and student ID before calling this proc. Loop through and pass the ID.

5- Investigate a SQL Bulk Copy operation.

</div
 
 
 
 
2
 
vote

ルックアップを最適化するために、学生と学校の表に索引を作成する必要があります。私はあなたのデータをテーブルに段階的に段階的にして、 <コード> SqlBulkCopy C#から アップロードする。ストアドプロシージャはデータを変換してキーを挿入することができます。

<事前> <コード> CREATE PROCEDURE spSchoolStudentTransform AS BEGIN INSERT INTO [dbo].[School_Student](School_Id, Student_Id) SELECT School.Id, Student.Id FROM SchoolStudent ss JOIN School ON School.School_Name = ss.SchoolName JOIN Student ON Student.Student_Name = ss.StudentName; TRUNCATE TABLE SchoolStudent; END GO CREATE TABLE SchoolStudent ( Id INT IDENTITY PRIMARY KEY ,StudentName VARCHAR(50) NOT NULL ,SchoolName VARCHAR(100) NOT NULL ); GO CREATE NONCLUSTERED INDEX ixStudentIdStudentName ON Student (Id, Student_Name); GO CREATE NONCLUSTERED INDEX ixSchoolIdSchoolName ON School (Id, School_Name); GO using (var connection = new SqlConnection(connectionString)) { using(var sqlBulkCopy = new SqlBulkCopy(connection)) { sqlBulkCopy.DestinationTableName = "SchoolStudent"; sqlBulkCopy.EnableStreaming = true; sqlBulkCopy.BatchSize = 1000; sqlBulkCopy.WriteToServer(dataReader); } }
 

You should create indexes on your Student and School tables to optimize lookups. I would also stage your data into a table and use SqlBulkCopy from C# to upload it. A Stored Procedure could transform the data and insert the keys.

CREATE PROCEDURE spSchoolStudentTransform AS BEGIN      INSERT INTO [dbo].[School_Student](School_Id, Student_Id)     SELECT School.Id, Student.Id FROM SchoolStudent ss     JOIN School     ON School.School_Name = ss.SchoolName     JOIN Student     ON Student.Student_Name = ss.StudentName;      TRUNCATE TABLE SchoolStudent; END GO  CREATE TABLE SchoolStudent (      Id INT IDENTITY PRIMARY KEY     ,StudentName VARCHAR(50) NOT NULL     ,SchoolName VARCHAR(100) NOT NULL ); GO  CREATE NONCLUSTERED INDEX ixStudentIdStudentName ON Student (Id, Student_Name); GO  CREATE NONCLUSTERED INDEX ixSchoolIdSchoolName ON School (Id, School_Name); GO  using (var connection = new SqlConnection(connectionString)) {     using(var sqlBulkCopy = new SqlBulkCopy(connection))     {         sqlBulkCopy.DestinationTableName = "SchoolStudent";         sqlBulkCopy.EnableStreaming = true;         sqlBulkCopy.BatchSize = 1000;          sqlBulkCopy.WriteToServer(dataReader);     } } 
</div
 
 
       
       
0
 
vote

手順自体はかなり良いです。問題は1000回呼び出されます。ここでは、コールごとに接続切断するため、パフォーマンスコストが得られます。

XML、またはそれぞれバッチごとに100から500程度の区切りの区切りの構文を受け入れる手順を変更します。

バッチインサート中に何かが発生した場合、それをステージングテーブル(ダミー、または物理的)に最初に保存します。学校の名前と学生の名前を保存してください。挿入中に選択しないでください。

最後に、ステージングテーブルからトランザクション/マスターテーブルへのすべてのレコードを挿入するストアプロシージャを実行します。より大きなレコードを持つテーブルから選択を使用すると、1で1を実行するよりも優れています。

トランザクション/マスターテーブルからトランザクション/マスターテーブルへの挿入を行う前にデータを再検証することを忘れないでください.NULL(not founs)データ、重複データ、文字列区切り文字などによる破損データを確認します。

最後に、あなたのコメントに記載されているように、パフォーマンスを向上させるためにいくつかの索引付けとパフォーマンスの調整を行います。それは正しく処理された場合は10分以内に実行時間を与えるはずです。

 

The procedure itself is quite good. The problem is you call it 1000 times, where you will get performance cost because it will connect-disconnect per call.

Modify the procedure to accept xml, or delimited syntaxes that you can pass around 100 to 500 per batch each.

Store it first to staging table (dummy, but physical), that it will not harm the transactional/master table if something happen during the batch insert. Store the school name and student name, don't do any select during the insert.

Last, execute a store procedure which will insert ALL the record from staging table to transaction/master table. Using select from table with larger records perform better than doing 1 by 1 select.

Don't forget to re-validate the data before doing insert from staging to transaction/master table, check for null (not found) data, duplicate data, corrupt data due to string delimiter or etc.

Last, as mentioned in your comment, do some indexing and performance tuneup for better performance. It should give you less than 10 mins execution time if handled correctly.

</div
 
 

関連する質問

62  データベース内の固有のインデックス付き列値をスワップします  ( Swap unique indexed column values in database ) 
データベーステーブルと、一方のフィールド(プライマリキーではありません)が一意のインデックスを持つことです。これで、この列の下に2行の値を交換します。これはどうやって行うことができますか?私が知っている2つのハック: 行の両方を削除してそれらを再挿入します。...

26  SQL全文索引付けが#を含む単語の結果を返すのはなぜですか?  ( Why doesnt sql full text indexing return results for words containing ) 
例えば、SQL Server 2005を使用して、次のようなマイクエリは次のようなものです。 <事前> <コード> SELECT * FROM Table WHERE FREETEXT(SearchField, 'c#') 使用時に結果を返す列Searc...

2520  データベースのインデックス作成はどのように機能しますか? [閉まっている]  ( How does database indexing work ) 
閉鎖。この質問はもっと焦点を絞ったにする必要があります。現在答えを受け付けていません。 この質問を改善したいですか?このPOST...

50  CX_ORACLE:結果セットをどのように繰り返すのですか?  ( Cx oracle how do i iterate over a result set ) 
結果セットを繰り返す方法はいくつかあります。それぞれのトレードオフは何ですか? ...

126  フラットファイルデータベース[閉じる]  ( Flat file databases ) 
閉鎖。この質問は意見ベースです。現在答えを受け付けていません。 この質問を改善したいですか?この記事を編集することで事実や引用を...

146  SQL Serverテーブルへの変更を確認してください。  ( Check for changes to an sql server table ) 
トリガを使用せずにテーブルへの変更のためにSQL Serverデータベースを監視する方法やデータベースの構造を変更する方法はありますか。私の優先プログラミング環境は、 .NET とc#。 私は SQL Server 2000 SP4以降をサポートできるように...

43  ASP.NETサイトマップ  ( Asp net site maps ) 
誰もがSQLベースのASP.NETサイトマッププロバイダを作成する経験がありますか? 私は私のメニューとSiteMappathコントロールと正しく機能しているデフォルトのXMLファイル web.sitemap を持っていますが、私のサイトのユーザーが動的にペー...

65  C#/ vb.netでのT-SQLキャストをデコードする  ( Decoding t sql cast in c vb net ) 
最近、当社のサイトは、 asprox botnet SQL Injection 攻撃。詳細に入ることなく、攻撃は t-sql コマンドをエンコードすることによってSQLコードを実行しようとします。 ASCIIエンコードされたバイナリ文字列。それはこのような...

125  データベース構造の変更のバージョン管理システムはありますか?  ( Is there a version control system for database structure changes ) 
私はしばしば次の問題に遭遇します。 データベース内の新しいテーブルまたは列を必要とするプロジェクトにいくつかの変更を処理します。データベースの変更を加えて、仕事を続けます。通常、私はそれらがライブシステムで複製できるように変更を書き留めることを忘れないでくださ...

66  データベース列を索引付けする方法  ( How do i index a database column ) 
うまくいけば、私はデータベースサーバーごとに回答を受けることができます。 索引付けの仕組みの概要については、データベースの索引付けがどのように機能しますか? ...




© 2022 cndgn.com All Rights Reserved. Q&Aハウス 全著作権所有