Query Techniques Research Articles

Optimization Techniques for Complex Queries in Redshift and BigQuery

Query optimization becomes particularly relevant in big data analytics systems due to the need that is required. Such environments are faced with queries involving joins aggregation and sorting of terabytes or petabytes of data. If we have them in their nonoptimized forms, finishing these queries could take hours or days if the results are required in a real-time or near real-time fashion. Query optimization covers data retrieval processes and eliminates the difficulties of searching for data not required, hence making them faster and charging less computational power. Keywords: Amazon Redshift, Google BigQuery, Query Optimization, Performance, MPP

Detecting Data Leaks due to SQL Injection

Abstract—SQL injection is a prevalent web application vulner- ability that allows attackers to manipulate database queries, often leading to severe data leaks. This paper surveys existing research on SQL injection attacks and various detection and prevention techniques. We explore classical and modern approaches, includ- ing machine learning paradigms, input verification methods, and parameterized query techniques. By examining the strengths and weaknesses of these approaches, this paper proposes a compre- hensive prevention framework. The challenges, limitations, and future directions for enhancing SQL injection security are also discussed. Index Terms—SQL, vulnerability, queries, leaks, approaches, prevention, machine learning, parameterized query, framework, injection

Space-time tree: a spatiotemporal construct for efficient similarity matrix calculations among network-constrained trajectories

Data mining of network-constrained trajectories has broad applications in the GIScience field. The calculation of a complete trajectory similarity matrix is a key step in various data mining algorithms. However, computing this matrix is computationally intensive for large datasets, as it involves numerous point-to-point shortest-path (PPSP) queries. To tackle this issue, we propose a new spatiotemporal construct called the space-time tree, which directly delineates the network distance from a query trajectory to any network space-time point. By constructing the space-time tree, we can efficiently compute the trajectory similarity matrix without additional PPSP queries. The space-time tree supports several similarity metrics, including closest pair distance, furthest pair distance, longest common subsequence (LCSS), and distance-weighted LCSS. It can further integrate with advanced spatiotemporal query techniques for scalable partial trajectory similarity matrix calculations. A case study using real datasets was conducted to apply the space-time tree in the trajectory clustering application. The results show that the space-time tree completed the clustering task on 0.5 million trajectories within 49 minutes, achieving a nearly 147-fold speedup compared to state-of-the-art methods.

User Interface and User Experience Design for Vidyanusa on Android Mobile Phones Using an Evaluation Framework

This research will evaluate the User Interface and User Experience (UI/UX) of Vidyanusa for Android mobile phones. User Experience will be measured using sub-variables such as attractiveness, agility, efficiency, dependability, stimulation, and novelty. The object of this research is User Experience. The results of this research will be used to evaluate Vidyanusa's Android mobile product to achieve a product that is suitable for users at the junior high school level. User experience testing on the Vidyanusa mobile application uses evaluation methods with query techniques. Evaluation is a test of the level of system usability and functionality that is carried out in the laboratory, in the field, or in collaboration with users. What is evaluated is the design and implementation, and the technique used is the evaluation technique with the User Experience Questionnaire. The results of the user experience testing of the Vidyanusa mobile application tend to be negative on the points of attractiveness, perspicuity, efficiency, dependability, and novelty based on the User Experience Questionnaire in the benchmark table, but positive on the point of stimulation.

FurMoLi: A Future Query Technique for Moving Objects Based on a Learned Index

The future query of moving objects involves predicting their future positions based on their current locations and velocities to determine whether they will appear in a specified area. This technique is crucial for positioning and navigation, and its importance in our daily lives has become increasingly evident in recent years. Nonetheless, the growing volume of data renders traditional index structures for moving objects, such as the time-parameterized R-tree (TPR-tree), inefficient due to their substantial storage overhead and high query costs. Recent advancements in learned indexes have demonstrated a capacity to significantly reduce storage overhead and enhance query efficiency. However, most existing research primarily addresses static data, leaving a gap in the context of future queries for moving objects. We propose a novel future query technique for moving objects based on a learned index (FurMoLi for short). FurMoLi encompasses four key stages: firstly, a data partition through clustering based on velocity and position information; secondly, a dimensionality reduction mapping two-dimensional data to one dimension; thirdly, the construction of a learned index utilizing piecewise regression functions; and finally, the execution of a future range query and future KNN query leveraging the established learned index. The experimental results demonstrate that FurMoLi requires 4 orders of magnitude less storage overhead than TPR-tree and 5 orders of magnitude less than B+-tree for moving objects (Bx-tree). Additionally, the future range query time is reduced to just 41.6% of that for TPR-tree and 34.7% of that for Bx-tree. For future KNN queries, FurMoLi’s query time is only 70.1% of that for TPR-tree and 47.4% of that for Bx-tree.

