產生SQL Server 的DB Schema文件

網際網路上有許多產生DB schema的作法, 今天看到一個滿簡單的, 介紹給大家:
http://www.codeproject.com/KB/database/SQL_DB_DOCUMENTATION.aspx?msg=2424192#xx2424192xx

作法:

1. 解開附件Script_to_generate_DB_Document.zip
2. 執行Query Analyzer(SQL 2000) 或SQL Server Management Studio(SQL 2005)
3. 選定要製作文件的資料庫(Use xxx)
4. 點選選單, 查詢>結果至>將結果存檔, 或直接按 CTRL+Shift+F
5. 執行Script_to_generate_DB_Document.sql內容

That's all :-)

資料庫存取速度提升

我們通常使用索引來提升SQL指令的執行速度, 其實做大量資料更新時還有一個簡單的地方可以提升速度, 就是使用交易(Transaction), 新增一萬筆資料至MS SQL Server, 大約快了三倍多, 測試程式如下:
1. 不使用交易
Dim cnn As New SqlConnection("server=.;database=sampledb;integrated security=sspi;")
cnn.Open()

Dim MyWatch As New Stopwatch
MyWatch.Start()
Dim mycommand As New SqlCommand()
mycommand.Connection = cnn
Dim n As Integer
Try
mycommand.CommandText = "truncate TABLE ex1;"
mycommand.ExecuteNonQuery()
Catch
End Try

For n = 0 To 10000 - 1
mycommand.CommandText = String.Format("INSERT INTO ex1 (a,b,c) VALUES('a','c',{0})", n + 1)
mycommand.ExecuteNonQuery()
Next
MsgBox("MyWatch.ElapsedMilliseconds=" & MyWatch.ElapsedMilliseconds)

2. 使用交易
Dim cnn As New SqlConnection("server=.;database=sampledb;integrated security=sspi;")
cnn.Open()

Dim MyWatch As New Stopwatch
MyWatch.Start()
Dim mycommand As New SqlCommand()
Dim mytransaction As SqlTransaction = cnn.BeginTransaction
mycommand.Connection = cnn
mycommand.Transaction = mytransaction
Dim n As Integer
Try
mycommand.CommandText = "truncate TABLE ex1;"
mycommand.ExecuteNonQuery()
Catch
End Try

' use transaction to make the process more quick
For n = 0 To 10000 - 1
mycommand.CommandText = String.Format("INSERT INTO ex1 (a,b,c) VALUES('a','c',{0})", n + 1)
mycommand.ExecuteNonQuery()
Next
mytransaction.Commit()
MsgBox("MyWatch.ElapsedMilliseconds=" & MyWatch.ElapsedMilliseconds)

3. 不使用交易, 但採用批次更新, 比第一種快1/5
Dim cnn As New SqlConnection("server=.;database=sampledb;integrated security=sspi;")
cnn.Open()

Dim MyWatch As New Stopwatch
MyWatch.Start()
Dim mycommand As New SqlCommand()
mycommand.Connection = cnn
Dim n As Integer
Try
mycommand.CommandText = "truncate TABLE ex1;"
mycommand.ExecuteNonQuery()
Catch
End Try

Dim da1 As New SqlDataAdapter()
da1.InsertCommand = mycommand
da1.UpdateBatchSize = 10000
For n = 0 To 100000 - 1
mycommand.CommandText = String.Format("INSERT INTO ex1 (a,b,c) VALUES('a','c',{0})", n + 1)
da1.InsertCommand.ExecuteNonQuery()
Next
MsgBox("MyWatch.ElapsedMilliseconds=" & MyWatch.ElapsedMilliseconds)