如何使用ABAP open SQL的locator-阿里云开发者社区

开发者社区> c位出道> 正文

如何使用ABAP open SQL的locator

简介: 如何使用ABAP open SQL的locator
+关注继续查看

Recently I learned a new approach to access database table content without having to transport the column data into ABAP program using locator. There is a sample program provided in SAP help.


I make modifications on this sample. The modified one:


REPORT demo_db_locator.

DATA: otr_text_locator TYPE REF TO cl_abap_db_c_locator,

     length           TYPE i.

DATA: pattern  TYPE string VALUE 'ABAP',

     lv_index TYPE int4 VALUE 1.

zcl_abap_benchmark_tool=>start_timer( ).

TRY.

   SELECT text FROM sotr_textu WHERE langu = @sy-langu INTO @otr_text_locator.

     length = length + otr_text_locator->get_length( ).

     IF otr_text_locator->find( start_offset = 0

                                pattern      = pattern ) <> -1.

       lv_index = lv_index + 1.

     ENDIF.

     otr_text_locator->close( ).

   ENDSELECT.

 CATCH cx_lob_sql_error.

   WRITE 'Exception in locator' COLOR = 6.

   RETURN.

ENDTRY.

zcl_abap_benchmark_tool=>stop_timer( ).

WRITE: / 'total length:', length, ' matched for ABAP:', lv_index.

zcl_abap_benchmark_tool=>print_used_memory( ).

CLEAR: otr_text_locator.

zcl_abap_benchmark_tool=>gc( ).

zcl_abap_benchmark_tool=>print_used_memory( ).So this report just calculates the total number of records in table sotr_textu in which the data of column “text” ( type String in ABAP dictionary ) has a substring of “ABAP”, and meantime calculate the total length of text fields from all table records.


For example if the table contains these three table rows below,

the total length should be 5 * 3 = 15 and number of records whose Text fields has pattern “ABAP” is 2.



image.pngThe find method provided by locator is implemented in kernel:image.pngAccording to SAP help, the benefit for us to use locators is the data transfer from database to ABAP program could be avoided, at the cost of high resource ( I guess mainly from memory usage point of view ) consumption. I cannot not help wondering that using this approach, what kinds of runtime behavior could we get? Shorter execution time?


For comparison I write another program which uses the traditional way to get the same result:

According to SAP help, the benefit for us to use locators is the data transfer from database to ABAP program could be avoided, at the cost of high resource ( I guess mainly from memory usage point of view ) consumption. I cannot not help wondering that using this approach, what kinds of runtime behavior could we get? Shorter execution time?


For comparison I write another program which uses the traditional way to get the same result:

REPORT demo_db_locator.

DATA: lv_text TYPE string,

     length  TYPE i.

DATA: pattern  TYPE string VALUE 'ABAP',

     lv_index TYPE int4 VALUE 1.

zcl_abap_benchmark_tool=>start_timer( ).

SELECT text FROM sotr_textu WHERE langu = @sy-langu INTO @lv_text.

 length = length + strlen( lv_text ).

 IF find( val = lv_text sub = pattern  ) <> -1.

   lv_index = lv_index + 1.

 ENDIF.

ENDSELECT.

zcl_abap_benchmark_tool=>stop_timer( ).

WRITE: / 'total length:', length, ' matched for ABAP:', lv_index.

zcl_abap_benchmark_tool=>print_used_memory( ).

CLEAR: lv_text.

zcl_abap_benchmark_tool=>gc( ).

zcl_abap_benchmark_tool=>print_used_memory( ).In the traditional way I use an ABAP variable lv_text to hold the text content from database, and perform the find operation in ABAP layer.

I compare the performance and memory consumption of both. The result shows traditional approach is 10 times faster and has less memory consumption than locator approach. So why do we need it at all?



image.pngSince the total length calculated is only 883309 bytes which is not a big number. So I make further testing. I copy several Z table from SOTR_TEXTU with post fix 1 ~ 9.


image.pngAnd I copy content from original table to these 9 tables with following report:DATA: lt_new_table TYPE TABLE OF zsotr_textu1,

     lt_old_table TYPE TABLE OF sotr_textu.

SELECT * INTO TABLE lt_old_table FROM sotr_textu.

MOVE-CORRESPONDING lt_old_table TO lt_new_table.

