CN112115158A - SQL statement execution time recording method, device, equipment and readable storage medium - Google Patents

Abstract

The present application discloses a method, device, equipment and readable storage medium for recording the execution time of SQL statements. The method disclosed in the present application includes: obtaining the identification information of at least one client that is executing SQL statements on a Sybase database; determining whether the identification information is recorded in any monitoring list; if not, adding the identification information to the target monitoring list, and using the monitoring thread corresponding to the target monitoring list to poll the client, if the client is polled to execute the target SQL statement on the Sybase database, then recording the execution time of the target SQL statement according to the preset strategy. If the overall performance of the database is reduced, the technician can find out the SQL statements that cause the overall performance of the database to be reduced based on the recorded execution times, and then optimize the corresponding SQL statements to improve the database performance. Correspondingly, the SQL statement execution time recording device, equipment and readable storage medium provided by the present application also have the above-mentioned technical effects.

Description

SQL statement execution time recording method, apparatus, device and readable storage medium

technical field

The present application relates to the field of computer technologies, and in particular, to a method, apparatus, device, and readable storage medium for recording the execution time of an SQL statement.

Background technique

At present, in the process of simultaneously executing SQL statements on the Sybase database by many clients, the overall performance of the database may be degraded due to excessive concurrency and/or slow execution of individual SQL statements. However, since the Sybase database does not have the relevant function to record the execution information of SQL statements, it is impossible to find out the SQL statements that cause the performance of the database to decrease in time.

Therefore, how to record the relevant information of the SQL statement executed by the client on the Sybase database is a problem to be solved by those skilled in the art.

SUMMARY OF THE INVENTION

In view of this, the purpose of the present application is to provide a method, apparatus, device and readable storage medium for recording the execution time of an SQL statement, so as to record the relevant information of the SQL statement executed by the client on the Sybase database. Its specific plan is as follows:

In a first aspect, the present application provides a method for recording the execution time of an SQL statement, including:

Obtain the identification information of at least one client that is executing the SQL statement against the Sybase database;

Determine whether the identification information is recorded in any monitoring list;

If not, add the identification information to the target monitoring list, and use the monitoring thread corresponding to the target monitoring list to poll the client, if it is polled that the client is executing the target SQL on the Sybase database statement, the execution time of the target SQL statement is recorded according to the preset strategy.

Preferably, the described acquisition is the identification information of at least one client that is executing the SQL statement to the Sybase database, including:

Use the scheduling thread to obtain identification information of all clients that are executing SQL statements on the Sybase database at a preset period, and obtain a list of identification information.

Preferably, the judging whether the identification information is recorded in any monitoring list includes:

Determine whether any identification information in the identification information list is recorded in any monitoring list.

Preferably, the adding the identification information to the target monitoring list includes:

A monitoring list with the least amount of information is selected from a plurality of monitoring lists as the target monitoring list, and the identification information is added to the tail of the target monitoring list.

Preferably, if it is polled that the client is executing the target SQL statement on the Sybase database, the execution time of the target SQL statement is recorded according to a preset strategy, including:

If it is polled for the first time that the client is executing the target SQL statement on the Sybase database, the execution time of the record target SQL statement is 0;

If it is polled again that the client is executing the target SQL statement on the Sybase database, the execution time is incremented by one polling period;

If it is not polled again that the client is executing the target SQL statement on the Sybase database, the current execution time, the identification information and the target SQL statement are recorded in a log.

Preferably, it also includes:

If it is not polled for the first time that the client is executing the target SQL statement on the Sybase database, the identification information in the target monitoring list is deleted.

Preferably, the identification information of the monitored client is recorded in any one of the monitoring lists.

Preferably, if it is not polled again that the client is executing the target SQL statement on the Sybase database, but it is polled that the client is executing other SQL statements on the Sybase database, the current execution time , the identification information and the target SQL statement are recorded in the log, and the execution time of the other SQL statements is recorded at the same time.

