利用ADO Command加速SQL的效率

當我們利用ADO物件執行SQL指令時，必須經過SQL的Compiler將指令編譯完才可以執行！如果
我們執行SQL的次速非常頻繁，那麼透過Command的prepared屬性可以將SQL指令事先編譯完成
，當我們每次執行SQL的時候就不需再編譯，以下為VB的範例程式碼。

範例：
Dim objConn as new ADODB.Connection
Dim objCmd as new ADODB.Command
Dim objRS as ADODB.ResultSet
Dim i as Integer
Dim strParam as String

objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;UserId=admin;" + _
"Password=;Data Source=NWIND.mdb;"
objConn.Open

'SQL指令未編譯
For i = 1 to 1000
strParam = "Genen Shouyu"
Set objRS = objConn.Execute("SELECT * FROM Products WHERE productname = '" + _ strParam + "'")
Next

'SQL指令事先編譯
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT * FROM Products WHERE productname = ?"
objCmd.Prepared = True
objCmd.Parameters.Append objCmd.CreateParameter(, adBSTR, adParamInput)
For i = 1 to 1000
objCmd.Parameters(0).Value = strParam
Set objRS = objCmd.Execute
Next

objConn.Close

SQL指令未經過編譯執行時間：3.755秒
SQL指令事先編譯執行時間：2.834秒

透過這個範例就可以看到兩者的差別！以這個資料庫的Products的資料為77筆，所以時間
不太明顯，如果資料上千筆或上萬筆將有可看出明顯差異，除了資料量外在UPDATE、
INSERT、DELETE也可以應用上去。

尋找Visual Basic 6 Secrets書藉

本來只是去Google Book搜尋ActiveX Database Object，列出來的其中一本書Visual Basic 6 Secrets，看了這本書的Index就想去買這本書，因為內容介紹許多Advanced技巧。

Book Description
Visual Basic 6 offers a series of new enhancements that enable programmers to write better applications faster than ever. Take advantage of these new features, along with Harold Davis' expert advice and techniques, with Visual Basic® 6 Secrets®. You explore the ins and outs of this sophisticated programming language and learn a variety of approaches for expanding your programming potential.

Among the topics covered by Visual Basic 6 Secrets are complete coverage of Visual Basic's new Web tools and Internet features, creating ActiveX applications and controls, advice on database development, and instruction on producing full-featured Dynamic HTML Web applications.

Visual Basic 6 Secrets is accompanied by a CD-ROM containing third-party evaluation software, including Help Composer, RoboHTML, SpyWorks, InstallShield Express, and much more.

另外，在Visual Basic Secrets這個網站主要介紹的是在VB如何使用指標包含VarPtr()、StrPtr() 、AddressOf的介紹。

Visual Basic Secrets
http://www.thevbzone.com/secrets.htm


Visual Basic 6 Secrets 部分內容PDF檔

最近透過ADO的Recordset Object和Command Object去存取資料庫的資料，發現這兩個物件都有Properties可以設定，並且分成Connection Object、Command and Recordset Object、Table Object、Column Object、Index Object來介紹，下面的表格列了幾個Properties作參考！

Property Name	Description
Cache Authorization	A Boolean value (read-only) that indicates whether the data source is allowed to cache sensitive authentication information, such as a password, in an internal cache. For the Microsoft Jet provider, this value is always True.
Encrypt Password	A Boolean value (read-only) that indicates whether the password can be sent without encryption. For the Microsoft Jet provider, this value is always False.
Mask Password	A Boolean value (read-only) that indicates whether the consumer requires that the password be sent to the data source in a masked form.
Password	A String value (read/write) that specifies the password to be used when connecting to the data source.
Persist Encrypted	A Boolean value (read-only) that indicates whether the data source object saves sensitive authentication information in an encrypted format. For the Microsoft Jet provider, this value is always False (no encryption).
Persist Security Info	A Boolean value (read-only) that indicates whether the data source object can save sensitive authentication information. For the Microsoft Jet provider, this value is always False.
User ID	A String value (read/write) that specifies the user ID to be used when connecting to the data source.
Asynchronous Processing	A Long value (read/write). Not supported by the Microsoft Jet provider.
Data Source	A String value (read/write) that specifies the name of the data source. Typically this consists of the path and file name of the data source.
Window Handle	A Long value (read/write) that specifies the window handle to be used if the data source object needs to prompt the user for additional information.
Locale Identifier	A Long value (read/write) that specifies the locale ID (LCID) to use when opening a database. The LCID specifies the database's collating order, which is the character set that will be used to determine how values in the database are sorted. This property does not guarantee that all text returned to the consumer will be translated according to the LCID. The Locale Identifier property can also be used to specify the LCID when you create a new database by using the Create method of the ADOX Catalog object. See Locale Identifier Property Settings later in this document for a listing of the values that can be assigned.
Mode	A Long value (read/write) that specifies a bitmask that indicates access permissions. When read from the Properties collection, the value returned is a number. When using the Mode property of the Connection object, you can use built-in constants, such as adModeRead. The Mode property can be one of the following values: Read-only (adModeRead) 1 Write (adModeWrite) 2 Read/Write (adModeReadWrite) 3 Share Deny Read (adModeShareDenyRead) (Prevents others from opening in read mode.) 4 Share Deny Write (adModeShareDenyWrite) (Prevents others from opening in write mode.) 8 Share Deny Exclusive (adModeShareDenyExclusive) (Prevents others from opening in read/write mode.) 12 Share Deny None (adModeShareDenyNone) (Neither read nor write access is denied to others.) 16
Prompt	A Long value (read/write) that specifies whether to prompt the user for connection information during initialization. The Prompt property can be one of the following constants: Prompt Always (adPromptAlways) (Always prompt the user for initialization information.) 1 Prompt Complete (adPromptComplete) (Prompt the user only if more information is needed.) 2 Prompt Complete Required (adPromptCompleteRequired) (Prompts the user only if more information is needed. Doesn't allow the user to enter optional information.) 3 Prompt Never (adPromptNever) (Don't prompt the user.) 4
Extended Properties	A String value (read/write) that specifies a string that contains provider-specific connection information that can't be explicitly described through standard ADO properties. For the Microsoft Jet provider, this property is used to pass the Microsoft Jet connection string for opening or creating databases of other file formats. For information about settings, see Extended Properties Property Settings later in this document.

ADO Provider Properties and Settings
http://msdn2.microsoft.com/en-us/library/aa140022.aspx