Evaluating Aggregate Functions and Machine Learning Integration: A Comparative Analysis of Performance, Security, and NoSQL Connectivity in Oracle, SQL Server, and MySQL

1. INTRODUCTION

In modern database systems, the vast size of databases necessitates having tools that can efficiently provide the desired information [1]. Much of this work is done through aggregate functions, which allow us to do some action within groups of data [2], [3]. Which database management system (DBMS) you favor, and how you carry out query optimization, tend to hinge on how well each system supports aggregate functions [1], [4].

Relational database management system (RDBMS) such as Oracle, SQL Server, and MySQL are major in use in practically every industry or domain of human endeavor [5]. Each RDBMS comes with some distinct features and offers strength in particular clustered task [4], [5]. This paper explores these features by presenting standard functions, such as COUNT, SUM, AVG, MIN, and MAX and up to a more advanced functions, such as MEDIAN, STRING_AGG, and PERCENTILE_CONT, from each point of view of handing these functions by each RDBMS [6]. The subtlety of all these functions can help each database administrator or developer see which RDBMS best suits their needs for achieving set objectives in designing database specifications[4]–[6]. However, advanced data management often integrates machine learning (ML) workflows that enable predictive analysis, pattern recognition and data-informed decisions [7], [8]. Each of these databases provides a different degree of ML integration using in-database ML algorithms, external tool integration or cloud-based ML services [8]. Exploring the levels of these capabilities will reveal the strengths and limitations of each RDBMS in advanced analytics [8]. RDBMS data models lack the flexibility to set up and store data for analytics capable of managing big data and various data types of new data types, such as structured, semi-structured and unstructured data, have become popular [2], [9]. However, data models are highly optimized to handle and process specific data using a standardized relational data model [9]. As relational databases are designed to govern the lifecycle of data, large amounts of unmanaged data attached to the system can lead to quality issues on the applications that rely on this data [9], [10]. That’s why it’s highly recommended to use new storage or computing models designed for unstructured and semi-structured data [10]. Integrating non-relational databases called NoSQL databases with RDBMS data can overcome the downsides of RDBMS data models as they can store extra data, manage data quality and provide access to applications designed for semi-structured data types [11]. The most popular NoSQL database systems include key-value stores, document-oriented databases, and graph databases, which have their own advantages and disadvantages [12]. Therefore, can be used this flexibility and scalability of the NoSQL environment to support analytical-engineering objectives. Security is one of the key concerns. It is important to make sure that trusted data have been kept online at the right time, without being accessible to unauthorized approach or some any threats [12].

The major reason for such a research initiative is that no comprehensive, in-depth study exists comparing these three major RDBMS platforms in the context of their execution of aggregate functions with the big dataset, embedding ML models, and linking with NoSQL data stores. This also justifies research on the optimization techniques that can be applied to enhance query performance measures across these systems.

The objectives of this study are as follows:

By meeting these goals, this paper will therefore help database administrators and developers to select the most suitable RDBMS for their needs in aggregation, ML integration, and NoSQL connectivity.

This paper provides a detailed comparison between Oracle, SQL Server, and MySQL on the following aspects:

1.6. Security Comparison

Evaluate the integrity, confidentiality and availability of the security features in different DBMS. Security of the DBMS is of a key as any DBMS (Table 1).

TABLE 1: Security features comparison

1.6.1. Data encryption at rest

Data encryption at rest is a process that encrypts files and documents such that other than the document’s owner, anyone attempting to view them is rendered with useless files unless the correct key is provided. This method helps entirely remove data leakage, unauthorized entry and physical theft aside from a situation where an attacker has infiltrated your key management system and has access to the key.

1.6.2. Data Encryption in transit

Encryption in transit refers to the encryption characteristics in a network while the transmitted data are moving from source to destination, and the data may not be encrypted in the source and destination storage systems [24].

1.6.3. Access control

A DBMS must provide some kind of security mechanism to prevent unauthorized access to the database. The DBMS creates user accounts, and controls the login process, to accomplish this [25].

1.6.4. Authentication

It authenticates that a user logs in according to the privileges given to perform to database activities. This prevents access to sensitive data by asking for proper authentication [25].

1.6.5. Auditing