In a second aspect, the present application provides a device for recording the execution time of an SQL statement, including:

an obtaining module, used to obtain the identification information of at least one client that is executing the SQL statement on the Sybase database;

Judging module, for judging whether the identification information is recorded in any monitoring list;

A recording module, configured to add the identification information to the target monitoring list if the identification information is not recorded in any monitoring list, and use the monitoring thread corresponding to the target monitoring list to poll the client, if When it is polled that the client is executing the target SQL statement on the Sybase database, the execution time of the target SQL statement is recorded according to a preset strategy.

In a third aspect, the present application provides a device for recording the execution time of an SQL statement, including:

memory for storing computer programs;

The processor is configured to execute the computer program, so as to realize the method for recording the execution time of the SQL statement disclosed above.

In a fourth aspect, the present application provides a readable storage medium for storing a computer program, wherein when the computer program is executed by a processor, the aforementioned method for recording the execution time of an SQL statement is implemented.

As can be seen from the above scheme, the present application provides a method for recording the execution time of an SQL statement, including: obtaining the identification information of at least one client that is executing the SQL statement on the Sybase database; judging whether the identification information is recorded in any monitoring list; If not, add the identification information to the target monitoring list, and use the monitoring thread corresponding to the target monitoring list to poll the client, if it is polled that the client is executing the target SQL on the Sybase database statement, the execution time of the target SQL statement is recorded according to the preset strategy.

It can be seen that after obtaining the identification information of at least one client that is executing the SQL statement on the Sybase database, the application determines whether the identification information is recorded in any monitoring list; if not, it indicates that the currently obtained client is not monitored, Then add the identification information to the target monitoring list, and use the monitoring thread corresponding to the target monitoring list to poll the client. If the polling indicates that the client is executing the target SQL statement on the Sybase database, the execution of the target SQL statement is recorded according to the preset strategy. time. In this way, the execution time of the SQL statement that the client is executing on the Sybase database can be recorded. If many clients execute SQL statements on the Sybase database at the same time, the overall performance of the database is reduced. The overall performance of the SQL statement is reduced, and then the corresponding SQL statement is optimized to avoid the frequent occurrence of this situation, thereby improving the database performance.

Correspondingly, the device, device and readable storage medium for recording the execution time of an SQL statement provided by the present application also have the above technical effects.

Description of drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the accompanying drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are only It is an embodiment of the present application. For those of ordinary skill in the art, other drawings can also be obtained according to the provided drawings without any creative effort.

1 is a flowchart of a method for recording the execution time of a SQL statement disclosed by the application;

FIG. 2 is a schematic diagram of specific execution steps of a scheduling thread disclosed in the present application;

3 is a schematic diagram of specific execution steps of a monitoring thread disclosed in the application;

4 is a schematic diagram of a SQL statement execution time recording device disclosed in the application;

FIG. 5 is a schematic diagram of a device for recording the execution time of an SQL statement disclosed in the present application.

Detailed ways

The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, but not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present application.

At present, the Sybase database does not have the relevant function to record the execution information of SQL statements, so it is impossible to find out the SQL statements that reduce the performance of the database in time. To this end, the present application provides a SQL statement execution time recording solution, which can record the relevant information of the SQL statement executed by the client on the Sybase database, and provides assistance for screening problematic SQL statements.

Referring to FIG. 1, an embodiment of the present application discloses a method for recording the execution time of an SQL statement, including:

S101. Acquire identification information of at least one client that is executing the SQL statement on the Sybase database.

It should be noted that there may be multiple clients executing SQL statements against the Sybase database, or there may be only one client. The identification information of all clients that are executing SQL statements on the Sybase database can be recorded, or the identification information of only one client can be recorded at a time.

In a specific embodiment, acquiring the identification information of at least one client that is executing the SQL statement on the Sybase database includes: using a scheduling thread to acquire the identification information of all clients that are executing the SQL statement on the Sybase database with a preset cycle, and obtaining the identification list of information. For example, the scheduling thread obtains the identification information of all clients that are executing SQL statements against the Sybase database every 1 second.

