CLI 编程基础

最新推荐文章于 2024-07-12 10:22:10 发布

Cteddy

最新推荐文章于 2024-07-12 10:22:10 发布

阅读量585

点赞数

文章标签：编程 sql variables 数据库 insert 测试

CLI 编程基础

CLI (Call Level Interface) 是一种基于 C/C++ 的软件编程接口标准，它定义了应用程序如何使用统一的接口来向 DBMS（Database Management System）发送 SQL 请求以及处理 DBMS 返回的查询结果集。

DB2 CLI 是 IBM 提供的一系列用来访问 DB2 数据库的编程接口（API），它基于 ISO CLI 国际标准，并且提供了 ODBC 标准的大部分功能。

CLI 与 Embedded SQL 对比

Embedded SQL 是一种广泛使用的数据库编程语言，它采用将 SQL 语句直接嵌入到其他编程语言中（如 C、JAVA）的编程方式。这种编程技术有一系列潜在的劣势，比如 SQL 语句与应用程序的交互能力比较差；因为不用的数据库所支持的 SQL 语句并不完全兼容，这会导致 Embedded SQL 程序的可移植性很差，为 DB2 开发的 Embedded SQL 程序必须经过修改才能支持其他的关系型数据库。

与 Embedded SQL 不同，CLI 使用一系列标准的 API 来向数据库发送 SQL 语句。与 Embedded SQL 相比，CLI 有很多的优点，比如 CLI 不需要预编译（pre-compile）源文件，因为它本身就是可以被编译的 C 语言（或其他高级语言）文件；为不同版本的 DB2 数据库系统目录（database catalog）提供了统一的访问接口，程序员可以不用关心不用版本数据库之间的各种具体差异。

CLI 的主要编程步骤

CLI 使用 handle 来访问和操作 CLI 管理的各种资源。CLI 包含四种 handle：Environment handle、Connection handle、statement handle 以及 descriptor handle。

CLI 程序由三部分组成：程序初始化（initialization）、Transaction 处理、程序结束（terminate）。CLI 程序的流程图如图 1 所示。

图 1. CLI 程序流程图

限于篇幅，本文不会对各个 CLI API 做详细地阐述，如果需要了解 API 的详细信息，可以访问 IBM DB2 信息中心 CLI 编程来获帮助。

回页首

影响数据库访问性能的主要因素

影响数据库应用程序性能的因素是多方面的，但是经过归类总结，我们可以考虑从以下的三个方面入手。

优化数据库 server 端来提升性能

一般来说，数据库管理员会专注于这方面的工作，主要是通过一系列数据库参数的设置和调优，来改进和提高数据库的性能。

通过配置数据库（database）的各种参数可以提高数据库操作的性能，例如改变 agent 的数量来提高并发时的性能等；通过创建更加合理的表（table）也可以提高数据库操作的性能，例如指定某一列作为表的索引（index）。

由于调整数据库的参数来获取高性能与 CLI 编程本身并没有直接的联系，所以这部分内容并不在 CLI 程序优化的策略范围之内。

优化网络流量 / 交互次数来提升性能

当数据库与客户端不在同一台物理主机上时，它们将通过网络来交换数据。很多场合下，网络的传输速度会成为产品性能的瓶颈，所以减少网络流量，尤其是减少同一操作在网络中请求 - 应答的次数，会大幅度的提高程序性能。在下面章节中，会通过实例来阐述如何通过减少网络的流量来提高效率，如使用 array 操作。

优化客户端程序来提高升能

客户端程序本身也会对产品的性能造成较大影响，通过合理并充分的使用现有的资源，可以提高程序的效率。在下面章节中，对于 LOB 操作就是通过对客户端程序的优化而得到了性能的提升。

回页首

可提高 CLI 程序性能的一些技术及实例分析

实例的测试环境

本文中，所有实例都是在以下的环境中测试并得出结论的：Client 与 server 在不同的物理主机上，它们之间通过 1000M 的以太网相连。

不同环境下，测试得到的各个指标的绝对值可能会有所不同，但是我们关注的只是程序优化前后执行所需时间的一个对比，所以只要程序优化前后测试的环境是一样的，测试得到的结果就是有意义的。

