Method and apparatus for post-migration suboptimal query identification and index optimization

Abstract

The invention discloses a method (800) and a device (900) for sub-optimal query identification and index optimization after migration to cloud storage. The method (S800) for identifying a suboptimal query comprises the following steps: determining (S801) query execution statistical information in a source database (SRC-DB); the method comprises the following steps: determining (S802) query execution statistics in a target database (DST-DB), and determining (S802) the query execution statistics in the target database (DST-DB); the query execution statistics in the SRC-DB and the query execution statistics in the DST-DB are compared (S803).

Description

Technical Field

[0001] The subject matter described herein relates generally to cloud storage and, more particularly, to a method and apparatus for post-migration suboptimal query identification and index optimization. Background Art

[0002] Cloud storage is a computer data storage model in which digital data is stored in a logical pool, or "cloud". Physical storage spans multiple servers (sometimes in multiple locations), and the physical environment is usually owned and managed by a hosting company. These cloud storage providers are responsible for keeping the data available and accessible and ensuring that the physical environment is secure, protected, and functioning properly. Individuals and organizations purchase or lease storage capacity from providers to store user, organizational, or application data. Cloud storage services can be accessed through co-located cloud computing services, web service application programming interfaces (APIs), or through applications that use APIs (for example, cloud desktop storage, cloud storage gateways, or web-based content management systems).

[0003] Today, customers are choosing cloud frameworks to reduce the costs associated with on-premises database upgrades and maintenance. Therefore, the cloud database market is expected to grow exponentially in the coming years. More and more customers are moving / migrating their on-premises databases to relational databases on the cloud, which poses a challenge in evaluating the performance / stability / functionality of the migrated database. The more specific challenge faced by customers is to identify possible technical solutions on how to reduce costs for their clients. The preferred option for customers is to migrate from licensed databases to open source databases, but there are many challenges, which are discussed below:

[0004] (a) Migration Tools: There is no universal tool available for evaluation across multiple target databases (DBs). All commercial vendors support migration to their commercial DBs, but at a cost.

[0005] (b) Cost assessment: A migration and optimization team consisting of professional database administrators (DBAs) must be established. The cost of establishing such a team is generally very high.

[0006] (c) Feedback time: The feedback time for the following aspects is quite long.

[0007] ● Migration;

[0008] ● Execute simulation application on new DB;

[0009] ● Identifying bottlenecks and suboptimal queries is complex and time-consuming, and most of them are discovered late in the cycle;

[0010] ●Another cycle was added for repairs and regressions.

[0011] (d) Automatic report generation after migration: There is no tool that can provide detailed reports on suboptimal queries and recommend index changes to optimize space / speed.

[0012] As part of the migration to a cloud database (schema, application and data), efforts are being made to provide frameworks / tools / capabilities to evaluate and understand the data in the source system, but there are few / non-existent frameworks / capabilities to determine how the migrated data and the migrated SQL / application / workload will perform in the target environment. There is still a chance that the migrated queries may not meet the performance goals or need further tuning, which will become a long-term task after the migration. This requires manual work by the DBA / user, which is time-consuming and labor-intensive. A live application can include 100s to 1000s of tables, each table can have multiple indexes, and the entire application can have 1000s of queries in progress. Therefore, it is practically impossible to check, evaluate and confirm each table one by one.

[0013] According to the latest industry standards and trends, most optimizer plan comparison methods and index recommendation methods are used in the same vendor-specific database management system (different versions). In this case, there are some workloads where plans in the same or different versions of the same database may need to be compared. DBAs can provide index recommendations and tuning methods based on specific query performance, which involves manual operations. The Microsoft Cloud Service team is conducting research on SQL databases (deployed in the cloud) on automatically improving query performance by adjusting indexes. Currently, there is no cloud service that can help customers evaluate multiple target databases, save cost and time, and obtain more detailed in-depth migration reports.

[0014] The following discusses technical issues associated with the prior art:

[0015] 1. High workload for locating and identifying delays: Delays and manually identifying suboptimal queries generated by the new database migration process (e.g., Oracle to PG / MySQL / …):

[0016] a. In actual situations, users / customers will identify the impact of suboptimal queries on the overall application performance at a later stage after the migration;

[0017] b. This requires the DBA to manually analyze the entire application and identify the exact query that is causing the impact.

[0018] 2. Long waiting time: Users must wait for the entire application and data migration to evaluate and confirm whether to continue using the new target DB.

[0019] a. Users cannot make decisions and have no comprehensive understanding of whether the decision to migrate to a new target DB is feasible and will not have an impact. Evaluating the new target DB generates duplicate costs / efforts.