Specifically, the identification information of the client that is executing the SQL statement on the Sybase database is marked as running in the system table sysprocesses of the database. Therefore, the scheduling thread is used to query the system table sysprocesses at a preset period, and the SQL execution on the Sybase database can be obtained. Identification information for the client of the statement. The identification information of the client is such as client address, artificial mark number and so on.

S102: Determine whether the identification information is recorded in any monitoring list; if yes, no operation; if not, execute S103.

In this embodiment, the identification information of the client being monitored by the monitoring thread is recorded in each monitoring list. Generally, one client corresponds to one monitoring thread, and one monitoring thread corresponds to one monitoring list, and the monitoring list records the identification information of the client monitored by the corresponding monitoring thread. Of course, one monitoring thread can also correspond to multiple clients. When it corresponds to multiple clients, the monitoring thread polls each client in the order of the identification information recorded in the monitoring list.

In a specific implementation manner, judging whether the identification information is recorded in any monitoring list includes: judging whether any identification information in the identification information list is recorded in any monitoring list. If any identification information in the identification information list is recorded in any monitoring list, it indicates that the client corresponding to the identification information is already under a certain monitoring thread, so no other operation is required. If any identification information in the identification information list is not recorded in any monitoring list, it indicates that the client corresponding to the identification information is not monitored by a monitoring thread, so it is added to a monitoring list so that the corresponding monitoring thread Monitor the client corresponding to the identification information.

In a specific implementation manner, identification information of the monitored client is recorded in any monitoring list.

S103. Add the identification information to the target monitoring list, and use the monitoring thread corresponding to the target monitoring list to poll the client. If it is found that the client is executing the target SQL statement on the Sybase database, record the target SQL statement according to a preset policy. execution time.

In a specific embodiment, adding the identification information to the target monitoring list includes: selecting a monitoring list with the least amount of information from multiple monitoring lists as the target monitoring list, and adding the identification information to the tail of the target monitoring list. If there are multiple monitoring lists with the least amount of information, a certain monitoring list can be randomly selected from the multiple monitoring lists as the target monitoring list.

In a specific embodiment, if it is polled that the client is executing the target SQL statement on the Sybase database, the execution time of the target SQL statement is recorded according to a preset strategy, including: if the client is polled for the first time that the client is executing the Sybase database The target SQL statement, the execution time of the target SQL statement is recorded as 0; if it is polled again that the client is executing the target SQL statement on the Sybase database, the execution time is incremented by one polling cycle; if it is not polled again that the client is executing the target SQL statement When the target SQL statement is executed against the Sybase database, the current execution time, identification information and the target SQL statement are recorded in the log. If it is not polled again that the client is executing the target SQL statement on the Sybase database, but it is polled that the client is executing other SQL statements on the Sybase database, the current execution time, the The identification information and the target SQL statement are recorded in the log, and the execution time of the other SQL statements is recorded at the same time.

In a specific embodiment, the method further includes: if it is not polled for the first time that the client is executing the target SQL statement on the Sybase database, deleting the identification information in the target monitoring list.

Specifically, after adding the identification information of an unmonitored client to the target monitoring list, if the client is polled for the first time and no SQL statement being executed by the client is found, it indicates that the SQL statement corresponding to the client has been After the execution is completed, the identification information in the target monitoring list can be deleted.

If the client is polled for the first time and the SQL statement being executed by the client is found, the execution time of the SQL statement is recorded as 0; when the SQL statement is polled for the second time, its execution time is incremented by one poll cycle; after that, if the SQL statement is still polled, its execution time is incremented by another polling cycle, and so on.