It helps detect, in a timely way, unauthorized acts and activities by authorized users [24].

1.6.6. Data masking

Data masking changes the structure of sensitive information to produce fake versions of a company’s data [26].

1.6.7. Compliance

Data compliance is the act of ensuring that an organization and any of its associated systems adhere to legal, regulatory and operational requirements regarding data [26].

1.6.8. Intrusion detection

An intrusion detection system (IDS) is any application that monitors network traffic for malicious activities and known threats. An IDS can monitor network traffic on suspicious hosts or network segments where malicious activities are likely to occur. When identified, an IDS notifies the IT and security teams about potential security risks and threats [27].

2. LITERATURE REVIEW

Comparative study on RDBMSs has been a notable part of the research focus due its impatience in dealing with data for almost any application. This literature review concentrates the result of several studies being conducted in the same field, with providing some of their range of research and their field of study, methodologies, results and limitations, as shown in the table below see (Table 2). In a broad comparison of SQL and NoSQL databases, Lee et al. (2019) address the fact that each type of DB is still relevant in its context. This provided a new perspective for this study to address RDBMS connectivity in NoSQL databases that demonstrate the flexibility of these applications. However, they did not study which specific features from different DBMSs lead to how performance is impacted, something we try to address in this study.

TABLE 2: Difference research on DBMS comparation

Islam (2017) investigated performance efficiency and response time while managing real-time huge data. He found that MYSQL yielded the best results in executing performances when dealing with huge structured/semi-structured/unlike data. Islam’s work focused on insert operations only, and here the scope is extended to cover a wider range of operations including complex queries that are important when comparing performance across different DBMS for aggregate functions.

In a study by Matallah 2021: MySQL versus MongoDB, he states that you would better use MongoDB for unstructured data than the structured one, and it trades off with MySQL as well. This distinction underlies a study of the execution strategies for aggregate functions in various DBMSs on diverse data environments, which motivates our work in this paper. Nonetheless, Matallah’s study is limited to simpler systems and warrants further investigations which have been pursued by this paper.

Zhang et al. (2018) and Singh et al. (2018) examined ML integration within RDBMS, particularly SQL Server and Oracle. Their work on in-database ML capabilities has direct relevance to this study’s objective of understanding the integration, capabilities, and functionalities for ML in RDBMS. However, the limitation of focusing on just two RDBMS highlights a gap that this study seeks to address by including more databases in the analysis.

Lee et al. (2019) and Gonzalez et al. (2019) reviewed NoSQL databases and the integration of SQL as well as RDBMS in different industries. Their findings emphasize the importance of seamless data exchange, which this study further investigates in the context of database security and performance optimization within RDBMS.

Finally, Abbas et al. (2020) and Chen et al. (2021) explored optimization techniques for aggregate functions and big data integration strategies. While their work is largely theoretical, this study builds on it by providing experimental validation and examining how these optimization techniques impact performance and security in different RDBMS.

In summary, existing work covers a broad range of RDBMS aspects, but there are still significant gaps to fill, particularly in the areas of aggregate function performance, ML integration, and NoSQL connectivity. This study aims to address these deficiencies by offering a more comprehensive analysis focused on these critical components.

An overview of a variety of aggregate function across Oracle, SQL Server and MySQL along with how to implement ML Model Integration, Materialized Views, NoSQL Database Connectivity as well as some insights about Security. The study, conducted in a uniform benchmarking environment on 1 billion rows, assesses the impact of performance metrics such as execution time (ET), CPU utilization, memory consumption and disk space utilization across key model logic as well as addressing capabilities related to ML models and security features. In contrast to more focused or example-based studies. the work by Smith et al. Brown et al.’s incremental expansion to IBM’s work on benchmarking aggregate functions had previously been homed in older versions. Zhang et al.’s security analysis, without tracking performance stats. this paper gives a comprehensive overview of an extensive variety of features across the three prevalent RDBMSs with a base scale dataset. Key takeaways are that Oracle is strongest in the advanced functions and security, SQL Server has a leg up on complex aggregations and ML integration while MySQL performs well with basic aggregation but falls short where more features or native ML capability may be required.

3. MATERIALS AND METHODS

4. RESULTS

