在网络上有效率地使用Microsoft SQL Server外文翻译

1、在网络上有效率地使用Microsoft SQL Server夏阳(计算机科学与技术部，中国矿业大学,徐州 221008)摘要：在这篇论文中主要分析如何在网络上有效率地使用Microsoft SQL(结构查询语言) Server。如何使用 ADO 管理 SQL Server 数据库和他们的策略，以及在网络如何远程触发 SQL Server数据库以改进查询速率的关键技术。关键词：SQL Server; ADO; ASP; 数据库1、介绍：微软公司的SQL Server是目前小型网络中常用的数据库管理系统之一。面向这种网络数据库的应用程序也在日益增多,数据库应用程序设计的关键是应用程序代码如何与数据

2、库进行交互。一些应用程序视数据库仅仅为记录的存储。应用程序本身在数据上执行大多数的操作，例如过滤，计算和记录的匹配。其他的应用程序视数据库为一个数据管理发动机,在服务器执行所有的这些数据运算。数据库存取的第一种类型通常是用带索引序列存取方法（ISAM）编写数据库程序的，数据库存取的第二种类型对表示关系的数据库交互的程序是更适当的。微软公司的SQL Server确实是这种类型的数据库。2、关于SQL：结构化查询语言 (SQL) 是微软公司的SQL Server的语言。如果开发者想让应用程序有效的和服务器通信，那么流利地操作这种语言是有意义的。SQL 的有效使用能减少对服务器的磁盘装置读、写的数据

3、量。同时，SQL 的有效使用能将SQL Server访问网络的数据量减到最少。储存磁盘输入输出和网络输入输出是改进应用程序效率的最重要的因数。SQL 的能力之一是它能在服务器端过滤数据，以便只有最少的必需数据回到用户端。使用这些设施将把服务器和用户端之间的网络流量减到最少。这就意谓着服务器必须对客户请求有一定的限制，客户只得到应用程序必需的数据。它总是更有效率在服务器过滤数据, 在应用程序中过滤它并把它发送给客户端。这也应用于被请求的服务器的列。一段应用程序：select from它指述服务器需要返回所有的列数据给用户端，是否用户端应用程序为程序变量的使用有这些列的范围。选择只有名字的必需列将

4、会避免不必要的网络流量。因为重新附加的列将不被返回到用户端应用程序，所以它也将会在表定义变化情况下使你的应用程序变成更强健,除 SQL 语法本身，效率也依赖你的应用程序如何请求来自服务器的一个结果。在一个使用 ODBC 的应用程序中, “how”指由声明选择项决定的在 select之前运行。 当你在默认的数值离开声明选择项的时候,SQL Server以最有效率的方法送给结果。SQL Server 使得你的应用程序将会接受来自一个被立刻设定的默认结果的所有列。 因此,你的应用程序必须为不被立刻用但在稍后可能被需要的所有行设立缓冲区。这个缓冲需求对你所选择（用SQL）的数据是尤其重要的。看起来它是

5、很有效的请求一个默认的结果集和获取你的应用程序所需要的数据，但这是错误的措施。如果没有从默认的结果集获得需要的数据，可以建立你和服务器的连接，能阻断相同处理其他的工作。同样的，如果没有从默认的结果集获得需要的数据，能导致 SQL 服务器在服务器支撑闭锁,尽可能地阻止其他的使用者更新。这种隐藏的问题可能在小规模的测试中不会出现，但是当应用程序正在运行的时候，它能变得比较迟。这里讲的是快速，简单的从默认的结果集获得需要的数据。一些应用程序不能从服务器请求得到所有的缓冲区数据。举例来说, 查询一个大表而且允许使用者指定选择标准的应用程序, 可能没返回列或返回数以百万计列。使用者当然不想要见到这样的结

6、果。于是，使用者要精确的定义查询选择标准才更有可能得到预期的结果。这种情况，只有通过使用者丢弃获取或缓存区的数据，这是一个时间和资源的废料。对一些类似这样的应用程序，SQL Server提供服务器光标，光标允许一个应用程序从一个任意大的数据集获取少量或整块数据。如果使用者想要见到来自相同的结果组的其他记录, 一个服务器光标应让应用程序获取来自结果的任何行值，包括下一个N行、早先的N行或者在结果集中从某一行编号开始的N行。SQLServer只有需要的时候，才开始工作,完成每一个数据快的请求。SQL Server 通常不支持从服务器光标上接受来的数据快的封锁。服务器光标也允许应用程序对获取行做更新

