A Clustering and PL/SQL-Based Method for Assessing MLP-Kmeans Modeling

Abstract

With new high-performance server technology in data centers and bunkers, optimizing search engines to process time and resource consumption efficiently is necessary. The database query system, upheld by the standard SQL language, has maintained the same functional design since the advent of PL/SQL. This situation is caused by recent research focused on computer resource management, encryption, and security rather than improving data mining based on AI tools, machine learning (ML), and artificial neural networks (ANNs). This work presents a projected methodology integrating a multilayer perceptron (MLP) with Kmeans. This methodology is compared with traditional PL/SQL tools and aims to improve the database response time while outlining future advantages for ML and Kmeans in data processing. We propose a new corollary: $h_k\to H=SSE\left(C\right),wherek>0and\exists X$, executed on application software querying data collections with more than 306 thousand records. This study produced a comparative table between PL/SQL and MLP-Kmeans based on three hypotheses: line query, group query, and total query. The results show that line query increased to 9 ms, group query increased from 88 to 2460 ms, and total query from 13 to 279 ms. Testing one methodology against the other not only shows the incremental fatigue and time consumption that training brings to database query but also that the complexity of the use of a neural network is capable of producing more precision results than the simple use of PL/SQL instructions, and this will be more important in the future for domain-specific problems.

1. Introduction

With the arrival of new servers in the world of database technology, it is necessary to implement search engine optimization to improve the process time and resource consumption. It is essential to understand how AI can reduce costs and consumption. In recent years, cloud computing development has focused on the explosive growth of non-structured data, which leads to cloud storage and control loss risks, including privacy leakage [1], among others. The growth of mobile devices has promoted the development of apps that rely on external loading services. These limitations often lead to latency issues in service functions, requiring multi-server cooperation through pervasive edge computing, which aids data migration in highly dynamic mobile networks [2].

Data stored on these devices cannot meet security requirements independently. Blockchain technology has been applied alongside storage and connection technology within an integrated warehouse model [3]. Some applications involve transferring data, for example, from a mobile device to a vehicle hub or a server, which can merge through a fixed measure—a technique known as superposing data [4]. Similarly, cloud computing, and the internet of things emerged in the 21st century as communication technology platforms. Their adoption has enabled improved service levels and systematic, continuous innovation [5]. These factors underscore the need to enhance query speed and reduce the percentage of database usage.

In terms of security, rapid network development has increased the exposure of databases, requiring SQL instructions to be rebuilt and recompiled. However, existing audit products need more precise analysis [6]. One primary technique violating database security is SQL injection, which involves using DDL commands (like DROP TABLE), DML commands (like DELETE FROM), and DCL commands (like REVOKE) to delete data or to block everyday activities. Using a tow environment to retrieve the universal resource locator (URL), associated with the SQL injections’ origin, has proved to be effective for the detection of websites subject to vulnerabilities [7].

Data are critical; their leak or corruption can undermine confidence and lead to the collapse of any enterprise. Concerns about cloud computing’s lack of security are significant because this lack of security can fundamentally affect a business [8]. Unique aspects, such as content perception, real-time computing, and parallel processing, introduce new challenges to security and privacy, creating paradigms in mobile and fog computing [9]. For example, vehicle network security (a vehicular ad hoc network) is affected by message identity validation and reliability when data are shared between various units. A security and storage system based on corporate blockchain has been utilized [10]. Increasing the computing resource speed requires improving the algorithm design for those who manage data queries.

The database query system is currently supported by a standard SQL model that has maintained the same functional design since the emergence of PL/SQL. Research and development centers have focused on computing capabilities, encryption, and security. However, AI tools like Generative AI and ML models, such as artificial neural networks (ANNs), have yet to receive similar investment.

This work aimed to present a methodology integrating an MLP with Kmeans and compare it with traditional PL/SQL tools. This approach aims to improve the database response time, and this work outlines future advantages of using already trained data. The research question posed is the following: What is the effect of an MLP-Kmeans classification model on data servers during search engine execution compared to PL/SQL deterministic models? This methodology will be presented in three parts: 1. Establishing the PL/SQL model for query production; 2. Establishing the MLP-Kmeans model, including parameterization; and 3. Developing a comparative scheme between both models.

