Improving performance of database queries

• US 7,895,191 B2
• Filed: 04/09/2003
• Issued: 02/22/2011
• Est. Priority Date: 04/09/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Colossi, Nathan Gevaerd, Dere, Jason, Sit, Steven, DeKimpe, Daniel Martin
• Primary Examiner(s): Padmanabhan; Kavita
• Application Number:
  US10/410,793
• Publication Number:
  US 20040215626A1
• Time in Patent Office: 2,876 Days
• Field of Search: None
• US Class Current: 707/717
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A system, method, and program for improving the performance for SQL queries. Multidimensional metadata associated with a cube model metadata object is obtained. One or more summary tables to be built are automatically identified based on the obtained multidimensional metadata. One or more indexes to create are automatically identified based on the obtained multidimensional metadata.

Claims

1. A method for improving performance for SQL queries, comprising:

receiving a request for advice in a computer having a processor regarding one or more summary tables to be built for a cube model metadata object, wherein the request specifies one or more types of queries intended to be issued for the cube model metadata object, and wherein the one or more types of queries include extract, drilldown, report, and drillthrough;

obtaining multidimensional metadata associated with the cube model metadata object including measures;

forming measure sets with the measures by creating one measure set with symmetric measures and, for each group of asymmetric measures with a same dimensionality, creating an additional measure set;

for each of the measure sets,

identifying one or more summary tables recommended to be built based on the obtained multidimensional metadata and based on the one or more types of queries intended to be issued, wherein identifying the one or more summary tables further comprises;

rating a cost of creating a slice within one of the summary tables based on an estimated number of rows in the slice, an estimated size of the slice, a maximum row width of the slice, a number of columns in the slice, and an amount of time required to build the slice; and

identifying one or more indexes recommended to be created for the one or more summary tables based on the obtained multidimensional metadata; and

generating one or more statements to be executed to create the recommended one or more summary tables and the recommended one or more indexes.

2. The method of claim 1, wherein identifying the one or more summary tables is based on at least one of database statistics, sampling, diskspace limitations, time limitations, and multidimensional regions to be optimized.

3. The method of claim 1, wherein identifying the one or more indexes is based on at least one of database statistics, sampling, diskspace limitations, time limitations, and the generated one or more summary tables.

4. The method of claim 1, wherein at least one of the SQL queries is submitted by an on-line analytical processing system.

5. The method of claim 1, further comprising:

consolidating multiple hierarchies within each dimension; and

replacing one or more of the consolidated multiple hierarchies with one or more virtual hierarchies that contain a set of attributes from one or more of the consolidated multiple hierarchies.

6. The method of claim 1, further comprising:

defining a virtual hierarchy for each network hierarchy within the cube model metadata object, wherein each virtual hierarchy includes attributes from an associated network hierarchy in a selected order.

7. The method of claim 1, wherein identifying the one or more summary tables further comprises:

identifying the one or more summary tables without derivative measures.

8. The method of claim 1, wherein identifying the one or more summary tables further comprises:

rating effectiveness of a slice based on what coverage is provided by the slice and how much performance would improve with the use of the slice.

9. The method of claim 1, further comprising:

rating a subset of two or more slices including a combination of zero or more non-crossing slices and two or more crossing slices from a set of candidate slices.

10. The method of claim 1, wherein the request for advice identifies the cube model metadata object and indicates that drilldown queries are to be issued and further comprising:

determining cost effective slices for the drilldown queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preference to higher level slices, wherein the drilldown queries are generated in response to user interaction.

11. The method of claim 1, wherein the request for advice identifies the cube model metadata object and indicates that report queries are to be issued and further comprising:

determining cost effective slices for the report queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preferences to lower level slices, wherein the report queries are generated in response to user interaction.

12. The method of claim 1, wherein the request for advice identifies the cube model metadata object and indicates that drillthrough queries are to be issued and further comprising:

determining cost effective slices for the drillthrough queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preferences to lower level slices, wherein the drillthrough queries are generated when a user navigates from an external data store to a relational database.

13. The method of claim 1, wherein the request for advice identifies the cube model metadata object and indicates that extract queries are to be issued and further comprising:

receiving one or more regions to optimize for the extract queries, wherein the extract queries are generated against a relational database to retrieve data for populating an external data store;

for each region, identifying a bottom slice; and

