1 / 9

Leveraging Stored Procedures for Enhanced Database Application Performance

This guide discusses the advantages of using stored procedures in database applications, specifically focusing on reduced code duplication, decreased data transfer between client and server, and enhanced performance. By defining stored procedures and functions in the database, applications can leverage these reusable components to streamline operations. The document outlines the general syntax for creating stored procedures, invokes examples, and highlights variations across popular database languages. Overall, it emphasizes the efficiency gained compared to applications that do not utilize stored procedures.

miles
Télécharger la présentation

Leveraging Stored Procedures for Enhanced Database Application Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Programming Using databases from applications Using Stored Procedures UCN T&B / PBA DB

  2. Using Databases in Applications • Embedded SQL • EXEC SQL statement • Requires language specific pre-processor • Library of database functions • ADO.NET • ODBC, JDBC • Connect, execute SQL statements, disconnect • Both types can use stored procedures UCN T&B / PBA DB

  3. Stored Procedures? Procedures or functions stored in the database Executes on the database server UCN T&B / PBA DB

  4. Why? • A stored procedure can be used by several applications • Reduces code duplication and maintenance • Reduce data transfer between client and server • Intermediate results that the client does not need do not have to be marshaled or transferred between server and client • Multiple rounds of query parsing can be avoided • Shift workload from client to server • More complex modelling than what views offer • Result: Considerable performance increase as compared to an application that does not use stored procedures/functions UCN T&B / PBA DB

  5. Stored Procedure Syntax General syntax: CREATE PROCEDURE <procedure name>(<parameters>) <local declarations> <procedure body>; CREATE FUNCTION <procedure name>(<parameters>) RETURNS <return type> <local declarations> <procedure body>; UCN T&B / PBA DB

  6. Using a Stored Procedure Stored procedures can be invoked from the usual SQL interfaces: CALL <procedure or function name> (<argument list>); UCN T&B / PBA DB

  7. SQL/PSM Stored Procedure Language Constructs IF <condition> THEN <statement list>ELSEIF <condition> THEN <statement list>ELSE <statement list> END IF; WHILE <condition> DO<statement list> END WHILE; REPEAT<statement list> UNTIL <condition> END REPEAT; FOR <loop name> AS <cursor name> CURSOR FOR <query> DO<statement list> END FOR; UCN T&B / PBA DB

  8. Stored Procedure Example UCN T&B / PBA DB

  9. Languages for Stored Procedures • Standard SQL • SQL/PSM (SQL/Persistent Stored Modules) • Oracle • PL/SQL • MS SQL Server • Transact-SQL • PostgreSQL • PL/pgSQL • PL/Tcl • PL/Perl • PL/Python • MySQL UCN T&B / PBA DB

More Related