文章
姚 鑫 · 四月 1, 2021 阅读大约需 7 分钟

第十四章 使用SQL Shell界面(三)

第十四章 使用SQL Shell界面(三)

SQL元数据、查询计划和性能指标

显示元数据

SQL Shell支持MMetadata命令以显示有关当前查询的元数据信息。

对于每个结果集项目,此命令列出以下元数据:列名称(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]USER>>EXPLAIN SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'
  • SHOW PLAN:发出查询,然后发出show plan shell命令。例如:
SQL]USER>>SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'
SQL]USER>>SHOW PLAN

EXPLAIN SQL命令显示有关指定选择查询的查询计划信息而不执行查询。EXPLAIN Alt允许显示备用查询计划。EXPLAIN Stat返回性能统计信息以及查询计划。EXPLAIN只能用于返回选择查询的查询计划;它不会返回用于执行查询操作的InsertUpdateDELETE语句等其他命令的查询计划。

Show Plan SQL shell命令允许显示SQL Shell成功发布的上次查询的查询计划信息。显示计划可用于执行查询操作的任何SQL命令,包括选择,插入,更新和删除。默认情况下,必须执行查询。可以避免通过设置executemode=deferred,执行查询,发出查询,然后发出以下SQL shell命令之一:

  • SHOW PLAN、SHOW PL(或简单的SHOW)显示关于当前查询的查询计划信息。
    查询计划可用于调试和优化查询的性能。
    它指定查询的执行方式,包括索引的使用和查询的成本值。
    可以返回查询计划的语句有:SELECTDECLAREnon-cursor UPDATE or DELETEINSERT…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以执行SybaseMSSQL代码。
若要更改当前方言,请将“方言”设置为SybaseMSSQL或IRIS。
默认值是Dialect=IRIS
这些设置的方言选项不区分大小写。

下面是一个从SQL Shell中执行MSSQL程序的例子:

DHC-APP>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
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
---------------------------------------------------------------------------

SybaseMSSQL方言支持这些方言中有限的SQL语句子集。
它们支持SELECTINSERTUPDATEDELETE语句。
它们对永久表支持CREATE TABLE语句,但对临时表不支持。
支持创建视图。
支持创建触发器和删除触发器。
但是,如果CREATE TRIGGER语句部分成功,但在类编译时失败,则此实现不支持事务回滚。
支持CREATE PROCEDURECREATE FUNCTION

Setting COMMANDPREFIX

可以使用SET COMMANDPREFIX指定必须追加到后续SQL Shell命令的前缀(通常是单个字符)。
在SQL Shell提示符发出的SQL语句中不使用此前缀。
这个前缀的目的是防止SQL Shell命令和SQL代码语句之间的歧义。
例如,SET是一个SQL Shell命令;
SET也是SybaseMSSQL中的SQL代码语句。

默认情况下,没有命令前缀。
要建立命令前缀,设置COMMANDPREFIX=prefix,指定的前缀不带引号。
要恢复为没有命令前缀,设置COMMANDPREFIX=""
以下示例显示了命令前缀/(斜杠字符)被设置、使用和恢复的示例:

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命令。

当发出SETSET 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命令返回指定这些选项的提示符,示例如下:

[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命令执行相同的过程。

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.
00
1 0 0 7
Log in or sign up to continue