CodingPleasure

cultivate passion for everything else that goes on around programming

Monthly Archives: October 2010

Developers and blogs

@devs:

This weekend I watched Scott Hanselman’s speech about developers and social networking: http://channel9.msdn.com/blogs/glucose/hanselminutes-on-9-social-networking-for-developers-part-1-every-developer-needs-a-blog

His postulate (with justification, around minute 17:30) seems very provocative. I realized I totally agree with him, so I encourage you to listen to his speech.

As a proof I agree with Scott Hanselman:

Blog: https://mcanti.wordpress.com/

Google Buzz: https://www.googleapis.com/buzz/v1/activities/109036436253449335398/@public

Twitter: http://twitter.com/#!/mcanti

Facebook: http://www.facebook.com/#!/profile.php?id=100000506914553

I would love to receive links to your blogs! Or your twitter id 🙂

PS: I also encourage you to find out more about Uncle Bob Martin, Roy Osherove, Scott Hanselman, Martin Fowler, Jeff Atwood, Ovulex, the_dev, cobrul, etc. 🙂

Use SQL Server Profiler!

Unless you’re writing a small academic application that no one will use that targets SQL Server, make sure to use SQL Server Profiler to avoid running into performance problems.

In 99% of all performance problems, the real cause is badly written code. Specially when using abstraction layers like Linq2SQL, Entity Framework and even pure ADO.NET, the developer rarely knows what actual SQL queries are generated. And most of the time the developer doesn’t really know when the SQL queries are executed.

The most useful method for performance optimization is thus understanding of what queries are generated. So here are some tips:

  1. Use SQL Server Profiler to actually see the queries!
  2. Use SQL Server Management Studio to display the Estimated Execution Plan for the generated queries. Try to add indexes!
  3. Try to identify repeating queries and cache them!

Additional tip: if multiple applications are hitting the same SQL Server, set the application name in your connection string so that you can filter the queries in SQL Server Profiler by the application name, in web.config:

   1: <add name="MyEntities" connectionString="metadata=res://DataAccessLayer/;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=sqlserver;Initial Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=secret;MultipleActiveResultSets=True; Application Name='MyApplication'&quot;" providerName="System.Data.EntityClient" />

or built through code:

   1: EntityConnectionStringBuilder conStrIntegratedSecurity = new EntityConnectionStringBuilder()

   2: {

   3:     Metadata = "res://DataAccessLayer/",

   4:     Provider = "System.Data.SqlClient",

   5:     ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder

   6:     {

   7:         InitialCatalog = catalog,

   8:         DataSource = datasource,

   9:         UserID = userID, // User ID such as "sa"

  10:         Password = password, // hide the password

  11:         MultipleActiveResultSets = true,

  12:         ApplicationName = ConfigurationManager.AppSettings["ApplicationName"]

  13:  

  14:     }.ConnectionString

  15:  

  16: };

Entity Framework Generated SQL Queries

During an optimization session for a ASP.NET application based on EntityFramework (and Linq to Entities) I found out a simple optimization method based on rewriting the Linq to Entities queries for many-to-many objects.

In a simple scenario, we have CareTypes, Establishments and a many-to-many table between them: EstablishmentCareTypes. If we want to fetch all CareTypes for a given Establishment, we would write the following TSQL query:

   1: select * from CareTypes ct

   2: inner join EstablishmentCareTypes ect on ect.CareTypeId = ct.CareTypeId

   3: where ect.EstablishmentId='6B555389-3DAC-4B42-9CB4-903A572AED09'

Using SQL Server Management Studio you can see the estimated execution plan, which is rather simple:

image

In Linq to Entities, the query was written like this:

   1: var careTypes = (from e in context.EstablishmentSet

   2:                   where e.EstablishmentId == establishmentId

   3:                   select e.CareTypes.OrderBy(ct => ct.Name)).FirstOrDefault();

