Оптимизација на прашалници и погледи

views.sql​

1. View1 view_payment_audit

-Опис: Погледот view_payment_audit овозможува брза финансиска ревизија преку споредба на вкупните уплати со реалната вредност на издадените билети, со цел да се детектираат преплатени или недоволно платени износи.

Времето потребно за извршување на овој прашалник е ~30s. Времето на извршување не е прифатливо.

-Оптимизација: Погледот е претворен во Materialized View за да се избегнат тешките JOIN и SUM пресметки при секое пребарување. Со физичко зачувување на резултатите, драстично се забрзува генерирањето извештаи и сортирањето на големи сетови податоци, додека оптоварувањето на базата е минимално.

Времето потребно за извршување на овој прашалник е ~1.5s.

-Query plan:

2. View2 view_trip_performance

-Опис: Погледот view_trip_performance служи за следење на ефикасноста на возовите преку анализа на доцнењата. Ги обединува податоците за рутите, возовите и возачите, категоризирајќи го секое патување како точно, со мало или со значително доцнење.

Времето потребно за извршување на овој прашалник е ~470ms. Времето на извршување е прифатливо и тоа може да го видиме со query планот.

-Query plan:

3. View3 view_route_infrastructure_stats

-Опис: Погледот view_route_infrastructure_stats дава преглед на техничките карактеристики на железничките рути. Ги пресметува вкупната должина на секоја рута, бројот на сегменти и ги идентификува критичните точки преку најмалата дозволена брзина долж целата линија.

Бидејќи станува збор за аналитичко query кое вклучува пресметки, дополнителна оптимизација не е неопходна. Воедно, времето на извршување е прифатливо и за конкретното query изнесува околу ~17 ms.

4. View4 view_route_passenger_summary

-Опис: Погледот view_route_passenger_summary нуди јасен преглед на комерцијалната успешност на секоја железничка рута. Преку него се следи вкупниот број на продадени билети и вкупниот приход генериран од сите патувања поврзани со одредена рута.

Времето потребно за извршување на овој прашалник е ~15s. Времето на извршување не е прифатливо.

-Оптимизација: Овој поглед е оптимизиран како Materialized View поради двојното поврзување (JOIN) и потребата од сумирање на приходите низ огромна база на податоци. Наместо при секој деловен извештај базата да ги пребројува сите билети поединечно, податоците се пресметуваат однапред, овозможувајќи инстантна анализа на најпрофитабилните рути.

Времето потребно за извршување на овој прашалник е ~20ms.

-Query plan:

5. View5 route_trip_stats

-Опис: Погледот route_trip_stats обезбедува статистички преглед на фреквенцијата и точноста на железничките рути. Преку него се следи вкупниот број на реализирани патувања по рута и просечното време на доцнење, што помага во идентификување на линиите кои се најкритични во однос на возниот ред.

Времето потребно за извршување на овој прашалник е ~300ms. Времето на извршување е прифатливо и тоа може да го видиме со query планот.

-Query plan:

6. View6 view_train_utilization

-Опис: Погледот view_train_utilization е наменет за анализа на искористеноста и доверливоста на секој поединечен воз. Тој прикажува колку пати секој воз бил во сообраќај и какво е неговото просечно доцнење, што помага при планирање на сервисирање и замена на возилата.

Времето потребно за извршување на овој прашалник е ~140ms. Времето на извршување е прифатливо и тоа може да го видиме со query планот.

-Query plan:

7. View7 active_delays

-Опис: Погледот active_delays е оперативен поглед кој овозможува следење на сите возови кои моментално се во сообраќај и се соочуваат со доцнење. Овој поглед ги филтрира само активните патувања (кои не се завршени), давајќи прецизни информации за локацијата на возот и времетраењето на неговото доцнење.

Времето потребно за извршување на овој прашалник е ~40ms. Времето на извршување е прифатливо и тоа може да го видиме со query планот.

-Query plan:

8. View8 train_maintenance_stats

-Опис: Погледот train_maintenance_stats служи за следење на историјата на сервисирање на возовите. Тој дава детален преглед на вкупниот број извршени поправки по воз и бројот на различни техничари кои биле вклучени во процесот на одржување.

Времето потребно за извршување на овој прашалник е ~1.8s и е прифатиливо, но може да се подобри.

