Necessary Evils, Building Optimized CRUD Procedures

Necessary Evils, Building Optimized
CRUD Procedures

Introduction
Jason
Strate

e: jstrate@pragmaticworks.com
e: jasonstrate@gmail.com
b: www.jasonstrate.com
t: StrateSQL

Resources

jasonstrate.com

MAKING BUSINESS INTELLIGENT

www.pragmaticworks.com

About Pragmatic Works

•

•
•
•
•

•
•

Industry leaders in Microsoft BI and SQL Server
Platform
SQL Server Professionals - PASS Board of Directors,
Speakers, Authors and MVP’s
National Sales Team Divided by Microsoft
Territories
National System Integrator (NSI)
Gold Certified in Business Intelligence and Data
Platform
Platform Modernization/Safe Passage
Premier Partner for PDW SI Partner Program
MS PDW Partner of Year FY13
Frontline Partnership Partner of the Year for Big Data
Executive sponsor - Andy Mouacdie, WW sales director
PDW

•
•

Over 7,200 customers worldwide
Over 186,000 people in PW database for demand
generation


Session Goals
1. Describe the uses for CRUD procedures in
databases
2. Identify the common problems associated with
CRUD procedures
3. Demonstrate methods for writing performant
CRUD procedures



What is CRUD?
CREATE, READ, UPDATE, DELETE
•
•
•
•
•
•

ABCD: add, browse, change, delete
BREAD: browse, read, edit, add, delete
QDIM: query, delete, insert, modify
SAID: show, alter, insert, delete
VEDAS: view, edit, delete, add, search
VADE(R): view, add, delete, edit



Code Generation
• Many tools available:
– T-SQL Script
– Code Smith
– Entity Framework
– LINQ to SQL
– NHibernate



CRUD Issues?
• Treat tables as objects
–
–
–

People?
Relationships?
Relationship Type?

• Overly generic
– All columns touched
– One procedure to rule them all

• Performance
– Set versus Singleton
– Index selection


Perspectives



Overview

CREATE, UPDATE, AND DELETE



CREATE
CREATE = INSERT
EXEC CRUD.FooInsert
@Column1 = 'Value‘,
@Column2 = 2

INSERT INTO dbo.Foo
(Column1, Column2)
VALUES(@Column1,
@Column2)

Issues:
• Singleton insert
• All columns affected


UPDATE
UPDATE = UPDATE
EXEC CRUD.FooUpdate
@Column1 = 'Value‘,
@Column2 = 2

UPDATE dbo.Foo
SET Column1 = @Column1
WHERE Column2 =
@Column2

Issues:
Singleton update
All columns affected


DELETE
DELETE = DELETE
EXEC CRUD.FooDelete
@Column2 = 2

DELETE FROM dbo.Foo
WHERE Column2 = Column2

Issues:
• Singleton delete



T-SQL Features
• Performance
– Table Valued Functions (TVF)
– Table Valued Parameters (TVP)
– XML document
– Merge statement



Demos

CREATE, UPDATE, AND DELETE



Overview

READ



READ
• READ = SELECT
EXEC CRUD.FooSelect
@Column1 = NULL
,@Column2 = 2

SELECT Column1
,Column2
FROM dbo.Foo
WHERE (@Column1 IS NULL
OR Column1 = @Column1)
AND (@Column2 IS NULL
OR Column2 = @Column2)

Issues
• Index selectivity


Demo

READ



Summary
1. CRUD isn’t necessarily bad
2. Generic templates offer ability to tune and
extend data model behind the scenes.
3. Be cautious in choosing a template for CRUD
4. Know what SQL Server is doing
5. Leverage RECOMPILE as needed



Learn More About Performance



For more information…
Name: Jason Strate
Email: jstrate@pragmaticworks.com
Blog: www.jasonstrate.com

Products
BI products to covert to a Microsoft BI
platform and simplify development on
the platform.

Services
Speed development through training, and
rapid development services from
Pragmatic Works.

Foundation
Helping those who do not have the
means to get into information technology
achieve their dreams.

Necessary Evils, Building Optimized CRUD Procedures

Recommended

Recommended

More Related Content

Similar to Necessary Evils, Building Optimized CRUD Procedures

Similar to Necessary Evils, Building Optimized CRUD Procedures (20)

More from Jason Strate

More from Jason Strate (16)

Recently uploaded

Recently uploaded (20)

Necessary Evils, Building Optimized CRUD Procedures