CN112231329B - Method, apparatus, device and readable storage medium for inserting data into database - Google Patents
Method, apparatus, device and readable storage medium for inserting data into database Download PDFInfo
- Publication number
- CN112231329B CN112231329B CN202011096453.0A CN202011096453A CN112231329B CN 112231329 B CN112231329 B CN 112231329B CN 202011096453 A CN202011096453 A CN 202011096453A CN 112231329 B CN112231329 B CN 112231329B
- Authority
- CN
- China
- Prior art keywords
- data
- data set
- execution plan
- server
- database
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2379—Updates performed during online database operations; commit processing
- G06F16/2386—Bulk updating operations
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present disclosure provides a method, apparatus, device, and readable storage medium for inserting data into a database. The method comprises the following steps: dividing data to be inserted according to a preset threshold value to obtain at least one data set, wherein the data set comprises at least two pieces of data, and generating an insertion command corresponding to the data set for each data set, wherein the insertion command comprises an insertion operation instruction and the data set, and the insertion command is used for indicating a server to insert the data in the data set into a database and sending the insertion command to the server. According to the method, when data are inserted into the database in batches, the data set is used as a unit for transmission and the database is inserted, so that the consumption of transmission resources, receiving resources and CPU resources is reduced, the time for inserting operation is shortened, and the inserting efficiency is improved.
Description
Technical Field
The present disclosure relates to the field of database technologies, and in particular, to a method, an apparatus, a device, and a readable storage medium for inserting data into a database.
Background
With the development of new technologies such as the internet of things and fifth Generation mobile communication technologies (5 th Generation mobile networks or 5th Generation wireless systems, 5th-Generation, abbreviated as 5G or 5G technologies), data are growing at an exponential rate, which makes it more and more difficult for the conventional database technology to meet application requirements.
At present, when inserting a plurality of data into a database by using an open database connection (Open Database Connectivity, abbreviated as ODBC) driving component, a cyclic execution mode is often adopted, the ODBC driving component forms an insertion statement message according to one piece of data in the insertion data each time, the insertion statement message is sent to a database server, after the database server executes the insertion operation, an execution result is sent to the ODBC driving component, and if the execution result is that the insertion is completed, the ODBC driving component continues to send the data to be inserted in the next row.
Thus, the insertion operation is time consuming when inserting data into a database batch.
Disclosure of Invention
To solve or at least partially solve the above technical problems, the present disclosure provides a method, apparatus, device, and readable storage medium for inserting data into a database.
In a first aspect, the present disclosure provides a method of inserting data into a database, comprising:
Dividing data to be inserted according to a preset threshold value to obtain at least one data set, wherein the data set comprises at least two pieces of data;
generating a structured query language insertion command corresponding to each data set, wherein the insertion command comprises an insertion operation instruction and the data set, and the insertion command is used for instructing a server to insert data in the data set into a database;
And sending the insertion command to the server.
Optionally, before generating the insert command corresponding to the data set, the method further includes:
Writing the data to be inserted into a memory;
the generating, for each data set, an insert command corresponding to the data set includes:
and for each data set, reading the data set from the memory, and generating an insertion command corresponding to the data set.
Optionally, after the sending the insert command to the server, the method further includes:
Receiving an execution result sent by the server, wherein the execution result is used for indicating whether the server successfully inserts the data in the data set into the database or not;
And if the server successfully inserts the data in the data set into the database, sending an insertion command corresponding to the next data set.
Optionally, after generating the insert command corresponding to the data set, the method further includes:
if the quantity of the data contained in the data set is equal to the preset threshold value, generating a first execution plan corresponding to the data set;
if the quantity of the data contained in the data set is smaller than the preset threshold value, generating a second execution plan corresponding to the data set;
And sending an execution plan corresponding to the data set to the server, wherein the execution plan is a first execution plan or a second execution plan.
In a second aspect, the present disclosure provides an apparatus for inserting data into a database, comprising:
The obtaining module is used for dividing the data to be inserted according to a preset threshold value to obtain at least one data set, wherein the data set comprises at least two pieces of data;
A generation module, configured to generate, for each data set, an insertion command corresponding to the data set, where the insertion command includes an insertion operation instruction and the data set, and the insertion command is used to instruct a server to insert data in the data set into a database;
and the sending module is used for sending the insertion command to the server.
Optionally, the apparatus further includes:
the writing module is used for writing the data to be inserted into the memory;
the generation module is specifically configured to, for each data set, read the data set from the memory, and generate an insert command corresponding to the data set.
Optionally, the apparatus further includes:
The receiving module is used for receiving an execution result sent by the server, and the execution result is used for indicating whether the server successfully inserts the data in the data set into the database or not;
the sending module is further configured to: and if the server successfully inserts the data in the data set into the database, sending an insertion command corresponding to the next data set.
Optionally, the generating module further includes:
if the quantity of the data contained in the data set is equal to the preset threshold value, generating a first execution plan corresponding to the data set;
if the quantity of the data contained in the data set is smaller than the preset threshold value, generating a second execution plan corresponding to the data set;
the sending module is further configured to: and sending an execution plan corresponding to the data set to the server, wherein the execution plan is a first execution plan or a second execution plan.
In a third aspect, the present disclosure provides an apparatus for inserting data into a database, comprising:
A memory for storing processor-executable instructions;
a processor for implementing the method as described in the first aspect above when the computer program is executed.
In a fourth aspect, the present disclosure provides a computer-readable storage medium having stored therein computer-executable instructions which, when executed by a processor, are adapted to carry out a method of inserting data into a database as described in the first aspect above.
Compared with the prior art, the technical scheme provided by the embodiment of the disclosure has the following advantages:
Dividing data to be inserted according to a preset threshold value to obtain at least one data set, wherein the data set comprises at least two pieces of data, and generating an insertion command corresponding to the data set for each data set, wherein the insertion command comprises an insertion operation instruction and the data set, and the insertion command is used for indicating a server to insert the data in the data set into a database and sending the insertion command to the server. When data are inserted into the database in batches, the data are transmitted and inserted into the database by taking the data set as a unit, so that a large amount of transmission resources and CPU resources are consumed, the time for inserting operation is shortened, and the inserting efficiency is improved.
Drawings
The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the disclosure and together with the description, serve to explain the principles of the disclosure.
In order to more clearly illustrate the embodiments of the present disclosure or the solutions in the prior art, the drawings that are required for the description of the embodiments or the prior art will be briefly described below, and it will be obvious to those skilled in the art that other drawings can be obtained from these drawings without inventive effort.
FIG. 1 is a schematic diagram of a database system;
FIG. 2 is a schematic diagram of the structure of an SQL statement;
FIG. 3 is a flowchart of a method for inserting data into a database according to an embodiment of the present disclosure;
FIG. 4 is a schematic diagram of the structure of an SQL statement provided by the present disclosure;
FIG. 5 is a diagram showing a method for reading data to be inserted from a memory;
FIG. 6 is a flow chart of another method for inserting data into a database according to an embodiment of the present disclosure;
FIG. 7 is a schematic diagram of another method for reading data to be inserted from a memory;
FIG. 8 is a flow chart of yet another method for inserting data into a database according to an embodiment of the present disclosure;
FIG. 9 is a flow chart of yet another method for inserting data into a database according to an embodiment of the present disclosure;
FIG. 10 is a schematic structural diagram of an apparatus for inserting data into a database according to an embodiment of the present disclosure;
Fig. 11 is a schematic structural diagram of an apparatus for inserting data into a database according to an embodiment of the present disclosure.
Detailed Description
In order that the above objects, features and advantages of the present disclosure may be more clearly understood, a further description of aspects of the present disclosure will be provided below. It should be noted that, without conflict, the embodiments of the present disclosure and features in the embodiments may be combined with each other.
In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present disclosure, but the present disclosure may be practiced otherwise than as described herein; it will be apparent that the embodiments in the specification are only some, but not all, embodiments of the disclosure.
First, the terms involved in the present invention will be explained:
The structured query language (Structured Query Language, abbreviated as SQL) refers to a database query and programming language for accessing data and querying, updating and managing relational database systems.
Open database connection ODBC: the DataBase access interface standard is based on Windows environment and is used for solving the problem of data sharing among heterogeneous databases, providing a unified interface for heterogeneous DataBase access, allowing an application program to access data managed by different DataBase management systems (DataBase MANAGEMENT SYSTEM, DBMS for short) according to SQL data access standards; the application program directly manipulates the data in the database, and avoids the change along with the change of the database.
Fig. 1 is a schematic structural diagram of a database system, as shown in fig. 1, where the database system includes a client and a server, and one or more clients may be provided, and an application and an ODBC driver may be executed in the client. The number of servers may be one or more, and 3 servers are illustrated in fig. 1 as server 1, server 2, and server 3, respectively, it being understood that the number of servers does not constitute a limitation of the present disclosure, and that database data is stored in the servers. For example, in a heterogeneous database system, the servers may carry different types of databases. And running an application program in the client, and accessing the database by using an ODBC driver.
In some scenarios, when inserting a plurality of data into a database by using an ODBC driver component, a cyclic execution manner is often adopted, fig. 2 is a schematic structural diagram of an SQL statement, a form of the SQL statement generated by an application program is shown in fig. 2, the SQL statement includes an instruction and a parameter, where the instruction is an insertion operation instruction, the parameter is data to be inserted, interaction with the database is implemented by calling the ODBC driver, the ODBC driver component does not change the SQL statement, and reads one piece of data (also a row of data) in the data to be inserted each time according to the SQL statement to form an insertion command message, the insertion command message is sent to a database server, after the database server executes the insertion operation of the piece of data, an execution result is sent to the ODBC driver, and if the execution result is that insertion is completed, the ODBC driver component continues to send the next piece of data to be inserted. It can be understood that when data are inserted into a database in batches, a mode of sending one piece of data at a time is adopted, so that the process of inserting operation is long, each piece of data needs to be sent to a server independently, a large amount of sending resources, receiving resources and CPU resources need to be consumed, and the inserting efficiency is low.
In order to solve the above technical problems, the present disclosure provides a method for inserting data into a database, dividing data to be inserted according to a preset threshold value to obtain at least one data set, generating an insertion command corresponding to the data set for each data set, where the insertion command is used to instruct a server to insert data in the data set into the database, and sending the insertion command to the server. When data are inserted into the database in batches, the data set is used as a unit for transmitting and inserting the data into the database, so that the consumption of transmission resources, receiving resources and CPU resources is reduced, the time for inserting operation is shortened, and the inserting efficiency is improved.
The following describes the technical scheme of the present invention and how the technical scheme of the present invention solves the above technical problems in detail with specific embodiments.
Fig. 3 is a flowchart of a method for inserting data into a database according to an embodiment of the present disclosure, where, as shown in fig. 3, the method of the present embodiment is executed by a client, and the method of the present embodiment is as follows:
s301, dividing data to be inserted according to a preset threshold value to obtain at least one data set.
Wherein the dataset comprises at least two pieces of data.
In this embodiment, the data to be inserted is data that needs to be inserted into the database for the current task, that is, data that needs to be written into the database, in a scenario where data needs to be inserted into the database, it is first determined whether the number of data to be inserted is equal to 1, if the number of data to be inserted is 1, the data is processed according to an existing manner, if the number of data to be inserted is greater than 1, the data to be inserted is divided into data sets according to a preset threshold, where the preset threshold is used to represent the number of data contained in each data set when the data to be inserted is divided, and the preset threshold may be an integer greater than 2, for example, the preset threshold may be 100. The number of data contained in each data set is smaller than or equal to a preset threshold value, if the number of data to be inserted is smaller than or equal to the preset threshold value, the data to be inserted is divided into one data set, and all the data to be inserted are contained in the data set; if the number of the data to be inserted is greater than the preset threshold, dividing the data to be inserted into a plurality of data sets, wherein when the data sets are divided into the plurality of data sets, the number of the data contained in each data set except the last data set is the preset threshold, and the number of the data contained in the last data set is smaller than or equal to the preset threshold.
Alternatively, the number P of divided data sets may be obtained by the following equations (1) and (2):
x = n% BatchInsertSize formula (2);
Wherein BatchInsertSize is a preset threshold, n is the number of data to be inserted, and x is the value of n for BatchInsertSize.
When the value of n of the data to be inserted to the remainder of the preset threshold BatchInsertSize is greater than 0, the number of the divided data sets needs to be added by 1, and the number of the data contained in the data sets is the value of n to the remainder of BatchInsertSize.
For example, the preset threshold is 100, and assuming that the total data to be inserted is 50, the data set is divided into a data set containing the 50 pieces of data; assuming that the total data to be inserted is 300, dividing the data into 3 data sets, wherein each data set contains 100 pieces of data; assuming that there are 150 pieces of data to be inserted, it is divided into 2 data sets, one data set containing 100 pieces of data and the other data set containing 50 pieces of data.
S302, generating an insertion command corresponding to each data set.
The insertion command comprises an insertion operation instruction and a data set, and the insertion command is used for instructing the server to insert data in the data set into the database.
For each of the partitioned data sets, an insert command corresponding to the data set is generated, i.e., one data set generates one insert command.
Optionally, for each data set, performing a stitching operation on the SQL statement corresponding to each data in the data set to obtain the SQL statement corresponding to the data set, and obtaining the insert command corresponding to the data set according to the SQL statement corresponding to the data set, where the stitching operation is to stitch the SQL statement corresponding to the data contained in each data set into one SQL statement, the SQL statement corresponding to the data set includes an instruction and at least one parameter, and the number of parameters is the same as the number of data in the data set. Fig. 4 is a schematic structural diagram of an SQL statement provided in the present disclosure, where, as shown in fig. 4, a splicing operation is performed on an original SQL statement, where the spliced SQL statement includes an instruction and m parameters, m is an integer greater than 1, and the m parameters include parameter 1, parameter 2, … … and parameter m, and each parameter of the m parameters corresponds to one data to be inserted. In the process of splicing the SQL statements corresponding to the data contained in each data set, it can be understood that, assuming that each page of data has a piece of data and each piece of data has b columns, the operation of a row and b columns of the database which is originally required is changed into the operation of a x b columns of the database through the splicing operation of the SQL statements. And then generating an insertion command corresponding to the data set according to the SQL statement corresponding to each data set.
S303, sending SQL sentences to the server.
And the client side respectively sends SQL sentences corresponding to each data set to the server.
In this embodiment, according to a preset threshold, data to be inserted is divided to obtain at least one data set, the data set includes at least two pieces of data, and for each data set, an insertion command corresponding to the data set is generated, where the insertion command includes an insertion operation instruction and the data set, and the insertion command is used to instruct a server to insert data in the data set into a database, and send the insertion command to the server. When data are inserted into the database in batches, the data set is used as a unit for transmitting and inserting the data into the database, so that the consumption of transmission resources, receiving resources and CPU resources is reduced, the time for inserting operation is shortened, and the inserting efficiency is improved.
Further, in heterogeneous databases, the databases may adopt a plurality of different data structures, and the insertion processes performed by the databases may not be the same, so that the insertion operation time is not easy to implement on the database side, and the present disclosure is improved on the client side without changing the insertion processes of the servers with different database structures, and is easier to implement.
In some scenarios, the data to be inserted needs to be written into the memory first, the ODBC driver reads the data from the memory and sends the data to the database server together with the insertion operation instruction, one piece of data can be fetched from the memory at a time, and then the insertion operation instruction and the data are sent to the database, where all the data to be inserted can be written into the memory at one time, or a writing threshold can be set, and the data to be inserted are written into the memory in batches according to the writing threshold. Fig. 5 is a schematic diagram of reading data to be inserted from a memory, where n pieces of data are n pieces of data, including data 1, data 2, data 3, data 4, data 5, data 6, and data 7 … …, respectively, as shown in fig. 5, all the data to be inserted may be written into the memory at a time, one piece of data is fetched from the memory at a time, and an insert command is formed together with an insert operation instruction, and the insert command is sent to a database, for example, data 1 is fetched from the memory first, and the insert command is formed by data 1 and sent to the database.
However, this makes it possible to transmit only one piece of data at a time, and in the case where the amount of data to be inserted is large, a large amount of transmission resources, reception resources, and CPU resources will be consumed, the time taken for the insertion operation is long, and the insertion efficiency is not high.
The following describes in detail how embodiments of the present disclosure solve the above technical problems with the embodiment shown in fig. 6.
Fig. 6 is a flowchart of another method for inserting data into a database according to an embodiment of the present disclosure, and fig. 6 is a flowchart of a method for inserting data into a database according to an embodiment of fig. 3, further, as shown in fig. 6, S302 may further include S302a before S302b, where S302 includes:
s302a, writing the data to be inserted into the memory.
In one possible implementation, all the data to be inserted are written into the memory at one time, in another possible implementation, a writing threshold is set, the data to be inserted are written into the memory in batches according to the writing threshold, for example, 200 writing thresholds are set, and 200 pieces of data are written into the memory each time.
S302b, for each data set, the data set is read from the memory, and an insertion command corresponding to the data set is generated.
According to the data set, data in the data set is read from the memory each time, and then an insert command corresponding to the data set is generated. Fig. 7 is a schematic diagram of another method for reading data to be inserted from the memory, as shown in fig. 7, n total data to be inserted are written into the memory at a time, m data in the data set are m data, that is, m is the number of lines of the data contained in the data set, then m data can be read from the memory at a time, the m data are spliced, and as parameters, the m data and the inserting instruction together form an inserting command, and the inserting command is sent to the database server.
According to the embodiment, the data to be inserted is written into the memory, the data is read from the memory according to the data set, and the data of the data set is read from the memory each time, so that the insertion command is sent to the server by taking the data set as a unit, the consumption of sending resources, receiving resources and CPU resources is reduced, the time for insertion operation is shortened, and the insertion efficiency is improved.
Fig. 8 is a flowchart of another method for inserting data into a database according to an embodiment of the present disclosure, and fig. 8 is a flowchart of the embodiment shown in fig. 3 or fig. 6, further, as shown in fig. 8, S303 may further include S304 and S305:
s304, receiving an execution result sent by the server, wherein the execution result is used for indicating whether the server successfully inserts the data in the data set into the database.
The client sends an insertion command to the server, the server inserts the data set into the database according to the insertion command, and an execution result is obtained after the insertion operation is completed, namely whether the data in the data set is successfully inserted into the database at the time or not. The server sends the execution result to the client.
S305, if the server successfully inserts the data in the data set into the database, an insertion command corresponding to the next data set is sent.
If the server successfully inserts the data in the data set into the database, an insertion command corresponding to the data set which is not yet transmitted next is transmitted. Further, if the server fails to insert the data in the data set into the database, the client sends an insert command corresponding to the next data set, or the client resends the insert command corresponding to the data set to the server. By resending the insert command corresponding to the data set to the server, reliable transmission of the data can be achieved.
In this embodiment, the client receives an execution result sent by the server, where the execution result is used to instruct the server whether to insert the data in the data set into the database successfully, and if the server inserts the data in the data set into the database successfully, sends an insert command corresponding to the next data set. The data set is used as a unit to orderly send the insertion command to the server, so that a large amount of transmission resources and CPU resources are consumed, the time for insertion operation is shortened, and the insertion efficiency is improved.
In other scenarios, the client generates an execution plan corresponding to the data set according to the insert operation instruction in the SQL statement, the client sends the execution plan to the server, and the server can execute the corresponding insert operation according to the execution plan. It will be appreciated that if the amount of data executed is the same, the execution plan generated accordingly is the same. The embodiment shown in fig. 9 will be described in detail.
Fig. 9 is a flowchart of another method for inserting data into a database according to an embodiment of the present disclosure, and fig. 9 is a flowchart of the embodiment shown in fig. 3, fig. 6, or fig. 8, further, as shown in fig. 9, S302 may further include S302c, S302d, and S302e after S302:
S302c, if the number of data contained in the data set is equal to a preset threshold value, generating a first execution plan corresponding to the data set.
S302d, if the number of data contained in the data set is smaller than a preset threshold value, generating a second execution plan corresponding to the data set.
The number of data contained in the data set into which the data to be inserted is divided is less than or equal to a preset threshold.
In one possible case, if the number of data contained in the data set is equal to a preset threshold, a first execution plan corresponding to the data set is generated, for example, the number of data contained in the data set is equal to the preset threshold, and may be that the number of data to be inserted is equal to the preset threshold, the data to be inserted is divided into one data set, and the data contained in the data set is the data to be inserted; or the number of the data to be inserted is larger than the preset threshold value, and the preset threshold value can be divided completely, the data to be inserted is divided into a plurality of data sets, and the number of the data contained in each data set in the plurality of data sets is equal to the preset threshold value. It will be appreciated that for a data set containing data equal in number to a preset threshold, the corresponding execution plans are identical, and one execution plan may be generated.
In another possible case, if the number of data included in the data set is smaller than the preset threshold, a second execution plan corresponding to the data set is generated. Dividing the data to be inserted into a data set, wherein the data contained in the data set is the data to be inserted, if at most one of the data sets contains less than a preset threshold value, for example, the number of the data to be inserted is less than the preset threshold value; when the number of data to be inserted is greater than the preset threshold and the number of data to be inserted is divided by the preset threshold, a remainder exists, and the number of data contained in one of the divided data sets is the remainder.
S302e, sending an execution plan corresponding to the data set to a server, wherein the execution plan is a first execution plan or a second execution plan.
When the execution plan corresponding to the data set is sent to the server, the execution plan corresponding to the data set may be sent to the server each time, or the execution plans may be sent to the server together, where, in the case that a plurality of data sets correspond to the first execution plan, the first execution plan may be sent only when an insert command is sent to the server for the first time, that is, only the first execution plan may be sent once in the process of inserting the data to be inserted into the database. If the data sets are divided into data sets, the data sets correspond to a first execution plan and data sets correspond to a second execution plan, the data sets may be sent to the server in the first execution plan and the second execution plan, or the data sets may be sent to the server together with the first execution plan and the second execution plan.
Correspondingly, the server performs execution by using the corresponding execution plan according to the quantity of the data contained in the data set. For example, if the number of data to be inserted can be divided by the preset threshold, that is, the number of data to be inserted is% of the preset threshold=0, the server only needs to use the first execution plan, and the execution plan does not need to be replaced in the execution process of the server; if the number of data to be inserted cannot be divided by the preset threshold value, namely the number% of data to be inserted is preset threshold value-! =0, and there is only one dataset, then the server only needs to use the second execution plan; if the number of the data to be inserted cannot be divided by the preset threshold value, the number of the data sets is larger than 1, the server executes according to the first execution plan, the data in the data sets are inserted into the database, and when the number of the data contained in the data sets is smaller than the preset threshold value, the server replaces the first execution plan with the second execution plan, so that the data sets are executed according to the second execution plan.
In this embodiment, if the number of data included in the data set is equal to the preset threshold, a first execution plan corresponding to the data set is generated, and if the number of data included in the data set is less than the preset threshold, a second execution plan corresponding to the data set is generated, and the execution plan corresponding to the data set is sent to the server, where the execution plan is the first execution plan or the second execution plan, so that compared with the prior art, the time required for replacing the execution plan is at most replaced once, and therefore, the embodiment does not consume excessive time when the server performs the insert operation.
To further view the technical effects of the present disclosure, the insertion time may be calculated by the following test, and the difference between the insertion method using the related art and the insertion method provided by the embodiments of the present disclosure may be compared.
Test scenario: creating a table in a database, wherein the table comprises two fields, a digital type and a character type, inserting 5000 pieces of data into the table, and printing out execution time; writing a test case, calling an ODBC interface, and inserting 5000 pieces of data into a database; adding a database connection configuration item, wherein configuration 1 uses an ODBC driver in the prior art, and configuration 2 uses the ODBC driver of the method provided by the embodiment of the disclosure; by executing test programs calling different configurations, the execution time of the configuration 1 and the execution time of the configuration 2 are counted respectively, and the execution time of the configuration 1 and the execution time of the configuration 2 are compared.
Through the above test, the execution time of the configuration 2 is reduced by about 10 times compared with the execution time of the configuration 1, which indicates that the method for inserting data into the database provided by the embodiment of the disclosure can shorten the insertion time and improve the insertion efficiency.
Fig. 10 is a schematic structural diagram of an apparatus for inserting data into a database according to an embodiment of the present disclosure, where, as shown in fig. 10, the apparatus provided in this embodiment includes:
The obtaining module 101 is configured to divide data to be inserted according to a preset threshold value to obtain at least one data set, where the data set includes at least two pieces of data;
A generating module 102, configured to generate, for each data set, an insert command corresponding to the data set, where the insert command includes an insert operation instruction and the data set, and the insert command is used to instruct the server to insert data in the data set into the database;
and a sending module 103, configured to send the insert command to the server.
Optionally, the apparatus further comprises:
the writing module is used for writing the data to be inserted into the memory;
The generating module 102 is specifically configured to, for each data set, read the data set from the memory, and generate an insert command corresponding to the data set.
Optionally, the apparatus further comprises:
The receiving module is used for receiving an execution result sent by the server, and the execution result is used for indicating whether the server successfully inserts the data in the data set into the database or not;
The sending module 103 is further configured to: if the server successfully inserts the data in the data set into the database, the next data set is sent.
Optionally, the generating module 102 further includes:
if the quantity of the data contained in the data set is equal to a preset threshold value, generating a first execution plan corresponding to the data set;
If the quantity of the data contained in the data set is smaller than a preset threshold value, generating a second execution plan corresponding to the data set;
The sending module 103 is further configured to: and sending an execution plan corresponding to the data set to a server, wherein the execution plan is a first execution plan or a second execution plan.
The device of the above embodiment may be used to implement the technical solution of the above method embodiment, and its implementation principle and technical effects are similar, and are not repeated here.
Fig. 11 is a schematic structural diagram of an apparatus for inserting data into a database according to an embodiment of the present disclosure, where, as shown in fig. 11, the apparatus provided in this embodiment includes:
a memory 111, a memory for storing processor-executable instructions;
a processor 112 for implementing the method of any of the embodiments shown in fig. 3, 6, 8, 9 described above when the computer program is executed.
The device of the above embodiment may be used to implement the technical solution of the above method embodiment, and its implementation principle and technical effects are similar, and are not repeated here.
Embodiments of the present disclosure provide a computer readable storage medium having stored therein computer executable instructions that when executed by a processor are configured to implement a method of inserting data into a database as in any of the embodiments shown in fig. 3, 6, 8, and 9.
It should be noted that in this document, relational terms such as "first" and "second" and the like are used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Moreover, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.
The foregoing is merely a specific embodiment of the disclosure to enable one skilled in the art to understand or practice the disclosure. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the disclosure. Thus, the present disclosure is not intended to be limited to the embodiments shown and described herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims (8)
1. A method of inserting data into a database, comprising:
Dividing data to be inserted according to a preset threshold value to obtain at least one data set, wherein the data set comprises at least two pieces of data;
generating an insertion command corresponding to each data set, wherein one data set generates an insertion command, the insertion command comprises an insertion operation instruction and the data set, and the command is used for instructing a server to insert data in the data set into a database;
sending the insertion command to the server;
After the generating of the insert command corresponding to the data set, the method further comprises:
if the quantity of the data contained in the data set is equal to the preset threshold value, generating a first execution plan corresponding to the data set;
if the quantity of the data contained in the data set is smaller than the preset threshold value, generating a second execution plan corresponding to the data set;
Sending an execution plan corresponding to the data set to the server, wherein the execution plan is a first execution plan or a second execution plan, so that the server executes corresponding insertion operation according to the execution plan; wherein, the quantity of the data contained in the data set is the same, and the corresponding execution plans are the same;
dividing the data to be inserted according to a preset threshold value to obtain at least one data set, wherein the step of obtaining the at least one data set comprises the following steps:
If the number of the data to be inserted is greater than the preset threshold and a remainder exists when the number of the data to be inserted is divided by the preset threshold, the number of the data contained in the last data set in the at least one data set obtained by dividing is the remainder, and the remainder is smaller than the preset threshold;
Correspondingly, the sending the execution plan corresponding to the data set to the server, where the execution plan is a first execution plan or a second execution plan, so that the server executes the corresponding insertion operation according to the execution plan, and the method includes:
The first execution plan corresponding to other data sets and the second execution plan corresponding to the last data set are sent to the server, so that the server inserts data in the other data sets into a database according to the insertion operation corresponding to the first execution plan, then the first execution plan is replaced with the second execution plan, and the data contained in the last data set is inserted into the database according to the insertion operation corresponding to the second execution plan; the other data sets are data sets, except the last data set, whose number of data is equal to the preset threshold.
2. The method of claim 1, wherein prior to generating the insert command corresponding to the data set for each of the data sets, further comprising:
Writing the data to be inserted into a memory;
the generating, for each data set, an insert command corresponding to the data set includes:
and for each data set, reading the data set from the memory, and generating an insertion command corresponding to the data set.
3. The method according to claim 1 or 2, wherein after the sending of the insert command to the server, further comprising:
Receiving an execution result sent by the server, wherein the execution result is used for indicating whether the server successfully inserts the data in the data set into the database or not;
And if the server successfully inserts the data in the data set into the database, sending an insertion command corresponding to the next data set.
4. An apparatus for inserting data into a database, comprising:
The obtaining module is used for dividing the data to be inserted according to a preset threshold value to obtain at least one data set, wherein the data set comprises at least two pieces of data;
The generation module is used for generating an insertion command corresponding to each data set, wherein one data set generates an insertion command, the insertion command comprises an insertion operation instruction and the data set, and the insertion command is used for instructing a server to insert data in the data set into a database;
The sending module is used for sending the inserting command to the server;
The generating module is further configured to, after generating an insert command corresponding to the data set:
if the quantity of the data contained in the data set is equal to the preset threshold value, generating a first execution plan corresponding to the data set;
if the quantity of the data contained in the data set is smaller than the preset threshold value, generating a second execution plan corresponding to the data set;
Sending an execution plan corresponding to the data set to the server, wherein the execution plan is a first execution plan or a second execution plan, so that the server executes corresponding insertion operation according to the execution plan; wherein, the quantity of the data contained in the data set is the same, and the corresponding execution plans are the same;
The obtaining module is specifically configured to, if the number of data to be inserted is greater than the preset threshold and there is a remainder when the number of data to be inserted is divided by the preset threshold, obtain the number of data contained in the last data set in the at least one data set by dividing as the remainder, where the remainder is smaller than the preset threshold;
Correspondingly, when sending an execution plan corresponding to the data set to the server, the generating module is specifically configured to: the first execution plan corresponding to other data sets and the second execution plan corresponding to the last data set are sent to the server, so that the server inserts data in the other data sets into a database according to the insertion operation corresponding to the first execution plan, then the first execution plan is replaced with the second execution plan, and the data contained in the last data set is inserted into the database according to the insertion operation corresponding to the second execution plan; the other data sets are data sets, except the last data set, whose number of data is equal to the preset threshold.
5. The apparatus of claim 4, wherein the apparatus further comprises:
the writing module is used for writing the data to be inserted into the memory;
the generation module is specifically configured to, for each data set, read the data set from the memory, and generate an insert command corresponding to the data set.
6. The apparatus according to claim 4 or 5, characterized in that the apparatus further comprises:
The receiving module is used for receiving an execution result sent by the server, and the execution result is used for indicating whether the server successfully inserts the data in the data set into the database or not;
the sending module is further configured to: and if the server successfully inserts the data in the data set into the database, sending an insertion command corresponding to the next data set.
7. An apparatus for inserting data into a database, comprising:
A memory for storing processor-executable instructions;
A processor for implementing the method of any of the preceding claims 1 to 3 when the computer program is executed.
8. A computer readable storage medium having stored therein computer executable instructions which when executed by a processor are adapted to carry out a method of inserting data into a database as claimed in any of claims 1 to 3.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011096453.0A CN112231329B (en) | 2020-10-14 | 2020-10-14 | Method, apparatus, device and readable storage medium for inserting data into database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011096453.0A CN112231329B (en) | 2020-10-14 | 2020-10-14 | Method, apparatus, device and readable storage medium for inserting data into database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112231329A CN112231329A (en) | 2021-01-15 |
| CN112231329B true CN112231329B (en) | 2024-04-26 |
Family
ID=74113500
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011096453.0A Active CN112231329B (en) | 2020-10-14 | 2020-10-14 | Method, apparatus, device and readable storage medium for inserting data into database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112231329B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116069843A (en) * | 2023-02-02 | 2023-05-05 | 浪潮卓数大数据产业发展有限公司 | Data set insertion method, device, equipment and medium |
Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JP2000322293A (en) * | 1999-05-10 | 2000-11-24 | Hitachi Ltd | Database management method, apparatus for executing the method, and recording medium recording the processing program |
| CN102521409A (en) * | 2011-12-28 | 2012-06-27 | 北京人大金仓信息技术股份有限公司 | Method for updating data of database in batch mode based on communication protocol |
| CN102542054A (en) * | 2011-12-29 | 2012-07-04 | 厦门雅迅网络股份有限公司 | Method utilizing cache tables to improve insertion performance of data in database |
| CN102999604A (en) * | 2012-11-20 | 2013-03-27 | 北京奇虎科技有限公司 | Method and device for detecting performance of database |
| CN103577551A (en) * | 2013-10-16 | 2014-02-12 | 青岛海信传媒网络技术有限公司 | Method and device for submitting data to database in batch |
| CN105335482A (en) * | 2015-10-14 | 2016-02-17 | 西北工业大学 | Batch insert method facing mass distributed database |
| US10061834B1 (en) * | 2014-10-31 | 2018-08-28 | Amazon Technologies, Inc. | Incremental out-of-place updates for datasets in data stores |
| CN109271394A (en) * | 2018-08-27 | 2019-01-25 | 武汉达梦数据库有限公司 | A kind of batch data insertion update implementation method based on ID caching |
| CN109710624A (en) * | 2018-12-19 | 2019-05-03 | 泰康保险集团股份有限公司 | Data processing method, device, medium and electronic equipment |
| CN110781231A (en) * | 2019-09-19 | 2020-02-11 | 平安科技（深圳）有限公司 | Batch import method, device, equipment and storage medium based on database |
Family Cites Families (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7711730B2 (en) * | 2003-12-22 | 2010-05-04 | International Business Machines Corporation | Method of returning data during insert statement processing |
| US10452676B2 (en) * | 2014-01-31 | 2019-10-22 | Hewlett Packard Enterprise Development Lp | Managing database with counting bloom filters |
| US10083203B2 (en) * | 2015-08-11 | 2018-09-25 | International Business Machines Corporation | Reducing the cost of update, delete, and append-only insert operations in a database |
| US10296614B2 (en) * | 2016-12-07 | 2019-05-21 | International Business Machines Corporation | Bulk data insertion in analytical databases |
| US10824519B2 (en) * | 2018-04-16 | 2020-11-03 | Sap Se | Database recovery and optimization with batch processing |
| US11048756B2 (en) * | 2019-01-31 | 2021-06-29 | EMC IP Holding Company LLC | Inserting datasets into database systems utilizing hierarchical value lists |
-
2020
- 2020-10-14 CN CN202011096453.0A patent/CN112231329B/en active Active
Patent Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JP2000322293A (en) * | 1999-05-10 | 2000-11-24 | Hitachi Ltd | Database management method, apparatus for executing the method, and recording medium recording the processing program |
| CN102521409A (en) * | 2011-12-28 | 2012-06-27 | 北京人大金仓信息技术股份有限公司 | Method for updating data of database in batch mode based on communication protocol |
| CN102542054A (en) * | 2011-12-29 | 2012-07-04 | 厦门雅迅网络股份有限公司 | Method utilizing cache tables to improve insertion performance of data in database |
| CN102999604A (en) * | 2012-11-20 | 2013-03-27 | 北京奇虎科技有限公司 | Method and device for detecting performance of database |
| CN103577551A (en) * | 2013-10-16 | 2014-02-12 | 青岛海信传媒网络技术有限公司 | Method and device for submitting data to database in batch |
| US10061834B1 (en) * | 2014-10-31 | 2018-08-28 | Amazon Technologies, Inc. | Incremental out-of-place updates for datasets in data stores |
| CN105335482A (en) * | 2015-10-14 | 2016-02-17 | 西北工业大学 | Batch insert method facing mass distributed database |
| CN109271394A (en) * | 2018-08-27 | 2019-01-25 | 武汉达梦数据库有限公司 | A kind of batch data insertion update implementation method based on ID caching |
| CN109710624A (en) * | 2018-12-19 | 2019-05-03 | 泰康保险集团股份有限公司 | Data processing method, device, medium and electronic equipment |
| CN110781231A (en) * | 2019-09-19 | 2020-02-11 | 平安科技（深圳）有限公司 | Batch import method, device, equipment and storage medium based on database |
Non-Patent Citations (2)
| Title |
|---|
| 分布式有序表中一种有效的在线计划批量插入方法;韩希先 等;《计算机研究与发展》;20091231(第z2期);331-338 * |
| 基于Mybatis框架的批量数据插入的性能问题的探讨;魏静敏 等;《计算机光盘软件与应用》;第16卷(第19期);第153、155页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112231329A (en) | 2021-01-15 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111339041B (en) | File analysis and storage method and device and file generation method and device | |
| CN108845816B (en) | Application program updating method, system, computer device and storage medium | |
| US9832275B2 (en) | Message processing method, device and system for internet of things | |
| CN112783868B (en) | Distributed database table structure grayscale upgrade method, device and system | |
| US20240143658A1 (en) | Methods and apparatuses for inserting data into graph database | |
| CN113626217B (en) | Asynchronous message processing method, device, electronic equipment and storage medium | |
| CN107330014B (en) | Data table creating method and device | |
| CN108540351A (en) | The automated testing method of distributed big data service | |
| CN112231329B (en) | Method, apparatus, device and readable storage medium for inserting data into database | |
| CN108614697A (en) | Backstage Dex compiles the method and device of management and control | |
| CN116842244A (en) | Search engine data synchronization method, system, device and storage medium | |
| US11740867B2 (en) | Data sorting method, apparatus and device, storage medium and program product | |
| US11544260B2 (en) | Transaction processing method and system, and server | |
| CN111124883A (en) | Method, system and device for introducing test case library based on tree table | |
| CN112765269B (en) | Data processing method, device, equipment and storage medium | |
| CN102930045A (en) | Multi-device formula management method of integrated circuit production line | |
| CN115952239B (en) | Expression-based distributed hierarchical computing system, electronic device and storage medium | |
| CN105320763A (en) | Method and apparatus for importing contents of XML file into database | |
| CN114995869A (en) | A configuration file parsing method, device, device and medium | |
| CN115827725A (en) | Method, device and equipment for merging and tracing multi-source tree data and chain data | |
| CN111930642A (en) | Cache management method, electronic equipment and cache management device | |
| WO2020253041A1 (en) | Data storage method employing redis, apparatus, and computer-readable storage medium | |
| CN111506303A (en) | Data interaction method and device | |
| CN111162988B (en) | Message distribution method and system | |
| US7925476B2 (en) | Model-based theory combination |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| CP03 | Change of name, title or address | ||
| CP03 | Change of name, title or address |
Address after: 100102 201, 2 / F, 101, No. 5 building, No. 7 Rongda Road, Chaoyang District, Beijing Patentee after: China Electronics Technology Group Jincang (Beijing) Technology Co.,Ltd. Country or region after: China Address before: 100102 201, 2 / F, 101, No. 5 building, No. 7 Rongda Road, Chaoyang District, Beijing Patentee before: BEIJING KINGBASE INFORMATION TECHNOLOGIES Inc. Country or region before: China |