Run-time performance of a database
Methods and systems are disclosed improving run-time performance of a database. A non-production database is accessed, at a computer system, during a design phase 4 of the non-production database, wherein the non-production database will be converted to a production database after the design phase. Data statistics are generated, at the computer system, based on performance metrics of how the non-production database would perform as the production database. The data statistics are displayed, at the computer system, to a developer of the non-production database during the design phase. At least one suggestion is offered to the developer, at the computer system, to implement into the SQL of the non-production database to improve a performance of the non-production database.
1. A method for improving run-time performance of a database, said method comprising:
accessing a non-production database, at a computer system, during a design phase of said non-production database, wherein said non-production database will be converted to a production database after said design phase, said non-production database comprising code;
testing said code of said non-production database against an existing production database, said testing said code comprising:
testing a query written for said non-production database against said production database, wherein said query is tested against performance metrics of said production database;
generating data statistics, at said computer system, based on said testing said code, said data statistics based on said performance metrics of how said non-production database would perform as said production database;
displaying said data statistics, at said computer system, to a developer of said non-production database during said design phase; and
offering at least one suggestion to said developer, at said computer system, to implement into a code of said non-production database to improve a performance of said non-production database.
2. The method as recited in claim 1 wherein said non-production database comprises structured query language (SQL).
3. The method as recited in claim 1 wherein said generating said data statistics points said non-production database to an existing production database with actual data to determine said performance metrics.
4. The method as recited in claim 1 wherein said data statistics and said performance metrics are based on a cardinality of data.
5. The method as recited in claim 1 wherein said data statistics and said performance metrics are based on a data skew of data.
6. The method as recited in claim 1 wherein said data statistics and said performance metrics are based on a size of a data set.
7. The method as recited in claim 1 wherein said performance metrics test a best case scenario and a worst case scenario.
8. The method as recited in claim 1 wherein said displaying said data statistics and said offering said at least one suggestion occur in a pop-up window in an interface used for said design phase.
9. The method as recited in claim 1 wherein said offering said at least one suggestion suggests a query that employs a combination of a data for index searching.
10. The method as recited in claim 1 wherein said offering said at least one suggestion suggests a new covering index for a query.
11. A non-transitory computer readable storage medium having instructions embodied therein that when executed cause a computer system to perform a method for improving run-time performance of a database, said method comprising:
accessing a non-production database, at a computer system, during a design phase of said non-production database, said non-production database comprising code;
testing said code of said non-production database against an existing production database with actual data, said testing said code comprising:
testing a query written for said non-production database against said production database, wherein said query is tested against performance metrics of said production database;
generating data statistics, at said computer system, based on said testing said code, said data statistics based on said performance metrics of how said non-production database would perform as a production database by pointing said non-production database to said existing production database with said actual data to determine said performance metrics;
displaying said data statistics, at said computer system, to a developer of said non-production database during said design phase; and
offering at least one suggestion to said developer, at said computer system, for a query that employs a combination of a data for index searching to improve a performance of said non-production database.
12. The non-transitory computer readable storage medium as recited in claim 11 wherein said non-production database comprises structured query language (SQL).
13. The non-transitory computer readable storage medium as recited in claim 11 wherein said data statistics and said performance metrics are based on a cardinality of data.
14. The non-transitory computer readable storage medium as recited in claim 11 wherein said data statistics and said performance metrics are based on a data skew of data.
15. The non-transitory computer readable storage medium as recited in claim 11 wherein said data statistics and said performance metrics are based on a size of data.
16. The non-transitory computer readable storage medium as recited in claim 11 wherein said offering said at least one suggestion further suggests a new covering index for a query.
17. A method for improving run-time performance of a database, said method comprising:
accessing a non-production database, at a computer system, during a design phase of said non-production database, said non-production database comprising code;
testing said code of said non-production database against an existing production database with actual data, said testing said code comprising:
testing a query written for said non-production database against said production database, wherein said query is tested against performance metrics of said production database;
generating data statistics, at said computer system, based on said testing said code, said data statistics based on said performance metrics of how said non-production database would perform as a production database by pointing said non-production database to said existing production database with said actual data to determine said performance metrics;
displaying said data statistics, at said computer system, to a developer of said non-production database during said design phase; and
offering a suggestion to said developer, at said computer system, to add a new covering index associated with said non-production database.
18. The method as recited in claim 17 wherein said non-production database comprises structured query language (SQL).
19. The method as recited in claim 17 wherein said data statistics and said performance metrics are based on a cardinality of data.
20. The method as recited in claim 17 wherein said data statistics and said performance metrics are based on a data skew of data.
21. The method as recited in claim 17 wherein said data statistics and said performance metrics are based on a size of a data set.
22. The method as recited in claim 17 wherein said offering said suggestion further suggests a query that employs a combination of a data for index searching.