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

In this issue, the editor will bring you a simple sql optimization sharing. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

After the system development is launched, as the amount of data used by users increases, the detection of sql has only just begun.

One year after the launch of the contract system, each list query slows down and performance optimization is on the agenda.

After a month of optimization, the memory is still fresh.

The new system focuses on the optimization of sql before it is launched in development. Let's make a brief record here.

First of all, ten thousand pieces of business data are simulated with the stress test tool.

First of all, po gives the optimization results.

18927s before optimization

The execution time of a single sql is listed in Table 8.908:

475s after optimization

A single sql can be executed for 0.328s:

Optimization steps:

Before optimization, I don't know who wrote this sql, and I want to find out what it is.

First analyze what the historical sql queries and what is shown to the user in the list:

List the specific query paths for each list of content analysis. And analyze the specific ways that can be optimized.

List

A.id, oa_ot_application

A.code, oa_ot_application

A.title, oa_ot_application

A.module_id, oa_ot_application

A.pro_def_id, oa_ot_application

M.oa_req_url, oa_ot_app_module template jump path

A.pro_inst_id, oa_ot_application

E.real_name, ims_ot_employee real name

O.organization_name, ims_ot_organization Real Department

T.taskName, optimize sql specifically

T.realName, optimize sql specifically

A.create_time, oa_ot_application

Sql combines business before analysis and optimization

Sql before optimization:

Select distinct a.id

To_char (a.code)

To_char (a.title)

To_char (a.module_id)

To_char (a.pro_def_id)

To_char (m.oa_req_url)

To_char (a.pro_inst_id)

E.real_name

O.organization_name

T.taskName

T.realName

A.create_time

To_char (runtask.name_) as runtaskname

From oa_ot_application a

Left join (select distinct r.procured instantly installed _ as procInstId

To_char (wmsys.wm_concat (distinct)

To_char (t.taskName) as taskName

To_char (wmsys.wm_concat (distinct e.real_name)) as realName

From oa_ot_process_record r

Left join (select t. Procuring instantly _ as procInstId

T.name_ as taskName

To_char (nvl (t.assigneecake, i.usersigneeid _) as userId

From act_ru_task t

Left join act_ru_identitylink i

On i.taskroomid _ = t.id _

Union

Select c.proc_inst_id as procInstId

C.name as taskName

To_char (c.user_id) as userId

From oa_ru_circulation c) t

On t.procInstId = r.procured instantly _

Left join ims_ot_user u

On u.user_login_name = t.userId

Left join ims_ot_employee e

On e.id = u.employee_id

Where (r.nextroomuserroomid _ = 'XXXXX' or r.userroomid_ =' XXXXXX')

Group by r.procession instantly installed _) t

On t.procInstId = a.pro_inst_id

Left join oa_ot_app_module m

On m.id = a.module_id

Left join ims_ot_organization o

On o.id = a.create_org_id

Left join ims_ot_user u

On u.user_login_name = a.creator

Left join ims_ot_employee e

On e.id = u.employee_id

Left join oa_ot_doccheckuser ckuser

On ckuser.app_id = a.id

Left join KM_OT_DOCRED b

On b.app_id = a.id

Left join act_ru_task runtask

On a.pro_inst_id = runtask.proc_inst_id_

Where ((a.creator = 'XXXX') or)

(a.creator! = 'XXXX' and t.procInstId is not null) or

Ckuser.login_name = 'XXXX')

And a.module_id = 'XXXXXXXXX'

Order by a.create_time desc

Optimized sql:

Select distinct a.id

To_char (a.code)

To_char (a.title)

To_char (a.module_id)

To_char (a.pro_def_id)

To_char (m.oa_req_url)

To_char (a.pro_inst_id)

A.create_time

E.real_name

O.organization_name

A.creator

Nvl (case

When a.pro_inst_id is null then 'draft' else

(select to_char (t.name)

From (select t. Procuring instantly _ as proc_inst_id

T.name_ as name

From act_ru_task t

Union

Select c.proc_inst_id as proc_inst_id

C.name as name

From oa_ru_circulation c) t

Where t.proc_inst_id = a.pro_inst_id

And rownum = 1)

End

'done') as taskName

From oa_ot_application a

Left join oa_ot_app_module m

On m.id = a.module_id

Left join ims_ot_user u

On u.user_login_name = a.creator

Left join ims_ot_employee e

On e.id = u.employee_id

Left join ims_ot_organization o

On o.id = a.create_org_id

Left join oa_ot_doccheckuser d

On a.id = d.app_id

Where (a.creator = 'XXXX' or d.login_name =' XXXX')

And a.module_id = 'XXXXXXXXXX'

Order by a.create_time desc

The above is the simple sql optimization shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.