Notes on Stored Procedures CSSE333 Intro to Database Systems Winter 2005 2006 Outline Application Design Tiered Architectures O R Mapping The CRUD Pattern Why use stored procedures Advice Lab Tiered Architectures The standard three tiered architecture consists of the presentation application and data access tiers The problem is many applications require a high coupling between the bottom two tiers Changing database architectures often dramatically affects the system at the application tier Can be due to poor design but also strongly influenced by changing database connection technologies N Tiers and so on There are many designs variations based on the 3 tier architecture Many introduce a data services layer to abstract the database connection technology From www novicksoftware com Web Services for Data Access Web services are Web based enterprise applications that use open XML based standards and transport protocols to exchange data with calling clients Sun com SOAP Simple Object Access Protocol is a simple XML based protocol to let applications exchange information over HTTP W3C Useful for distributed application designs Can be used to reduce coupling to database connection technologies Especially common in modern websites Persistence Framework The application layer should use a persistence framework or layer to solve the coupling problem A good persistence framework will hide the fact that objects are even being stored in a relational database O R Mapping Most modern business application development projects use object technology such as Java or C to build the application software and relational databases to store the data There is an impedance mismatch between object and relational design methods Object relational mapping also ORM is used to map object oriented programming objects to relational databases O R Mapping Additional Concepts Shadow information is any data that objects need to maintain above and beyond their normal domain data to persist themselves This typically includes primary key information particularly when the primary key is a surrogate key that has no business meaning concurrency control markings such as timestamps or incremental counters and versioning numbers A common practice is for each class to implement an isPersistent boolean flag that is set to true when the data is read in from the database and set to false when the object is newly created Lazy Reads An important performance consideration is whether the attribute should be automatically read in when the object is retrieved When an attribute is very large for example the picture of a person could be 100k whereas the rest of the attributes are less than 1k and rarely accessed you may want to consider taking a lazy read approach The basic idea is that instead of automatically bringing the attribute across the network when the object is read you instead retrieve it only when the attribute is actually needed This can be accomplished by a getter method an operation whose purpose is to provide the value of a single attribute that checks to see if the attribute has been initialized and if not retrieves it from the database at that point From http www ambysoft com essays mappingObjects html Another decent reference http www chimu com publications objectRelational The CRUD Pattern Organizes the persistence operations of an application into Create Retrieve Update and Delete operations that are implemented by a persistence layer Patterns in Java Volume 3 The CRUD pattern is usually implemented on the database Stored procedures are the recommended method for SQL Server It is suggested that they be created in an automated manner They will have to deal with O R mapping but are not expected to contain application logic Why use stored procedures The reasons for using Stored Procedures to implement the Data Storage Layer instead of allowing ad hoc SQL statements are The best possible performance Removes the SQL code from the other layers of the application Prevents SQL injection attacks Prevents casual table browsing and modifications Easy to create atomic transactions Can catch exceptions Recommended by vendors Derived from www novicksoftware com and Rob Howard s Blog There is a debate Flame war really Issues with performance Issues with DBA vs Developer control Issues with DB portability Issues with source control Issues with dogma Dynamically SQL Parameterized Queries can be preferred In general concatenated SQL is to be avoided to prevent injection attacks string query SELECT FROM Users WHERE UserID tbxUserId Text Theory Practice Business logic often gets into data layer Many times designers model objects after data structure and visa versa Many glue layers are tedious to create and maintain manually A many tiered application can seem over architected and may not have immediate advantages Abstraction increases load Legacy code complicates things Best solution requires consideration of case Additional Wisdom Do not use a highly privileged SQL account like SA to access from the application Also do not use a highly privileged domain account Encrypt the DB connection string Avoid fetching static information from the DB every time you need it Cache it in the application Do not use SELECT ALWAYS define the table columns once you have determined them Do not prefix user sprocs with sp This actually stands for system procedure Your procedure may become inaccessible Use parameter validation at all layers You may get unexpected results otherwise Derived from Doug Seven s 10 Things You Shouldn t Do with SQL Server Stored Procedures Are Code Use source control This can be difficult Always include comments good variable names and exception handling Try to re use code T SQL does not support OOP However you can call other sprocs and UDFs Test and validate the code New in SQL Server 2005 CLR is embedded inside the database engine meaning T SQL functions can be converted to CLR functions for significant performance gains and applications can be run inside the engine A new tool called Service Broker also stands to alter the way applications are designed The new feature is described as an asynchronous programming environment that creates a persistent dialogue between the client application and the server Offers native XML support From SearchSQLServer com Today s Lab Probably really long but hopefully fun May not be good example of data layer abstraction Good opportunity to understand possibilities for stored procedures We will try