[0020] 3. There are challenges in recommending index optimization: It is complex and error-prone for the DBA to understand the complete DB schema to identify the unused indexes / fields in the index.

[0021] a. Over time, the schema may change, impacting indexes, and the DBA cannot analyze these changes manually (when the customer script has 1000s of tables). These changes can be analyzed and optimized when migrating to the target DB.

[0022] 4. No industry tools are recommended for cross-store / engine migration: There is no recommended tool for migration between different stores / engines. KV storage

[0023] RDBMS Non-relational …

[0024] 5. Attract more customers to adopt cloud-native deployment: By bringing the service to the cloud, customers can be attracted to migrate their services / applications / databases directly to Huawei Cloud.

[0025] 6. CAICT Performance Level 3: This meets one of the requirements of Application Object Reconstruction - Level 3 Capability (CAICT - China Academy of Information and Communications Technology):

[0026] "Supports comparative analysis of execution plans and performance of application SQL statements collected in source and target databases."

[0027] Therefore, there is a need to automatically perform query cost comparisons between the source database and the migrated target database and generate suboptimal query reports as output in order to improve space utilization and / or query performance compared to traditional cloud migration techniques. In addition, detailed query reports (where conditions) and indexes on columns and index optimizations are also required to provide a cost-effective and systematic approach to identifying index optimizations.

[0028] The above suboptimal query identification and index optimization after migration are only intended to summarize some shortcomings of traditional systems / mechanisms / techniques and are not exhaustive. After reading the following description, other problems / shortcomings associated with traditional systems / mechanisms / techniques and the corresponding benefits of various non-limiting embodiments described herein will become more apparent. Summary of the invention

[0029] This summary is provided to introduce concepts related to a method and apparatus for post-migration suboptimal query identification and index optimization, and is further described in the detailed description below. This summary is neither intended to identify key features of the claimed subject matter nor to determine or limit the scope of the claimed subject matter.

[0030] The purpose of the present invention is to provide suboptimal query identification and index optimization after migration to cloud storage.

[0031] Another object of the present invention is to automatically perform a query cost comparison between a source database and a migrated target database to generate a report of suboptimal queries as output.

[0032] Another object of the present invention is to provide suggestions for index optimization to improve storage space and query performance.

[0033] Yet another object of the present invention is to compare the query costs of two different engines / vendors of a source database and a target database as part of the migration process before the actual data migration and application migration begins.

[0034] Specifically, the present invention discloses a method and apparatus for suboptimal query identification and index optimization after migration to cloud storage to overcome the shortcomings associated with the prior art.

[0035] According to a first aspect of the present invention, a method for post-migration suboptimal query identification and index optimization is provided. The method comprises the following steps: determining query execution statistics in a source database (SRC-DB); determining query execution statistics in a destination database (DST-DB); and comparing the query execution statistics in the SRC-DB with the query execution statistics in the DST-DB.

[0036] According to the first aspect, in a first possible implementation of the method, determining (S801) query execution statistics in the SRC-DB includes the following steps: executing a query on the SRC-DB; extracting and capturing a query plan from the executed query; parsing the query plan to obtain query information from the SRC-DB.

[0037] According to the first aspect, in a second possible implementation of the method, determining (S802) query execution statistics in the DST-DB includes the following steps: executing a query on the DST-DB; extracting and capturing a query plan from the executed query; and parsing the query plan to obtain query information from the DST-DB.

[0038] According to the first aspect, in a third possible implementation of the method, the query information includes a table schema, table data statistics / indicators, and a table catalog.

[0039] According to the first aspect, in a fourth possible implementation of the method, the comparing the query execution statistics in the SRC-DB and the query execution statistics in the DST-DB (S803) includes the following steps: extracting the cost associated with executing the query on the SRC-DB from the query plan; extracting the cost associated with executing the query on the DST-DB from the query plan; comparing the cost associated with executing the query on the SRC-DB and the cost associated with executing the query on the DST-DB.

[0040] According to a second aspect of the present invention, there is provided a device for post-migration suboptimal query identification and index optimization. The device comprises: a source query execution unit for determining query execution statistics in a source database (SRC-DB); a target query execution unit for determining query execution statistics in a target database (DST-DB); and a comparison unit for comparing the query execution statistics in the SRC-DB with the query execution statistics in the DST-DB.

[0041] According to the second aspect, in a first possible implementation of the device, the source query execution unit is used to: execute a query on the SRC-DB; extract and capture a query plan from the executed query; and parse the query plan to obtain query information from the SRC-DB.

[0042] According to the second aspect, in a second possible implementation of the device, the target query execution unit is used to: execute a query on the DST-DB; extract and capture a query plan from the executed query; and parse the query plan to obtain query information from the DST-DB.