consolidating bottom slices of the one or more regions based on cost of the bottom slice identified for each region.

14. The method of claim 1, wherein the cube model metadata object includes one or more sets of asymmetric measures, wherein each set of asymmetric measures includes one or more slices, and further comprising:

for each set of compatible asymmetric measures, selecting one or more slices that match an inner-most select statement of a multi-step select statement.

15. The method of claim 1, further comprising:

building a clustering index for a summary table by using columns of one or more hierarchies and factoring in one or more of estimated index cardinality, average number of rows per index entry, estimated data distribution of index entries, the inclusion of time attributes, and the one or more types of queries being optimized.

16. The method of claim 1, further comprising:

building non-clustering indexes for a summary table by using columns of one or more hierarchies and selecting indexes based on one or more of estimated index cardinality, estimated data distribution of index entries, the inclusion of time attributes, and the one or more types of queries being optimized until the combined sizes of the indexes for the summary table exceed a user specified size limit or the number of indexes for the summary table reaches a defined limit.

17. The method of claim 1, further comprising:

when at least one measure in a set of measures uses a non-distributive function, determining cost effective slices by evaluating the set of measures to determine whether a set of slices is needed in the summary table to optimize a region of the cube model metadata object.

18. The method of claim 1, wherein identifying the one or more summary tables further comprises:

rating effectiveness of a region based on what coverage is provided by the region and how much performance would improve with the use of the region.

19. The method of claim 1, wherein identifying the one or more summary tables further comprises:

rating a cost of creating a region within one of the summary tables based on an estimated number of rows in the region, an estimated size of the region, a maximum row width of the region, a number of columns in the region, and an amount of time required to build the slice.

20. An article of manufacture comprising a computer readable storage medium storing a program for improving performance for SQL queries, wherein the program when executed by a processor on a computer causes operations to be performed, the operations comprising:

receiving a request for advice regarding one or more summary tables to be built for a cube model metadata object, wherein the request specifies one or more types of queries intended to be issued for the cube model metadata object, and wherein the one or more types of queries include extract, drilldown, report, and drillthrough;

obtaining multidimensional metadata associated with the cube model metadata object including measures;

forming measure sets with the measures by creating one measure set with symmetric measures and, for each group of asymmetric measures with a same dimensionality, creating an additional measure set;

for each of the measure sets,

identifying one or more summary tables recommended to be built based on the obtained multidimensional metadata and based on the one or more types of queries intended to be issued, wherein the operations for identifying the one or more summary tables further comprise;

rating a cost of creating a slice within one of the summary tables based on an estimated number of rows in the slice, an estimated size of the slice, a maximum row width of the slice, a number of columns in the slice, and an amount of time required to build the slice; and

identifying one or more indexes recommended to be created for the one or more summary tables based on the obtained multidimensional metadata; and

generating one or more statements to be executed to create the recommended one or more summary tables and the recommended one or more indexes.

21. The article of manufacture of claim 20, wherein identifying the one or more summary tables is based on at least one of database statistics, sampling, diskspace limitations, time limitations, and multidimensional regions to be optimized.

22. The article of manufacture of claim 20, wherein identifying the one or more indexes is based on at least one of database statistics, sampling, diskspace limitations, time limitations, and the generated one or more summary tables.

23. The article of manufacture of claim 20, wherein at least one of the SQL queries is submitted by an on-line analytical processing system.

24. The article of manufacture of claim 20, the operations further comprising:

consolidating multiple hierarchies within each dimension; and

replacing one or more of the consolidated multiple hierarchies with one or more virtual hierarchies that contain a set of attributes from one or more of the consolidated multiple hierarchies.

25. The article of manufacture of claim 20, wherein the operations further comprise:

defining a virtual hierarchy for each network hierarchy within the cube model metadata object, wherein each virtual hierarchy includes attributes from an associated network hierarchy in a selected order.

26. The article of manufacture of claim 20, wherein the operations for identifying the one or more summary tables further comprise:

identifying the one or more summary tables without derivative measures.

27. The article of manufacture of claim 20, wherein the operations for identifying the one or more summary tables further comprise:

rating effectiveness of a slice based on what coverage is provided by the slice and how much performance would improve with the use of the slice.

28. The article of manufacture of claim 20, the operations further comprising:

rating subset of two or more slices including a combination of zero or more non-crossing slices and two or more crossing slices from a set of candidate slices.