7、或删除操作，但是表的视图和行的主键值是不能改变的。如果在获取数据和请求更新同时进行的时候，行数据的值改变了，SQL Server发现问题并且避免丢失更新。所有服务器光标的这些功能是以时间浪费为代价的。如果你的程序将要运用来自 SELECT 指令的所有结果，服务器光标总是要比默认的结果集花费更多的时间。默认的结果集总是要在用户端和服务器之间通信。而且，一些类型的服务器光标（动态的）只能被限制使用唯一的索引, 其他的类型（键盘和静态光标）在服务器端做临时存储用。因为这些原因，所以只有应用程序需要它起作用的时候，才会用到服务器光标。3 增加查询速度的方法3.1 设计思想：由 SQL Server提供

8、的 SQLEnterpriseManager 工具能创建和操作数据库。而且这是很有用的,使用者们通过它能够远程操作用SQL Server设计的管理数据库，应用程序需要这种特殊的管理数据库和设备。为了要连接数据库, 你应该选择某一个数据库访问接口。虽然有能在网络上被选择的许多接口，如 RDO和DAO 的继承，最新的数据库访问接口ADO可能是最好的选择,因为ADO可以实现以浏览器为基础的数据库应用程序。远程操作SQL Server在已经存在的数据库上创建临时的视图和表，这种设备能够为下一次的查询存储以前的结果。它可以减小查询范围和提高查询速度。这种方法并不复杂。在配置 ODBC 之后，使用 ASP

9、 的服务器对象创建连接，然后，操作来自ADO的数据库。详细的方法如下列各项:1、 产生执行的句子：strSQL create =“CREATE VIEW dbo.“&session (“view name”)&“ AS SELECT TqueryView . FROM TqueryView where ” &textfield;2、和数据库建立连接：Set objPagingConn =Server.CreateObject(“ADODB. Connection”) objPagingConn.Open“Tlogin”,“sa”;3、 运行应用程序并且创建临时视图：ob

10、jPagingRS. Open session (“querystring ”) ,objPagingConn, 3,13. 2 现存问题和解决方案我们知道，在使用CREATEDATABASE语句创建一个数据库之前，必须存在一个有剩余空间的数据库设备，或者事先使用DISKINIT语句创建一个新设备。但是这些语句含有很多必需的参数，而且如果不使用SQLServer的管理工具，很多参数值往往难以确定。 以创建数据库设备的DISKINIT语句为例，这个语句的完整语法如下：DISKINIT NAME=logical_name', PHYSNAME=physical_name', VDE

11、VNO=virtual_device_number, SIZE=number_of_2K_blocks,VSTART=virtual_address其中NAME和SIZE这两个参数都很容易得到，麻烦的是物理名PHYSNAME和虚拟设备号VDEVNO这两个参数。前者要求是一个服务器上的物理文件全路径名；后者要求在1255之间找一个没有被别的设备占用的号码。而在编写数据库管理程序时，用户的服务器上有哪些设备号已经被占用，SQLServer装在哪个驱动器上，都是无法预料的。虽然，使用SQLServer的管理工具SQLEnterpriseManager，但是，这个工具仍然要求我们输入很多不太常用的参数

12、。如果想要在网络上操纵SQLServer将会变的很复杂。为了实现这样的目标，我们提出下列的方案：1） 创建设备的语句参数创建设备的语句即前面提到的DISKINIT语句。为了简化问题，我们可以指定与数据库名相同的设备文件名，并将设备文件保存在主设备所在的子目录中。数据库名是在设计应用程序时已经确定；而主设备所在的子目录，可以从系统表sysdevices中查询得到。这样，设备文件的物理名参数就确定下来了。虚拟设备号的问题则比较复杂，因为sysdevices系统表中没有“虚拟设备号”这样一个字段，因此，必须另想办法。对SQLServer的系统存储过程sp_helpdevice进行分析之后，我们发现，