[0043] According to the second aspect, in a third possible implementation of the device, the comparison unit is used to: extract the cost associated with executing the query on the SRC-DB from the query plan; extract the cost associated with executing the query on the DST-DB from the query plan; and compare the cost associated with executing the query on the SRC-DB with the cost associated with executing the query on the DST-DB.

[0044] According to the second aspect, in a fourth possible implementation manner of the device, the query information includes a table schema, table data statistics / indicators, and a table catalog.

[0045] According to a third aspect of the present invention, there is provided a computer readable storage medium for post-migration suboptimal query identification and index optimization. The computer readable storage medium stores a computer program, and when the computer program is executed on a processor, the processor performs the method described in the first aspect of the present invention.

[0046] Other aspects, advantages, and salient features of the present invention will become apparent to those skilled in the art through the following detailed description of exemplary embodiments of the present invention disclosed in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] The detailed description is described with reference to the accompanying drawings. In the drawings, one or more digits in a reference number indicate the drawing in which the reference number first appears. The same numerals are used throughout the drawings to indicate similar features and components.

[0048] Figure 1 A block diagram showing data collection and data processing performed at different stages by a suboptimal query identification system in one implementation of the present invention is shown.

[0049] Figure 2 A block diagram of a typical cloud service deployment in an implementation of the present invention is shown.

[0050] Figure 3 A block diagram of a general query plan for suboptimal query identification in one implementation of the present invention is shown.

[0051] Figure 4 A flow chart showing a process flow for suboptimal query identification in one implementation of the present invention.

[0052] Figure 5 Sample query plans on a source DB and a target DB in one implementation of the present invention are shown.

[0053] Figure 6 A flow chart for identifying a sequence for index optimization in one implementation of the present invention is shown.

[0054] Figure 7 A graphical representation of columns and index columns in a table in one implementation of the present invention is shown.

[0055] Figure 8 A flow chart of a method for post-migration suboptimal query identification and index optimization provided by another embodiment of the present invention is shown.

[0056] Fig. 9A block diagram of an apparatus for post-migration suboptimal query identification and index optimization provided by another embodiment of the present invention is shown.

[0057] It is to be understood that the drawings are for the purpose of illustrating the concepts of the present invention and may not be drawn to scale. DETAILED DESCRIPTION

[0058] The technical scheme in the embodiment of the present invention is clearly described below in conjunction with the accompanying drawings in the embodiment of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work should belong to the scope of protection of the present invention.

[0059] The present invention can be implemented in a variety of ways as a process, device, system, material composition, computer-readable medium (e.g., computer-readable storage medium or computer network), wherein program instructions are sent via optical or electronic communication links. In this specification, these implementations or any other form that the present invention may take may be referred to as techniques. In general, the order of the steps of the disclosed process can be changed within the scope of the present invention.

[0060] One or more embodiments of the present invention and the accompanying drawings illustrating the principles of the present invention are described in detail below. The present invention is described in conjunction with these embodiments, but the present invention is not limited to any embodiment. The scope of the present invention is limited only by the claims, and the present invention includes many alternatives, modifications and equivalents. In order to provide a thorough understanding of the present invention, many specific details are set forth in the following description. These details are provided for the purpose of example, and the present invention can be practiced according to the claims without some or all of these specific details. For the sake of clarity, technical materials known in the technical field related to the present invention are not described in detail to avoid unnecessary confusion of the present invention.

[0061] In the following specific embodiments, in order to provide a thorough understanding of the present invention, many specific details are set forth. However, it will be appreciated by those skilled in the art that the present invention can be practiced without these specific details. In other cases, well-known methods, processes, components, modules, units and / or circuits are not described in detail to avoid confusing the present invention.

[0062] Although embodiments of the present invention are not limited in this regard, discussions using terms such as "processing," "computing / calculating," "determining," "establishing," "analyzing," and "examining" may refer to one or more operations and / or one or more processes of a computer, computing platform, computing system, or other electronic computing device that operates on data represented as physical quantities (e.g., electronic quantities) within computer registers and / or memories and / or other data similarly represented as physical quantities converted into computer registers and / or memories or other information non-transitory storage media that may store instructions for performing the operations and / or processes.

[0063] Although embodiments of the present invention are not limited thereto, the term "multiple" used herein may include "two or more" and the like. The term "multiple" may be used throughout the specification to describe two or more components, devices, elements, units, parameters, and the like. Unless explicitly stated, the method embodiments described herein are not limited to a particular order or sequence. In addition, some of the described method embodiments or their elements may occur or be performed at the same time or simultaneously.