This part gives detailed outcomes of the study performed on Oracle, SQL Server, and MySQL with a one billion rows in each database. This is given in two major sections: query ET before and after indexing was applied, plus analysis of simple security features, integration support and connectivity with NoSQL connectivity.

4.1. Query Times before Indexing

The initial performance tests were conducted without applying any indexing on the dataset. This setup allows us to observe the raw performance of each DBMS when managing various aggregate functions. Nearly Oracle and SQL Server are same exhibited the fastest ETs for all aggregate function the results are summarized in Table 3 and Fig. 1.

TABLE 3: The relative performance of different database management system without Indexing

Fig. 1. The execution times (in seconds) for various aggregate function types across three different database management system.

4.1.1 Basic aggregate functions

• Count(*): Oracle and SQL Server had around the same ET, 59 and 57 s, respectively. As expected, MySQL had the longest ET, being the least optimized SQL server, at 160 s. These results show how MySQL struggles with the large dataset when there are no indexes as shown in Table 3, SQL Server and Oracle exhibited the fastest ETs before indexing for the COUNT function than MySQL.

• SUM (salary) and AVG (salary): Both Oracle and SQL Server exhibited identical times for these functions, taking 93–94 s for SUM and 62–63 s for AVG. MySQL trailed again by wide margins, logging 170 s for SUM and 160 s for AVG.

• MIN (salary) and MAX (salary): Oracle and SQL Server were even closer here, too: 92 and 95 s, respectively. MySQL came in at around 155 s for both functions. Fig. 1 illustrates the ETs for basic function before indexing.

4.1.2 Advanced aggregate functions

STRING_AGG, This example demonstrates that Oracle performs significantly faster than the other databases, while MySQL does not perform as effectively in comparison. For Window Functions SQL Server approximately was faster than Oracle, in many cases Fig. 2 illustrates the ETs for basic function before indexing. while MySQL could not implement these functions natively and did not support them as shown in Table 3.

Fig. 2. The execution times for various windows function types across Oracle and SQL Server.

4.2. Query ET (After Indexing) for Aggregate Function

However, when used indexing then, there was a dramatic increase in performance.

This can be clearly seen in Table 4, SQL Server exhibited the fastest ETs for the COUNT function, and for (Min and Max) Oracle faster than other. In addition, (Sum and AVG) nearly Oracle and SQL Server are equal as demonstrated in Fig. 3.

TABLE 4: The relative performance of different database management system with Indexing

Fig. 3. The execution times (in seconds) for various aggregate function types across three different database management system with indexing.

4.3. Advanced Functions After Indexing

For advanced function after indexing SQL Server rapidly increase in performance as shown in Table 4. It is evident the power of indexing in improving the response time for any query in windows function Fig. 4 illustrates the ETs for windows function before indexing. STRING_AGG, this example demonstrates that Oracle performs significantly faster than the other databases.

Fig. 4. The execution times for various windows function types across oracle and SQL server with indexing.

4.4. Security Evaluation Comparing

Three DBMSs were compared for their security features on data encryption, access control, auditing, and data masking under three aspects: CPU cost, memory cost, and IO cost.

1. Data Encryption: encryption in-flight and at rest is supported by all three DBMSs, with only Oracle providing extensive in-flight encryption features.

2. Authentication and Access Control: granular role-based access control (SQL Server = best, Oracle, MySQL less flexible).

3. Auditing: Oracle supports by far the most robust auditing options for fine-grained tracking of your database activity. SQL Server offered robust auditing features, and MySQL’s auditing was more basic.

4. Data Masking: As a pioneer in masking technology, Oracle was well equipped with both dynamic data masking and redaction features. SQL Server also had a good reputation for data masking. However, the options for MySQL were fewer. If you follow these security tools and best practices, can take the security of MySQL to the next level. MySQL can be used as a good backend storage solution for secure applications such as those handling finance, health, government and other legislation-critical data as shown in Table 5.

TABLE 5: Overview of security tools and practice for MySQL

4.5. ML Integration

The integration of ML capabilities was compared across the three DBMSs:

SQL Server worked with Azure ML to provide rich capabilities for performing real-time data processing and predictive analytics.

Oracle came with some powerful in-database ML algorithms but did not have great flexibility, unlike SQL Server’s integration with external tools.