2. Background

To illustrate the proposed methodology, Figure 1 presents a comparative scheme between the current PL/SQL query technique and the general MLP model, which includes training and clustering. While the PL/SQL data query is performed based on data indexation, the MLP model starts with a Kmeans model.

2.1. Concerning the SQL Current Model

The process of assessing the quality of a query becomes exponentially challenging as query engines advance their techniques and optimization strategies, such as those used in an SQL Server [11]. Some modern databases are empowered with machine learning (ML), including SQL4ML, where users can write an objective function in a model similar to an SQL sentence. The model translates this into its equivalent in a TensorFlow graph, which can differentiate and optimize the model’s learning weights, achieving a 50% improvement in execution time and an 85% reduction in exported data [12]. Traditional database optimization techniques—such as cost estimation, joint arrangement selection, and mandate adjustment—often fail to meet high-performance requirements for large-scale database instances with diverse applications and users, especially in the cloud. Basic ML techniques can help alleviate these issues through historic data optimization learning [13].

In SQL, there are five instruction categories [14]: 1. Data Definition Language (DDL), which deals with table definitions, views, procedures, and functions—some instructions include CREATE TABLE, CREATE CONSTRAINT, DROP, and TRUNC; 2. Data Manipulation Language (DML), which focuses on data updates—instructions include INSERT, UPDATE, and DELETE; 3. Data Query Language (DQL), which manages data queries—the primary instruction is SELECT; 4. Data Control Language (DCL), which grants database privileges to users—the primary instruction is GRANT; and 5. Transaction Control Language (TCL), which assists with internal data administration, primarily data recovery—its primary instruction is ROLLBACK.

Sequences in SQL instructions are produced by application query generators like Relational OLAP, where report and analysis tasks are automated [15]. Formatting rules include identifying the necessity of indexes on specific columns if filters are included to optimize queries, avoiding unnecessary data usage and reducing execution time [16].

However, to the authors of [17], there are two main problems in optimizing queries:

• Computing the graph’s transitive closure.
• Obtaining the power of the adjacency matrix, evaluated using four graph types in efficient ascending order: binary trees, lists, cyclic graphs, and complete graphs.

Alternative data analysis techniques, such as Bayesian algorithms, offer solutions with advantages like handling parametric uncertainty in the study of irregular models [18]. In regression analysis involving predictors, variables are traced from the idea of sufficiency through subdivisions identified by categorical variables [19].

2.2. Concerning MLP-Kmeans

Generating symbolic rules is a natural way to determine inherent knowledge in interconnected models. When using MLP architecture-based rules with parallel axes positioned precisely, the values transformed into vectors can be effectively utilized in neural networks and support vector machines (SVMs) [20]. MLP and SVM algorithms, along with random forest and decision tree algorithms, have been employed to build predictive models for academic performance, including for students with disabilities, to enhance their college potential [21]. Query optimization is a sophisticated task, and validating its precision and effectiveness requires using relational algebra. This includes various classification and sorting techniques, such as the modular isomorphism problem, which assesses the performance of similar morphological groups and group classification by performance [22,23]. AI Models like deep learning (DL) models, convolutional neural networks (CNNs), and recurrent neural networks (RNNs) have been barely used in solving information security problems such as attack detection [24]. However, ref. [25], a relational and comparative analysis involving DL, the internet of things, and big data (BD), showed that current security solutions require updates similar to those already implemented with DL, which researchers and organizations have widely accepted.

The MLP algorithm is an artificial neural network (ANN) with at least three layers: input, hidden, and output [26]. It is commonly used due to its ability to model linear and non-linear estimative structures [27]. MLPs are also widely employed in classification tasks, capable of arbitrarily approximating complex functions to obtain results [28]. The two most frequently used activation functions in learning models are Sigmoid and ReLu [29]. The MLP has been applied effectively in treating and diagnosing chronic kidney disease, making it one of the most effective applications of an ANN [30].

