Transcript Stephen Forte DAT401 from s in dat.Speaker select s.Bio; Chief Strategy Officer of Telerik Certified Scrum Master Active in the community: International Conference Speaker for.

Stephen Forte
DAT401
from s in dat.Speaker select s.Bio;
Chief Strategy Officer of Telerik
Certified Scrum Master
Active in the community:
International Conference Speaker for 12+ years
RD, MVP and INETA Speaker
Co-moderator & founder of NYC .NET
Developers Group http://www.nycdotnetdev.com
Author of SQL Server 2008 Developers Guide (MS Press)
MBA from the City University of New York
Past:
CTO and co-founder of Corzen, Inc. (TXV: WAN)
CTO of Zagat Survey
Agenda
ADO.NET: Passing a .NET Custom Collection to a
Stored Procedure
LINQ: Understanding the difference between
IEnumerable and IQueryable by eavesdropping
on the server
Entity Framework: Model Complex Relationships
with Views
Silverlight: Binding REST data to Silverlight
Database Architecture: Pre-calculate and Transform
Data for Faster Performance
Agenda
ADO.NET: Passing a .NET Custom Collection to a
Stored Procedure
LINQ: Understanding the difference between
IEnumerable and IQueryable by eavesdropping
on the server
Entity Framework: Model Complex Relationships
with Views
Astoria: Binding REST data to Silverlight
Database Architecture: Pre-calculate and Transform
Data for Faster Performance
Hack #1: Pass .NET Collection to SPs
SQL Server 2008 allows Table Valued Variables to be
passed into a stored procedure (TVP)
ADO .NET 3.51 allows a DataSet to be passed into a
SP via a TVP
Your custom .NET collection is a graph of the data you
want to insert/change
Must implement IEnumerable
Create a C# 3.0 Extension method that takes any
IEnumerable collection and converts to a DataSet
Call your stored procedure with yourcollection.ToDataSet()
Using TVPs and .NET 3.5 Extension Methods
Agenda
ADO.NET: Passing a .NET Custom Collection
to a Stored Procedure
LINQ: Understanding the difference between
IEnumerable and IQueryable by eavesdropping
on the server
Entity Framework: Model Complex Relationships
with Views
Astoria: Binding REST data to Silverlight
Database Architecture: Pre-calculate and Transform
Data for Faster Performance
Hack #2: Spying on Your Server
Use implicitly typed local variables
IEnumerable will execute locally (Linq to Objects, XML)
IQueryable will convert your LINQ to TSQL and
execute remotely
When executing remotely, LINQ queries will generate
TSQL on your server
You can spy on this SQL via code or even better, watch
the whole conversation via SQL Server Profiler
Learning a very small amount about TSQL, Indices, and
Execution Plans will make you a far better LINQ programmer
LINQ to SQL, LINQ to EF, LINQ to ORM +
SQL Server Profiler
Agenda
ADO.NET: Passing a .NET Custom Collection to a
Stored Procedure
LINQ: Understanding the difference between
IEnumerable and IQueryable by eavesdropping
on the server
Entity Framework: Model Complex Relationships
with Views
Astoria: Binding REST data to Silverlight
Database Architecture: Pre-calculate and Transform
Data for Faster Performance
Hack #3: Deconstruct Complex
Databases Before You Map Them
Some DBAs go overboard and super normalize
the database
Hard to represent a relationship via too many joins
ORMs/EF are supposed to do this for us; however,
it creates difficult models to work with
Many models are just 1:1 representations of the database
Create a series of views that deconstruct your complex
data and optimize those views with an index
Updates? Use stored procedures or you can still model
the tables and only use them for updates
Creating Views Out of Complex Data
Relationships for EF
Agenda
ADO.NET: Passing a .NET Custom Collection
to a Stored Procedure
LINQ: Understanding the difference between
IEnumerable and IQueryable by eavesdropping
on the server
Entity Framework: Model Complex Relationships
with Views
Astoria: Binding REST data to Silverlight
Database Architecture: Pre-calculate and Transform
Data for Faster Performance
Hack #4: Silverlight Databinding to
RESTful Data Services
Use LINQ to ADO .NET Data Services on the SL client
Leverage your investment in LINQ; however, you have to be
100% asynchronous
Hack:
Encapsulate all the asynchronous LINQ calls into its own
helper library using Generics
Beware of cross-domain issues
Clientaccesspolicy.xml
WCF Web Service, then expose as RESTful data
Asynchronous ADO .NET Data Services Data
Binding via LINQ to Astoria in Silverlight
Agenda
ADO.NET: Passing a .NET Custom Collection
to a Stored Procedure
LINQ: Understanding the difference between
IEnumerable and IQueryable by eavesdropping
on the server
Entity Framework: Model Complex Relationships
with Views
Astoria: Binding REST data to Silverlight
Database Architecture: Pre-calculate and Transform
Data for Faster Performance
Hack #5: “Reporting DB” Design Pattern
Optimize for a high read environment with multiple views of the
data and multiple data access strategies
What data to move? Anything that can be derived from an OLTP
database for high read
Reporting data
Web view (Amazon catalog, not orders)
Traditionally “flatter”
Some de-normalized aspects
Prevents query against normalized “raw” data
Must accept latency
Good implementation of XML
Highly indexed since the data is “published”
Building the Database for Database Reads
[email protected]
http://stephenforte.net
Resources
www.microsoft.com/teched
www.microsoft.com/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
www.microsoft.com/learning
Microsoft Certification and Training Resources
Complete an
evaluation on
CommNet and
enter to win!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.