MySQL: No native ML integration; there was a need to use it beyond the database to perform more complex data processing. There are plenty of third-party tools and APIs that assist in the integration of ML with MySQL, which help bridge the gap between the database for a much easier workflow between it and the ML. Some of them are described below. Illustrated in Table 6.

TABLE 6: Overview of Third-Party tools and API for MySQL in ML integrations

4.6. Connectivity with Non-Relational Databases

The ability of each DBMS to connect with NoSQL databases was also evaluated.

Oracle provided high-performance, low-latency connectivity to MongoDB, Cassandra, and other NoSQL databases. Enabled enterprises to manage hybrid database environments effectively.

SQL Server: by seamlessly extending operations to NoSQL databases, including Azure Cosmos DB, extending capabilities.

MySQL: third-party plugins offered a limited ability to connect to NoSQL databases. The following are third-party integrations or plugins to extend MySQL’s competitive advantage in NoSQL environments, by reducing complexity and resource costs: These third-party integrations and plugins can help MySQL remain competitive in NoSQL environments by extending its capabilities, improving scaling, and optimizing resource usage, all while retaining a relational footing. Illustrated in Table 7.

TABLE 7: Overview of Third-Party Integrations and Plugins Enhancing MySQL in NoSQL Environments

5. DISCUSSION

A comparison of aggregate functions in Oracle, SQL Server and MySQL shows differences in performance, functionality as well as optimization techniques also some integration features. Of course, each RDBMS has its unique pros and cons that make them suitable for several types of use cases. The time taken for a given query to run on one large dataset varies due to some optimization the database engine performs internally, as well as factors such hardware present in the system and how complex that is and naturally size of data.

Factors to Consider

Each DBMS Type with Real world use cases so that it makes sense for practical applications -

Oracle: Is used by large financial services organizations for sophisticated risk calcs and reporting. Faster and Simple Analytics: Real-time analysis with advanced aggregate functions & in-database ML.

SQL Server: E-commerce platforms running on SQL Server system for analyzing customer sales patterns and maintaining inventory. High performance window functions combined with Azure ML integration for dynamic pricing and personalized recommendations.

MySQL: Web applications; startups using MySQL for collating user data and tracking activity. It works perfectly fine for high-traffic webs due to the simplicity of basic aggregations.

6. CONCLUSIONS

This study is a comparative analysis of Oracle, SQL Server, and MySQL databases in areas of aggregate functions and windows functions, ML workflows, non-relational (NoSQL) integration, and data security. The findings show that all the three databases are appropriate for analytical and data science queries, but with varying capabilities.

Aggregate Functions: Oracle and SQL Server are faster at running grouped functions over large sets of data than MySQL, which can mostly just handle simple aggregations.

ML Integration: Oracle and SQL Server both have advanced in-database ML features. If you want to work with a relational DBMS that can handle ML operations directly on the data without needing to send it somewhere else, Oracle and SQL Server are your safest bets. Interestingly, MySQL is weakest in this area. Although MySQL can be configured to send data for ML operations to external ML tools, this requires a significant investment of time and resources sometimes it requires cost.

NoSQL Integration: Both Oracle and SQL Server score highly on integrating with NoSQL databases, so they are well-suited to hybrid data environments of the kind found here—while MySQL can not integrate natively, integration is available third-party.

Data Security: Oracle and SQL Server offer strong security features, capable of providing end-to-end data encryption or at least strong auditing features. MySQL offers only basic security features. Enhancing security with third-party services can be effective, but it often requires additional effort and can be expensive. In addition, these findings indicate that Oracle and SQL Server can be better choices for systems that require more complex data processing, demanding security, scaling, and integration with non-relational or unstructured data. MySQL, on the other hand, can be used for lighter applications that do not require advanced features.

Future lines of research could investigate if these databases perform better in different hardware configurations/cloud environments, which are increasingly becoming the norm in a majority of modern applications. It would also be interesting to see if the newer ML models fare better once integrated with the NoSQL databases. Another interesting point to study would be how does the cost competitiveness of each DBMS in the market at play when compared on a scale such as performance in cases where a decision-maker wants to sell something basis its relative cost. Concept with possible directions for future research in the field.