各指标的测试结果均为多次测量求平均值。

分离 SQL 语句的 Prepare 与 execute

CLI 提供了两种执行 SQL 语句的方法：SQLPrepare() 与 SQLExecute() 顺序的分步执行；使用 SQLExecDirect() 来直接预处理以及执行一个 SQL 语句。

在编写代码的时候，使用 SQLExecDirect() 会使代码显得更简洁和容易操作，但是程序的执行效率也会受到影响。

将 SQL 语句的预处理与执行分别在不同的步骤中完成，当需要再次执行这个 SQL 语句的时候，可以省略 prepare 过程而直接执行。这会在两个方面使性能得到提高：减少网络上请求 - 应答的交互次数以及网络中的数据流量；减少 server 端的数据处理量。

下面这段程序展示了如何使用 SQLPrepare() 与 SQLExecute() 来向表中插入数据，插入的行数由 recordNum来确定。

清单 1. SQLPrepare() 与 SQLExecute() 分步执行

				
 /* Pre-process CLI program, including: 
 Allocate the need variables; 
  Allocate env handle, connection handle, statement handle; 
  Connect to database; 
 */ 
 sprintf((char*)sql_stmt, "INSERT INTO %s values (?,?) ", a_tbname); 

 //Prepare the SQL statement. 
 cliRC = SQLPrepare (hstmt, sql_stmt, SQL_NTS); 
 HANDLE_CHECK(cliRC); 

 // Binding variables to the parameters here 

 /*Some code omitted here*/ 

 // Get the starting time 
 gettimeofday (&_start, &tz); 

 for (int index = 0; index < recordNum; index ++) 
 { 
  // Generate the data 
  intVal = index; 
  sprintf ((char*)charVal, "%06d abcdef ", index); 

  cliRC = SQLExecute (hstmt); 
  HANDLE_CHECK(cliRC); 
 } 
  
 // Get the end of time 
 gettimeofday (&_end, &tz);

向表中插入不同行数的数据时，测试得到的结果如表 1 所示。

表 1. 清单 1 代码的测试结果

向表中插入的行数	所有时间（秒）
1000	4.2
5000	21.4
10,000	42.7
20,000	86.4
50,000	214.2

清单 2. 使用 SQLExecDirect() 直接执行 SQL 语句

				
 /* Pre-process CLI program, including: 
 Allocate the need variables; 
  Allocate env handle, connection handle, statement handle; 
  Connect to database; 
  Binding variables to parameters. 
 */ 

 // Turn off SQL_ATTR_DEFERRED_PREPARE. 
 cliRC = SQLSetStmtAttr(hstmt, 
  SQL_ATTR_DEFERRED_PREPARE, 
  (SQLPOINTER) SQL_DEFERRED_PREPARE_OFF, 
  0); 
 // Get the starting time 
 gettimeofday (&_start, &tz); 

 for (int index = 0; index < recordNum; index ++) 
 { 
  // Generate the values used for testing 
  intVal = index; 
  sprintf ((char*)charVal, "%06d abcdef ", index); 

  cliRC = SQLExecDirect (hstmt, sql_stmt, SQL_NTS); 
  HANDLE_CHECK(cliRC); 
 } 

 // Get the end of time 
 gettimeofday (&_end, &tz);

当向表中插入不同行数的数据时，测试得到的结果如表 2 所示。

表 2. 清单 2 代码的测试结果

向表中插入的行数	所用时间（秒）
1000	4.4
5000	22.8
10000	44.9
20000	88.4
50000	219.7

把以上两组测试结果进行对比，结果如表 3 所示。

表 3. 两种法案测试结果的对比

向表中插入的行数	优化前所需时间（秒）	优化后所需时间（秒）
1000	4.4	4.2
5000	22.8	21.4
10000	44.9	42.7
20000	88.4	86.4
50000	219.7	214.2

经过分析发现，将 SQLPrepare() 和 SQLExecute() 分开，重复执行一个已经 prepare 过的 SQL 语句，可以得到大约 5% 左右的性能提升。