This query does the job, but has the following generated TSQL:

   1: SELECT 

   2: [Project3].[EstablishmentId] AS [EstablishmentId], 

   3: [Project3].[C1] AS [C1], 

   4: [Project3].[StatusId] AS [StatusId], 

   5: [Project3].[Description] AS [Description], 

   6: [Project3].[LastEditTime] AS [LastEditTime], 

   7: [Project3].[LastEditedBy] AS [LastEditedBy], 

   8: [Project3].[CareTypeId] AS [CareTypeId], 

   9: [Project3].[Name] AS [Name], 

  10: [Project3].[Parameter1] AS [Parameter1], 

  11: [Project3].[Parameter2] AS [Parameter2], 

  12: [Project3].[Parameter3] AS [Parameter3], 

  13: [Project3].[Parameter4] AS [Parameter4], 

  14: [Project3].[Parameter5] AS [Parameter5], 

  15: [Project3].[Parameter6] AS [Parameter6], 

  16: [Project3].[Parameter7] AS [Parameter7], 

  17: [Project3].[Parameter8] AS [Parameter8], 

  18: [Project3].[Parameter9] AS [Parameter9], 

  19: [Project3].[Parameter10] AS [Parameter10], 

  20: [Project3].[Parameter11] AS [Parameter11], 

  21: [Project3].[Parameter12] AS [Parameter12], 

  22: [Project3].[Parameter13] AS [Parameter13], 

  23: [Project3].[Parameter14] AS [Parameter14], 

  24: [Project3].[NrChildren] AS [NrChildren], 

  25: [Project3].[NrHoursPerWeek] AS [NrHoursPerWeek], 

  26: [Project3].[ArrivingTime] AS [ArrivingTime], 

  27: [Project3].[LeavingTime] AS [LeavingTime], 

  28: [Project3].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption], 

  29: [Project3].[LeavingTimeInterruption] AS [LeavingTimeInterruption], 

  30: [Project3].[PersonIndex] AS [PersonIndex], 

  31: [Project3].[HasInterruption] AS [HasInterruption], 

  32: [Project3].[PersonIndexAtNoon] AS [PersonIndexAtNoon]

  33: FROM ( SELECT 

  34:     [Limit1].[EstablishmentId] AS [EstablishmentId], 

  35:     [Project2].[CareTypeId] AS [CareTypeId], 

  36:     [Project2].[StatusId] AS [StatusId], 

  37:     [Project2].[Description] AS [Description], 

  38:     [Project2].[LastEditTime] AS [LastEditTime], 

  39:     [Project2].[LastEditedBy] AS [LastEditedBy], 

  40:     [Project2].[Name] AS [Name], 

  41:     [Project2].[Parameter1] AS [Parameter1], 

  42:     [Project2].[Parameter2] AS [Parameter2], 

  43:     [Project2].[Parameter3] AS [Parameter3], 

  44:     [Project2].[Parameter4] AS [Parameter4], 

  45:     [Project2].[Parameter5] AS [Parameter5], 

  46:     [Project2].[Parameter6] AS [Parameter6], 

  47:     [Project2].[Parameter7] AS [Parameter7], 

  48:     [Project2].[Parameter8] AS [Parameter8], 

  49:     [Project2].[Parameter9] AS [Parameter9], 

  50:     [Project2].[Parameter10] AS [Parameter10], 

  51:     [Project2].[Parameter11] AS [Parameter11], 

  52:     [Project2].[Parameter12] AS [Parameter12], 

  53:     [Project2].[Parameter13] AS [Parameter13], 

  54:     [Project2].[Parameter14] AS [Parameter14], 

  55:     [Project2].[NrChildren] AS [NrChildren], 

  56:     [Project2].[NrHoursPerWeek] AS [NrHoursPerWeek], 

  57:     [Project2].[ArrivingTime] AS [ArrivingTime], 

  58:     [Project2].[LeavingTime] AS [LeavingTime], 

  59:     [Project2].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption], 

  60:     [Project2].[LeavingTimeInterruption] AS [LeavingTimeInterruption], 

  61:     [Project2].[PersonIndex] AS [PersonIndex], 

  62:     [Project2].[HasInterruption] AS [HasInterruption], 

  63:     [Project2].[PersonIndexAtNoon] AS [PersonIndexAtNoon], 

  64:     [Project2].[C1] AS [C1]

  65:     FROM   (SELECT TOP (1) 

  66:         [Extent1].[EstablishmentId] AS [EstablishmentId]

  67:         FROM [dbo].[Establishments] AS [Extent1]

  68:         WHERE [Extent1].[EstablishmentId] = '6B555389-3DAC-4B42-9CB4-903A572AED09' ) AS [Limit1]

  69:     LEFT OUTER JOIN  (SELECT 

  70:         [Extent2].[EstablishmentId] AS [EstablishmentId], 

  71:         [Extent3].[CareTypeId] AS [CareTypeId], 

  72:         [Extent3].[StatusId] AS [StatusId], 

  73:         [Extent3].[Description] AS [Description], 

  74:         [Extent3].[LastEditTime] AS [LastEditTime], 

  75:         [Extent3].[LastEditedBy] AS [LastEditedBy], 

  76:         [Extent3].[Name] AS [Name], 

  77:         [Extent3].[Parameter1] AS [Parameter1], 

  78:         [Extent3].[Parameter2] AS [Parameter2], 

  79:         [Extent3].[Parameter3] AS [Parameter3], 

  80:         [Extent3].[Parameter4] AS [Parameter4], 

  81:         [Extent3].[Parameter5] AS [Parameter5], 

  82:         [Extent3].[Parameter6] AS [Parameter6], 

  83:         [Extent3].[Parameter7] AS [Parameter7], 

  84:         [Extent3].[Parameter8] AS [Parameter8], 

  85:         [Extent3].[Parameter9] AS [Parameter9], 

  86:         [Extent3].[Parameter10] AS [Parameter10], 

  87:         [Extent3].[Parameter11] AS [Parameter11], 

  88:         [Extent3].[Parameter12] AS [Parameter12], 

  89:         [Extent3].[Parameter13] AS [Parameter13], 

  90:         [Extent3].[Parameter14] AS [Parameter14], 

  91:         [Extent3].[NrChildren] AS [NrChildren], 

  92:         [Extent3].[NrHoursPerWeek] AS [NrHoursPerWeek], 

  93:         [Extent3].[ArrivingTime] AS [ArrivingTime], 

  94:         [Extent3].[LeavingTime] AS [LeavingTime], 

  95:         [Extent3].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption], 

  96:         [Extent3].[LeavingTimeInterruption] AS [LeavingTimeInterruption], 

  97:         [Extent3].[PersonIndex] AS [PersonIndex], 

  98:         [Extent3].[HasInterruption] AS [HasInterruption], 

  99:         [Extent3].[PersonIndexAtNoon] AS [PersonIndexAtNoon], 

 100:         1 AS [C1]

 101:         FROM  (SELECT 

 102:       [EstablishmentCareTypes].[EstablishmentId] AS [EstablishmentId], 

 103:       [EstablishmentCareTypes].[CareTypeId] AS [CareTypeId]

 104:       FROM [dbo].[EstablishmentCareTypes] AS [EstablishmentCareTypes]) AS [Extent2]

 105:         INNER JOIN [dbo].[CareTypes] AS [Extent3] ON [Extent3].[CareTypeId] = [Extent2].[CareTypeId] ) AS [Project2] ON [Limit1].[EstablishmentId] = [Project2].[EstablishmentId]

 106: )  AS [Project3]

 107: ORDER BY [Project3].[EstablishmentId] ASC, [Project3].[C1] ASC, [Project3].[Name] ASC

