Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without the right pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Talking about code

ADO.NET SqlClient, SQLXML and SQLCLR

Could you write a small ADO.NET in a few hours

Could you write a few queries and return tables

Is XML developer technology? Yes, but also admin...

Need to understand what a developer feels like

Most of the time waiting for the SQL Server...

Interfaces, ADO.NET

Interface for an application to get to the data

Chunky vs Chatty - Big vs Small interfaces

Impacts performance - Each has its place

Chunky Interfaces

Extreme case: SELECT with no WHERE clause

Looks like: GetAllProducts()

Get lots of data in single a calls

Large network hit, locks longer, longer load time

Less batches, potentially less overall I/O and network traffic

Chatty Interface

Extreme case: Always SELECT ... WHERE Column=value

Looks like: GetProducts(int[])

Get just the exact data you need at that time

Less network traffic, less locking, more seeks, more batches

Potentially more overall I/O and network traffic

Sometimes unecessary calls over time

Finding a balance

How much data does your app need to work at once?

Is a redesign possible?

From GetProductsAll() to GetProducts(int[])

From single updates to bulk/batch updates

Demo

SQL Profiler running on the SQL side

RowGetter demo application on client - Queries on SalesDetails table

App options from MinSalesID/MaxSalesID, Chunky/Chatty, etc...

Comparing batch query (chunky) vs. One request per row (chatty)

SQL can also execute the batch as chatty/chunky/semi-chatty

General Rules

Think also about network (not only CPU and IO).

The queries are sometimes larger than the results. Think outbound and inbound.