However, if the SQL statement is not polled for the second time, it indicates that the SQL statement corresponding to the client has been executed. Therefore, the current execution time, identification information and target SQL statement can be recorded in the log, and the target monitoring list can be deleted. identification information. Or if the SQL statement is polled for the second time, but the SQL statement is not polled for the third time (other SQL statements may be polled), the current execution time, identification information and target SQL statement are recorded to the log. At this time, if it is polled that the client is executing other SQL statements on the Sybase database, it means that the client has finished executing the target SQL statement, but other SQL statements are executed immediately, then the current execution time, identification information and the target SQL statement are recorded to log, and simultaneously record the execution time of the other SQL statements.

It can be seen that the embodiment of the present application judges whether the identification information is recorded in any monitoring list after obtaining the identification information of at least one client that is executing the SQL statement on the Sybase database; if not, it indicates that the currently obtained client is not For monitoring, the identification information is added to the target monitoring list, and the monitoring thread corresponding to the target monitoring list is used to poll the client. If the polling indicates that the client is executing the target SQL statement on the Sybase database, the target SQL statement is recorded according to the preset strategy. execution time. In this way, the execution time of the SQL statement that the client is executing on the Sybase database can be recorded. If many clients execute SQL statements on the Sybase database at the same time, the overall performance of the database is reduced. The overall performance of the SQL statement is reduced, and then the corresponding SQL statement is optimized to avoid the frequent occurrence of this situation, thereby improving the database performance.

Referring to Fig. 2, the specific execution steps of the scheduling thread mentioned in the above embodiment include:

1. The scheduling thread goes to the system table sysprocesses of the database to obtain the client number (ie, client identification information) whose status is running, and forms a list.

2. The scheduling thread organizes and sorts the monitoring lists corresponding to each monitoring thread, and obtains the client number in each monitoring list at the same time.

3. The scheduling thread compares the client numbers obtained in steps 1 and 2 to find out the client numbers that are not currently being monitored.

4. The scheduling thread finds the list with the fewest elements in the monitoring list, and adds the newly found client number in step 3 to it.

5. Repeat steps 1-4 after the scheduling thread rests for 1 second. The scheduling period is 1 second.

Referring to Fig. 3, the specific execution steps of any monitoring thread mentioned in the above embodiment include:

1. The monitoring thread checks whether its corresponding monitoring list is empty. If the list is empty, repeat this step after resting for 1 second.

2. If the list is not empty, the monitoring thread extracts the client number to be monitored from the monitoring list, and monitors the SQL statement executed by the corresponding client in the database according to the extracted number.

3. The monitoring thread detects whether the SQL statement currently executed by the client is empty. If it is empty, the client is not currently executing the SQL statement. If the execution time is recorded at this time, the monitored SQL statement and execution time will be saved. to the log, and delete the client number from the list at the same time, and no longer monitor the client.

4. If the monitored SQL statement is the same as the last monitored SQL statement, it is considered that the SQL statement is in continuous execution, and the recorded execution time of the SQL statement is +1 second, that is, incrementing a polling cycle.

5. If the monitored SQL statement is different from the last monitored SQL statement, it is considered that the last monitored SQL statement has been executed, and the last monitored SQL statement and its execution time are saved in the log. Then record the SQL statement monitored this time, and record the execution time as 0.

6. Repeat steps 1-5 after resting for 1 second. Among them, the polling period is 1 second.

It should be noted that, other implementation steps in this embodiment are the same as or similar to the above-mentioned embodiments, and thus are not described again in this embodiment.

As can be seen from the above, the present embodiment can record the execution time of the SQL statement that the client is executing to the Sybase database. If many clients simultaneously execute the SQL statement to the Sybase database, the overall performance of the database is reduced. Execution time Find out the SQL statement that reduces the overall performance of the database, and then optimize the corresponding SQL statement to avoid the frequent occurrence of this situation, thereby improving database performance.

A device for recording an execution time of an SQL statement provided by an embodiment of the present application will be introduced below. The device for recording an execution time of an SQL statement described below and a method for recording an execution time of an SQL statement described above may refer to each other.

Referring to FIG. 4 , an embodiment of the present application discloses a device for recording the execution time of an SQL statement, including:

