Computer-implemented method for improving query execution in relational databases normalized at level 4 and above
Computer implemented methods for executing a database query in a database using an electronic computing device are presented, the method including: causing the electronic computing device to provide at least one data structure including at least one object having at least two properties of different data types; identifying a property of the at least one data structure that includes the smallest unique values in terms of type of data stored in that particular property; executing the database query, including any limiting parameters, configured to retrieve only data from the property including the smallest unique values that are retrievable for a record; retrieving a predefined number of results of the executed database query; and for each set of predefined number of retrieved results, executing a new database access thread that is configured to retrieve data that are present in the records identified with the smallest unique values.
1. A computer implemented method for improving efficiency and reducing computational complexity of executing queries in a database using an electronic computing device, the method comprising:
(a) creating a database comprising:
(1) a first data structure comprising definitions of data sets, wherein a definition of a data set comprises (i) a unique data set identifier and (ii) a set name,
(2) a second data structure comprising definitions of data properties of the data sets, wherein a definition of a data property comprises (i) a unique identifier of the data property and (ii) a data set identifier, from the first data structure, that the data property is assigned to, and wherein at least two data properties are characterized by different sizes,
(3) a third data structure comprising definitions of data objects of the data sets, wherein a definition of a data object comprises (i) a unique data object identifier and (ii) a data set identifier, from the first data structure, that the data object is assigned to,
(4) a fourth data structure comprising definitions of data object values of the data sets, wherein a definition of a data object value comprises (i) a data object identifier, from the third data structure, and (ii) a unique identifier of the data property, from the second data structure, that the data object value is assigned to,
(5) a fifth data structure comprising definitions of relations of the data sets, wherein a definition of a relation comprises (i) a unique identifier of the relation and (ii) a name of the relation,
(6) a sixth data structure comprising definitions of data object relations between the data sets, wherein a definition of a data object relation associates a relation, from the fifth data structure, with two data object identifiers from the third data structure, and
(7) a seventh data structure comprising definitions of set relations between the data sets, wherein a definition of a set relation associates a relation, from the fifth data structure, with two data set identifiers from the first data structure;
(b) identifying a first data property in the second data structure having the smallest size of all the data properties in the second data structure by comparing the respective data types of data object values, from the fourth data structure, assigned to identifiers of the data properties, from the second data structure;
(c) executing a database query on the fourth data structure and identifying data records satisfying the database query;
(d) retrieving first results from the data records identified in (c), wherein the first results are data of a portion of the first data property; and
(e) repeating (d) on a remainder of the data records to retrieve second results and threading the second results into the first results, until all the data records are retrieved.
2. The method according to claim 1 wherein the identifying the first data property is based on database schema information.
3. The method according to claim 1 wherein the first or the second results comprise a predefined number of data records, the predefined number between 75 and 150.
4. The method according to claim 1 wherein the retrieved results are stored in a hash table, wherein the hash table comprises (i) keys comprising the smallest size and (ii) values comprising the retrieved results.
5. The method according to claim 1 wherein the database query executed by each thread, utilizes UNION operators between SELECT database queries limited with the smallest size.
6. The method according to claim 1 wherein the executing the database query utilizes, in case of cross-querying numerous data sets, an INTERSECT operator between subqueries related to different data sets.
7. The method according to claim 1 , further comprising creating a mind map structure comprising nodes and directed links, wherein each node of the mind map represents a data set of the data sets and each directed link of the directed links represents a relation in the fifth data structure.
8. The method according to claim 1 , wherein the data object relations comprise a self-referencing data object relation.
9. The method according to claim 1 , wherein (e) is performed in parallel.
10. A non-transitory computer-readable medium comprising machine-executable code that, upon execution by a computer, implements a method for improving efficiency and reducing computational complexity of executing queries in a database, the method comprising:
(a) creating a database comprising:
(1) a first data structure comprising definitions of data sets, wherein a definition of a data set comprises (i) a unique data set identifier and (ii) a set name,
(2) a second data structure comprising definitions of data properties of the data sets, wherein a definition of a data property comprises (i) a unique identifier of the data property and (ii) a data set identifier, from the first data structure, that the data property is assigned to, and wherein at least two data properties are characterized by different sizes,
(3) a third data structure comprising definitions of data objects of the data sets, wherein a definition of a data object comprises (i) a unique data object identifier and (ii) a data set identifier, from the first data structure, that the data object is assigned to,
(4) a fourth data structure comprising definitions of data object values of the data sets, wherein a definition of a data object value comprises (i) a data object identifier, from the third data structure, and (ii) a unique identifier of the data property, from the second data structure, that the data object value is assigned to,
(5) a fifth data structure comprising definitions of relations of the data sets, wherein a definition of a relation comprises (i) a unique identifier of the relation and (ii) a name of the relation,
(6) a sixth data structure comprising definitions of data object relations between the data sets, wherein a definition of a data object relation associates a relation, from the fifth data structure, with two data object identifiers from the third data structure, and
(7) a seventh data structure comprising definitions of set relations between the data sets, wherein a definition of a set relation associates a relation, from the fifth data structure, with two data set identifiers from the first data structure;
(b) identifying a first data property in the second data structure having the smallest size of all the data properties in the second data structure by comparing the respective data types of data object values, from the fourth data structure, assigned to identifiers of the data properties, from the second data structure;
(c) executing a database query on the fourth data structure and identifying data records satisfying the database query;
(d) retrieving first results from the data records identified in (c), wherein the first results are data of a portion of the first data property; and
(e) repeating (d) on a remainder of the data records to retrieve second results and threading the second results into the first results, until all the data records are retrieved.
11. The non-transitory computer-readable medium according to claim 10 , wherein (e) is performed in parallel.
12. The non-transitory computer-readable medium according to claim 10 , wherein the data object relations comprise a self-referencing data object relation.
13. The medium according to claim 10 wherein the identifying the first data property is based on database schema information.
14. The non-transitory computer-readable medium according to claim 10 wherein the first or the second results comprise a predefined number of data records, the predefined number between 75 and 150.
15. The non-transitory computer-readable medium according to claim 10 wherein the retrieved results are stored in a hash table, wherein the hash table comprises (i) keys comprising the smallest size and (ii) values comprising the retrieved results.
16. The non-transitory computer-readable medium according to claim 10 wherein the database query executed by each thread, utilizes UNION operators between SELECT database queries limited with the smallest size.
17. The non-transitory computer-readable medium according to claim 10 wherein the third programmatic instructions for executing the database query utilizes, in case of cross-querying numerous data sets, an INTERSECT operator between subqueries related to different data sets.
18. The non-transitory computer-readable medium according to claim 10 , wherein the method further comprises creating a mind map structure comprising nodes and directed links, wherein each node of the mind map represents a data set of the data sets and each directed link of the directed links represents a relation in the fifth data structure.