29. The article of manufacture of claim 20, wherein the request for advice identifies the cube model metadata object and indicates that drilldown queries are to be issued and wherein the operations further comprise:

determining cost effective slices for the drilldown queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preference to higher level slices, wherein the drilldown queries are generated in response to user interaction.

30. The article of manufacture of claim 20, wherein the request for advice identifies the cube model metadata object and indicates that report queries are to be issued and wherein the operations further comprise:

determining cost effective slices for the report queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preferences to lower level slices, wherein the report queries are generated in response to user interaction.

31. The article of manufacture of claim 20, wherein the request for advice identifies the cube model metadata object and indicates that drillthrough queries are to be issued and wherein the operations further comprise:

determining cost effective slices for the drillthrough queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preferences to lower level slices, wherein the drillthrough queries are generated when a user navigates from an external data store to a relational database.

32. The article of manufacture of claim 20, wherein the request for advice identifies the cube model metadata object and indicates that extract queries are to be issued and wherein the operations further comprise:

receiving one or more regions to optimize for the extract queries, wherein the extract queries are generated against a relational database to retrieve data for populating an external data store;

for each region, identifying a bottom slice; and

consolidating bottom slices of the one or more regions based on cost of the bottom slice identified for each region.

33. The article of manufacture of claim 20, wherein the cube model metadata object includes one or more sets of asymmetric measures, wherein each set of asymmetric measures includes one or more slices, the operations further comprising:

for each set of compatible asymmetric measures, selecting one or more slices that match an inner-most select statement of a multi-step select statement.

34. The article of manufacture of claim 20, the operations further comprising:

building a clustering index for a summary table by using columns of one or more hierarchies and factoring in one or more of estimated index cardinality, average number of rows per index entry, estimated data distribution of index entries, the inclusion of time attributes, and the one or more types of queries being optimized.

35. The article of manufacture of claim 20, the operations further comprising:

building non-clustering indexes for a summary table by using columns of one or more hierarchies and selecting indexes based on one or more of estimated index cardinality, estimated data distribution of index entries, the inclusion of time attributes, and the one or more types of queries being optimized until the combined sizes of the indexes for the summary table exceed a user specified size limit or the number of indexes for the summary table reaches a defined limit.

36. The article of manufacture of claim 20, wherein the operations further comprise:

when at least one measure in a set of measures uses a non-distributive function, determining cost effective slices by evaluating the set of measures to determine whether a set of slices is needed in the summary table to optimize a region of the cube model metadata object.

37. The article of manufacture of claim 20, wherein the operations for identifying the one or more summary tables further comprise:

rating effectiveness of a region based on what coverage is provided by the region and how much performance would improve with the use of the region.

38. The article of manufacture of claim 20, wherein the operations for identifying the one or more summary tables further comprise:

rating a cost of creating a region within one of the summary tables based on an estimated number of rows in the region, an estimated size of the region, a maximum row width of the region, a number of columns in the region, and an amount of time required to build the slice.

39. A system for improving performance for SQL queries, comprising:

a computer system having hardware logic for;

receiving a request for advice regarding one or more summary tables to be built for a cube model metadata object, wherein the request specifies one or more types of queries intended to be issued for the cube model metadata object, and wherein the one or more types of queries include extract, drilldown, report, and drillthrough;

obtaining multidimensional metadata associated with the cube model metadata object including measures;

forming measure sets with the measures by creating one measure set with symmetric measures and, for each group of asymmetric measures with a same dimensionality, creating an additional measure set;

for each of the measure sets,

identifying one or more summary tables recommended to be built based on the obtained multidimensional metadata and based on the one or more types of queries intended to be issued, wherein for identifying the one or more summary tables, the hardware logic implements code for;

rating a cost of creating a slice within one of the summary tables based on an estimated number of rows in the slice, an estimated size of the slice, a maximum row width of the slice, a number of columns in the slice, and an amount of time required to build the slice; and

identifying one or more indexes recommended to be created for the one or more summary tables based on the obtained multidimensional metadata; and

generating one or more statements to be executed to create the recommended one or more summary tables and the recommended one or more indexes.

40. The system of claim 39, wherein identifying the one or more summary tables is based on at least one of database statistics, sampling, diskspace limitations, time limitations, and multidimensional regions to be optimized.