Obtaining module 401, for obtaining the identification information of at least one client that is executing SQL statement to Sybase database;

Judging module 402, for judging whether the identification information is recorded in any monitoring list;

The recording module 403 is configured to add the identification information to the target monitoring list if the identification information is not recorded in any monitoring list, and use the monitoring thread corresponding to the target monitoring list to poll the client. When the Sybase database executes the target SQL statement, the execution time of the target SQL statement is recorded according to the preset strategy.

In a specific embodiment, the acquisition module is specifically used for:

Use the scheduling thread to obtain identification information of all clients that are executing SQL statements on the Sybase database at a preset period, and obtain a list of identification information.

In a specific embodiment, the judgment module is specifically used for:

Determine whether any identification information in the identification information list is recorded in any monitoring list.

In a specific implementation, the recording module is specifically used for:

Select the monitoring list with the least amount of information from multiple monitoring lists as the target monitoring list, and add the identification information to the tail of the target monitoring list.

In a specific implementation, the recording module is specifically used for:

If it is polled for the first time that the client is executing the target SQL statement against the Sybase database, the execution time of the target SQL statement is recorded as 0;

If it is polled again that the client is executing the target SQL statement on the Sybase database, the execution time is incremented by one polling cycle;

If it is not polled again that the client is executing the target SQL statement on the Sybase database, the current execution time, identification information and the target SQL statement are recorded in the log.

In a specific embodiment, the recording module is also used for:

If the client is not polled for the first time to execute the target SQL statement on the Sybase database, delete the identification information in the target monitoring list.

In a specific implementation manner, identification information of the monitored client is recorded in any monitoring list.

For the more specific working process of each module and unit in this embodiment, reference may be made to the corresponding content disclosed in the foregoing embodiments, which will not be repeated here.

It can be seen that this embodiment provides a SQL statement execution time recording device, which can record the execution time of the SQL statement that the client is executing on the Sybase database. If many clients simultaneously execute the SQL statement on the Sybase database, the entire database will be If the performance is degraded, the technician can find out the SQL statement that causes the overall performance of the database to degrade based on the recorded execution times, and then optimize the corresponding SQL statement to avoid the frequent occurrence of this situation, thereby improving the database performance.

The following describes an SQL statement execution time recording device provided by the embodiments of the present application. The SQL statement execution time recording device described below and the SQL statement execution time recording method and apparatus described above can be referred to each other.

Referring to FIG. 5 , an embodiment of the present application discloses a device for recording the execution time of an SQL statement, including:

Memory 501, used to save computer programs;

The processor 502 is configured to execute the computer program to implement the method disclosed in any of the foregoing embodiments.

A readable storage medium provided by an embodiment of the present application is introduced below. A readable storage medium described below and a method, apparatus, and device for recording the execution time of an SQL statement described above can be referred to each other.

A readable storage medium for storing a computer program, wherein, when the computer program is executed by a processor, the method for recording the execution time of an SQL statement disclosed in the foregoing embodiments is implemented. For the specific steps of the method, reference may be made to the corresponding content disclosed in the foregoing embodiments, which will not be repeated here.

References in this application to "first", "second", "third", "fourth", etc. (if any) are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It is to be understood that data so used may be interchanged under appropriate circumstances so that the embodiments described herein can be practiced in sequences other than those illustrated or described herein. Furthermore, the terms "comprising" and "having", and any variations thereof, are intended to cover non-exclusive inclusion, for example, a process, method or apparatus comprising a series of steps or elements is not necessarily limited to those steps or elements expressly listed , but may include other steps or elements not expressly listed or inherent to these processes, methods or apparatus.

It should be noted that the descriptions involving "first", "second", etc. in this application are only for the purpose of description, and should not be construed as indicating or implying their relative importance or implying the number of indicated technical features . Thus, a feature delimited with "first", "second" may expressly or implicitly include at least one of that feature. In addition, the technical solutions between the various embodiments can be combined with each other, but must be based on the realization by those of ordinary skill in the art. When the combination of technical solutions is contradictory or cannot be realized, it should be considered that the combination of such technical solutions does not exist. , is not within the scope of protection claimed in this application.

