Experimental comparison of syntax and semantics of DBS
Experimental comparison of syntax and semantics of DBS Oracle and My. SQL Michal Kvet Lucia Fidesová FRUCT – 7 – 11. 2016, Finland
Slovakia, university FRUCT – 7 – 11. 2016, Finland 2
Introduction § Development of almost any information system requires management of data stored in the database. § data amount § time limited validity ce n a m r § storing whole time spectrum perfo § sensor data processing § Why temporal data? § decision making § analysis § creating future prognoses, § progress of the changes monitoring, § reduce amount of data to be transferred and processed, §. . FRUCT – 7 – 11. 2016, Finland 3
Analysis § Several types of database systems § managing, storing, modifying and selecting large data amounts. § transport systems, industry, medicine, business applications, … § Database administration – relational databases § Oracle QL S n d o e s a lect b a i d § My. SQL own § Postgre. SQL § Informix § DB 2 => syntax & semantics performance limitations & definitions FRUCT – 7 – 11. 2016, Finland 4
History (1) § Development of relational database management system began in the 60 s of the 20 th century. § § § File systems Hierarchical and network based DBS Relational DBS (Edgar Codd, 70 s) Object oriented DBS No. SQL FRUCT – 7 – 11. 2016, Finland ata d e Larg nts u amo 5
History (2) Properly designed structure is the basis for the optimization and efficient processing. Temporality (backups, log files) Index structures Function processing Object level vs. Column level temporal architecture time FRUCT – 7 – 11. 2016, Finland 6
Models (object level) Temp oral mod el vs. Conv entio temp nal mod oral attrib el with utes Validity, Transaction validity, Time locality, Reliability, . . . FRUCT – 7 – 11. 2016, Finland ate t s ch a f e o – y it ly d n i l o a v D B e th e Or n s t o i s m i u l e o i d prev 7
Temporality § Operations: § Insert, § Update, § Delete § Restore, § Purge § Select Data : Histo rical, Actua l, Futur e val id § Such solution is inappropriate for sensor data processing! FRUCT – 7 – 11. 2016, Finland 8
Attribute level temporal system Cons i with stency exist syste ing ms nt u o m a Data FRUCT – 7 – 11. 2016, Finland 9
My. SQL vs. Oracle § Oracle database system is developed for wide range and wide amount of commercial data. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All requirements are accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. § Vice versa, My. SQL is one of the most favourite open-source DBS mostly provided by web applications. § When dealing with My. SQL, it is worth to mention also Maria. DB community, which has been founded by the original My. SQL developers. They maintain high compatibility of Maria. DB with My. SQL. FRUCT – 7 – 11. 2016, Finland 10
Data types § Numeric data types My. SQL Size (in bytes) TINYINT SMALLINT MEDIUMINT INTEGER (INT) BIGINT DOUBLE 1 2 3 4 8 8 FLOAT(x<=24) FLOAT(25<=x<=53 DECIMAL, REAL 4 8 8 Oracle NUMBER(3, 0) NUMBER(5, 0) NUMBER(7, 0) NUMBER(10, 0) NUMBER(19, 0) BINARY_FLOAT BINARY_DOUBLE BINARY_FLOAT Data type Allocation (bytes) TINYINT 1 SMALLINT 3 MEDIUMINT 3 INTEGER (INT) 4 Minimal value With(out) sign -128 0 -32768 0 -8388608 0 -2147483648 0 BIGINT -922337203 6854775808 8 0 Maximal value With(out) sign 127 255 32767 65535 8388607 16777215 2147483647 4294967295 92233720368 54775807 184467440737 09551615 § Date and time data types § My. SQL – Date, Datetime, Timestamp § Oracle – Date, Timestamp § String data types Input data CHAR(4) Size VARCHAR(4) Size ‘’ ‘abcd’ ‘abcdef’ ‘’ ‘abcd’ 4 bytes ‘’ ‘abcd’ 1 byte 3 bytes 5 bytes FRUCT – 7 – 11. 2016, Finland 11
SQL § DDL § DML (Data Definition Language) (Data Manipulation Language) 1 3 2 4 § DCL (Data Control Language) § TCL (Transaction Control Language) FRUCT – 7 – 11. 2016, Finland 12
Experiments - parameters CPU Machine 1 Machine 2 Intel Core i 7 4710 HQ, 3300 Intel Core i 5 4200 M, 3100 MHz, 4 cores, 8 threads 2 cores, 4 threads RAM HDD OS 16 GB 8 GB Samsung SSD 840 EVO Samsung SSD 850 EVO SATA 3 Windows 8. 1 Pro Windows 10 Pro PHP My. SQL Oracle 7. 0. 0 64 bit thread safe My. SQL Community Edition 5. 7. 9 Oracle Database 11 g Express Edition Release 11. 2. 0 - 64 bit Multiple experiments have been performed and evaluated, we will highlight some specifics of proposed systems. ISAM y M , B Inno. D FRUCT – 7 – 11. 2016, Finland 13
Experiment (1) CT DISTIN FRUCT – 7 – 11. 2016, Finland 14
Experiment (2) BY P U O R G FRUCT – 7 – 11. 2016, Finland 15
Experiment (3) Oracle DBS performance provides following improvements: (My. SQL is reference 100%): Machine 1 o 89, 27% (Inno. DB) o 92, 26% (My. ISAM) Machine 2 o 83, 54% (Inno. DB) o 83, 53% (My. ISAM) DATE NT E M E G MANA FRUCT – 7 – 11. 2016, Finland 16
Experiment (4) Based on experiments, performance improvement of Oracle DBS in comparison with My. SQL is 51, 17% (Inno. DB reference 100%) and 29, 63% (My. ISAM reference 100%). When dealing with worse hardware characteristics, difference is not so significant: 16, 76% (Inno. DB reference 100%), 17, 46% (My. ISAM reference 100%). FRUCT – 7 – 11. 2016, Finland TE A G E R AGG IONS T C N U F 17
Summary § Based on these experiments, we can say that in most cases Oracle database processing command requirements are less than a My. SQL database (processing time). Database system Oracle, however, lags significantly in choosing unique record values provided by the DISTINCT feature. § It was further found that a change in the method of storing data in the My. SQL database has greater impact on the experiments for machine 1 in comparison with machine 2. § When dealing with date formats, storage engine Inno. DB je faster than My. ISAM. On the other hand, when managing aggregate functions and ORDER BY clause in the Select statements, My. ISAM is better choice. FRUCT – 7 – 11. 2016, Finland 18
Conclusion § Intelligent systems § temporal changes locality, diversity and reliability § large data amount § Future: § Temporal database § object level § attribute level § Wide range of DBS systems § index distribution techniques, § extended temporality, § No. SQL vs. relational concept. § various methods FRUCT – 7 – 11. 2016, Finland 19
Thank you for your attention Any questions can be e-mailed to the authors. Looking forward to the next conference in person… FRUCT – 7 – 11. 2016, Finland 20
- Slides: 20