Secure traceability mechanism of green electricity based on smart contracts and provenance model

Green and low-carbon development is an important part of global sustainable development. Green power trading provides strong support and assurance for promoting green and low-carbon development. Due to the long cycle of green power data chains and their susceptibility to malicious tampering, the integrity and traceability of data are difficult to guarantee. Therefore, this paper first proposes a security provenance model with enhanced relations based on the core structure of PROV and blockchain technology, which can securely capture provenance records, use the transfer time and number of transactions between various links in the traceability network as reasoning clues, realize the correlation tracing of the green electricity transfer process. Under the model, a traceability mechanism of green electricity is designed based on smart contracts. Trustworthy green electricity data collection is achieved through data filling and data verification techniques. Traceability query technique is adopted to achieve trustworthy traceability of green electricity. And the effectiveness of the proposed solution is demonstrated through simulation experiments.

SPARQL Optimization Using Re-ordering Joining Patterns with Surrogate Key Concept and Subset Patterns

Semantic web data resides on the web in the form of knowledge graphs known as RDF graphs and searching around the web has been always a crucial task. For the data retrieval of RDF data of the semantic web, SPARQL query language has been used which in turn is based on triple patterns and joins. Optimization of SPARQL query has been a problematic concern for decades due to the large amount of triple patterns associated with RDF data. Although several researchers have put a lot of effort into the optimization of SPARQL query, it is difficult to understand the concept from scratch due to its diversified nature. This paper analyses various optimization techniques for the SPARQL query used with the semantic web to process knowledge graphs. These techniques include join-based, heuristic-based, rule-based, and indexing-based approaches for optimization. This paper will help researchers in this domain to easily get into the core concept of SPARQL execution along with various optimization approaches used for query processing, which can help in various other domains like linked open data and information retrieval. In this paper, an optimization algorithm HSOA (hybrid SPARQL optimization algorithm) has been proposed, which comprises the features of index-based, cost-based, and triple reordering-based optimization approaches. The proposed hybrid algorithm has been designed specifically for n-triple RDF data, which comprises subset patterns, and surrogate key concepts. The results produced by the proposed algorithm are encouraging and have also been tested and compared with the benchmark dataset and SPARQL queries like LUBM, BSBM, and SP2Bench.

Novel Probabilistic Collision Detection for Manipulator Motion Planning Using HNSW

Collision detection is very important for robot motion planning. The existing accurate collision detection algorithms regard the evaluation of each node as a discrete event, ignoring the correlation between nodes, resulting in low efficiency. In this paper, we propose a novel approach that transforms collision detection into a binary classification problem. In particular, the proposed method searches the k-nearest neighbor (KNN) of the new node and estimates its collision probability by the prior node. We perform the hierarchical navigable small world (HNSW) method to query the nearest neighbor data and store the detected nodes to build the database incrementally. In addition, this research develops a KNN query technique tailored for linear data, incorporating threshold segmentation to facilitate collision detection along continuous paths. Moreover, it refines the distance function of the collision classifier to enhance the precision of probability estimations. Simulation results demonstrate the effectiveness of the proposed method.

Unveiling the anti-obesity potential of Kemuning (Murraya paniculata): A network pharmacology approach.

