Database Performance, Correctness, Compostion, Compromise, and Linq too

MSDN Blog Postings » Database Performance, Correctness, Compostion, Compromise, and Linq too

Sat, 01 Sep 2007 04:43:28 GMT

PingBack from http://msdnrss.thecoderblogs.com/2007/08/31/database-performance-correctness-compostion-compromise-and-linq-too/

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

bizguy — Sat, 01 Sep 2007 05:36:28 GMT

Good analytics, but not code to show samples.

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

ricom — Sat, 01 Sep 2007 06:55:52 GMT

Sheesh it was already way too long. :)

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

kfarmer@microsoft.com — Sat, 01 Sep 2007 08:52:22 GMT

I don't see any reason to have code -- it'd distract from the argument.

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

Frank Hileman — Sat, 01 Sep 2007 16:53:49 GMT

Is it now possible to send an array of parameters to a SP? In generaly, "chunky" communication with a DB (fewer round trips) seems poorly supported in SQL server.

Now that we have SP that can be written in any CLR language, it would be nice to have better support for sending a chunk of requests to a general purpose SP that can process it in pieces, all on the server, instead of a multitude of round trips.

While the Linq abstractions are nice, I have a feeling it will further encourage poor practices such as client side joins with large data sets. Linq on the server side SP (written in C#) seems like a great thing.

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

ricom — Sat, 01 Sep 2007 20:53:50 GMT

Linq to SQL joins are resolved on the server -- the only thing that happens on the client is the coversion to SQL and of course parsing the result set.

I think you're right about Managed Stored Procedures -- my pet peeve, that you couldn't write procs that accepted arrays conveniently -- is taken care of.

I never liked TSQL much at all, it's highly useful but not much fun :)

Referential Integrity

Ryan Haney — Mon, 03 Sep 2007 06:13:22 GMT

Very interesting article. I am one of those developers that really didn't understand isolation.

What can you say about sites like MySpace and eBay that move the responsibility of referential integrity to the application level due to the "poor scalability" of database engines? MySpace has supposedly split their DB into sections - i.e. a group of machines serve user data. So users with last names A-E may be on one server, while users with last names F-I may be on another etc. I often wonder if it is because they don't understand the nature of the engine and opt in for the methods they can control.

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

Norman Diamond — Mon, 03 Sep 2007 07:26:45 GMT

> the only guarantee is that we will get some

> total that represents the sum of the balances

> as they could have existed at some moment

> where all the data was committed.

The guarantee is less than that. The guarantee is that the total represents the sum of the balances as they could have existed at some moment during some ordering of the transactions which only by accident might happen to match the actual ordering.

For example:

You read a bunch of accounts including #1020.

Someone deposits to #1020.

Someone else deposits to #1080.

You read a bunch of accounts including #1080.

Your total includes the second deposit but not the first. Your total matches a different hypothetical ordering.

> I wanted to subtract money from #1060 and put

> in #1070 and everything is great. But what if

> I had wanted to put the money in account

> #1030?

Your scenario has too much 6. Here have a free 3 ^_^ ... No, that's not enough, because your later sentences have a mix of #1030 and #1060 but lose #1070. Oops, now I have to bite my fingers off to keep from speculating about what company made your database, but maybe I can do more proofreading later.

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

Ollie — Mon, 03 Sep 2007 16:14:31 GMT

Context is everything :)

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

ricom — Mon, 03 Sep 2007 20:15:04 GMT

Keep in mind my strong admonition that this article is only approximately correct in the name of bevity and clarity.

The "real" situation would require an entire book if I'm lucky :)

So don't read too much into the particulars they're simplified for illustration.

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

alephnaut — Mon, 03 Sep 2007 23:11:37 GMT

I've been doing the database thing for a while now. IMHO it's rare that I need to modify the default transaction isolation level (read committed). I've spent about an equal amount of time in the Oracle and SQL Server worlds and it has still proven true. Almost every app involving concurrency designs its locking strategy with the assumption of basic data integrity (e.g., not reading a partially formed int or double); anything more should be handled at the app level.

In my experience upping the isolation level has almost always been an indicator of a design problem. A higher level of isolation provides a false sense of security when what's usually needed is for the app design to handle synchronization at a higher level.

Also, coming from the Oracle world I was baffled by the notion of optimistic concurrency. Maybe it was the lack of a query designer but I assumed that "last one wins" semantics was a given (e.g., updates use only the key field in the where clause). In every place where I saw it used a better approach would have been to push locking to the app level; the first person to get the lock wins, others can't edit until they've released the lock and the lock can timeout.

I can't agree more with you about database normalization, especially in OLTP type systems. The benefits of a normalized design are similar to the benefits of highly cohesive data structures.

About bulk inserts and updates, I've found that it's better to push these as close to the server as possible and do it off-line if possible. Use replica tables, update the current secondary then make it the master once it's ready. If it's a periodic thing, say for product catalog updates use BCP if possible (sql loader in the oracle world).

I'm still deciding about LINQ; every time I've seen attempts to hide the boundary between the OO model and the relational model the result has been an extremely chatty abstraction that doesn't scale and eventually has to be abandoned for high volume applications. From what I've seen, it does look to be useful for replacing boilerplate iteration-filtering code (along the lines of the STL's functions like remove_if).

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

ricom — Tue, 04 Sep 2007 00:39:14 GMT

Alephanaut is saying two things:

1) Greater isolation usually isn't needed -- In my experience this is usually true

2) Moving the locks into the application and then timing out has been a useful technique for him/her. The described technique is a form of the "pessimistic" locking strategy and while it has its place my own feelign is that the opposite approach is appropriate more often. See http://blogs.msdn.com/ricom/archive/2004/06/24/165063.aspx

This is of course a matter of opinion, and experience in particular environments. YMMV :)

I think Alephanaut and I substantially agree in our experience other than which locking form has proved more useful to us in our past.

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

alephnaut — Tue, 04 Sep 2007 01:13:23 GMT

To be honest, for the problems I've worked on most of the time last-one-wins has been ok. Reservations have usually only been necessary when the app explicitly involved it (e.g., a content management system or a revision control system where you expect to check things out).

Or maybe I'm just lazy; update <table> set col=val, ... where <keyfield> = @keyValue is a lot less to type than update <table> set col=val, ... where col1=oldval1 and col2=oldval2 ... especially for tables with a lot of fields :)

re: Database Performance, Correctness, Compostion, Compromise, and Linq too

ricom — Tue, 04 Sep 2007 04:53:42 GMT

Linq gives you optimistic locking automatically, no typing :)

Getting Started With LINQ To SQL

Hilton Giesenow's Jumbled Mind — Wed, 20 Feb 2008 19:04:10 GMT

Considering I've put a few posts up about LINQ To SQL, I realised I've never shared some of the

Getting Started With LINQ To SQL(转)

王德水 — Wed, 02 Apr 2008 09:50:18 GMT

原文地址:http://dotnet.org.za/hiltong/archive/2008/02/20/getting-started-with-linq-to-sql.aspxConsideri...