41. The system of claim 39, wherein identifying the one or more indexes is based on at least one of database statistics, sampling, diskspace limitations, time limitations, and the generated one or more summary tables.

42. The system of claim 39, wherein at least one of the SQL queries is submitted by an on-line analytical processing system.

43. The system of claim 39, wherein the hardware logic implements code for:

consolidating multiple hierarchies within each dimension; and

replacing one or more of the consolidated multiple hierarchies with one or more virtual hierarchies that contain a set of attributes from one or more of the consolidated multiple hierarchies.

44. The system of claim 39, wherein the hardware logic implements code for:

defining a virtual hierarchy for each network hierarchy within the cube model metadata object, wherein each virtual hierarchy includes attributes from an associated network hierarchy in a selected order.

45. The system of claim 39, wherein for identifying the one or more summary tables, the hardware logic implements code for:

identifying the one or more summary tables without derivative measures.

46. The system of claim 39, wherein for identifying the one or more summary tables, the hardware logic implements code for:

rating effectiveness of a slice based on what coverage is provided by the slice and how much performance would improve with the use of the slice.

47. The system of claim 39, wherein the hardware logic implements code for:

rating a subset of two or more slices including a combination of zero or more non-crossing slices and two or more crossing slices from a set of candidate slices.

48. The system of claim 39, wherein the request for advice identifies the cube model metadata object and indicates that drilldown queries are to be issued and wherein the hardware logic implements code for:

determining cost effective slices for the drilldown queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preference to higher level slices, wherein the drilldown queries are generated in response to user interaction.

49. The system of claim 39, wherein the request for advice identifies the cube model metadata object and indicates that report queries are to be issued and wherein the hardware logic implements code for:

determining cost effective slices for the report queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preferences to lower level slices, wherein the report queries are generated in response to user interaction.

50. The system of claim 39, wherein the request for advice identifies the cube model metadata object and indicates that drillthrough queries are to be issued and wherein the hardware logic implements code for:

determining cost effective slices for the drillthrough queries by calculating cost effectiveness of a current slice starting from a highest level of each hierarchy in one or more dimensions and rating one or more combinations of slices one level below a current level from each of the hierarchies until a predefined limitation has been reached, wherein rating gives preferences to lower level slices, wherein the drillthrough queries are generated when a user navigates from an external data store to a relational database.

51. The system of claim 39, wherein the request for advice identifies the cube model metadata object and indicates that extract queries are to be issued and wherein the hardware logic implements code for:

receiving one or more regions to optimize for the extract queries, wherein the extract queries are generated against a relational database to retrieve data for populating an external data store;

for each region, identifying a bottom slice; and

consolidating bottom slices of the one or more regions based on cost of the bottom slice identified for each region.

52. The system of claim 39, wherein the cube model metadata object includes one or more sets of asymmetric measures, wherein each set of asymmetric measures includes one or more slices, and wherein the hardware logic implements code for:

for each set of compatible asymmetric measures, selecting one or more slices that match an inner-most select statement of a multi-step select statement.

53. The system of claim 39, wherein the hardware logic implements code for:

building a clustering index for a summary table by using columns of one or more hierarchies and factoring in one or more of estimated index cardinality, average number of rows per index entry, estimated data distribution of index entries, the inclusion of time attributes, and the one or more types of queries being optimized.

54. The system of claim 39, wherein the hardware logic implements code for:

building non-clustering indexes for a summary table by using columns of one or more hierarchies and selecting indexes based on one or more of estimated index cardinality, estimated data distribution of index entries, the inclusion of time attributes, and the one or more types of queries being optimized until the combined sizes of the indexes for the summary table exceed a user specified size limit or the number of indexes for the summary table reaches a defined limit.

55. The system of claim 39, wherein the hardware logic implements code for:

when at least one measure in a set of measures uses a non-distributive function, determining cost effective slices by evaluating the set of measures to determine whether a set of slices is needed in the summary table to optimize a region of the cube model metadata object.

56. The system of claim 39, wherein for identifying the one or more summary tables, the hardware logic implements code for:

rating effectiveness of a region based on what coverage is provided by the region and how much performance would improve with the use of the region.

57. The system of claim 39, wherein for identifying the one or more summary tables, the hardware logic implements code for:

rating a cost of creating a region within one of the summary tables based on an estimated number of rows in the region, an estimated size of the region, a maximum row width of the region, a number of columns in the region, and an amount of time required to build the slice.