Obesity has become a global issue that affects the emergence of various chronic diseases such as diabetes mellitus, dysplasia, heart disorders, and cancer. In this study, an integration method was developed between the metabolite profile of the active compound of Murraya paniculata and the exploration of the targeting mechanism of adipose tissue using network pharmacology, molecular docking, molecular dynamics simulation, and in vitro tests. Network pharmacology results obtained with the skyline query technique using a block-nested loop (BNL) showed that histone acetyltransferase p300 (EP300), peroxisome proliferator-activated receptor gamma (PPARG), and peroxisome proliferator-activated receptor gamma coactivator 1-alpha (PPARGC1A) are potential targets for treating obesity. Enrichment analysis of these three proteins revealed their association with obesity, thermogenesis, energy metabolism, adipocytokines, fat cell differentiation, and glucose homeostasis. Metabolite profiling of M. paniculata leaves revealed sixteen active compounds, ten of which were selected for molecular docking based on drug-likeness and ADME results. Molecular docking results between PPARG and EP300 with the ten active compounds showed a binding affinity value of ≤ -5.0 kcal/mol in all dockings, indicating strong binding. The stability of the protein-ligand complex resulting from docking was examined using molecular dynamics simulations, and we observed the best average root mean square deviation (RMSD) of 0.99 Å for PPARG with trans-3-indoleacrylic acid, which was lower than with the native ligand BRL (2.02 Å). Furthermore, the RMSD was 2.70 Å for EP300 and the native ligand 99E, and the lowest RMSD with the ligand (1R,9S)-5-[(E)-2-(4-Chlorophenyl)vinyl]-11-(5-pyrimidinylcarbonyl)-7,11-diazatricyclo[7.3.1.02,7]trideca-2,4-dien-6-one was 3.33 Å. The in vitro tests to validate the potential of M. paniculata in treating obesity showed that there was a significant decrease in PPARG and EP300 gene expressions in 3T3-L1 mature adipocytes treated with M. paniculata ethanolic extract starting at concentrations 62.5 μg/ml and 15.625 μg/ml, respectively. These results indicate that M. paniculata can potentially treat obesity by disrupting adipocyte maturation and influencing intracellular lipid metabolism.

Query Model Framework Design for Conservation History and Endowments Database: A Case Study on the Digitization of the Sumedang Larang Kingdom’s History and Endowments in Indonesia

The use of structured query formulation languages is a method for gaining access to data in information management systems. Writing structured queries is a powerful method of data retrieval, and end users can generate complex database queries by becoming proficient in a particular query language. Digitization of database information systems, history and endowment, and technology conservation are some examples of database conservation techniques in the process of displaying data that require optimal query techniques. Historical and endowment properties comply with a certain set of laws governing waqf recipients, which sets them apart from heritage and cultural assets. Trusted property typically comprises real estate, valuables, or both and has an infinite preservation term so long as it may still be put to use. A solid information technology infrastructure is essential for ensuring comprehensive data security. Given the availability of knowledge and the quick development of information technology, ensuring its sustainability is a challenge. This research is aimed at preserving historical and waqf databases based on the case study of the Kingdom of Sumedang Larang (KSL), Indonesia, through examination of the database, synchronization of digital data with physical data, as well as looking at its relationship with organizations and management. The proposed method, namely the Design and Analysis of Database Query Model for Preservation Information System (DAQMP), creates a recurring query model from the original tabular data placed in the next table as a special table check for data specifications containing important data. The results must be accurately aligned between digital and physical records regularly to serve as a record of any changes and as evidence to determine whether data has been corrupted, altered, or lost.

Spatial Blockchain: Enhancing Spatial Queries and Applications through Integrating Blockchain and Spatial Database Technologies

The fusion of spatial data with blockchain technologies presents an innovative approach towards a decentralized, secure, and trustworthy framework for spatial information management. This integration brings spatial representation to the forefront of blockchain, opening avenues for various sectorial applications. However, challenges like slow processing times, restricted query capabilities, and consistency issues have been identified within the blockchain system. Addressing these challenges, we propose an optimized method for spatial queries by leveraging the high-performance capabilities of spatial databases. Unlike conventional off-chain query techniques, our approach synergistically combines hyperledger fabric with a specialized spatial database. This fusion facilitates distributed spatial queries within blockchain framework, incorporating spatial operation functionalities into smart contracts while preserving the distributed nature of nodes and spatial databases. Enhancing system security, we incorporate a dual-stage verification mechanism alongside a salt-hash storage strategy to counteract potential unauthorized alterations. Initial results validate the efficacy of our methodology in terms of performance and security. Building on this foundation, we introduce a rental transaction system that effectively merges spatial data with blockchain, demonstrating the feasibility and potential of integrating spatial information into the blockchain, especially in the realm of real estate.