This has a rather complicated execution plan:

image

Obviously not what would be expected after knowing the execution plan of our manually written TSQL!

After some Internet research, I wrote the Linq to Entities query like this:

   1: var careTypes = (from e in context.EstablishmentSet

   2:                  from ct in e.CareTypes

   3:                  where e.EstablishmentId == establishmentId

   4:                  orderby ct.Name

   5:                  select ct);

This returns the exact same objects, but has the execution plan we would expect:

image

The generated TSQL is also much simpler (and shorter):

   1: SELECT 

   2: [Extent2].[StatusId] AS [StatusId], 

   3: [Extent2].[Description] AS [Description], 

   4: [Extent2].[LastEditTime] AS [LastEditTime], 

   5: [Extent2].[LastEditedBy] AS [LastEditedBy], 

   6: [Extent2].[CareTypeId] AS [CareTypeId], 

   7: [Extent2].[Name] AS [Name], 

   8: [Extent2].[Parameter1] AS [Parameter1], 

   9: [Extent2].[Parameter2] AS [Parameter2], 

  10: [Extent2].[Parameter3] AS [Parameter3], 

  11: [Extent2].[Parameter4] AS [Parameter4], 

  12: [Extent2].[Parameter5] AS [Parameter5], 

  13: [Extent2].[Parameter6] AS [Parameter6], 

  14: [Extent2].[Parameter7] AS [Parameter7], 

  15: [Extent2].[Parameter8] AS [Parameter8], 

  16: [Extent2].[Parameter9] AS [Parameter9], 

  17: [Extent2].[Parameter10] AS [Parameter10], 

  18: [Extent2].[Parameter11] AS [Parameter11], 

  19: [Extent2].[Parameter12] AS [Parameter12], 

  20: [Extent2].[Parameter13] AS [Parameter13], 

  21: [Extent2].[Parameter14] AS [Parameter14], 

  22: [Extent2].[NrChildren] AS [NrChildren], 

  23: [Extent2].[NrHoursPerWeek] AS [NrHoursPerWeek], 

  24: [Extent2].[ArrivingTime] AS [ArrivingTime], 

  25: [Extent2].[LeavingTime] AS [LeavingTime], 

  26: [Extent2].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption], 

  27: [Extent2].[LeavingTimeInterruption] AS [LeavingTimeInterruption], 

  28: [Extent2].[PersonIndex] AS [PersonIndex], 

  29: [Extent2].[HasInterruption] AS [HasInterruption], 

  30: [Extent2].[PersonIndexAtNoon] AS [PersonIndexAtNoon]

  31: FROM  (SELECT 

  32:       [EstablishmentCareTypes].[EstablishmentId] AS [EstablishmentId], 

  33:       [EstablishmentCareTypes].[CareTypeId] AS [CareTypeId]

  34:       FROM [dbo].[EstablishmentCareTypes] AS [EstablishmentCareTypes]) AS [Extent1]

  35: INNER JOIN [dbo].[CareTypes] AS [Extent2] ON [Extent1].[CareTypeId] = [Extent2].[CareTypeId]

  36: WHERE [Extent1].[EstablishmentId] = '6B555389-3DAC-4B42-9CB4-903A572AED09'

