Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Learning Session overlapping problems (4)-- re-optimization

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Following the previous article:

Requirements description and first version solution (execution time 90 seconds)

Http://blog.itpub.net/29254281/viewspace-2150229/

Optimize and modify the version of bug (execution time 25 seconds)

Http://blog.itpub.net/29254281/viewspace-2150259/

I think the previous article has reached the best in the way of collective thinking.

If you give up the complete set processing thinking, you can actually be more optimized.

Several questions mentioned in the previous article.

1. Too many table structures have been introduced.

two。 It takes time to write the table itself.

3. The previous batch processing, the particle size is still fine. It should be the fastest to get all the batches out.

4. The previous part of calculating the minimum interval range, because of the application of collective thinking, difficult to understand the performance is still poor.

The previous part of calculating the minimum interval range is as follows

Select roomid,as DATETIME) starttime,as DATETIME) endtime from (select @ d as starttime,@d:=d,v3.roomid,v3.d endtime from (select distinct roomid,when nums.id=1 then v1swhen nums.id=2 then v1ewhen nums.id=3 then v2swhen nums.id=4 then v2eend d from (select v1.roomid, v1.s v1sMagi v1.e v1e v2.s v2s) V2.e v2efrom T1 v1inner join T1 v2 on ((v1.s between v2.s and v2.e or v1.e between v2.s and v2.e) and v1.roomid=v2.roomid) where v2.roomid in (select distinct roomid from T1 where date (s) = pTime) and v2.s > = pTime and v2.s = b.roomstartAND a.roomend

< b.roomend)) s2WHERE s2.e >

S.sAND s.roomid = s2.roomidAND s.userid = s2.userid) AS eFROM (SELECT DISTINCT roomid, userid, roomstart AS sFROM u_room_log aWHERE NOT EXISTS (SELECT * FROM u_room_log bWHERE a.roomid = b.roomidAND a.userid = b.useridAND a.roomstart > b.roomstartAND a.roomstart = b.roomstartAND a.roomend < b.roomend) eWHERE s.roomid = e.roomidAND s.userid = e.userid) T1, numswhere nums.id1) v7 group by roomid,date (s); END

After all are condensed into a process, there is no need to create additional regular tables, just create temporary tables in the process. Achieve high cohesion and low coupling.

Call p

The result returned by the process is the final result.

All three tests took less than 10.3 seconds.

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.

Views: 232

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report