KGNav: A Knowledge Graph Navigational Visual Query System

Visual query is a vital technique for comprehending and analyzing knowledge graphs, which provides an effective method to lower the barrier of querying knowledge graphs for non-professional users. Nevertheless, visual query techniques for knowledge graphs and ontologies that have emerged in recent years cannot bridge the gap between global information provided by the knowledge graph schema and underlying data of knowledge graph. Thus it cannot fully exploit the global information to navigate users for querying knowledge graphs. This demonstration showcases KGNav, a Knowledge Graph Navigational visual query system. KGNav (1) redefines the minimal unit of operation to abstract the conceptual hierarchy, i.e., Knowledge Graph Schema, in the domain from the original knowledge graph in an offline semi-automatic way through the equivalence relations between these units; it also (2) provides a series of operators and an interactive GUI to capture user query intentions, guiding users to explore the Knowledge Graph Schema to achieve in-depth analysis of knowledge graphs. We will demonstrate the capability of KGNav in reducing tedious queries, enabling users to swiftly grasp the structure of the knowledge graph, and performing queries through several fundamental scenarios.

Optimization of Queries on Databases by Using the Ingres Algorithm

Bahrul Ulum Islamic Boarding School is the oldest cottage in Jombang district which was founded in 1830 AD until now. which of course has a very large number of students, both students and alumni status. The author wishes to research how to manage data in information systems with hundreds or millions of data quickly and easily. Proper query design is very influential on information system applications, if you only rely on conventional query techniques, the results needed to process queries are relatively slow, and the time required is also longer. The purpose of this research is to optimize the query using the ingress algorithm which will later be tested and compared the results with queries that have not been optimized or conventional to determine the extent to which the query process can be accelerated. The experimental results show that the execution time is more effective and faster using an optimized command using the Ingres algorithm with an average processing time of 0.13226 seconds, and an average request that has not been optimized 0.2079 seconds, in other words the result is 65% faster than unoptimized queries.

Effective matching of patients to clinical trials using entity extraction and neural re-ranking

Introduction:Clinical trials (CTs) often fail due to inadequate patient recruitment. Finding eligible patients involves comparing the patient’s information with the CT eligibility criteria. Automated patient matching offers the promise of improving the process, yet the main difficulties of CT retrieval lie in the semantic complexity of matching unstructured patient descriptions with semi-structured, multi-field CT documents and in capturing the meaning of negation coming from the eligibility criteria. Objectives:This paper tackles the challenges of CT retrieval by presenting an approach that addresses the patient-to-trials paradigm. Our approach involves two key components in a pipeline-based model: (i) a data enrichment technique for enhancing both queries and documents during the first retrieval stage, and (ii) a novel re-ranking schema that uses a Transformer network in a setup adapted to this task by leveraging the structure of the CT documents. Methods:We use named entity recognition and negation detection in both patient description and the eligibility section of CTs. We further classify patient descriptions and CT eligibility criteria into current, past, and family medical conditions. This extracted information is used to boost the importance of disease and drug mentions in both query and index for lexical retrieval. Furthermore, we propose a two-step training schema for the Transformer network used to re-rank the results from the lexical retrieval. The first step focuses on matching patient information with the descriptive sections of trials, while the second step aims to determine eligibility by matching patient information with the criteria section. ResultsOur findings indicate that the inclusion criteria section of the CT has a great influence on the relevance score in lexical models, and that the enrichment techniques for queries and documents improve the retrieval of relevant trials. The re-ranking strategy, based on our training schema, consistently enhances CT retrieval and shows improved performance by 15% in terms of precision at retrieving eligible trials. ConclusionThe results of our experiments suggest the benefit of making use of extracted entities. Moreover, our proposed re-ranking schema shows promising effectiveness compared to larger neural models, even with limited training data. These findings offer valuable insights for improving methods for retrieval of clinical documents.