The SQL Server Profiler also shows a big improvement over the previous query:

Before:

image

After:

image

The difference between the two queries, related to the effort done by SQL Server to process them, is astonishing. If the 2 tables referred were very large, having a lot of users accessing the ASP.NET application would bring the SQL Server eventually to its limits.

By also noticing that some queries executed twice for the same request and by introducing caching, the performance gain is considerable.

Important to this type of improvement described earlier is that it’s risk free. It is very easy to rewrite these queries, without having to think about introducing possible bugs.

TFS 2008 Permission Issues

No idea why, but today I was unable to give permissions to a domain user on a specific projects. Normally, all it would take is to add the user to the [Project]\Contributors group from within Visual Studio. I did that, but the user could not see the sources in Source Control Explorer. He received this error:

Either source control has not been configured for this team project or you do not have permission to access it. Would you like to create the source control folder, $/ProjectName ?

After some more experiments, I run the following command line to check his permissions for the project:

   1: tf perm $/ProjectName /r

He did not show up, like the rest of the users. So I tried to give him permissions through the command line:

   1: C:\Program Files\Microsoft Visual Studio 9.0\VC>tf perm /allow:* /server:ServerName /user:UserName $/ProjectName

After this he showed up using the first command line. Then I had to add him again to the [Project]\Contributors group and everything was fine after that.

Only problem I have right now is that I don’t know what caused all these troubles.

Windows Live Writer, WordPress, Tweet It plugin

Using Live Writer for blog posting, WordPress as the actual blog host and Tweet It plugin for Live Writer.

Are there better options for doing blogs and tweets at the same time for WordPress?

Edit: actually the plugin’s name is Twitter Update

Migrating from old notebook to new notebook

Yesterday I tried to migrate from Toshiba Satellite L300 to L500. I made some plans, did some research and noticed it will take a lot of time to reinstall and to migrate the settings from all Applications I have installed (in Add/Remove Programs I have 350 items!). So I searched for a tool to automate this process and found PCmover. Installed it on both notebooks, both notebooks runnging on Windows7, started migrating everything through network and left it running, while continuing work normally on the old notebook. After 12 hours it finished migrating everything (50GB, compressed to about 20GB) and I was surprised of how much it managed to do on its own. There were problems with the settings of some programs (like Total Commander Ultima Prima, Firefox and Visual Studio), but I was very happy with the results. Also, everything I did after starting the migration process was not migrated (like chat history and settings I changed during the migration took place).

I also found out a good tip: make sure that on the new computer there are no installed programs. PCmover did not manage (or intentionally didn’t) upgrade the settings for the already installed apps.

Finally I erased everything from the new notebook and sticked to the old one, because it has a taller screen, a nicer keyboard (without the num pad) and because… well, I’m to used to the old one and the performance improvement I would have gained was not worth the trouble.

But nonetheless, PCmover is something I would recommend!

Migration to WordPress

Just migrated to WordPress from Live Spaces. It’s interesting that Microsoft admits that others are better, e.g. WordPress offers a better blogging experience. This also shows (as so many other sites) that you can be even better than Microsoft in your specific area.