Specifically, perceptron theory addresses how information is perceived and remembered, influencing recognition and behavior even after the input stops [31]. A perceptron is a weight optimization model that associates inputs (variables in a query) to desired results (the query) through an initial state (selected randomly) and an optimization process based on descending gradients, where the optimized variable is the response error compared to the desired outputs. On a broader scale, this also involves understanding the basic hardware for an ANN, projected on a larger scale depending on each neuron’s productivity at the execution level. This approach has shown favorable results in reconfiguring systems for integrated circuits, becoming an essential task [32].

The MLP architecture depends on the number of selected layers, hidden nodes in each layer, and the objective function. An improvement in this design is establishing control for each layer and removing those layers without connections, assigning a binary variable to each connection, which takes the value of 1 if the connection exists [33]. Learning models based on the standardization of non-linear processing elements have been designed to reduce the error cost function, which applies to predicting chaos in time series [34]. It is crucial to correctly select parameters to allow the model to adapt to observed data, appropriate layers, and weights. To address this, combinations of genetic algorithms and backpropagation have been proposed using one hidden layer [35].

3. Materials and Methods

This research was divided into three stages: the first one produces random data and stores it on a comma-separated value (CSV) file; the second one builds a schema on an standard Oracle ^® database instance, where PL/SQL queries will be executed; and the third one constructs the MLP-Kmeans model, where training data will be used for a query, as described in Figure 2. On the PL/SQL side, queries were made through database indexation. On the MLP-Kmeans side, after training the dataset, the query, now termed a hypothesis, performed clustering operations.

3.1. Database and Schema Creation

The dataset simulates retail business operations and was created randomly, resulting in a CSV file with 306,849 records. The dataset includes the following fields:

• line: corresponds to the product line and is an Integer type.
• idproduct: corresponds to the product ID number and is an Integer type.
• amount: corresponds to the quantity sold and is a Double type.

All fields are numeric. Classification and clustering could be applied on fields line and idproduct.

In the sophisticated importation from text to SQL models (text-to-SQL), a binding a schema is considered easy, with errors mainly attributed to noise [36]. Systems that interpret human expressions with SQL instructions leverage exact lexical synchronization methods between keywords and schema elements. These methods face challenges in scenarios such as synonym substitution, where differences arise during synchronization [37]. To address this problem, schema preprocessing for tables and method redesign, consisting of schema expansion and schema pruning, have been incorporated [38].

3.2. PL/SQL Modeling

According to [39], database schema structures are sometimes dismissed either because they are too plain or because they are expected to be analyzed during the training or testing stages. For this work, a schema was built within an Oracle ^® instance using DDL instructions: CREATE PROFILE..., CREATE TABLESPACE...and CREATE USER...

Data were imported from the CSV file into a table with the same name (ROWDATA) and identical data configuration. A test table was created from the same source by selecting one out of every 100 records, resulting in a table with 3069 records called ROWDATA_TEST.

PL/SQL Query Process

An essential part of software development is understanding how time is spent during program execution. However, more training is needed in using profilers, which often show bugs. These issues can be addressed by establishing a metaphorical relationship, where a bridge is built between statistical coverage and existing mutations [40]. Data profiling ranges from simple additions to complex statistics, aiding in understanding data. From a scientific perspective, it is crucial to know the data profile for manipulation and to update it according to new data schemas [41]. For this work, three queries were defined as follows:

• Line query. Request for line, product, and amount columns.
• Group query. Request for the sum of amount for each product.
• Total query. Request for the sum of all data amount.

3.3. MLP Modeling

Clustering modeling algorithms have been used to elaborate on the three kinds of queries, which, according to [42], are defined by an input and an output.

3.3.1. Input

The input is defined as a set of elements X and a distance function d as follows: $d:X\times X\to {\mathbb{R}}^{+}$, where $R^{+}$ is the set of non-negative real numbers, and when it is asymmetric, it satisfies $d(x,x)=0$ for all subsets x that belong to X. In some cases, it requires a parameter k that determines the number of clusters, and according to the three queries established, we can have $k=0$ (line query), $k\ast$ (group query), and $k^d$ (total query).

3.3.2. Output

The output is a domain partition of X where $C=(C_1,C_2,\cdots ,C_k)$, producing the expression ${\bigcup }_i^k{C}_i=X$ and that for every $i\ne j,C_i\bigcap C_j=\varnothing$.