Feasibility study for the implementation of number portability in Nicaragua

The document “Feasibility Study for the Implementation of Number Portability in Nicaragua” proposes the mechanisms to implement operator Number Portability, taking into account the ITU-T Series Q and E regulations. The existing information is analyzed, after the collection of information through interviews with operator officials, as well as surveys of users of telephone services. After reviewing the advantages and conditions of the different techniques to implement number portability, we recommend the All Call Query technique, a simple and effective method, as well as operator Number Portability, for which you need intelligent network capabilities to recognize the ported number, the status, the conditions and the operator must develop activities to adapt their networks to this model. The documents that legally and regulatory support the implementation of Number Portability are: Administrative Agreement No. 036-2003, the Numbering Resource Regulations and the National Numbering Plan, all of these issued by TELCOR Regulatory Entity, the Free Trade Agreement between the Dominican Republic - Central America and the United States (DR-CAFTA) and the Regional Technical Telecommunications Commission of Central America, COMTELCA The elaboration of regulatory documents such as the Number Portability Regulation is proposed and indications are given for the creation of the Centralized Database Administrator with its subordinates in the different nodes that make up the network and a proposal for a General Implementation Plan.

Quantum Time–Space Tradeoff for Finding Multiple Collision Pairs

We study the problem of finding K collision pairs in a random function f : [N] → [N] by using a quantum computer. We prove that the number of queries to the function in the quantum random oracle model must increase significantly when the size of the available memory is limited. Namely, we demonstrate that any algorithm using S qubits of memory must perform a number T of queries that satisfies the tradeoff T 3 S ≥ Ω (K 3 N) . Classically, the same question has only been settled recently by Dinur [ 22 ], who showed that the Parallel Collision Search algorithm of van Oorschot and Wiener [ 36 ] achieves the optimal time–space tradeoff of T 2 S = Θ (K 2 N) . Our result limits the extent to which quantum computing may decrease this tradeoff. Our method is based on a novel application of Zhandry’s recording query technique [ 42 ] for proving lower bounds in the exponentially small success probability regime. As a second application, we give a simpler proof of the time–space tradeoff T 2 S ≥ Ω (N 3 ) for sorting N numbers on a quantum computer, which was first obtained by Klauck, Špalek, and de Wolf [ 30 ].

Dataopsy: Scalable and Fluid Visual Exploration using Aggregate Query Sculpting.

We present aggregate query sculpting (AQS), a faceted visual query technique for large-scale multidimensional data. As a "born scalable" query technique, AQS starts visualization with a single visual mark representing an aggregation of the entire dataset. The user can then progressively explore the dataset through a sequence of operations abbreviated as P6: pivot (facet an aggregate based on an attribute), partition (lay out a facet in space), peek (see inside a subset using an aggregate visual representation), pile (merge two or more subsets), project (extracting a subset into a new substrate), and prune (discard an aggregate not currently of interest). We validate AQS with DATAOPSY, a prototype implementation of AQS that has been designed for fluid interaction on desktop and touch-based mobile devices. We demonstrate AQS and Dataopsy using two case studies and three application examples.

Efficient and Privacy-Preserving Arbitrary Polygon Range Query Scheme Over Dynamic and Time-Series Location Data

Location-based services (LBSs) provide enhanced functionality of mobile applications and convenience for mobile users, which plays a more and more remarkable role in people’s daily life. In LBSs, spatial range query is an essential tool for users to find interesting points in a specific region. However, during spatial range query, it is necessary for data owners and query users to exchange their location data, and the leakage of private location information has drawn significant attention in both governmental and social aspects. Meanwhile, most existing location privacy protection schemes only focus on achieving regular geometry range query over static location datasets. In this paper, we present an efficient and privacy-preserving arbitrary polygon range query scheme, named EPAPRQ. With EPAPRQ, the arbitrary and fine-grained polygon range query can be executed over a dynamic and time-series location dataset with privacy protection. Specifically, in EPAPRQ, an arbitrary polygon range query algorithm is first introduced with sub-range query technique. Then, to protect the private location information of the data owner and query users, a series privacy-preserving data computation protocols are constructed with a symmetric homomorphic encryption algorithm, and a ciphertext-based location dataset updating strategy is also designed. Finally, we propose a double filtration method through combining the quadtree index structure and minimum bounded rectangle, which is able to greatly improve the query efficiency over ciphertexts. Detailed security analysis shows that the sensitive location information in EPAPRQ can be well protected. Furthermore, we evaluate the performance of EPAPRQ in the real map, and the results demonstrate that EPAPRQ is indeed efficient.

