#
第十四章 使用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.
```