The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments, and the same or similar parts of the various embodiments may be referred to each other.

The steps of a method or algorithm described in conjunction with the embodiments disclosed herein may be directly implemented in hardware, a software module executed by a processor, or a combination of the two. A software module can be placed in random access memory (RAM), internal memory, read only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, hard disk, removable disk, CD-ROM, or any other in the technical field. in any other form of readable storage medium that is well known.

The principles and implementations of the present application are described herein by using specific examples. The descriptions of the above embodiments are only used to help understand the methods and core ideas of the present application. There will be changes in the specific implementation and application scope. To sum up, the content of this specification should not be construed as a limitation on the application.

Claims (10)

1. A method for recording the execution time of an SQL statement is characterized by comprising the following steps:

acquiring identification information of at least one client executing SQL statements on a Sybase database;

judging whether the identification information is recorded in any monitoring list;

if not, adding the identification information to a target monitoring list, polling the client by using a monitoring thread corresponding to the target monitoring list, and recording the execution time of the target SQL statement according to a preset strategy if the client is polling to execute the target SQL statement on the Sybase database.

2. The SQL statement execution time recording method according to claim 1, wherein the obtaining identification information of at least one client that is executing SQL statements on a Sybase database comprises:

and acquiring identification information of all clients executing SQL statements on the Sybase database by utilizing the scheduling thread in a preset period to obtain an identification information list.

3. The SQL statement execution time recording method according to claim 2, wherein the determining whether the identification information is recorded in any monitoring list comprises:

and judging whether any identification information in the identification information list is recorded in any monitoring list.

4. The SQL statement execution time recording method according to claim 1, wherein the adding the identification information to a target monitoring list comprises:

and selecting a monitoring list with the least information amount from a plurality of monitoring lists as the target monitoring list, and adding the identification information to the tail part of the target monitoring list.

5. The method for recording execution time of SQL statements according to claim 1, wherein if it is polled that the client executes a target SQL statement on the Sybase database, recording the execution time of the target SQL statement according to a preset policy, comprising:

if the client executes the target SQL statement on the Sybase database in the first polling mode, recording the execution time of the target SQL statement as 0;

if the client executes the target SQL statement on the Sybase database by polling again, the execution time is increased by one polling period;

if the client does not poll the Sybase database again until the client executes the target SQL statement on the Sybase database, recording the current execution time, the identification information and the target SQL statement to a log.

6. The SQL statement execution time recording method according to claim 5, further comprising:

deleting the identification information in the target monitoring list if the client is not polling the Sybase database to execute the target SQL statement for the first time.

7. The method for performing time recording of SQL statements according to claim 5, wherein if it is not polled again that the client is executing the target SQL statement on the Sybase database, but it is polled that the client is executing other SQL statements on the Sybase database, then the current execution time, the identification information and the target SQL statement are recorded to a log, and the execution times of the other SQL statements are recorded at the same time.

8. An SQL statement execution time recording apparatus, comprising:

the system comprises an acquisition module, a query module and a query module, wherein the acquisition module is used for acquiring identification information of at least one client executing SQL statements on a Sybase database;

the judging module is used for judging whether the identification information is recorded in any monitoring list;

and the recording module is used for adding the identification information to a target monitoring list if the identification information is not recorded in any monitoring list, polling the client by using a monitoring thread corresponding to the target monitoring list, and recording the execution time of the target SQL statement according to a preset strategy if the client is polling to execute the target SQL statement on the Sybase database.

9. An SQL statement execution time recording apparatus, comprising:

a memory for storing a computer program;

a processor for executing the computer program to implement the SQL statement execution time recording method according to any of claims 1 to 7.

10. A readable storage medium for storing a computer program, wherein the computer program, when executed by a processor, implements the SQL statement execution time recording method according to any one of claims 1 to 7.

Images