这种性能的提升主要是依靠减少网络交互的次数和减少数据库 server 端 SQL 请求的处理量。当网络状况越差、数据库 server 端的性能越低的时候，这种优化策略将会得到越大的性能提升。

CLI 还提供了一个参数 SQL_ATTR_DEFERRED_PREPARE，此参数控制是否推迟向数据库发送 prepare 请求。如果此参数设置为 ON，prepare 请求不会被立即发送，而是在执行 SQL 语句的时候才会被发送到数据库 server 端执行。所以应该尽量的使用 SQL_ATTR_DEFERRED_PREPARE 来推迟发送 prepare 请求，此参数默认是设置为 ON 的。

使用 Array 操作（insert、fetch）来提高性能

很多情况下，我们会使用同一个 SQL 语句来插入大批量的数据。这种场景的特点是：有大量的数据需要处理，SQL 语句会被重复的执行很多次，每次执行的 SQL 语句是相同的，并且 SQL 语句中的绑定的参数（parameter）也是相同的，只是每次 SQL 语句被执行时，被绑定的参数的值会改变。

此时，可以使用在上节中阐述的“将 SQL 语句的 prepare 和 execute 分开”来提高效率。此外，在数据量比较大的情况下，我们还可以作进一步的优化，即使用 Array 操作（insert、fetch）来提高大批量数据的处理效率。

Array 操作是将大块的数据同时发送到数据库的 server 端来处理，这将大幅度的的减少网络数据流量和交互次数，从而得到性能的提升。

Array 操作包含 INSERT 和 FETCH 功能，下面我们以 Array insert 为例子，来分析使用 Array 操作所得到的性能的提升。

本文着重于做性能分析，如果需要参考 Array 操作实现技术，可以参考相关的技术文档。

代码清单 3 展示了使用 Array insert 来向一个表中插入数据，为了保持程序的简洁清晰，示例中去除了部分错误处理代码。

清单 3. 使用 CLI Array 操作来执行 SQL 语句

				
 /* Pre-process CLI program, including: 
  Allocate env handle, connection handle, statement handle; 
  Connect to database; 
  Prepare the SQL statement. 
 */ 

 // Allocate memory for variables, arraySizemeans the size
  of the each array insert. 
 SQLINTEGER *intVal = (SQLINTEGER *)malloc(arraySize * sizeof (SQLINTEGER)); 
 SQLCHAR *charVal = (SQLCHAR *)malloc(arraySize *sizeof (SQLCHAR) * 20); 

 //Null indicators used in array insert 
 SQLINTEGER ind[10000] = {SQL_NTS}; 

 // Tell CLI driver we use array insert, and the array size is arraySize 
 cliRC=SQLSetStmtAttr( hstmt, SQL_ATTR_PARAMSET_SIZE,
  (SQLPOINTER)arraySize, 0); 
 HANDLE_CHECK(cliRC); 

 // Prepare the SQL statement 
 cliRC = SQLPrepare (hstmt, sql_stmt, SQL_NTS); 
 HANDLE_CHECK(cliRC); 

 // Binding variables to parameters 
 cliRC = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG , 
 SQL_INTEGER , 0, 0, (SQLPOINTER)intVal, 0, NULL); 
 HANDLE_CHECK(cliRC); 

 cliRC = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR , 
  SQL_CHAR , 20, 0, (SQLPOINTER)charVal, 20, ind); 
 HANDLE_CHECK(cliRC); 

 // Get the starting time 
 gettimeofday (&_start, &tz); 

 // recordNum mean the whole rows to be inserted. 
 for (int index = 0; index < recordNum/arraySize; index ++) 
 { 
 // Generate the values for each of the parameters 
  for (int i=0; i<arraySize; i++) 
  { 
  char *p = (char*)(charVal+i*20); 
  intVal[i] = i + index * 10000; 
  
				 sprintf (p, "%06dabcdef", i); 
  ind[i] = SQL_NTS; 
  } 
  
				 cliRC = SQLExecute (hstmt); 
  if (cliRC < SQL_SUCCESS) 
  HANDLE_CHECK(cliRC); 
 } 
 // Get the end of the time 
 gettimeofday (&_end, &tz);

在 Array size 设置为 200 的时候，表 4 明了插入不同行数时，程序执行所需要的时间。

表 4. Array Insert 代码的测试结果（Array size = 200）

向表中插入的行数	所需要的时间（秒）
1000	0.3
5000	1.6
10000	3.1
20000	6.4
50000	15.7

将表 4 中的测试结果与表 2 中的测试结果在同一曲线图中对比，就可以直观的看出使用 Array insert 所得到的性能的改善。性能对比结果如图 2 所示。

图 2. 使用 Array Insert 得到的性能改善

由此可见，使用 Array insert 使得程序的运行效率得到了大幅度的改善，在 Array size 为 200 的情况下，完成相同的工作比不使用此技术节约了 15 倍 (1500%) 左右的时间！

当 Array size 设置的不同的时候，执行效率也会有所变化。在插入相同行数的数据时（测试数据使用 50000 行），表 5 反映了 Array size 的设置不同时，所用的时间。

表 5. 改变 Array size 时插入 50000 行数据所需时间

Array size	所用时间（秒）
10	41.0
50	26.2
100	20.3
200	15.7
500	12.9

将表 5 中的数据绘制成曲线图，如图 3 所示。

图 3. Array size 对性能的影响

从上图来看，Array size 设置的越大，执行相同的任务需要的时间也就越少，但是，这并不表明 Array size 设置的越大越好。从上图分析发现，Array size 从 10 提高的 50，执行时间几乎减少了一倍（14 秒）；但是，把 Array size 从 200 提高到 500 时，执行时间只是减少了 20% （约 3 秒）。Array size 的设置还受内存的限制，此值设置的越大，就会占用更多的内存。所以在实际的应用中，应该根据实际的环境来调节 Array size，来达到资源的使用和效率的一个平衡。

CLI 中还有一种技术类似于 Array 操作，即 Compound SQL，它的原理是将一系列的 SQL 同时发送到数据库的 server 端执行，只返回最后的结果。这种技术在性能上的提高得益于减少了网络流量，其实每一条 SQL 语句还是独立执行的。Compound SQL 性能比普通执行 SQL 语句要好，但是比 SQL 语句的 Array 操作性能要低。

使用文件绑定来提高 LOB 操作的性能

LOB 是一种广泛使用的数据类型，这种数据类型用来存储大块的数据，其上限为 2GB。如果一个应用程序需要将整个文件的内容作为表中一个类型为 LOB 的列的值，在将向本列插入数据的时候，最直接的方式就是将文件中的内容分片的读出、发送给数据库；然而，我们还有一种更有效率的方法，即将这个文件直接绑定到 SQL 语句的 parameter 上。

下面我们分别测试分析这两种方法的效率，并且分析为什么直接绑定文件到参数上会有性能的提升。

首先我们测试从文件中分片的读出数据后发送给数据库。

清单 4. 从文件中分片的读出数据插入到表的 LOB 列中

				
 /* Pre-process CLI program, including: 
 Allocate the need variables; 
  Allocate env handle, connection handle, statement handle; 
  Connect to database; 
  Bind variables to the parameters; 
  Prepare the statement. 
 */ 

 // Get the starting time 
 gettimeofday (&_start, &tz); 

 /* execute the statement */ 
 cliRC = SQLExecute(hstmt); 
 HANDLE_CHECK(cliRC); 

 if (cliRC == SQL_NEED_DATA) 
 { 
  pFile = fopen((char *)fileName, "rb"); 
 if (pFile == NULL) 
 { 
 /* Some error process code omitted here */ 
 } 
 else 
 { 
 /* get next parameter for which a data value is needed */ 
 cliRC = SQLParamData(hstmt, (SQLPOINTER *)&valuePtr); 
 HANDLE_CHECK(cliRC); 

 while (cliRC == SQL_NEED_DATA) 
 { /* if more than 1 parameter used SQL_DATA_AT_EXEC then valuePtr would 
  have to be checked to determine which parameter needed data */ 
 while (!feof(pFile)) 
 { 
 n = fread(buffer, sizeof(char), sizeof(buffer), pFile); 

 /* passing data value for a parameter */ 
 cliRC = SQLPutData(hstmt, buffer, n); 
 HANDLE_CHECK(cliRC); 

 fileSize = fileSize + n; 
 } 
 /* get next parameter for which a data value is needed */ 
 cliRC = SQLParamData(hstmt, (SQLPOINTER *)&valuePtr); 
 HANDLE_CHECK(cliRC); 
  } 
 } 
 /* end transactions on a connection */ 
 cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); 
 HANDLE_CHECK(cliRC); 

 // Get the end of time 
 gettimeofday (&_end, &tz);

当 LOB 数据的大小变化时，测试结果表 6 所示。

表 6. 分片读文件时插入 LOB 数据所需要的时间

LOB数据的大小（M）	所用时间（秒）
10M	0.24
50M	1.00
100M	2.02
500M	10.62
1000M	21.86

代码清单 5 用来测试将一个文件直接绑定在 SQL 语句上时，插入 LOB 列所需要的时间。

清单 5. 将文件直接绑定给 SQL 语句的参数

				
 /* Pre-process CLI program, including: 
 Allocate the need variables; 
  Allocate env handle, connection handle, statement handle; 
  Connect to database; 
  Bind variables to the parameters; 
  Prepare the statement. 
 */ 

 /* bind the file parameter */ 
 rc = SQLBindFileToParam(hstmt, 
  1, 
  SQL_BLOB, 
 fileName, 
  &fileNameLength, 
  &fileOption, 
  14, 
  &fileInd); 
 HANDLE_CHECK(cliRC); 

 // Get the starting time 
 gettimeofday (&_start, &tz); 

 sprintf((char *)fileName, "%s", "mydata.lob"); 

 /* execute the statement */ 
 cliRC = SQLExecute(hstmt); 
 HANDLE_CHECK(cliRC); 

 /* end transactions on a connection */ 
 cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); 
 HANDLE_CHECK(cliRC); 

 // Get the end of time 
 gettimeofday (&_end, &tz);

随着 LOB 数据大小的改变，插入一个 LOB 列所需的时间如表 7 所示。

表 7. 直接绑定文件时插入 LOB 数据所需要的时间

LOB数据的大小（M）	测试所用时间（秒）
10M	0.31
50M	1.60
100M	3.03
500M	15.44
1000M	30.69

将表 6 和表 7 中的测试结果在同一张曲线图中对比，结果如图 4 所示。

图 4. SQL 语句操作 LOB 数据的效率对比

经过对图 4 的分析发现，直接将文件绑定到 SQL 语句的参数上，可以节省约 30% 的时间。

使用 SQLBindFileToParam（）的效率优于使用 SQLPutData（）来分片的向数据库发送数据。原因是，SQLPutData（）将各个片段的数据放到一个临时文件中，然后再使用 SQLBindFileToParam（）技术来将 LOB 数据发送给 server。所以，这种性能的提升得益于客户端自身的优化。

如本节所述，在操作类型为 LOB 的数据时，如果需要操作整个 LOB 的数据，建议使用 SQLBindFileToParam() 来提高效率。如果只是需要一个 LOB 列中一部分数据，则可以使用 LOB Locator 来定位需要的部分，从而避免提取整个 LOB 数据到客户端。

回页首

结束语

本文对优化 CLI 程序的性能做了简要的分析和定量的测试，可以利用测试结果来更加直观和客观的分析 CLI 程序性能。

除了文中列出的优化策略，还有很多技术和技巧来提高 CLI 程序的性能，本文也只是管中窥豹，不可能一一阐述，只是希望对广大读者起到一个抛砖引玉的作用。

Cteddy

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
CLI 编程基础

<br />CLI 编程基础<br />CLI (Call Level Interface) 是一种基于 C/C++ 的软件编程接口标准，它定义了应用程序如何使用统一的接口来向 DBMS（Database Management System）发送 SQL 请求以及处理 DBMS 返回的查询结果集。<br />DB2 CLI 是 IBM 提供的一系列用来访问 DB2 数据库的编程接口（API），它基于 ISO CLI 国际标准，并且提供了 ODBC 标准的大部分功能。<br />CLI 与 Embedded
复制链接

扫一扫