Pragmatic Application Design

with William R. Vaughn

Stored Procedures

I haven’t really been a big fan of Stored Procedure (SPs) lately, however, after this talk I’m leaning towards these ground rules.

1. If your code is simply doing CRUD (create/read/update/delete) then SPs are probably a clumsy way of handling these. Try an O/R mapper maybe.

2. If your code is retrieving a block of data, doing some processing on it, and then storing it back to the database. This should probably be done on the database side in a SP.

3. If the data you are retrieving does not map to objects in your use cases (reports for example contain specific data which do not match entities in your use cases) then you could probably benefit from SPs.

4. If your code is not running as fast as it needs to, then it is time to optimize. Run tests on different approaches to see which produces the best real time performance.

Performance

• Don’t put anything in form load for windows applications.
• Connections are not free. Do not create any more than you need.
• Performance is more often about how intelligently you ask the database your question
• Send answers to client, not the data to find the answer. Saves bandwidth.
• Select * is dangerous in a query. Returns too much unneeded data.
• Fully qualify objects in your select statements so time is not wasted qualifying them on the database side.
• Keep blobs out of the database. You only have to much cache space, don’t waste them on photos.
• Keep transactions short. Long transactions are expensive to commit and rollback.
• Watch your rowset size. Don’t bring back too much data.
• Make design change decisions based upon REAL performance information. Don’t prematurely optimize your queries.
• Don’t' bring data to the client for processing. Not only is it slow, it’s not secure.
• Keep data on the server where it is faster and more secure.
• Process data on the server which is designed to process data.
• SPs run the same as ad hoc queries as long as the ad hoc query plan is cached. Or, once a SQL statement has been run once, it will run as fast as a SP as long as it is still “compiled” on the server.
• Return focused subsets of your data using where clauses. Only return what you need.
• Be careful of the using operator to close connections. The connections will not closed until GC is called and you have no idea when that will be.
• VSs.net does not regenerate the data schema when the database changes so be careful of any auto generated code.

Good Design

• Leverage existing resources (code, people, libraries, etc.)
• Ensure your user knows you application has started. Don’t make them wait. Show a splash screen at the very least.
• Make your interface intuitive. Actually watch how the user uses your system.
• Be conscious of who owns data and who can change it. Remember concurrency. Do you lock data or let multiple users update a record (pessimistic versus optimistic). How do you handle collisions?
• Protect your data. Encryption.
• Manage your data in the database, no on the client.
• Get data to the user as soon as possible. Optimize where it needs to be done. Don’t leave your user waiting.
• Set indexes based on real life usage data, not expectations.
• Good design and architecture requires discipline.
• Use the right tools for the right job.
• Work with the client for reports!
• Use a gui and tools when it's easier to use a gui and tools
• Soft code business rules and values. Morph to business rules to data. Don’t hardcode.
• Before you use LINQ, do a lot of research. This applies to all new technologies.
• Logically / physically separate concerns.
• Asp.net must used disconnected data
• Test your backups (restores) before you need them.
• Remember, you must restore the base system as well (try virtualization).

Database design

• People and users are different things. Accounts are for roles in a database. Don’t have a Mark Gerow user in the database, have a developer role.
• Leverage nulls where nulls are appropriate (to show unknown values).
• Consider concurrency: Optimistic versus Pessimistic
• SQL server is half-duplex while Oracle is full duplex
• Distributed client systems (database sync) are coming for SQL Server.
• A middle-tier layer and a Data Access Layer are different.
• SQL Sever Compact Edition is a file database for client side databases.
• You can start SQLS manually or automatically.
• SQL Server has two files master/log = .mdb/.ldb
• Don't optimize database column sizes unnecessarily

Security

• Developers should run under least privileges. How little access does my code need?
• Ad hoc SQL queries are dangerous. They are vulnerable to SQL injection attacks.
• Use the runas command to test access restrictions.
• Do not edit data on an un-trusted client.

Environment

• Bill recommends dual monitors. Management studio on one, VS.Net on the other. Saves ALT-Tabbing and makes you more productive.

Bulk Data

• SQL is not designed for bulk operations.
• Use SSIS for bulk data operations even with oracle.

EFPRS improvement ideas

• Use the timestamp on an update to determine collisions.
• Create views or SPs for reports.

General DevTeach Stuff

• We need business cards for these courses
• Laptops are a great investment

The CLR and Stored Procedures

• CLR sp are dangerous and hard.
• Don't debug on prod server as it will stop working.
• There are performance issues.
• When you debug sp, all CLR executables within SQLSERVER are paused (CLR engine stops)

Definitions

• OSFA – One Size Fits All
• A DataTable is a RowSet. Set of rows returned from a query

The Book

• Chapter 9 is wrong. You can get the new one from the website

Notes

• Use .\Name for local database instances
• Tools: manager, profiler, bis, ssis, configuration manager, server explorer