13、虚拟设备号是“隐藏”在sysdevices系统表的low字段中的，借助另一个系统表spt_values，可以找到每个设备的虚拟设备号。这样，我们只需要在一个循环中找一下某个设备号是否存在于sysdevices中，就可以确定我们现在可用的虚拟设备号。至于数据库设备的大小，我们不妨设得大一些，或者让用户指定一下也可以。2） 创建数据库的语句参数创建数据库的语句如下： CREATEDATABASEdatabase_name ONDEFAULTdatabase_device=size ,database_device=size. LOGONdatabase_device=size ,database_

14、device=size. FORLOAD 其中，大部分参数都是可选的，我们只需要指定一个设备名及数据库的大小即可，而数据库名、设备名、大小在创建设备的时候已经确定好了，所以，这个语句的参数不存在问题。3. 3 以下是一些用于数据库及其设备管理的常用函数。 1）取当前的工作数据库由于管理任务一般都必须在主库中完成，因此，在执行管理任务之前，最好保存当前工作库，以便完成任务之后再切换回去。PublicFunctionSQLGetCurrentDatabaseName(CnAsADODB.Connection)AsString DimsSQLAsString DimRSAsNewADODB.Reco

15、rdset OnErrorGoToerrSQLGetCurrentDatabaseName sSQL=“selectCurrentDB=DB_NAME()" RS.OpensSQL,Cn SQLGetCurrentDatabaseName=Trim(RS!CurrentDB) RS.Close ExitFunction errSQLGetCurrentDatabaseName: SQLGetCurrentDatabaseName=“" EndFunction 2） 判断一个数据库设备是否存在PublicFunctionSQLExistDeviceName(CnAsADODB

16、.Connection,sDevNameAsString)AsBoolean （按照名称判断一个设备是否存在，如果存在，返回1，否则返回0） DimsSQLAsString DimRSAsNewADODB.Recordset DimbTmpAsBoolean OnErrorGoToerrSQLExistDeviceName sSQL=“sDevName”'" RS.OpensSQL,Cn IfRS!CntDev=0ThenbTmp=FalseElsebTmp=True RS.Close SQLExistDeviceName=bTmp ExitFunction errSQLEx

17、istDeviceName: SQLExistDeviceName=False EndFunction 3）判断一个虚拟设备号是否被占用：SQLExistDeviceNumber。4）找一个最小的尚未被占用的虚拟设备号：SQLGetUnusedDeviceNumber。 5）取得SQLServer安装目录下的DATA子目录路径：SQLGetDataPath。 6）创建一个新设备：SQLCreateDevice。 7）创建一个新的数据库：SQLCreateDatabase65。 8）取数据库设备的详细信息：SQLGetDeviceInfo。 9）扩大数据库设备的尺寸：SQLExpandDevic

18、e。10）判断一个数据库是否存在：SQLExistDatabase。 11）删除一个数据库：SQLDropDatabase。 12）删除一个数据库设备：SQLDropDevice。 13）取SQLServer的版本信息：SQLGetVersionString。 4 结论：这片论文讨论的是SQL Server开发技术。一些问题应该在将来被进一步研究。在摘要中，一个设计很好的SQL Server应用程序：1）高效的使用 SQL 。2）在处理期间减少网络对服务器的往返访问。3）应用存储进程。4）为 SQL Server合并必要的索引和配置步。这些特性即不是所有的都要应用，也不是都不应用。他们将在适当

19、的时候被应用程序组合起来一起使用。参考文献：1 美 Ron Soukup. Microsoft SQL Server 技术内幕M . 姜英, 彭梓东, 孔得志, 等译 北京: 清华大学出版社, 1999.138-156, 533-593。2 汪晓平, 吴勇强, 张宏林, 等 ASP 网络开发技术M 北京: 人民邮电出版社, 2000. 187 229。Using Microsoft SQL Server Efficiently on NetXia Yang (夏阳)(Department of Computer Science and Technology, CUMT, Xuzhou 2210

20、08 )AbstractHow to use Microsoft SQL(structured query language) Server efficiently on net is analyzed. And the key technology about how to use ADO to manage SQL server databases and their devices and how to remotely trigger off the SQL server database on net for improving query speed are mainly disc

21、ussed in this paper.Key words: SQL Server; ADO; ASP; database1 In troduceMicro SQL Server is in common use as database management system on current small network. Applications used to this kind of network database are progressive. Key aspect of database application design is how the application code

