SQL tuning (SQL TUNING) remote support completes performance optimizations

Source: Internet
Author: User

A few days ago, a friend found me and said a SQL performance problem to see if it could be optimized, the following is the process:

Snow Leopard 9:35:10

Are you there

Orchid Island Main 15:07:39

Busy forgetting, what's up?

Snow Leopard 15:07:49

Well

Snow Leopard 15:07:54

Database optimization Issues

Orchid Island Main 15:08:04

Oh, you said.

Snow Leopard 15:09:09

Select Distincta.suite_no,b.bd_nm,b.crt_date from (select Suite_no from All_suite where cus_id =1) a LEFT Join

(select B.suite_no,b.bd_nm,b.crt_date from building B,customer C where c.cus_no=b.cus_no

and c.cus_serial= ' 75806001113513 ' and b.back_date are null) b on b.suite_no=a.suite_no;

Snow Leopard 15:09:35

Snow Leopard 15:09:58

If the data is slow in tens,

Snow Leopard 15:10:35

There is no better wording that

Orchid Island Main 15:10:58

Which one is tens?

Snow Leopard 15:11:10

Building table

Orchid Island Main 15:11:25

What about customer?

Snow Leopard 15:11:26

Clustomer table is very small

Snow Leopard 15:11:36

In the million-piece data

Snow Leopard 15:12:00

All_suite This table is also tens of thousands of data

Orchid Island Main 15:13:38

What about the index on your building table?

Orchid Island Main 15:13:40

Let me see?

Snow Leopard 15:15:10

BUILDING_IDX1 (Bd_nm,area_id,cus_no,back_date,suite_no)

BUILDING_IDX2 (Crt_date)

BUILDING_IDX3 (Back_date)

Building_pkey (SID)

Snow Leopard 15:16:27

Whether the index of the building can be optimized

Orchid Island Main 15:16:36

What about the selectivity of this column?

Orchid Island Main 15:16:48

That is, the repetition value is many?

Snow Leopard 15:17:12

Not much crt_date.

Orchid Island Main 15:17:42

Not this, cus_serial.

Snow Leopard 15:17:57

area_id, cus_id, Suite_no, a couple of duplicate values.

Snow Leopard 15:18:27

This one's not repeating.

Snow Leopard 15:18:30

It's all unique.

Snow Leopard 15:18:44

Is this the customer's table?

Snow Leopard 15:18:48

He's not an index.

Orchid Island Main 15:19:17

What's the final result?

Snow Leopard 15:19:37

Check out the data?

Orchid Island Main 15:19:51

Yes, the result set.

Snow Leopard 15:21:22

Every cus_serial inquires all the corresponding all_suite all rooms correspond to the last check-in person

Snow Leopard 15:21:52

The number of rooms is not much.

Orchid Island Main 15:22:29

How many customer does a cum_serial correspond to?

Orchid Island Main 15:23:38

Probably?

Snow Leopard 15:23:57

One

Orchid Island Main 15:24:21

A cus_serial corresponds to a customer?

Orchid Island Main 15:25:38

BUILDING_IDX1 This index column is too many.

Snow Leopard 15:26:02

can delete

Snow Leopard 15:26:37

Keep several indexes

Orchid Island Main 15:26:54

No, just build an index on the cus_no alone.

Orchid Island Main 15:27:03

How long does it take?

Snow Leopard 15:28:01

Around 30s

Orchid Island Main 15:28:12

Well.

Orchid Island Main 15:28:39

As I said, see an index, not more than 1s.

Orchid Island Main 15:28:44

Built.

Snow Leopard 15:29:10

Ok

Orchid Island Main 15:29:16

In this case, your SQL optimization space is still relatively large.

Orchid Island Main 15:29:48

Results should be produced at up to hundreds of Ms.

Orchid Island Main 15:29:53

If it's optimized.

Snow Leopard 15:32:57

Yes

Snow Leopard 15:33:04

0.5 seconds

Snow Leopard 15:33:22

Faster than before.

Orchid Island Main 15:34:11

You just this 0.5s, is the way I say the index after the building?

Snow Leopard 15:34:25

After building the index

Snow Leopard 15:34:37

Yes

Orchid Island Main 15:34:51

Oh.

Orchid Island Main 15:35:12

Other, not quite understand your data in the situation, not very good fine optimization.

Orchid Island Main 15:35:44

Should still be quick.

Snow Leopard 15:36:34

Good

Snow Leopard 15:36:36

Let me see

Orchid Island Main 15:37:16

For example: The index of the other two tables

Snow Leopard 15:37:31

Well

Orchid Island Main 15:37:43

The full table scan, although the table is small, but to 0.5s, should also be a small proportion.

Snow Leopard 15:37:55

I see

Orchid Island Main 15:38:04

Right.

Snow Leopard 15:41:30

Optimized to 0.18 seconds.

Snow Leopard 15:44:52

0.07 seconds

Orchid Island Main 15:45:00

All_suite?

Orchid Island Main 15:45:07

Well, that's pretty much it.

Orchid Island Main 15:45:20

Dozens of MS

Snow Leopard 15:45:28

All_suite Suit_no is indexed.

Snow Leopard 15:45:40

That's almost it.

Orchid Island Main 15:45:44

Well, that's almost it.

At this point, the other side is relatively satisfactory, performance has also been greatly improved, in view of a number of factors, the necessary processing of the text, recorded in this, you can also think about it, whether there are better ways or other ideas, mutual encouragement.

SQL tuning (SQL TUNING) remote support completes performance optimizations

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.