-Explain plan:

Со користење на алатката за приказ на планот за извршување, забележуваме дека главната причина за доцнењето е Full Table Scan врз табелата Employee_performs_maintenance, па затоа правиме индекс:

По креирањето на индексот времето на извршување на прашалникот се намалува од ~1.8s на ~50ms.

-Query plan:

9. View9 station_traffic_and_revenue

-Опис: Погледот station_traffic_and_revenue врши сеопфатна анализа на активноста на железничките станици. Тој ги прикажува клучните индикатори за успех за секоја станица: вкупниот број на заминати патници, генерираниот приход од продадени билети и бројот на различни возови кои поминале низ станицата.

Времето потребно за извршување на овој прашалник е ~2s. Времето на извршување не е прифатливо.

-Оптимизација: Овој поглед е дефиниран како Materialized View бидејќи агрегира податоци преку сложени поврзувања на табелите за станици, билети и патувања. Со оглед на тоа што користи SUM и COUNT(DISTINCT), пресметките бараат значителна моќност. Со материјализацијата, менаџментот може веднаш да ги идентификува најпрометните и најпрофитабилните станици без да го забавува тековниот систем за продажба на билети. Иако времето на извршување е прифатливо, бидејќи самиот view е аналитички ние ја направувме оваа оптимизација.

Времето потребно за извршување на овој прашалник по оптимизацијата е ~20ms.

-Query plan:

10. View10 view_conductor_list

-Опис: Погледот view_conductor_list е детален преглед наменет за кондуктерите и железничкиот персонал. Погледот ги здружува податоците за билетите, патниците, возовите и станиците, овозможувајќи брз увид во тоа кој патник на кое седиште се наоѓа, во кој вагон е и на кои релации патува.

Времето потребно за извршување на овој прашалник е ~2s 400ms. Не е прифатливо.

-Explain plan:

Со користење на алатката за приказ на планот за извршување, забележуваме дека главната причина за доцнењето е Full Table Scan врз табелата Ticket, па затоа правиме индекс:

Сега времето на извршување е ~26ms и е прифатливо.

-Query plan:

11. View11 view_user_ticket_history

-Опис: Погледот view_user_ticket_history е наменет за кориснички профили и историја на патувања. Тој ги сумира сите билети на еден патник, класифицирајќи ги како „претстојни“ или „минати“ патувања, притоа прикажувајќи детали за рутата, времето на поаѓање и статусот на билетот.

Времето потребно за извршување на овој прашалник е ~3s 998ms. Не е прифатливо.

-Explain plan:

Со користење на алатката за приказ на планот за извршување, забележуваме дека главната причина за доцнењето е Full Table Scan врз табелата Ticket и врз табелата Payment, па затоа правиме два индекса:

Сега времето на извршување е ~139ms и е прифатливо.

-Query plan:

12. View12 view_station_departures

-Опис: Погледот view_station_departures функционира како дигитален информативен табла за станиците. Тој ги прикажува сите претстојни поаѓања, вклучувајќи ги името на рутата, статусот на патувањето и информациите за евентуални доцнења, подредени хронолошки.

Времето потребно за извршување на овој прашалник е ~140ms. Времето на извршување е прифатливо, заклучуваме дека не треба да го оптимизираме и тоа може да го видиме со query планот.

-Query plan:

13. View13 view_station_arrivals

-Опис: Погледот view_station_arrivals служи како дигитален информатор за пристигнувања на железничките станици. Тој ги прикажува сите возови чие пристигнување се очекува во текот на денешниот ден или во иднина, вклучувајќи детали за рутата, планираното време на пристигнување и евентуални доцнења.

Времето потребно за извршување на овој прашалник е ~17ms. Времето на извршување е прифатливо, заклучуваме дека не треба да го оптимизираме и тоа може да го видиме со query планот.

-Query plan:

14. View14 vw_free_seats_per_trip

-Опис: Погледот vw_free_seats_per_trip овозможува реално следење на слободните капацитети за секое патување преку автоматска пресметка на разликата помеѓу вкупниот капацитет на возот и бројот на продадени билети.

Времето потребно за извршување на овој прашалник е ~63ms. Времето на извршување е прифатливо, заклучуваме дека не треба да го оптимизираме и тоа може да го видиме со query планот.

-Query plan: