Shulou(Shulou.com)06/02 Report--

What is the big promotion of Sql optimization, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Recently, in the optimization of the group task center view, it is found that the sql view of several systems is slow and too direct po code core business sql has been deceived.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

fifty-five

fifty-six

fifty-seven

fifty-eight

fifty-nine

sixty

sixty-one

sixty-two

sixty-three

sixty-four

sixty-five

sixty-six

sixty-seven

sixty-eight

Create or replace view km_todotasklist as

Select "ID", "DEVICE_TYPE", "SYSTEM_TYPE", "TASK_CODE", "TASK_TYPE1", "TASK_TYPE2", "TITLE", "STATUS", "OWNER_ID", "OWNER_NAME", "PRIORITY", "CREATE_ID", "CREATE_TIME", "UPDATE_ID", "UPDATE_TIME" from (

Select t.id_ as id

CASE

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code = 'SERVER_H5_MODELID' and INSTR (t.cfg_value, a.module_id) > 0) is null THEN

'PC,APP'

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code = 'SERVER_H5_MODELID' and INSTR (t.cfg_value, a.module_id) > 0) is not null THEN

'PC'

ELSE

(select t.cfg_value from SYS_OT_CONFIG t where t.code = 'SERVER_H5_MODELID' and INSTR (t.cfg_value, a.module_id) > 0)

END as device_type

'KM' as system_type

'Core business URL' | | utl_raw.cast_to_varchar2 (utl_encode.base64_encode (utl_raw.cast_to_raw ((select to_char (t.nextroomuserroomid _))

From OA_OT_PROCESS_RECORD t

Where t.nexttaskroomid _ = t.id_ and rownum=1) as task_code

"Agent" as task_type1

To_char (mo.name) as task_type2

To_char (a.title) as title

'0' as status

(select to_char (t.nextroomuserroomid _)

From OA_OT_PROCESS_RECORD t

Where t.nexttaskroomid _ = t.id_ and rownum=1) as owner_id

(select em.real_name

From IMS_OT_USER t

Inner join IMS_OT_EMPLOYEE em

On t.employee_id = em.id

Where t.user_login_name =

(select t. NextUserID _

From OA_OT_PROCESS_RECORD t

Where t.nexttaskroomid _ = t.id_ and rownum=1) and rownum=1) as owner_name

'' as priority

A.creator as create_id

A.create_time as create_time

'' as update_id

'' as update_time

From oa_ot_application a

Inner join act_ru_task t

On t.procured instantly _ = a.pro_inst_id and t.taskworthy defensible key _ 'usertask2'

Inner join oa_ot_application_ext et

On et.app_id = a.id and et.business_type ='1'

Inner join OA_OT_APP_MODULE mo on a.module_id = mo.id

UNION ALL

Select t.id as id

'PC' as device_type

'KM' as system_type

'http://10.XX.XXX.XXX'||(select t.oa_req_url from OA_OT_APP_MODULE t where t.id = a.module_id) | |' / input.htm?taskId=' | | t.id | |'& appId=' | | a.id | |'& sid=' | | utl_raw.cast_to_varchar2 (utl_encode.base64_encode (utl_raw.cast_to_raw (to_char (t.user_id) as task_code

"ready to read" as task_type1

To_char (mo.name) as task_type2

To_char (a.title) as title

'0' as status

To_char (t.user_id) as owner_id

(select em.real_name

From IMS_OT_USER t

Inner join IMS_OT_EMPLOYEE em

On t.employee_id = em.id

Where t.user_login_name = t.user_id and rownum=1) as owner_name

'' as priority

A.creator as create_id

A.create_time as create_time

'' as update_id

'' as update_time

From oa_ot_application a

Inner join oa_ru_circulation t

On t.proc_inst_id = a.pro_inst_id

Inner join oa_ot_application_ext et

On et.app_id = a.id

Inner join OA_OT_APP_MODULE mo on a.module_id = mo.id

) t order by t.create_time desc

The global query is about 15 seconds.

1 understand core business requirements

2 understand the current sql pain points

3 improved sql

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

Create or replace view km_todotasklist as

Select t.id_ as id

CASE

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code = 'SERVER_H5_MODELID' and INSTR (t.cfg_value, a.module_id) > 0) is null THEN

'PC,APP'

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code = 'SERVER_H5_MODELID' and INSTR (t.cfg_value, a.module_id) > 0) is not null THEN

'PC'

ELSE

(select t.cfg_value from SYS_OT_CONFIG t where t.code = 'SERVER_H5_MODELID' and INSTR (t.cfg_value, a.module_id) > 0)

END as device_type

'KM' as system_type

'Core business URL' | | utl_raw.cast_to_varchar2 (utl_encode.base64_encode (utl_raw.cast_to_raw (re.next_user_id_) as task_code

"Agent" as task_type1

To_char (mo.name) as task_type2

To_char (a.title) as title

'0' as status

Re.next_user_id_ as owner_id

Emp.real_name as owner_name

'' as priority

A.creator as create_id

A.create_time as create_time

'' as update_id

'' as update_time

From oa_ot_application a

Inner join act_ru_task t

On t.procured instantly _ = a.pro_inst_id

Inner join oa_ot_application_ext et

On et.app_id = a.id and et.business_type ='1'

Inner join OA_OT_APP_MODULE mo on a.module_id = mo.id

Left join OA_OT_PROCESS_RECORD re on re.next_task_id_ = t.id _

Left join IMS_OT_USER uss on re.next_user_id_ = uss.user_login_name

Left join IMS_OT_EMPLOYEE emp on uss.employee_id= emp.id

Currently, sql has been optimized to 0.3 seconds.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.