Blockchain-based verifiable and dynamic multi-keyword ranked searchable encryption scheme in cloud computing

Rational miners in the blockchain may admit the validity of search results directly to conserve calculation overhead without executing the validation. Such phenomenon is known as the Verifier's Dilemma that the search results of the blockchain-based searchable encryption scheme would be claimed to have been verified but devoid of valid verification. Besides, the top-k query technique utilized by most existing searchable encryption schemes fails to filter the search results from the user demands during retrieval, which results in massive ineffective access lists with high complexity. Aiming at the two problems, we present a blockchain-based multi-keyword ranked searchable encryption scheme and design a fair payment protocol for multi-user scenarios. Our scheme may achieve public verifiability and user fairness by leveraging the TrueBit network, while the data owner (DO) can withdraw search privileges from the authorized data user (DU). Also, we extend the searchable encryption scheme based on inverted index to multi-DO/multi-DU scenarios, and the HashMap index is used to diminish the response time and space complexity. Moreover, the proposed scheme supports the update of index keywords and files dynamically. To enhance retrieval accuracy, we modify the information divergence algorithm in machine learning and utilize the monotonically weighted aggregation function, which improves retrieval accuracy to around 97%. Finally, Security analysis illustrates correctness and resilience against typical attacks to verify the robustness and utility of the proposed scheme.

An Efficient Index-Based Approach to Distributed Set Reachability on Small-World Graphs

Set reachability query in directed graphs has a plethora of graph-based applications such as dependency analysis and graph centrality calculation. Given two sets <inline-formula><tex-math notation="LaTeX">$S$</tex-math></inline-formula> and <inline-formula><tex-math notation="LaTeX">$T$</tex-math></inline-formula> of source and target vertices, set reachability query needs to acquire all pairs <inline-formula><tex-math notation="LaTeX">$(s,t)$</tex-math></inline-formula> where <inline-formula><tex-math notation="LaTeX">$s{\in }S$</tex-math></inline-formula> , <inline-formula><tex-math notation="LaTeX">$t{\in }T$</tex-math></inline-formula> and <inline-formula><tex-math notation="LaTeX">$s$</tex-math></inline-formula> can reach <inline-formula><tex-math notation="LaTeX">$t$</tex-math></inline-formula> . The state-of-the-art approach distributed set reachability (DSR) investigates the set reachability query in a distributed environment and adopts a static graph-based index to enhance the query efficiency. Nevertheless, DSR needs to store the graph-based index in all partitions, which causes a huge space overhead. Furthermore, it cannot efficiently solve the negative query <inline-formula><tex-math notation="LaTeX">$(s,t)$</tex-math></inline-formula> where <inline-formula><tex-math notation="LaTeX">$s$</tex-math></inline-formula> cannot reach <inline-formula><tex-math notation="LaTeX">$t$</tex-math></inline-formula> , since DSR needs to traverse the whole reachable paths and becomes unable to efficiently reduce the computations. To alleviate these issues, we propose a novel multi-level 2-hop (ML2hop) index for the set reachability query in a distributed environment. Based on ML2hop, we further present a bi-directional query algorithm, called MLQA, to achieve efficient support for both positive and negative queries in Pregel-like systems. Generally, MLQA is equipped with the following three significant properties: (1) Low computation costs. It reduces redundant local computations in each partition by controlling the rounds of path traversals. (2) Low communication costs. It restricts the message exchange among different partitions within one single round with guaranteed accuracy of query results. (3) High parallelism. It adopts a bi-directional query technique for message propagation, achieving the better query efficiency than the forward-traversal query strategy utilized in DSR. Experimental results over several real-world graphs demonstrate that MLQA significantly outperforms the state-of-the-art algorithm by up to two orders of magnitude speedup.