All groupings were taken from the sample X for each i cycle corresponding to the cluster defined by k, where the difference between epochs will produce an empty set. There is a link between clustering and optimal transport, equivalent to a restrained formulation where cluster dimensions are prescribed. This makes sense in a structure where each dataset class portion is known, but there is no information available at the individual level, aiming to establish concordant parameters [43].

In this work, the algorithm defined in [44] was used. Its mathematical representation is as follows:

$$SSE\left(C\right)=\sum _{k=1}^K\sum _{x_i\in C_k}{\Vert x_i-C_k\Vert }^2$$

(1)

where $SSE$ is the square sum of errors, C is a cluster, $x_1$ is the dataset instance that consists of N points, and $C_k$ is the cluster centroid in k, which is updated for each iteration according to

$$C_k=\frac{{\sum }_{x_i\in C_k}{X}_i}{|C_k|}$$

(2)

where $|C_k|$ is the total number of points in the kth cluster.

3.3.3. Hypothesis and Contribution

To address the proposed problem, the following corollary was derived from the theorem presented in Equation (1). For any query or hypothesis h, the search function $f\left(s\right)$ is established as follows:

$$h_k\to H=SSE\left(C\right),wherek>0and\exists X$$

(3)

where $h_k$ is the hypothesis corresponding to the cluster index k, H is the set of all possible hypotheses according to the data availability, $SSE\left(C\right)$ is the square sum of errors such that k is $>0$, and it exist in the dataset X, which constitutes the scientific proposition of this research.

3.3.4. Pseudocode

According to the last equation, the theorem’s executable application is set through the centroid closeness for each k measured using its Euclidean distance and established through the pseudocode of Algorithm 1.

Algorithm 1 Centroid definition for three dimensions (x,y,z) and its euclidean distance

Require: $k>0,Datase{t}_{Normalized}$ Ensure: $EuclideanDistance$  1: $Centroid$←$(x,y,z)position$  2: for $k\leftarrow 1$ to N do  3:     $Centroi{d}_k$←$Euclidean(x,y,z)$  4: end for  5: return $MLP\leftarrow NewCentroidValue$

3.3.5. Parameterization

According to the three hypothesis requested, cluster parameterization is defined as follows:

1.

Line query: when each element is a cluster or the size of the cluster is equal to the size of elements.

2.

Group query: when each k cluster is the sum of all i clusters with the same requested parameter.

3.

Total query: when there is only one cluster.

Under this proposal, 306,850 registers were analyzed with three points, A, B, and C, each one with three coordinates: x, y, and z. Although the algorithm was designed for objects in three dimensional calculations (coordinates), in this case, Euclidean and centroid distances were measured only using the x coordinate, corresponding to the point and data: A (line), B (idproduct), and C (amount). The coordinates y and z remained with their initial 0 value.

3.3.6. Training Using Kmeans

Kmeans is used for data classification into categories or clusters, referred to as k, based on the proximities of the Euclidean distances from their centroids. In this study, the training process began by selecting k values randomly from the dataset. Each record was then assigned to the nearest k, based on the Euclidean distance. After the initial assignment, the centroids of each k were iteratively adjusted over n epochs to achieve the optimal Cartesian position.

3.3.7. Parameters for MLP-Kmeans Model

The MLP architecture is defined by the triplet $(V;E;\sigma )$, where V represents the number of layers, E denotes the boundaries that correspond to the weights, and $\sigma$ is the activation function. With the Kmeans component integrated, the entire model is depicted in Figure 3.

The connection between the two models was established through the group query hypothesis provided by Kmeans, and the interpretability of the MLP model drives to the amount and categorization of the clusters that will be set for analysis in the Kmeans process. The proposed methodology helps as a hash table estimated to improve the computation and resources to minimize the response time in querying a data base. The layers V are defined as follows: $V_1$ is the input layer with 2 neurons, corresponding to the k values provided by Kmeans. $V_2$ is the hidden layer with 10 neurons, and $V_3$ is the output layer with 1 neuron, corresponding to the specific prediction or hypothesis. The value of $\sigma$ corresponds to the sigmoid function. In this work, each neuron requires 3 input values $X+1$, corresponding to the $W+bias$ values. The inner product <$X,V$> is calculated, the activation value is defined, and the weight value is transferred to the successive layers, as illustrated in Figure 4 and the algorithm pseudocode in Algorithm 2.

