Squash: A Tool for Analyzing, Tuning and Refactoring Relational Database Applications

  • Andreas M. Boehm
  • Dietmar Seipel
  • Albert Sickmann
  • Matthias Wetzka
Part of the Lecture Notes in Computer Science book series (LNCS, volume 5437)

Abstract

The performance of a large biological application of relational databases highly depends on the quality of the database schema design, the resulting structure of the tables, and the logical relations between them.

We have developed a tool named Squash (Sql Query Analyzer and Schema EnHancer) for visualizing, analyzing and refactoring database applications.Squash parses the Sql definition of the data-base schema and the queries into an Xml representation called Squashml, which is then processed in SwiProlog and the integrated Xmlquery and transformation language FnQuery.

Squash comes with a set of predefined methods for tuning the database application according to the load profile, and with methods for proposing refactorings, such as index creation, partitioning, splitting, or further normalization of the database schema. Sql statements are adapted simultaneously upon modification of the schema. Moreover, the declarative Squash framework can be flexibly extended by user–defined methods.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Agrawal, S., Narasayya, V., Yang, B.: Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design. In: Proc. ACM SIGMOD Intl. Conference on Management of Data, pp. 359–370. ACM Press, New York (2004)Google Scholar
  2. 2.
    Bellatreche, L., Karlapalem, K., Mohania, M.K., Schneider, M.: What Can Partitioning Do for Your Data Warehouses and Data Marts? In: Proc. Intl. Symposium on Database Engineering and Applications (IDEAS 2000), pp. 437–446. IEEE Computer Society Press, Los Alamitos (2000)Google Scholar
  3. 3.
    Boehm, A.M., Sickmann, A.: A Comprehensive Dictionary of Protein Accession Codes for Complete Protein Accession Identifier Alias Resolving. Proteomics 6(15), 4223–4226 (2006)CrossRefGoogle Scholar
  4. 4.
    Brass, S., Goldberg, C.: Proving the Safety of SQL Queries. In: Proc. 5th Intl. Conference on Quality of Software (2005)Google Scholar
  5. 5.
    Chamberlin, D.: XQuery: a Query Language for XML. In: Proc. ACM SIGMOD Intl. Conference on Management of Data 2003, pp. 682–682. ACM Press, New York (2003)CrossRefGoogle Scholar
  6. 6.
    Chaudhuri, S., Narasayya, V.: Autoadmin What–If Index Analysis Utility. In: Proc. Intl. Conference on Management of Data Archives. Proc. ACM SIGMOD Intl. Conference on Management of Data 1988, pp. 367–378. ACM Press, New York (1988)Google Scholar
  7. 7.
    Chaudhuri, S., Weikum, G.: Rethinking Database System Architecture: Towards a Self–Tuning RISC–Style Database System. In: Proc. 26th Intl. Conference on Very Large Data Bases (VLDB), pp. 1–10 (2000)Google Scholar
  8. 8.
    Choenni, S., Blanken, H.M., Chang, T.: Index Selection in Relational Databases. In: Proc. 5th Intl. Conference on Computing and Information (ICCI), pp. 491–496. IEEE, Los Alamitos (1993)Google Scholar
  9. 9.
    Clocksin, W.F., Mellish, C.S.: Programming in prolog, 5th edn. Springer, Heidelberg (2003)MATHGoogle Scholar
  10. 10.
    Dias, K., Ramacher, M., Shaft, U., Venkataramani, V., Wood, G.: Automatic Performance Diagnosis and Tuning in Oracle. In: Proc. 2nd Biennal Conference on Innovative Data Systems Research (CIDR), pp. 84–94 (2005)Google Scholar
  11. 11.
    Elmasri, R., Navathe, S.B.: Fundamentals of Database Systems, 5th edn. Benjamin/Cummings (2006)Google Scholar
  12. 12.
    Eng, J.K., McCormack, A.L., Yates, J.R.: An Approach to Correlate Tandem Mass Spectral Data of Peptides with Amino Acid Sequences in a Protein Database. Journal of the American Society for Mass Spectrometry 5(11), 976–989 (1994)CrossRefGoogle Scholar
  13. 13.
    Fagin, R.: Normal Forms and Relational Database Operators. In: Proc. ACM SIGMOD Intl. Conference on Management of Data (1979)Google Scholar
  14. 14.
    Finkelstein, S., Schkolnick, M., Tiberio, P.: Physical Database Design for Relational Databases. ACM Transactions on Database Systems (TODS) 13(1), 91–128 (1988)CrossRefGoogle Scholar
  15. 15.
    Goldberg, C., Brass, S.: Semantic Errors in SQL Queries: A Quite Complete List. In: Proc. 16. GI–Workshop Grundlagen von Datenbanken, pp. 58–62 (2004)Google Scholar
  16. 16.
    Gruenwald, L., Eich, M.: Selecting a Database Partitioning Technique. Journal of Database Management 4(3), 27–39 (1993)Google Scholar
  17. 17.
    Intl. Organization for Standardization: ISO/IEC 9075–14:2003 Information Technology – Database Languages – SQL – Part 14: xml Related Specifications, SQL/XML (2003)Google Scholar
  18. 18.
    Kwan, E., Lightstone, S., Schiefer, B., Storm, A., Wu, L.: Automatic Database Configuration for DB2 Universal Database: Compressing Years of Performance Expertise into Seconds of Execution. In: 10. Datenbanksysteme in Büro, Technik und Wissenschaft (BTW, Datenbanksysteme für Business, Technologie und Web), Bd. 26. LNI, pp. 620–629 (2003)Google Scholar
  19. 19.
    Perkins, D.N., Pappin, D.J.C., Creasy, D.M., Cottrell, J.S.: Probability–Based Protein Identification by Searching Sequence Databases Using Mass Spectrometry Data. Electrophoresis 20(18), 3551–3567 (1999)CrossRefGoogle Scholar
  20. 20.
    Ramakrishnan, R., Gehrke, J.: Database Management Systems, 3rd edn. McGraw-Hill, New York (2003)MATHGoogle Scholar
  21. 21.
    Rozen, S., Shasha, D.: A Framework for Automating Physical Database Design. In: Proc. 17th Intl. Conference on Very Large Data Bases (VLDB), pp. 401–411. Morgan Kaufmann, San Francisco (1991)Google Scholar
  22. 22.
    Seipel, D.: Processing xml Documents in prolog. In: Proc. 17th Workshop on Logic Programmierung (WLP) (2002)Google Scholar
  23. 23.
    Seipel, D., Baumeister, J., Hopfner, M.: Declarative Querying and Visualizing Knowledge Bases in XML. In: Proc. 15th Intl. Conference on Declarative Programming and Knowledge Management (INAP), pp. 140–151 (INAP)Google Scholar
  24. 24.
    Telford, R., Horman, R., Lightstone, S., Markov, N., O’Connell, S., Lohman, G.: Usability and Design Considerations for an Autonomic Relational Database Management System. IBM Systems Journal 42(4), 568–581 (2003)CrossRefGoogle Scholar
  25. 25.
    Wielemaker, J.: An Overview of the Swiprolog Programming Environment. In: Proc. 13th Intl. Workshop on Logic Programming Environments (WLPE), pp. 1–16 (2003)Google Scholar
  26. 26.
    Wielemaker, J.: swiprolog. Version (2007), http://www.swi-prolog.org/
  27. 27.
    Valentin, G., Zuliani, M., Zilio, D.C., Lohman, G., Skelley, V.: DB2 Advisor: An Optimizer Smart Enough to Recommend Its Own Indexes. In: Proc. 16th Intl. Conference on Data Engineering, pp. 101–110 (2000)Google Scholar
  28. 28.
    Wahler, V., Seipel, D., von Gudenberg, W.J., Fischer, G.: Clone Detection in Source Code by Frequent Itemset Techniques. In: 4th IEEE Intl. Workshop on Source Code Analysis and Manipulation (SCAM), pp. 128–135 (2004)Google Scholar
  29. 29.
    Weikum, G., Hasse, C., Mönkeberg, A., Zabback, P.: The Comfort Automatic Tuning Project. Information Systems 19(5), 381–432 (1994)CrossRefGoogle Scholar
  30. 30.
    Weikum, G., Mönkeberg, A., Hasse, C., Zabback, P.: Self–Tuning Database Technology and Information Services: from Wishful Thinking to Viable Engineering. In: Proc. 28th Intl. Conference on Very Large Data Bases (VLDB), pp. 20–31 (2002)Google Scholar
  31. 31.
    Zahedi, R.P., Sickmann, A., Boehm, A.M., Winkler, C., Zufall, N., Schönfisch, B., Guiard, B., Pfanner, N., Meisinger, C.: Proteomic Analysis of the Yeast Mitochondrial Outer Membrane Reveals Accumulation of a Subclass of Preproteins. Molecular Biology of the Cell 17(3), 1436–1450 (2006)CrossRefGoogle Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2009

Authors and Affiliations

  • Andreas M. Boehm
    • 1
  • Dietmar Seipel
    • 2
  • Albert Sickmann
    • 1
  • Matthias Wetzka
    • 2
  1. 1.Rudolf–Virchow–Center for Experimental BiomedicineUniversity of WürzburgWürzburgGermany
  2. 2.Department of Computer ScienceUniversity of WürzburgWürzburgGermany

Personalised recommendations