Overall, this new version doesn’t have many changes in the area of development and tooling, but there has been improvement for deploying ASP.NET MVC applications. The setup process now requires .NET 3.5 SP1 to be installed, where in the past it was optional because the additional assemblies where included with the install.
Don’t worry though /bin deployment is still supported, they are not taking a runtime dependency on SP1 other than our existing dependency on System.Web.Routing.dll and System.Web.Abstractions.dll. Thus you can still bin deploy your application to a hosting provider who has .NET 3.5 installed without SP1 by following these instructions.
They are also adding an option to the installer that enables installing on a server that does not have Visual Studio at all on the machine, which is useful for production servers and hosting providers. To do a server install you just need to run the following command to install MVC on your server.
Also because of the latest breaking changes from Beta to RC 1 & 2, we are taking the time between now and the final release of the MVC Framework to work on the book and make sure all the loose ends are tied up.
I also got noticed today that our final cover design is done. So we are in the final stretch of this book. The cover hasn’t been uploaded to Amazon yet, but if you are interested in pre-ordering a copy just click on the cover image to your right and it will take you to the Amazon page where you can place your order.
About a month ago I was experimenting with different ways to optimize my LINQ queries against the IdeaPipe database, in order to improve the read times. I wanted to improve the read times because our new Facebook Application was being launched and I anticipated an increase in our traffic to the server, which is used to host IdeaPipe and the Facebook Application component.
Whenever I am trying to optimize SQL queries I fire up SQL Server Profiler and take a look at how the queries are performing. This helps me identify queries that are taking a longer time to execute and probably need to be looked at or re-thought. One of the queries that I identified as needing improvement was the following LINQ query:
Old Query
from i in source
join xgl in GroupIdeaLinks on i.IdeaId equals xgl.IdeaId into groupLinksGroup
from gl in groupLinksGroup.DefaultIfEmpty()
let visibility = (gl == null ? 'O' : gl.Group.VisibilityPermission)
let groupId = (gl == null ? -1 : gl.GroupId)
where visibility == 'O' || GroupMembers.Count(m => m.GroupId == groupId && m.IsApproved && m.UserId == userId && (visibility == 'G' || (m.RoleId & (int)Role.Manager) == 0)) == 1
select i;
That produced this monster of a SQL statement:
SELECT [t5].[IdeaId], [t5].[CategoryId], [t5].[UserId], [t5].[IsPopular], [t5].[PopularOn], [t5].[Title], [t5].[SafeDescription], [t5].[Description], [t5].[Path], [t5].[Score], [t5].[Rank], [t5].[ExternalLink], [t5].[VideoLink], [t5].[BumpUpCount], [t5].[BumpDownCount], [t5].[TotalBumpCount], [t5].[TotalCommentCount], [t5].[CreatedOn], [t5].[rowversion]
FROM (
SELECT [t4].[IdeaId], [t4].[CategoryId], [t4].[UserId], [t4].[IsPopular], [t4].[PopularOn], [t4].[Title], [t4].[SafeDescription], [t4].[Description], [t4].[Path], [t4].[Score], [t4].[Rank], [t4].[ExternalLink], [t4].[VideoLink], [t4].[BumpUpCount], [t4].[BumpDownCount], [t4].[TotalBumpCount], [t4].[TotalCommentCount], [t4].[CreatedOn], [t4].[rowversion], [t4].[value],
(CASE
WHEN [t4].[test] IS NULL THEN @p1
ELSE [t4].[GroupId]
END) AS [value2]
FROM (
SELECT [t0].[IdeaId], [t0].[CategoryId], [t0].[UserId], [t0].[IsPopular], [t0].[PopularOn], [t0].[Title], [t0].[SafeDescription], [t0].[Description], [t0].[Path], [t0].[Score], [t0].[Rank], [t0].[ExternalLink], [t0].[VideoLink], [t0].[BumpUpCount], [t0].[BumpDownCount], [t0].[TotalBumpCount], [t0].[TotalCommentCount], [t0].[CreatedOn], [t0].[rowversion], [t2].[test], [t2].[GroupId],
(CASE
WHEN [t2].[test] IS NULL THEN @p0
ELSE CONVERT(NChar(1),[t3].[VisibilityPermission])
END) AS [value]
FROM [Ideas].[Ideas] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[GroupId], [t1].[IdeaId]
FROM [Groups].[GroupIdeaLink] AS [t1]
) AS [t2] ON [t0].[IdeaId] = [t2].[IdeaId]
INNER JOIN [Groups].[Groups] AS [t3] ON [t3].[GroupId] = [t2].[GroupId]
) AS [t4]
) AS [t5]
WHERE (UNICODE([t5].[value]) = @p2) OR (((
SELECT COUNT(*)
FROM (
SELECT
(CASE
WHEN ([t6].[GroupId] = [t5].[value2]) AND ([t6].[IsApproved] = 1) AND (([t6].[UserId]) = @p3) AND ((UNICODE([t5].[value]) = @p4) OR (([t6].[RoleId] & @p5) = @p6)) THEN 1
WHEN NOT (([t6].[GroupId] = [t5].[value2]) AND ([t6].[IsApproved] = 1) AND (([t6].[UserId]) = @p3) AND ((UNICODE([t5].[value]) = @p4) OR (([t6].[RoleId] & @p5) = @p6))) THEN 0
ELSE NULL
END) AS [value]
FROM [Groups].[GroupMembers] AS [t6]
) AS [t7]
WHERE [t7].[value] = 1
)) = @p7)
So I started playing around with the LINQ statement until I reduced the size of my SQL query significantly. The following is the result of that optimization:
New Query
var groupMembership = (from gm in GroupMembers
let visibility = gm.Group.VisibilityPermission
where visibility == 'O' || (gm.IsApproved && gm.UserId == userId && (visibility == 'G' || (gm.RoleId & (int)Role.Manager) == 0))
select gm.GroupId).Distinct();
from i in Ideas
join xgl in GroupIdeaLinks on i.IdeaId equals xgl.IdeaId into groupLinksGroup
from gl in groupLinksGroup.DefaultIfEmpty()
where gl == null || groupMembership.Contains(gl.GroupId)
select i;
Which outputs the following SQL query:
SELECT [t0].[IdeaId], [t0].[CategoryId], [t0].[UserId], [t0].[IsPopular], [t0].[PopularOn], [t0].[Title], [t0].[SafeDescription], [t0].[Description], [t0].[Path], [t0].[Score], [t0].[Rank], [t0].[ExternalLink], [t0].[VideoLink], [t0].[BumpUpCount], [t0].[BumpDownCount], [t0].[TotalBumpCount], [t0].[TotalCommentCount], [t0].[CreatedOn], [t0].[rowversion]
FROM [Ideas].[Ideas] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[GroupId], [t1].[IdeaId]
FROM [Groups].[GroupIdeaLink] AS [t1]
) AS [t2] ON [t0].[IdeaId] = [t2].[IdeaId]
WHERE ([t2].[test] IS NULL) OR (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT DISTINCT [t3].[GroupId]
FROM [Groups].[GroupMembers] AS [t3]
INNER JOIN [Groups].[Groups] AS [t4] ON [t4].[GroupId] = [t3].[GroupId]
WHERE (UNICODE([t4].[VisibilityPermission]) = @p0) OR (([t3].[IsApproved] = 1) AND (([t3].[UserId]) = @p1) AND ((UNICODE([t4].[VisibilityPermission]) = @p2) OR (([t3].[RoleId] & @p3) = @p4)))
) AS [t5]
WHERE [t5].[GroupId] = [t2].[GroupId]
))
As you can tell the second query is much more compact and it does the exact same thing as the first query. I was pretty proud of my self and riding high on my genius, until this happened:
Well this didn’t really happen, but you get the point. I quickly came down from my high when I tested the performance of the new query in SQL Server Profiler, and received these results from the two queries:
OLD QUERY (Reads 130, Durration 5)
NEW QUERY (Reads 218, Durration 28)
That is right my optimization increased the number of times SQL has to read the table by 68% and time it takes to execute by 460%. So I reversed all my changes and learned a lesson on how not to optimize a LINQ statement.
The moral of the story is you probably don’t need to optimize your SQL query through LINQ, just keep it simple and optimize your LINQ statement and leave the rest up to the professionals at Microsoft who created the LINQ to SQL expression query generator.
I just recently read Jeff Atwood’s Deadlocked! article. I just wanted to give some more insight in to the read committed snapshot so that it is not perceived as “magic”. It has some definite advantages when dealing with deadlocks, however if your code relies on row level locking you are not going to be able to use this type of reading in SQL Server.
First lets talk about how you enable it. It is not a transactional isolation level, so if you set it, it will effect your whole database. You have been warned!
alter database [YourDatebaseHere]
set read_committed_snapshot on
go
Basically what this does is create a snapshot or read-only database of your current results that is separate from your live database. So when you run a SELECT statement, to read your data, you are reading from a read-only copy of your database. When you change your database, it happens on the live database, and then a new copy or snapshot is created for reading against.
Personally I am using it on IdeaPipe, because like most Web 2.0 applications there are a heavy amount of reads and very few updates that effect the row. So chances are if you have a website this will decrease your number of deadlocks. But make sure to test thoroughly before implementing read committed snapshot.
For example, suppose READ COMMITTED SNAPSHOT is not enabled in the database and you want to assign one more ticket to a person, but only if that user does not already have high priority tickets:
BEGIN TRANSACTION
UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 1
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')
--- do not commit yet
Note that you have not explicitly specified an isolation level, so your transaction runs under the default READ COMMITTED level. If another connection issues a similar update:
UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 2
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')
it will hang in a lock waiting state. Once you commit your first transaction the second one will complete, but it will not assign ticket 2 to user 6, which is the correct behavior as designed.
However if read committed snapshot is enabled on the database the user will end up with two high priority tickets, because the first read happens against a snapshot and the update happens against the live database. So this will obviously cause problems for specifications and business rules that rely on row level locking. So be careful, and make sure you specifically know what is happening with your code before turning this on
Note: Chances are if you are using LINQ you don’t have to worry about the above scenario, however I am not a DBA expert, only a student of the practice. So take what I say with a grain of salt.
Phil Haack has announced the availability of ASP.NET MVC 1.0 Release Candidate 2.