Algorithm 2 Layer definition for MLP model linked with Kmeans model

Require: $k>0$ Ensure: $\sigma <X,V>$  1: function Loop($epoch\left[\right]$)  2:     N←$length\left(Dataset\right)$  3:     for $k\leftarrow 1$ to N do  4:         $loss\leftarrow prediction$  5:         $error\leftarrow MeanSquareLoss$  6:         $Output\leftarrow prediction$  7:     end for  8:     if $Output\in k$ return $Output$  9: end function

4. Results

The training dataset was rewritten from NoSQL by eliminating unnecessary data, removing columns with non-numeric values, and normalizing the remaining data to standardize different categories and visualize query results. For the MLP-Kmeans model, the design incorporated a clustering perspective with corresponding parameters attached to each aleatory category and hypothesis. Finally, a comparative analysis was conducted between both models to discuss the results and establish conclusions.

4.1. PL/SQL Results

At the end of the research activity, the results obtained for each case are the following.

4.1.1. Line Query

Table 1. Line query.

Line	Idproduct	Amount
68	10,505	139.50
21	10,531	51.90
68	10,505	139.50
17	10,526	252.90
18	10,509	379.20
21	10,511	107.00
...	...	...
39	10,491	78.90
	Time	0.009 s

displays the query line by line without category definitions. This would be the equivalent in Kmeans to k = 0, or the lack of k, showing all data and the total amount of the run-time consumption.

4.1.2. Group or Category Query

Table 2. Group query.

Idproduct	Sum (Amount)
218	50,872,993.50
222	78,224.60
265	10,404.10
272	451,400.20
399	67,086.75
400	64,177.30
449	1,203,522.50
452	146,898.59
465	375,147.40
...	...
472	24,944.70
Time	0.08 s

presents the data grouped by categories (lines), and the count of each idproduct by line. In Kmeans terms, this is equivalent to a hypothesis k. The third column shows the sum of the amount value for each line and the total process time consumption.

4.1.3. Total Query

When the query is applied for one category without using filters, the total calculation k value is 0, and there is no need to deploy data. The result is the total sum of 306,849 lines in 0.013 s. Group queries are the slowest due to the grouping operation.

4.2. MLP Results

The outcome of the MLP-Kmeans model is presented as follows, starting with the Kmeans analysis.

4.2.1. Line Query

For this query, the parameters were set as $k=0$ and $epochs=0$. The result was less than 0.009 s.

4.2.2. Group Query

Table 3. Clusters selected randomly from the dataset.

K	Dataset	A	B	C	Centroid
0	37154	0.14	0.96	1.00 × ${10}^{-3}$	0.37
1	9103	0.85	0.19	1.30 × ${10}^{-3}$	0.34
2	77408	1.00	0.96	2.20 × ${10}^{-3}$	0.65
3	26641	0.81	0.97	1.40 × ${10}^{-3}$	0.59
4	69240	0.00	0.01	1.20 × ${10}^{-3}$	0.00
5	48619	0.47	0.96	2.50 × ${10}^{-3}$	0.48
6	86328	0.47	0.96	4.00 × ${10}^{-3}$	0.48
7	65975	0.23	0.97	1.40 × ${10}^{-3}$	0.40

represents the classification of randomly selected categories, with parameters set as $k=8$ and $epochs=10$. The records (X values) were grouped according to the Euclidean distance concerning each k.

From the last table, training consisted of adjusting the centroid to the most efficient point from the Euclidean distance concerning each data centroid of its particular category k, resulting in

Table 4. Trained clusters (10 epochs).

K	A	B	C	Centroid
0	0.16	0.96	1.40 × ${10}^{-3}$	0.37
1	0.25	0.71	4.10 × ${10}^{-3}$	0.32
2	0.97	0.97	3.60 × ${10}^{-3}$	0.65
3	0.75	0.97	1.60 × ${10}^{-3}$	0.57
4	0.06	0.06	4.40 × ${10}^{-3}$	0.04
5	0.44	0.96	2.20 × ${10}^{-3}$	0.47
6	0.53	0.97	2.10 × ${10}^{-3}$	0.50
7	0.46	0.75	2.70 × ${10}^{-3}$	0.40