22、 interacts with the database. Some applications treat the database simply as a storage place for records. The application itself performs most of the operations on the data, such as filtering, counting, or matching records. Other applications treat the database as a data management engine, performin

23、g all of these data operations at the server. The first style of database access is common in programs written to use an indexed sequential access method ( ISAM ) database. The second style of database access is more appropriate for a program interacting with a relational database. Micro SQL Server

24、is just this kind of database.2 About SQLStructured query language (SQL ) is the language of Micro soft SQL Server. It makes sense that application developers learn to“speak”this language fluently if they want their applications to communicate effectively with the server. Effective use of SQL can mi

25、nimize the amount of data that must be read from and written to disk devices at the server. Simultaneously, effective use of SQL can minimize the amount of data shipped to and from SQL Server across the network. Saving disk I/O and network I/O are the most important factors for improving application

26、 performance.One of the capabilities of SQL is its ability to filter data at the server so that only the minimum data required is returned to the client. Using these facilities minimizes expensive network traffic between the server and client. This means that WHERE clauses must be restrictive enough

27、 to get only the data that is required by the application.It is always more efficient to filter data at the server then to send it to the client and filter it in the application. This also applies to columns requested from the server. An application that issues a SELECT * FROM. . . statement require

28、s the server to return all column data to the client, whether or not the client application has bound these columnsfor use in program variables. Selecting only the necessary columns by name will avoid unnecessary network traffic. It will also make your application more robust in the event of table d

29、efinition changes, because newly added columns wont be returned to the client application. Beyond the SQL syntax itself, performance also depends on how your application requests a result set from the server. In an application using ODBC, the“how is determined by the statement options set prior to e

30、xecuting a SELECT. When you leave the statement options at default values, SQL Server sends the result set with the most efficient way. SQL Server assumes that your application will fetch all the rows from a default result set immediately. Therefore, your application must buffer any rows that are no

31、t used immediately but may be needed later. This buffering requirement makes it especially important for you to specify (by using SQL ) only the data you need.It may seem economical to request a default result set and fetch rows only as your application user needs them , but this is false economy. U

32、nfetched rows from a default result set can tie up your connection with the server, blocking other work in the same transaction. Still worse, unfetched rows from a default result set can cause SQL Server to holdlocks at the server, possibly preventing other users from updating. This hidden problem m

33、ay not show up in small-scale testing, but it can appear later when the application is running. The lesson here is simple-immediately fetch all rows from a default result set.Some applications cannot buffer all the data they request from the server. For example, an application that queries a large t

34、able and allows the user to specify the selection criteria may return no rows or millions of rows. The user is unlikely to want to see millions of rows. Instead, the user is more likely to re-execute the query with narrower selection criteria. In this case, fetching and buffering millions of rows on

35、ly to have them thrown away by the user would be a waste of time and resources.For applications like these, SQL Server offers server cursors that allow an application to fetch a small subset or block of rows from an arbitrarily large result set. If the user wants to see other records from the same r

36、esult set, a server cursor allows the application to fetch any other block of rows from the result set, including the next n rows, theprevious n rows, or n rows starting at a certain row number in the result set. SQL Server does the work to fulfill each block fetch request only as needed, and SQL Se

37、rver does not normally hold locks between block fetches on server cursors. Server cursors also allow an application to do a positioned update or delete of a fetched row without having to figure out the source table and primary key of the row. If the row data changes between the time it is fetched an

38、d the time the update is requested, SQL Server detects the problem and prevents a lost update.All of these features of server cursors come at a cost. If all the results from a given SELECT statement are going to be used in your application, a server cursor is always going to be more expensive than a

39、 default result set. A default result set always requires communication between client and server. Moreover, some types of server cursors ( those declared as dynamic) are restricted to using unique indexes only, while other types ( keyset and static cursors) make heavy use of temporary storage at th

40、e server. For these reasons, only use server cursors where your application needs their features.3One Method of Increasing Query Speed3. 1Design ideaSQLEnterpriseManager tool provided by SQL Server can be used for creating and operating database. But it is best thing for user that they are able to r

