문화기술의 융합 (The Journal of the Convergence on Culture Technology)

  • 제6권4호
  • /
  • Pages.693-701
  • /
  • 2020
  • /
  • 2384-0358(pISSN)
  • /
  • 2384-0366(eISSN)
국제문화기술진흥원 (The International Promotion Agency of Culture Technology)
권호 표지
DOI QR코드

DOI QR Code

수강신청 시스템에서의 SQL 분석을 통한 데이터베이스 성능 향상 방안

Methods for improving Database Performance through SQL Analysis in the Course Registration System

  • 김희완 (삼육대학교 컴퓨터공학부)
  • Kim, Hee Wan (Div. of Computer Science and Engineering, Sahmyook University)
  • 투고 : 2020.07.13
  • 심사 : 2020.11.20
  • 발행 : 2020.11.30
PDF 다운로드
⟨ 이전 논문 다음 논문 ⟩

초록

본 논문에서는 현재 운영되는 수강신청 시스템에서의 데이터베이스 성능을 향상시키기 위하여 SQL문 분석을 하였다. 수강신청 업무와 관련된 트랜잭션들에서 사용 중인 SQL문장들을 실행계획을 통하여 현행 데이터베이스 성능을 측정하였으며, SQL 분석을 통하여 보완한 SQL문장들이 성능이 향상된 결과를 확인하였다. 전반적으로 실행계획 분석을 통하여 수강신청 데이터베이스 시스템의 성능을 향상시켰으며, 수강신청 SQL 의 일부 개선방법을 시험결과로 보였다. 개선된 방법은 데이터베이스 튜닝 작업을 통하여 수강신청과 관련된 테이블들과 인덱스 테이블들을 재조정하고, SQL의 기능을 활용함으로서 성능이 향상된 수강신청 데이터베이스 시스템으로 진화한 최적화된 시스템을 구현할 수 있게 되었다. 제안된 방법으로 재조정된 수강신청 시스템은 이전에 운영하던 수강신청시스템에 비하여 성능적 측면에서 우수한 결과를 나타내었으며, 통합 성능 시험 결과 1.8배 ~ 18배의 응답시간 단축을 가져왔다.

In this paper, SQL statements are analyzed to improve database performance in the current course registration system. The performance of the current database was measured through the execution plan of the SQL statements used in the transactions related to the course registration. Through the SQL analysis, the complemented SQL statements confirmed the improved performance. Overall, the performance of the course registration database system was improved through the analysis of the execution plan, and some improvement methods of the course registration SQL were shown as test results. The improved method is to reorganize the tables and index tables related to the course registration through database tuning, and utilize the SQL function to implement an optimized system that has evolved into a course database system with improved performance. The enrollment system re-adjusted by the proposed method showed excellent results in terms of performance compared to the previous enrollment system, and the integrated performance test result reduced the response time by 1.8 to 18 times.

키워드

참고문헌

  1. W. C. Lee, S. N. Kim, and S. M. Lee, "Performance Improvement Method of Course Registration System," 2014 Korea Information Science Society Winter Conference, pp. 162-164, Dec. 2014.
  2. D. H. Kim, H. G. Mun, and H. G. Shin, "Implementation of Web Service Overload Protection Using NetFUNNEL - Focused on the Course Registration Guide System for University ," Korea Information Science Society Summer Conference, pp.1980-1982, Jun. 2014.
  3. Y. J. Lim, and E. M. Choi, "Performance Comparison of HW and SW L4 Switches in Various Workload," Journal of Korean Information Science Society, Vol.30, No.2, pp475-477, 2003.
  4. J. J. Kim, and H. Y. Nho, "Design and Implementation of University Registration System Using MMDBMS," Basic Science Research, Vol. 21, pp.41-54, Kangwon University, 2010.
  5. J. Y. Shin, and S. W. Lee, "A Simulation for improvement course registration," Korea Information Science Society Summer Conference, pp.1736-1738, Jun. 2016.
  6. H. J. Choi,J. W. Ock, J. Y. Jeong, M. C. Kim, and E. J. Lee, "Analysis and improvement of class registration system," Korea Information Science Society Summer Conference, pp.1737-1739, Jun. 2019.
  7. Laghari M.S., "Automated course advising system," International Journal of Machine Learning and Computing, Vol. 4, No. 1, pp. 47-55, 2014. https://doi.org/10.7763/ijmlc.2014.v4.384
  8. Liu Y., Gao F., and Liu Y., "Design and implementation of student registration system for universities," IEEE 2nd International Conference on Consumer Electronics, Communications, and Networks (CECNet), pp. 1760-1763, 2012. https://doi.org/10.1109/cecnet.2012.6202263
  9. Peng Y., Liu N., Li Y., and Shao Z., "Design and implementation of the online course registration system at Tsinghua University," IEEE International Conference on Systems and Informatics (ICSI), pp. 1179-1182, 2012. https://doi.org/10.1109/icsai.2012.6223244
  10. Ala'a M., "Online registration system," International Journal of Computer Science and Security (IJCSS), Vo. 4, No 3, pp. 331-340, 2010.
  11. Tang Y.F., and Zhang Y.S., "Design and implementation of college student information management system based on web Services," IEEE International Symposium on IT in Medicine and Education (ITIME'09), pp. 1044-1048, 2009. https://doi.org/10.1109/itime.2009.5236230
  12. T. G. Lee, "Data Pattern Modeling for Bio-information Processing based on OpenBCI Platform," The Journal of the Convergence on Culture Technology (JCCT) Vol. 5, No. 4, pp.451-456, 2019. http://dx.doi.org/10.17703/JCCT.2019.5.4.451
  13. K. M. Park, "Partial Range Scan For Increased Relational Database System Performance," Korea Information Science Society Summer Conference, Vol. 37, No. 1C, pp.1737-1739, Jun. 2010.
  14. U. S. Han, The Guide for Data Architecture Professional, Korea Database Agency, 2010.