.

The Elbow Method was used to find the optimal value of k, where the sum of the square distance between one cluster and the centroid cluster (within-cluster sum of square, WCSS) was calculated. For comparative reasons, Figure 5 and Figure 6 are shown for cases where $k=8$ and $k=25$.

To determine the optimal cluster number, the k value must be selected when distortion or inertia begins to decrease linearly. In Figure 4, this happens from $k=4$, and in Figure 5, it happens when $k=7$.

4.2.3. Total Query

The parameters were established as $k=1$ and $epochs=0$.

4.2.4. Summary

In summary, the previous queries defined three hypotheses, and their efficiency relationship is shown in

Table 5. Solved hypothesis.

Hypothesis	Description	Time(s)
$k=m$	Line query	0.00
$C_k=i:\sum C_i\exists m$	Group query	2.46
$C_k=1:h_k\sum C_m$	Total query	0.27

. It is important to note that all queries were made after data loading and normalization, causing a 0-time consumption in line query. Also, the execution was not presented in the compiler console.

The model was run multiple times with different parameters to enhance the data analysis. The indicators obtained from the model execution after 50,000 epochs were the Mean Absolute Error (MAE) = 0.16, Mean Square Error (MSE) = 0.03, Root Mean Square Error (RMSE) = 0.18,, and Coefficient of Determination ($R^2$) = 0.29. The learning curve shown in Figure 7 indicates a consistent trend from the 20,000 epochs, so extending it further is unnecessary due to the emergence of overfitting.

4.3. Comparative Analysis between PL/SQL and MLP-Kmeans

Once the experimentation was realized, a comparative analysis between the two models was conducted.

Table 6. Comparative efficiency between PL/SQL and MLP-Kmeans (seconds).

Hypothesis	PL/SQL (s)	MLP-Kmeans (s)	Difference (s)
Line query	0.00	0.000	−0.00
Group query	0.088	2.46	2.37
Total query	0.013	0.27	0.26

summarizes the results, indicating the increase in time consumption in the MLP-Kmeans trained model concerning the PL/SQL model.

5. Discussion

In the case of the first hypothesis, the line query where $k=m$, it was not necessary to perform training because the number of clusters was the same as the number of data lines, so the data retrieval was immediate. In the case of the group query, $k=n$, where n is the number of clusters selected, the level of training defines the efficiency: time is proportionally direct to the value of k. In the case of the total query, where $k=1$, the data were obtained after grouping some randomly selected centroids. In the end, training consumption is represented by the model’s friction and fatigue: friction for the k number and fatigue for the $epoch$ quantity defined in the parameters. Once the model was trained, efficiency depended on the hardware resources.

Table 7. Quality comparison with other similar models.

Author	Model	Data	Epochs	K
[45]	Unsupervised Kmeans	400	11	k
[46]	Kmeans clustering jointly	N/A	N/A	$1\le k\le K$
[47]	Kmeans FE	50	N/A	N/A
[48]	Parallel Kmeans	Random Pool	N/A	$k=2$
[49]	Kmeans spherical	REST	N/A	$k=6$
Proposed work	MLP/KMeansk	306,849	4	$k=10$

shows some other models used in other research to define the optimal k value.

6. Conclusions

When comparing different methodologies, it becomes evident that training can lead to increased fatigue and consume a lot of time when dealing with database queries. Additionally, the complexity of using a neural network can produce more accurate results compared to using simple PL/SQL instructions in the testing scenario. This will be particularly significant in the future for specific problem-solving scenarios, such as intelligent environments characterized by prescriptive control achieved through self-programming processes, resulting in holistic functionality, for which this work should be extended to [50]. For instance, generative AI for tendency queries is required to analyze trends on social networks to group frequencies and data types and determine real proclivity for productive user entailment. This can be achieved using centroids.

The following contributions were made in this work:

• The proposal for a corollary from the basic Kmeans theorem.
• The definition of a centroid training model for Euclidean distance for n coordinates.
• An MLP-Kmeans model for classification.