41、emotely trigger off the management database designed by SQL Server, which specially manage databases and their devices needed by applications. In order to connect database, you should choice the one of the database access interfaces. Although there are many interfaces that can be selected on net, as

42、 the successor of both RDO and DAO, the newest database access interface ADO may be the best choice, because ADO makes it possible to realize database application based on browser.To remotely trigger off SQL Server for creating temporary view and table on the existed database and its device can stor

43、e the previous results for next querying. It shrinks the range of query as well as enhances query speed. The method is not complicated. After configured the ODBC, you can use the server object of ASP to create connection,then, operate the database from ADO. The detailed method is as follows:1) to ge

44、nerate executable sentencestrSQL create = “CREATE VIEW dbo.“&session(“view name”) &“AS SELECT TqueryView .FROM TqueryView where ” &textfield;2) to connect existed databaseSet objPagingConn =Server. CreateObject(“ADODB. Connection ”) objPagingConn. Open“Tlogin”,“sa”;3) to execute applicat

45、ion and create temporary viewobjPagingRS. Open session (“querystring ”) ,objPagingConn, 3, 1.3. 2Current problem and the method to solve the problemIt is well known that there must exist a database device with residual place before creating a database by using CREATEDATABASE sentence. Other wise we

46、have to use DISKINIT sentence to create a new device. However the sentence includes many essential parameters. We could hardly be sure of the parameters value without using the management tools of SQL. Take the DISKINIT sentence as an example, its complete syntax is as follows:DISKINIT NAME=logical_

47、name', PHYSNAME=physical_name', VDEVNO=virtual_device_number, SIZE=number_of_2K_blocks,VSTART=virtual_addressThe NAME and SIZE are easily fetched. However, the physical name PHYSNAME and the virtual device number VDEVNO are difficult to deal with. The former requires the whole path name of p

48、hysical file within a server; the latter needs to locate a number between 1 and 255 which is not occupied by other devices. While in writing the database management program , it is unexpected where SQL Server has been installed and which device numbers have been occupied. Even though using the SQLEn

49、terpriseManager of SQL Sever, however, we must also input many parameters that are not often used. To remotely trigger off the SQL Server on net will be more complex. In order to solve the problem , we put forward the following scheme.1) Creating sentence parameters for deviceThe DISKINIT sentence i

50、s the sentence for creating device. In order to simplify the problem mentioned above, we may set a device file that has the same name with the database and save it in the subdirectory which has saved master device. The name of the database has already fixed when the application was designed. The sub

51、directory of master device could be inquired from the system table SYSDEVICES. Thus the physical name parameter of the device file is fixed.The problem of virtual device number is pretty complex, because there is no such a field called “virtual device number”in the system table SYSDEVICES. Therefore

52、 we have to try another way.After analyzing the system-stored procedure SP-HELPDEVICE of the SQL Sever, we found that virtual device number was hidden in the LOW field of the system table SYSDEVICES. By using another system table SPT-VALUES, we can find the virtual device number of each device. Ther

53、efore if we can locate in a circle whether a certain device number is in the SYSDEVICES or not, we may find the virtual device number that could be used.As for the size of the database device, we had better make it a little bigger, or let users set it.2) Creating sentence parameters for databaseThe

54、sentence for creating database is as follows:CREATEDATABASEdatabase_name ONDEFAULTdatabase_device=size ,database_device=size. LOGONdatabase_device=size ,database_device=size. FORLOAD Most of the above parameters are optional. We only need to decide the device name and database size. How ever the dat

55、abase name, the device name and the size have already fixed when creating the device. Hence there is no problem about the parameters in this sentence.3. 3Frequently used functions in managing database and its device1) To pick up the current working databaseThe management task is usually completed in

56、 the master database. Therefore we had better save the current working database before the task is executed.In this way we may switch back conveniently after finishing the task.PublicFunctionSQLGetCurrentDatabaseName(CnAsADODB.Connection)AsString DimsSQLAsString DimRSAsNewADODB.Recordset OnErrorGoToerrSQLGetCurrentDatabaseName sSQL=“selectCurrentDB=DB_NAME()" RS.OpensSQL,Cn SQLGetCurrentDatabaseName=Trim(RS!CurrentDB) RS.Close ExitFunction errSQLGetCurrentDatabaseName: SQLGetCurrentDatabaseN