LOOP AT lt_new_table ASSIGNING FIELD-SYMBOL(<new>).

 DO 1 TIMES.

   <new>-text = <new>-text && <new>-text.

 ENDDO.

ENDLOOP.

INSERT zsotr_textu1 FROM TABLE lt_new_table.During copy, the text content of new Z table is assigned within the DO N TIMES loop.

The table ZSOTR_TEXTU will have its text field assigned with content with DO N TIMES. This means for example ZSOTR_TEXTU1 will have its every record with text content two times longer than original table, and length of text column in ZSOTR_TEXTU will be two to the power of n times long than original table.

I make a new series of test against these tables:


image.pngimage.pngThe comparison result clearly shows that the power of locator approach can only be released till a set of records which contain REALLY long String content as table column. If the content in String column is not long enough, it is not necessary to use locator approach. In real case it might take some effort to find this boundary value.


By the way, for traditional approach, there is one variant that instead of reading each record using SELECT inside a LOOP, an alternative could be read out all text using SELECT text INTO TABLE, and do calculation using LOOP on the result internal table.

REPORT demo_db_locator.

DATA: lt_text TYPE string_table,

     length  TYPE i.

DATA: pattern  TYPE string VALUE 'ABAP',

     lv_index TYPE int4 VALUE 1.

zcl_abap_benchmark_tool=>start_timer( ).

SELECT text FROM zsotr_textu1 WHERE langu = @sy-langu INTO TABLE @lt_text.

LOOP AT lt_text ASSIGNING FIELD-SYMBOL(<text>).

 length = length + strlen( <text> ).

 IF find( val = <text> sub = pattern  ) <> -1.

   lv_index = lv_index + 1.

 ENDIF.

ENDLOOP.

zcl_abap_benchmark_tool=>stop_timer( ).

WRITE: / 'total length:', length, ' matched for ABAP:', lv_index.

zcl_abap_benchmark_tool=>print_used_memory( ).

CLEAR: lt_text.

zcl_abap_benchmark_tool=>gc( ).

zcl_abap_benchmark_tool=>print_used_memory( ).Comparison result between traditional approach and its variantimage.pngAlthough the variant seems quite promising at a first glance, it could hardly be applied to productive usage – the huge memory consumption which is used to hold all result data in a single internal table should never be neglected.



image.pngIn real case at least in CRM this approach is never used in productive application – instead the pattern OPEN CURSOR and FETCH CURSOR is used to avoid out of memory exception.


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Python使用opencv
Python配置opencv 原理 Python调用opencv的原理是:opencv编译出共享库文件,python把这个共享库文件作为一个模块加载并使用。 通俗点就是,编译opencv的时候开启python接口选项,编译好了会产生cv2.so(linux下)或者cv2.pyd(windows下)这个共享库文件,python代码中import这个cv2就可以用了。
1625 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10095 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10886 0
Virgin Hyperloop One如何使用Koalas将处理时间从几小时降到几分钟--无缝的将pandas切换成Apache Spark指南
Koalas项目基于Apache Spark实现了pandas DataFrame API,从而使数据科学家能够更有效率的处理大数据。一份代码可以同时在pandas(用于测试,小数据集)和Spark(用于分布式datasets)两个平台上运行。
1149 0
使用OpenApi弹性管理云服务器ECS
阿里云的云服务器ECS除了提供控制台来进行日常的管理和资源创建,还提供了OpenApi来进行资源的管理和定制开发。通过OpenApi您可以更加灵活的管理和配置云服务器。 阿里云提供了SDK来包装OpenApi,可以让您将云服务器的管理集成到您的已有系统中。
10203 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13893 0
如何使用OpenSSL工具生成根证书与应用证书
如何使用OpenSSL工具生成根证书与应用证书 一、步骤简记 [java] view plain copy   // 生成顶级CA的公钥证书和私钥文件,有效期10年(RSA 1024bits,默认)   openssl req -new -x509 -days 3650 -keyout CARoot1024.
2089 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
7365 0
OpenTracing的使用实例(Java)
## 构件组织 OpenTracing API的Java构件如下: * opentracing-api:主要的API,无其他依赖。 * opentracing-noop:为主要API提供无意义实现(NoopTracer),依赖于opentracing-api。 * opentracing-util:工具类,例如GlobalTracer和默认的基于ThreadLocal存储的Scop
4843 0
+关注
2316
文章
0
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载