Article

Free access

Processing queries aganist database procedures: a performance analysis

Author:

Eric N. HansonAuthors Info & Claims

SIGMOD '88: Proceedings of the 1988 ACM SIGMOD international conference on Management of data

Pages 295 - 302

https://doi.org/10.1145/50202.50238

Published: 01 June 1988 Publication History

Abstract

A database procedure is a collection of queries stored in the database. Several methods are possible for processing queries that retrieve the value returned by a database procedure. The conventional algorithm is to execute the queries in a procedure whenever it is accessed. A second strategy requires caching the previous value returned by the database procedure. If the cached value is valid at the time of a query, the value is returned immediately. If the cached value has been invalidated by an update, the value is recomputed, stored back into the cache, and then returned. A third strategy uses a differential view maintenance algorithm to maintain an up-to-date copy of the value returned by the procedure. This paper compares the performance of these three alternatives. The results show that which algorithm is preferred depends heavily on the database environment, particularly, the frequency of updates and the size of objects retrieved by database procedures.

References

[1]

BIakele), J A, Larson, P and Tompa, F W Effic,ently Updating Matenahzed Views In Proceedings of the 1986 ACM-SIGMOD Conference on Manaeement of Data, Washington, DC, May 1986

Digital Library

[2]

uate, C J Referential Integrity In Proceedings of the 7th VLDB Conference, Cannes France, Set,ember 1981

[3]

rorgy, C L OPS5 User's Manual CMU-CS-81-135 Carnegie-Mellon Umverslty, Pittsburgh, PA 15213, July 1981

[4]

Forgy, C L Rete A Fast Algorithm for the Many Pattern/Many Object Pattern Match Problem Arhfi.lal lntelhgenee 19 (1982), 17-37, North Holland

[5]

vorgy, C L OPS83 Report CMU-CS-84-133 Carnegie- Mellon Umverstty, Pittsburgh, PA 15213, May 1984

[6]

~evarter, W B The Nature and Evaluation of Commercml F.xpert System Building Tools Computer (May 1987)

Digital Library

[7]

~ray, j N Notes on Database Operating Systems IBM Research Report RJ2254 IBM Research Laboratory, San Jose, CA, August 1978

[8]

Hanson, E N A Performance Analysm of View Mater, ahzatmn Strategaes In Proceedings of the 1987 A CM-SIGMOD Conference on Management of Data, San Francisco, CA May 1987

Digital Library

[9]

Hanson, E N Efftc,ent Support for Rules and Derived Obsects,n Rclat, onal Database Systems PhD Thesm, Umverslty of Callforma, Dept of F_~CS, Berkeley CA, 1987

Digital Library

[10]

Hanson, E N Processing Queries Against Database Procedures A Performance Analysis Umverslty of California Memo No UCB/ERL M87/68, Berkeley, CA 94720, 1 Sept 1987

[11]

Roussopoulos, N and Kang, H Principles and Techniques in the Design of ADMS~ Computer, December 1986

Digital Library

[12]

elhs," T Opt, m,zat, o, of Eztended Relat,onal Database Systems PhD Thesm, Umverslty of California, Dept of EECS, Berkeley CA, 198{~

[13]

Selhs, T Efficiently Supporting Procedures in Relational Database Systems In Proceed,ngs of the 1987 A CM-SIGMOD Conference on Management of Data, San Francisco CA, May 1987

Digital Library

[14]

Shoup, A personal communication Inference Corporatmn, San Francisco CA, 1987

[15]

Smith, J and Smith, D Database Abstractmns Aggregation and Generahzatton A CM Transact, ons on Database Systems 2, 2 (June 1977), 105-133

Digital Library

[16]

Stonebraker, M, Anderson, E, Hanson, E and Rubenstem, B QUEL as a Data Type In Proceed, ngs of the 198~ A CM- SIGMOD Conference on Management of Data, Boston, Mass, June 1984

Digital Library

[17]

Stonebraker, M, Anton, A and Hanson, E Extending a Database System with Procedures A CM Transactwns on Database Systems, 2, 3, September 1987, ~50-376

Digital Library

[18]

Stonebraker, M and Rowe, L The Design of POSTGRES In Proceedings of the 1986 A CM-SIGMOD Conference on Management of Data, Washington, DC, May 1986

Digital Library

Cited By

Carnduff TGray W(2005)Function materialization through object versioning in object-oriented databasesAdvances in Databases10.1007/3-540-56921-9_7(111-128)Online publication date: 30-May-2005
https://doi.org/10.1007/3-540-56921-9_7
Kemper AKilger CMoerkotte G(1994)Function Materialization in Object BasesIEEE Transactions on Knowledge and Data Engineering10.1109/69.2981756:4(587-608)Online publication date: 1-Aug-1994
https://dl.acm.org/doi/10.1109/69.298175
Kemper AKilger CMoerkotte G(1991)Function materialization in object basesACM SIGMOD Record10.1145/119995.11583320:2(258-267)Online publication date: 1-Apr-1991
https://dl.acm.org/doi/10.1145/119995.115833
Show More Cited By