[0064] In the present invention, "cloud storage" refers to a cloud computing model in which data is stored on the Internet through a cloud computing provider, who manages and operates the data storage as a service.

[0065] In the present invention, "index optimization" refers to identifying unused / unnecessary indexes. This optimization helps reduce storage space and can help improve performance.

[0066] In the present invention, "suboptimal query" refers to a query performance that does not meet the standard. There is an opportunity to modify the query to improve its performance.

[0067] In the present invention, "application migration" refers to the process of migrating a software application from one computing environment to another. Database migration involves two aspects, (i) data and (ii) application. The code executed for storage / modification / retrieval is called application. Generally speaking, these codes are dedicated to a specific database, and functions / procedures / system views, etc. are specially designed to improve performance and are proprietary codes. Migrating the entire SQL code from one database to another is called application migration.

[0068] In the present invention, "data migration" refers to the process of moving data from one database to another. As mentioned above, database migration involves two aspects, (i) data and (ii) applications. If a user decides to migrate from one database to another, the data stored in the database needs to be migrated. Different database vendors support specific data types or formats, and providing data from one database to another so that applications can run on the basis of these data is called "data migration".

[0069] In the present invention, "query" refers to the SELECT statement in SQL. This statement is used to obtain data.

[0070] In the present invention, a "query plan" refers to a plan prepared according to the execution mode of a query.

[0071] The present invention discloses a method and apparatus for suboptimal query identification and index optimization. Although various aspects are described that allow customers to evaluate migration in a short period of time, deploy minimal infrastructure, and provide detailed reports when migrating from a high-cost license to a free / open source database, the present invention can be implemented in any number of different computing systems, environments, and / or configurations, and various embodiments are described in the context of the following exemplary systems, devices / nodes / devices, and methods.

[0072] The following is an explanation of the embodiments of the present invention with the help of exemplary schematic diagrams and one or more examples. However, these exemplary schematic diagrams and examples are for a better understanding of the present invention and should not be interpreted as limiting the scope of the present invention.

[0073] In the latest technology and trends, the optimizer plan comparison method and the index recommendation method are used in the same vendor-specific database management system (different versions). In this case, there are some workloads in which plans in the same or different versions of the same database may need to be compared. The DBA can provide index recommendations and tuning methods based on specific query performance, which involves manual operations. The Microsoft Cloud Service team is conducting research on SQL databases (deployed on the cloud) on automatically improving query performance by adjusting indexes. Currently, there is no cloud service that can help customers evaluate multiple target databases, save cost and time, and obtain more detailed in-depth migration reports.

[0074] The drawback associated with traditional techniques is that manually scanning 1,000s of tables, indexes, and queries to prepare these details is virtually impossible, error-prone, and requires a lot of effort.

[0075] Therefore, it is necessary to compare the query costs of the two engines of the source and target databases (different vendors / engines) as part of the migration process before the actual data migration and application migration begins, in order to find a cost-effective and systematic approach for determining index optimization. The present invention discloses a method and apparatus for post-migration suboptimal query identification and index optimization by running customer queries on the source and target DBs, collecting plan information, and generating reports based on costs to indicate post-migration suboptimal queries.

[0076] The invention discloses running customer queries on source and target databases, collecting planning information (format, information is different on different DBs) and generating reports based on cost to indicate suboptimal queries after migration. These require user intervention to further optimize the queries. The complete database schema is scanned, unused indexes / fields are identified, or new indexes / fields are recommended to be added to existing indexes, which is required to optimize efficient storage usage and improve query performance. In addition, when the cost of the query in the target database is high compared to the source database, the query is regenerated and optimized to reduce the cost. If this cannot be done, the query is added to the list of suboptimal queries.

[0077] It is worth noting that the target DB for migration is determined by the user / customer. In addition, table statistics, metrics, and table catalog information are used to recreate data similar to the source database. It is worth noting that any commercially available tools can be used to generate similar data on the migrated DB, so it is not discussed here. In addition, the present invention is not a database management system (DBMS) specific index recommendation engine or comparison framework.

[0078] Figure 1A block diagram of data collection and data processing performed by a suboptimal query identification system in one implementation of the present invention at different stages is shown. The SQL pre-parser extracts table schemas, table data statistics / indicators, and table catalogs from a source database (SRC-DB). This information is passed through a migration tool (a third-party component) to convert SQL scripts according to a destination database (DST-DB). Using the table schema, table data statistics, and table catalog, a simulation data generator tool (i.e., a third-party component) simulates data on which the migrated SQL is executed to obtain a plan. The query executor and plan comparison module execute the migrated SQL on the DST DB and obtain plan details. The plans in the SRC DB and DST DB are parsed to extract the required information. The query executor and plan cost comparison module parse the plans in the SRC DB and DST DB and prepare the data in a tabular format for user understanding. Based on the costs involved, if the cost in the SRC-DB is lower than the cost in the DST DB, specific queries that require manual user intervention are marked, such as Figure 5 Using the table schema (table columns, indexes, index fields, and SQL), prepare the matrix to identify:

[0079] (a) Are there any columns that need to be included in the index (e.g. Figure 7 #1 in );

[0080] (b) Are there any indexed fields that are not used in any queries (e.g. Figure 7 3).

[0081] Figure 2 A block diagram of a typical cloud service deployment in an implementation of the present invention is shown. Specifically, suboptimal query identification technology is deployed as a service on the cloud. This is a typical cloud service deployment, which is mainly divided into three layers, namely the bottom layer, the middle layer and the top layer. It is worth noting that the bottom layer is the new service, the middle layer is the infrastructure, and the top layer is the user interface (UI). In addition, there are other supporting services / components, such as zookeeper, Kafka for messaging, databases for SRC and DST, repository DB, etc., to maintain information on the cloud. Load balancing (LB) can be used from the UI to control the load.

[0082] The core premise of the present invention is to automatically run customer queries in the source DB and migrated Structured Query Language (SQL) queries in the target DB, parse the query plan and collect all key information such as but not limited to cost, search path, sequential / index search, index search type and returned approximate rows. In order to identify suboptimal queries, the cost of the query in the source DB and the target DB is populated.

[0083] It is worth noting that preparing a report to list suboptimal queries in the target DB requires user / manual intervention to further review / optimize these queries. Depending on the maturity of the tool, the tool may perform some query optimizations based on the target DB schema.

[0084] It is worth noting that MySQL cannot predict how many rows there may be in the result between two values, while PG has better predictability. A few databases such as ORACLE can create dynamic hash indexes when subqueries are required for fast execution.

[0085] In the present invention, a table is created by scanning the target DB SQL query to identify the columns used in the "where" condition of the query, and a separate table is created to identify the columns used in the index. Machine learning techniques are used to analyze the source DB and the target DB to identify the following:

[0086] (a) Unused indexes, such as Figure 7 As shown;

[0087] (b) The fields in the index do not constitute the where condition in the query;

[0088] (c) A primary field that is part of the where condition in a query but is not part of any index.

[0089] ■Optimize space by deleting unused indexes and field parts of indexes.

[0090] ■Improve query performance through index optimization.

[0091] The complete technical solution proposed by the present invention can be deployed as a service on the cloud. This will allow users / customers to evaluate migration in a short time frame, deploy minimal infrastructure and provide detailed reports, thereby helping customers migrate from high-cost licenses to free / open source databases. Customers can evaluate multiple target databases with little manpower and / or cost. The advantage of using cloud infrastructure to evaluate target DBs is a significant advantage in itself, and the other benefits associated with cloud storage are already well known and will not be repeated here.

[0092] Figure 3A block diagram of a general query plan in one implementation of the present invention is shown. Specifically, Figure 3 A graphical representation of how the plan can be visualized is provided. The leaf nodes of the plan are executed first and then the data is propagated to the upper levels. Therefore, Est (estimated) number of rows and Est (estimated cost) represent the weight and number of rows that these nodes can produce. The same information is populated in a tabular format in Figure b. For ease of understanding, a simple query plan is considered. There are three depths of query execution. Hash join is applied to the result set obtained by hash join and index scan, and the next level hash join is done with the result set of two index scans. First, consider the total cost of the query:

[0093] Est cost: 35, Est lines: 200

[0094] Based on the above results, the following inferences can be drawn:

[0095] 1. There are queries with WHERE conditions on columns that are not part of any designed index. (C3): You can design a new index or add this column as part of the existing index (most queries have where conditions) to improve query performance;

[0096] 2. There are queries with WHERE conditions on the columns belonging to the index;

[0097] 3. Indexes exist on columns that are not part of any WHERE condition of the query. (C25, C27): Indexes can be optimized to improve space utilization and WRITE operations on the table.

[0098] Table 1: Sample table schema

[0099]

[0100] Figure 4 A flowchart of a process flow for suboptimal query identification in one implementation of the present invention is shown. The method comprises the following steps:

[0101] 1.a. Execute a query on SRC-DB;

[0102] 1.b. Extract and capture query plans from executed queries;

[0103] 1.c. Parse the query plan to get detailed information from SRC-DB.

[0104] 2.a. Execute query on DST-DB;

[0105] 2.b. Extract and capture query plans from executed queries;

[0106] 2.c. Parse the query plan to get detailed information from DST-DB.

[0107] After that, the query plan information in SRC-DB and the query plan information in DST-DB are input to the query executor and the plan cost comparison module for comparison. Specifically, the query plans are compared to check whether the query execution cost in SRC-DB is high or the query execution cost in DST-DB is high.

[0108] Figure 5 Sample query plans in the source DB and target DB in one implementation of the present invention are shown. The same query is executed on the source DB and the target DB to obtain the query plan. Information (i.e., table schema, table catalog, and SQL query) is extracted from the query plan and populated in a tabular format. For example, when the actual time on the source DB is less than the actual time on the target DB, the query is marked as requiring manual intervention because it takes a long time to execute the query on the target DB. This requires the DBA to investigate the cause and further optimize the query, which is not within the scope of the present invention.

[0109] Figure 6 The flowchart for identifying a sequence for index optimization in one implementation of the present invention is shown. The SQL pre-parser extracts all table information, i.e., table schema, table catalog, and SQL query, from the source (SRC) DB. The SQL pre-parser then parses this information and prepares a matrix. Based on the matrix, a visual representation can be created to indicate:

[0110] 1. The query has a where condition (Qry_wt is>1&idx_wt=0) on a field that is not part of any index. This helps performance optimization.

[0111] 2. The query does not use the indexed field (Qry_wt=0&idx_wt>0). This helps space optimization.

[0112] When both are non-zero, it indicates that there is a query with a where condition on the field that is also an index field.

[0113] The complete schema of the migrated database is prepared and fed into the machine learning algorithm to analyze and provide important insights into the relationship between query fields and index fields. The parser scans the entire database to understand the table schema (tables + indexes) and the queries executed on the database. The output is 2 data files, mainly consisting of sparse matrices.

[0114] Attached is the input data file for reference:

[0115] Query field table:

[0116]

[0117]

[0118] 1 means that the column belongs to the where condition in the query.

[0119] Index field table:

[0120] index C1 C2 C3 C4 C5 C6 …… C10 Index_1 0 0 0 1 0 0 …… 0

[0121] 1 means that the column belongs to the designed index.

[0122] These data files are provided as input to the machine learning algorithm. The first step is data cleaning (DATACLEANING), removing all headers (column / row names).

[0123] This data is further processed to identify the columns for which queries exist and for which indexes are available.

[0124] Recommendations / Suggestions Find columns that are part of the query but not part of any index to add to the index to improve performance.

[0125] It is recommended to find the columns that are not part of the index and are not part of any query conditions and remove them from the index to improve the performance of the WRITE operation.

[0126] Assume that the table statistics are as follows:

[0127] Table 2: Sample table statistics

[0128] Number of fields / columns# 50 The number of queries on this table 200 Number of Indexes 15

[0129] Figure 7 A graphical representation of the columns in a table and index columns in one implementation of the invention is shown. The idx_wt (orange) or Qry_wt (blue) lines represent fields that belong to an index and fields for which a query has a where condition, respectively. Only idx_wt means that an index exists, but no query with a where condition exists on these fields. Only Qry_wt lines mean that the fields used in the where condition of this particular query do not belong to any index. Therefore, both cases require user intervention in the form of index or space optimization, which is beyond the scope of this document.

[0130] The method for suboptimal query identification and index optimization formats the data file and parses the content of the input data file in a human readable format / visualization. The method can be further extended to a pure machine learning algorithm that has been trained / tested with more data. The method is limited to parsing the data and visualizing the data.

[0131] Figure 8 A flowchart of a method (800) for post-migration suboptimal query identification and index optimization provided by a second embodiment of the present invention is shown. The method (800) comprises the following steps:

[0132] Step 801: Determine query execution statistics in a source database (SRC-DB);

[0133] Step 802: Determine query execution statistics in a destination database (DST-DB);

[0134] Step 803: Compare the query execution statistics in the SRC-DB with the query execution statistics in the DST-DB.

[0135] The step of determining (S801) query execution statistics in SRC-DB includes the following steps: executing a query on SRC-DB; extracting and capturing a query plan from the executed query; parsing the query plan to obtain query information from SRC-DB. The step of determining (S802) query execution statistics in DST-DB includes the following steps: executing a query on DST-DB; extracting and capturing a query plan from the executed query; parsing the query plan to obtain query information from DST-DB. The step of comparing (S803) query execution statistics in SRC-DB with query execution statistics in DST-DB includes the following steps: extracting a cost associated with executing the query on SRC-DB from the query plan; extracting a cost associated with executing the query on DST-DB from the query plan; comparing the cost associated with executing the query on SRC-DB with the cost associated with executing the query on DST-DB. The query information includes table schema, table data statistics / metrics, and table directory.

[0136] Fig. 9A block diagram of an apparatus (900) for post-migration suboptimal query identification and index optimization provided by an embodiment of the present invention is shown. The apparatus (900) includes: a source query execution unit (901) for determining query execution statistics in a source database (SRC-DB); a target query execution unit (902) for determining query execution statistics in a target database (DST-DB); and a comparison unit (903) for comparing query execution statistics in the SRC-DB with query execution statistics in the DST-DB. The source query execution unit (901) is used to: execute a query on the SRC-DB; extract and capture a query plan from an executed query; and parse the query plan to obtain query information from the SRC-DB. The target query execution unit (902) is used to: execute a query on the DST-DB; extract and capture a query plan from an executed query; and parse the query plan to obtain query information from the DST-DB. The comparison unit (903) is used to: extract the cost associated with executing the query on the SRC-DB from the query plan; extract the cost associated with executing the query on the DST-DB from the query plan; compare the cost associated with executing the query on the SRC-DB with the cost associated with executing the query on the DST-DB. The query information includes table schema, table data statistics / indicators, and table catalog.

[0137] An embodiment of the present invention further provides a computer-readable storage medium. The computer-readable storage medium stores a computer program. When the computer program is executed on a processor, the processor executes Figure 8 The method described in steps 801 to 803 in .

[0138] In addition, another embodiment of the present invention discloses a computer-readable medium, wherein the computer-readable medium includes a computer program, and when the computer program is executed by a computer, the computer program enables the computer to perform the above method steps.

[0139] The present invention also discloses a computer-readable medium. The computer-readable medium includes a computer program. When the computer program is executed by a computer, the computer program enables the computer to execute the above-mentioned method for post-migration suboptimal query identification and index optimization.

[0140] The present invention is applied to the field of database migration. The technical effect provided by the present invention is that the efficiency of the migrated SQL (application) in terms of quality, time and reliability is greatly improved. In the early stages of migration, network pre-verification and network deployment, SQL performance is automatically evaluated. Currently, there are no tools that can support such evaluations for open source / commercial databases. All commercial database vendors want to protect their customer base. The present invention realizes post-migration evaluation from any DB vendor to any DB vendor and different DB engines.

[0141] Some non-limiting advantages of the present invention are as follows:

[0142] The present invention provides a systematic and holistic approach for identifying suboptimal queries after migration;

[0143] The present invention provides a cost-effective and systematic approach to identifying index optimization;

[0144] Users / customers can use the same report based on the Index Optimization Report to improve query performance. This report is more visual and provides 1 view;

[0145] The present invention ensures that the user does not need to invest months of time in evaluating and identifying problems.

[0146] Some future implementations of the present invention are described below:

[0147] The present invention can be further enhanced as a supplementary service on the cloud, helping users save costs / efforts in post-migration analysis, identifying suboptimal queries and storage issues;

[0148] The present invention may be further enhanced to automatically evaluate the correctness of data migrated from different tools or enhancements.

[0149] Those skilled in the art will appreciate that any known or new algorithm may be used to implement the present invention. However, it should be noted that the present invention provides a method and apparatus for identifying suboptimal queries and optimizing indexes after migration to cloud storage to achieve the above benefits and technical advances, regardless of the use of any known or new algorithm.

[0150] Those skilled in the art will appreciate that, in conjunction with the examples described in the embodiments disclosed in this specification, the units and algorithm steps may be implemented by electronic hardware or a combination of computer software and electronic hardware. Whether the functions are performed by hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art may use different methods to implement the described functions for each specific application, but these implementations should not be considered to be beyond the scope of the present invention.

[0151] Those skilled in the art can clearly understand that, for the convenience and simplification of description, the specific working processes of the above-mentioned systems, devices and units can refer to the corresponding processes in the above-mentioned method embodiments, and will not be repeated herein.

[0152] In several embodiments provided in the present application, it should be understood that the disclosed device, method or system can be implemented in other ways. For example, the described device embodiments are merely exemplary. For example, unit division is merely a logical function division, and other division methods may be used in actual implementation. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not performed. In addition, the mutual coupling or direct coupling or communication connection shown or described may be implemented through some interfaces. The indirect coupling or communication connection between devices or units may be implemented in electronic, mechanical or other forms.

[0153] When these functions are implemented in the form of software functional units and sold or used as independent products, these functions can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present invention can be implemented in the form of a software product in essence or in other words, the part that contributes to the prior art or the part of the technical solution. The computer software product is stored in a storage medium and includes several instructions to instruct a computer node (which can be a personal computer, a server or a network node) to perform all or part of the steps of the method described in the embodiment of the present invention. The above-mentioned storage medium includes any medium that can store program code, for example, a USB disk, a removable hard disk, a read-only memory (ROM), a random access memory (RAM), a disk or an optical disk, etc.

[0154] Devices in communication with each other need not be in continuous communication with each other unless expressly specified otherwise. In addition, devices in communication with each other may communicate directly or indirectly through one or more intermediaries.

[0155] When a single device or article is described herein, it is obvious that more than one device / article (whether or not they cooperate) can be used to replace a single device / article. Similarly, for the situation of more than one device or article described herein (whether or not they cooperate), it is obvious that a single device / article can be used to replace more than one device or article, or different numbers of devices / articles can be used to replace the devices or programs of the number shown. Alternatively, the function and / or feature of the device can be implemented by one or more other devices that are not clearly described as having such function / feature. Therefore, other embodiments of the present invention do not need to include the device itself.

[0156] Finally, the language used in the specification is selected primarily for readability and instructional purposes and does not necessarily appear to be selected for the purpose of defining or limiting the creative subject matter. Therefore, the scope of the present invention should not be limited to the specific embodiments, but to any claims issued based on the present application. Therefore, the disclosure of the embodiments of the present invention is intended to illustrate but not to limit the scope of the present invention, which is set forth in the appended claims.

[0157] With respect to the use of substantially any plural and / or singular terms herein, those skilled in the art may translate from the plural to the singular and / or from the singular to the plural, as appropriate to the context and / or application. For clarity, various singular / plural permutations may be expressly set forth herein.

[0158] Although implementations for suboptimal query identification and index optimization after migration to cloud storage have been described in language specific to structural features and / or methods, it should be understood that the appended claims are not necessarily limited to the specific features or methods described. Rather, specific features and methods are disclosed as examples of implementations that allow customers to evaluate migration in a short period of time, deploy minimal infrastructure, and provide detailed reporting when migrating from a high-cost license to a free / open source database.

Claims

1. A method for identifying suboptimal queries (S800), characterized in that: The method comprises the following steps: Determining (S801) query execution statistics in a source database (SRC-DB); Determining (S802) query execution statistics in a destination database (DST-DB); The query execution statistics in the SRC-DB and the query execution statistics in the DST-DB are compared (S803).

2. The method according to claim 1, characterized in that Determining (S801) query execution statistics in the SRC-DB comprises the following steps: Executing a query on the SRC-DB; extracting and capturing a query plan from the executed query; The query plan is parsed to obtain query information from the SRC-DB.

3. The method according to claim 1, characterized in that Determining (S802) query execution statistics in the DST-DB comprises the following steps: Execute a query on the DST-DB; extracting and capturing a query plan from the executed query; The query plan is parsed to obtain query information from the DST-DB.

4. The method according to claim 2 or 3, characterized in that: The query information includes table schema, table data statistics / indicators, and table catalog.

5. The method according to claim 1, characterized in that The comparing the query execution statistics in the SRC-DB with the query execution statistics in the DST-DB (S803) comprises the following steps: extracting from the query plan a cost associated with executing a query on the SRC-DB; extracting, from the query plan, a cost associated with executing a query on the DST-DB; The cost associated with executing a query on the SRC-DB is compared to the cost associated with executing a query on the DST-DB.

6. An apparatus (900) for identifying suboptimal queries, characterized in that: The method comprises the following steps: A source query execution unit (901), configured to determine query execution statistics in a source database (SRC-DB); A target query execution unit (902), configured to determine query execution statistics in a target database (DST-DB); A comparison unit (903) is used to compare the query execution statistics in the SRC-DB with the query execution statistics in the DST-DB.

7. The device (900) according to claim 6, characterized in that The source query execution unit (901) is used for: Executing a query on the SRC-DB; extracting and capturing a query plan from the executed query; The query plan is parsed to obtain query information from the SRC-DB.

8. The device (900) according to claim 6, characterized in that The target query execution unit (902) is used for: Execute a query on the DST-DB; extracting and capturing a query plan from the executed query; The query plan is parsed to obtain query information from the DST-DB.

9. The device (900) according to claim 7 or 8, characterized in that The query information includes table schema, table data statistics / indicators, and table catalog.

10. The device (900) according to claim 6, characterized in that The comparison unit (903) is used for: extracting from the query plan a cost associated with executing a query on the SRC-DB; extracting, from the query plan, a cost associated with executing a query on the DST-DB; The cost associated with executing a query on the SRC-DB is compared to the cost associated with executing a query on the DST-DB.

11. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed on a processor, the processor performs the method according to any one of claims 1 to 5.

Images