#
第十四章 使用SQL Shell界面(三) # SQL元数据、查询计划和性能指标 ## 显示元数据 SQL Shell支持`M`或`Metadata`命令以显示有关当前查询的元数据信息。 对于每个结果集项目,此命令列出以下元数据:列名称(SQL字段名称),键入(ODBC数据类型整数代码),PRE(精度或最大长度),比例(最大分数数字),`NULL(BOOLEAN:1 = NULL允许,0 =不允许空值)`,标签(标题标签,请参阅列别名),表(SQL表名称),架构(架构名称),`CTYPE`(客户端数据类型,请参阅`%SQL.statementColumn ClientType`属性)。 ## SHOW STATEMENT 可以执行查询,然后发出show语句或显示`st`以显示准备好的SQL语句。默认情况下,必须执行查询。可以避免通过设置`executemode =延迟`执行查询,从而发出查询,然后发出`show`语句sql shell命令。 显示声明信息包含实现类(缓存查询名称),参数(一个以逗号分隔的实际参数值,如上面条款和`WHERE`子句文字值),和语句文本(文字文本的SQL命令,包括字母大小写和参数值)。 ## EXPLAIN and Show Plan 有两种方式显示SQL查询的查询计划; 如果需要,两者都可以显示备用的查询计划。 - EXPLAIN:前言用解释命令选择SELECT查询。例如: ```sql SQL]USER>>EXPLAIN SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers' ``` - SHOW PLAN:发出查询,然后发出show plan shell命令。例如: ```sql SQL]USER>>SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers' SQL]USER>>SHOW PLAN ``` EXPLAIN SQL命令显示有关指定选择查询的查询计划信息而不执行查询。EXPLAIN Alt允许显示备用查询计划。EXPLAIN Stat返回性能统计信息以及查询计划。EXPLAIN只能用于返回选择查询的查询计划;它不会返回用于执行查询操作的`Insert`,`Update`或`DELETE`语句等其他命令的查询计划。 Show Plan SQL shell命令允许显示SQL Shell成功发布的上次查询的查询计划信息。显示计划可用于执行查询操作的任何SQL命令,包括选择,插入,更新和删除。默认情况下,必须执行查询。可以避免通过设置`executemode=deferred`,执行查询,发出查询,然后发出以下SQL shell命令之一: - SHOW PLAN、SHOW PL(或简单的SHOW)显示关于当前查询的查询计划信息。 查询计划可用于调试和优化查询的性能。 它指定查询的执行方式,包括索引的使用和查询的成本值。 可以返回查询计划的语句有:`SELECT`、`DECLARE`、`non-cursor UPDATE or DELETE`、`INSERT…SELECT`。 该命令有一个`V` (VERBOSE)选项。 - 显示PLANALT显示当前查询的备用显示计划。 该命令有一个`V` (VERBOSE)选项。 可以使用`$SYSTEM.SQL.Explain()`方法从ObjectScript生成查询计划。 ## SQL Shell Performance 成功执行一个SQL语句后,SQL Shell会显示四个语句准备值(`times(s)/globals/cmds/disk`)和四个语句执行值(`times(s)/globals/cmds/disk`): - 语句准备时间是指准备动态语句所花费的时间。 这包括生成和编译语句所花费的时间。 它包括在语句缓存中查找语句所花费的时间。 因此,如果执行了一条语句,然后按编号或名称回收,回收语句的准备时间接近于零。 如果一条语句已经准备好并执行,然后通过发出GO命令重新执行,那么重新执行时的准备时间为零。 - 经过的执行时间是从调用`%execute()`到`%Display()`返回所经过的时间。 它不包括输入参数值的等待时间。 语句`globals`是全局引用的计数,`cmds`是执行的SQL命令的计数,`disk`是磁盘延迟时间,单位是毫秒。 SQL Shell为准备操作和执行操作保留单独的计数。 这些性能值只在`“DISPLAYMODE”`设置为`“currentdevice”`,`“MESSAGES”`设置为`“ON”`时显示。 这些是SQL Shell的默认设置。 # Transact-SQL支持 默认情况下,SQL Shell执行InterSystems SQL代码。 但是,SQL Shell可以用来执行`Sybase`或MSSQL代码。 ## Setting DIALECT 默认情况下,SQL Shell将代码解析为InterSystems SQL。 可以使用`SET DIALECT`来配置SQL Shell以执行`Sybase`或`MSSQL`代码。 若要更改当前方言,请将`“方言”`设置为`Sybase`、`MSSQL`或IRIS。 默认值是`Dialect=IRIS`。 这些设置的方言选项不区分大小写。 下面是一个从SQL Shell中执行MSSQL程序的例子: ```java DHC-APP>DO $SYSTEM.SQL.Shell() SQL Command Line Shell ---------------------------------------------------- The command prefix is currently set to: <>. Enter q to quit, ? for help. DHC-APP>>SET DIALECT MSSQL dialect = MSSQL DHC-APP>>SELECT TOP 5 name + '-' + ssn FROM Sample.Person 1. SELECT TOP 5 name + '-' + ssn FROM Sample.Person Expression_1 yaoxin-111-11-1117 xiaoli-111-11-1111 姚鑫-111-11-1112 姚鑫-111-11-1113 姚鑫-111-11-1114 5 Rows(s) Affected statement prepare time(s)/globals/lines/disk: 0.1989s/46546/257369/114ms execute time(s)/globals/lines/disk: 0.0032s/24/676/3ms --------------------------------------------------------------------------- ``` `Sybase`和`MSSQL`方言支持这些方言中有限的SQL语句子集。 它们支持`SELECT`、`INSERT`、`UPDATE`和`DELETE`语句。 它们对永久表支持`CREATE TABLE`语句,但对临时表不支持。 支持创建视图。 支持创建触发器和删除触发器。 但是,如果`CREATE TRIGGER`语句部分成功,但在类编译时失败,则此实现不支持事务回滚。 支持`CREATE PROCEDURE`和`CREATE FUNCTION`。 ## Setting COMMANDPREFIX 可以使用`SET COMMANDPREFIX`指定必须追加到后续SQL Shell命令的前缀(通常是单个字符)。 在SQL Shell提示符发出的SQL语句中不使用此前缀。 这个前缀的目的是防止SQL Shell命令和SQL代码语句之间的歧义。 例如,`SET`是一个SQL Shell命令; `SET`也是`Sybase`和`MSSQL`中的SQL代码语句。 默认情况下,没有命令前缀。 要建立命令前缀,设置`COMMANDPREFIX=prefix`,指定的前缀不带引号。 要恢复为没有命令前缀,设置`COMMANDPREFIX=""`。 以下示例显示了命令前缀/(斜杠字符)被设置、使用和恢复的示例: ```java DHC-APP>>SET COMMANDPREFIX=/ commandprefix = / DHC-APP>>/SET LOG=ON log = xsql19388.log DHC-APP>> << entering multiline statement mode >> 1>>SELECT TOP 3 Name,Age 2>>FROM Sample.Person 3>>/GO 2. SELECT TOP 3 Name,Age FROM Sample.Person Name Age yaoxin 30 xiaoli 姚鑫 7 3 Rows(s) Affected statement prepare time(s)/globals/lines/disk: 0.0595s/46282/256257/9ms execute time(s)/globals/lines/disk: 0.0003s/3/714/0ms --------------------------------------------------------------------------- DHC-APP>>/SET COMMANDPREFIX commandprefix = / DHC-APP>>/SET COMMANDPREFIX="" commandprefix = "" DHC-APP>>SET COMMANDPREFIX commandprefix = ``` 当设置了命令前缀时,除了`?`、`#`和`GO`之外的所有SQL Shell命令都需要该命令前缀; 可以使用或不使用命令前缀发出这三个SQL Shell命令。 当发出`SET`或`SET COMMANDPREFIX`命令时,SQL Shell将显示当前命令前缀,作为SQL Shell初始化的一部分,并且在`?` 命令选项显示。 ## 运行命令 SQL Shell `RUN`命令执行SQL脚本文件。 在发出运行命令之前必须设置方言,以指定IRIS (InterSystems SQL)、Sybase (Sybase TSQL)或MSSQL (Microsoft SQL); 默认的方言是IRIS。 可以调用`RUN scriptname`,也可以只调用`RUN`,然后提示输入脚本文件名。 `RUN`加载脚本文件,然后准备并执行文件中包含的每个语句。 脚本文件中的语句必须分隔,通常用`GO`行或分号(`;`)分隔。 `RUN`命令提示指定分隔符。 SQL脚本文件结果显示在当前设备上,也可以显示在日志文件中。 还可以生成一个包含准备失败语句的文件。 `RUN`命令返回指定这些选项的提示符,示例如下: ```java [SQL]USER>>SET DIALECT=Sybase dialect = Sybase [SQL]USER>>RUN Enter the name of the SQL script file to run: SybaseTest Enter the file name that will contain a log of statements, results and errors (.log): SyTest.log SyTest.log Many script files contain statements not supported by IRIS SQL. Would you like to log the statements not supported to a file so they can be dealt with manually, if applicable? Y=> y Enter the file name in which to record non-supported statements (_Unsupported.log): SyTest_Unsupported.log Please enter the end-of-statement delimiter (Default is 'GO'): GO=> Pause how many seconds after error? 5 => 3 Sybase Conversion Utility (v3) Reading source from file: Statements, results and messages will be logged to: SyTest.log . . . ``` ## TSQL例子 下面的SQL Shell示例创建了一个`Sybase`过程`AvgAge`。 它使用`Sybase EXEC`命令执行这个过程。 然后,它将方言更改为InterSystems IRIS,并使用InterSystems SQL `CALL`命令执行相同的过程。 ```java DHC-APP>>SET DIALECT Sybase dialect = Sybase DHC-APP>> << entering multiline statement mode >> 1>>CREATE PROCEDURE AvgAge 2>>AS SELECT AVG(Age) FROM Sample.Person 3>>GO 3. CREATE PROCEDURE AvgAge AS SELECT AVG(Age) FROM Sample.Person statement prepare time(s)/globals/lines/disk: 0.0173s/8129/22308/4ms execute time(s)/globals/lines/disk: 0.0436s/3844/23853/34ms --------------------------------------------------------------------------- DHC-APP>>EXEC AvgAge 4. EXEC AvgAge Dumping result #1 Aggregate_1 50.68137254901960784 1 Rows(s) Affected statement prepare time(s)/globals/lines/disk: 0.0086s/8096/21623/0ms execute time(s)/globals/lines/disk: 0.1131s/90637/458136/19ms --------------------------------------------------------------------------- DHC-APP>>SET DIALECT=IRIS Dialect 'iris' is not supported. ```