Index Terms

Processing queries aganist database procedures: a performance analysis

Recommendations

Processing queries aganist database procedures: a performance analysis

A database procedure is a collection of queries stored in the database. Several methods are possible for processing queries that retrieve the value returned by a database procedure. The conventional algorithm is to execute the queries in a procedure ...
Performance analysis of "Groupby-After-Join" query processing in parallel database systems

Queries containing aggregate functions often combine multiple tables through join operations. This query is subsequently called "Groupby-Join". There is a special category of this query whereby the group-by operation can only be performed after the join ...
Efficient database support for olap queries (on-line analytical processing)

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '88: Proceedings of the 1988 ACM SIGMOD international conference on Management of data

June 1988

443 pages

ISBN:0897912683

DOI:10.1145/50202

ACM SIGMOD Record Volume 17, Issue 3
June 1988
431 pages
ISSN:0163-5808
DOI:10.1145/971701
Issue’s Table of Contents

Copyright © 1988 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 01 June 1988

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Qualifiers

Article

Conference

SIGMOD88

Sponsor:

SIGMOD

SIGMOD88: International Conference On Management of Data

June 1 - 3, 1988

Illinois, Chicago, USA

Acceptance Rates

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

11
Total Citations
View Citations
1,490
Total Downloads

Downloads (Last 12 months)1,054
Downloads (Last 6 weeks)81

Reflects downloads up to 03 Oct 2024

Other Metrics

View Author Metrics

Citations

Cited By

Carnduff TGray W(2005)Function materialization through object versioning in object-oriented databasesAdvances in Databases10.1007/3-540-56921-9_7(111-128)Online publication date: 30-May-2005
https://doi.org/10.1007/3-540-56921-9_7
Kemper AKilger CMoerkotte G(1994)Function Materialization in Object BasesIEEE Transactions on Knowledge and Data Engineering10.1109/69.2981756:4(587-608)Online publication date: 1-Aug-1994
https://dl.acm.org/doi/10.1109/69.298175
Kemper AKilger CMoerkotte G(1991)Function materialization in object basesACM SIGMOD Record10.1145/119995.11583320:2(258-267)Online publication date: 1-Apr-1991
https://dl.acm.org/doi/10.1145/119995.115833
Kemper AKilger CMoerkotte G(1991)Function materialization in object basesProceedings of the 1991 ACM SIGMOD international conference on Management of data10.1145/115790.115833(258-267)Online publication date: 1-Apr-1991
https://dl.acm.org/doi/10.1145/115790.115833
Blakeley JMartin N(1990)Join index, materialized view, and hybrid-hash join: a performance analysis[1990] Proceedings. Sixth International Conference on Data Engineering10.1109/ICDE.1990.113476(256-263)Online publication date: 1990
https://doi.org/10.1109/ICDE.1990.113476
Shekita ECarey M(1989)Performance enhancement through replication in an object-oriented DBMSProceedings of the 1989 ACM SIGMOD international conference on Management of data10.1145/67544.66957(325-336)Online publication date: 1-Jun-1989
https://dl.acm.org/doi/10.1145/67544.66957
Shekita ECarey M(1989)Performance enhancement through replication in an object-oriented DBMSACM SIGMOD Record10.1145/66926.6695718:2(325-336)Online publication date: 1-Jun-1989
https://dl.acm.org/doi/10.1145/66926.66957
Jhingran A(1988)A Performance Study of Query Optimization Algorithms on a Database System Supporting ProceduresProceedings of the 14th International Conference on Very Large Data Bases10.5555/645915.671805(88-99)Online publication date: 29-Aug-1988
https://dl.acm.org/doi/10.5555/645915.671805
Kemper AKilger CMoerkotte G(1994)Function Materialization in Object BasesIEEE Transactions on Knowledge and Data Engineering10.1109/69.2981756:4(587-608)Online publication date: 1-Aug-1994
https://dl.acm.org/doi/10.1109/69.298175
Beeri CKornatzky Y(1993)Algebraic optimization of object-oriented query languagesTheoretical Computer Science10.1016/0304-3975(93)90220-N116:1(59-94)Online publication date: 2-Aug-1993
https://dl.acm.org/doi/10.1016/0304-3975%2893%2990220-N
Show More Cited By

View Options

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

Media

Figures

Other

Tables

View Table of Contents