Category Archives: .NET

Shows content related to .NET

.NET Parametrized queries and sp_executesql performance improvements

Posted on by
Reply

If you fetching performance problems by executing sp_executesql consider to use in your query hint RECOMPILE. Note, that sp_executesql is used all the times when you execute parametrized queries in .NET for example. So, the need for this hint is much bigger than you think.
Initally your query can be something like 

select col1 from Test_table where col1 = @param1
order by col1

what is transfered to sql server like 

exec sp_executesql N'select col1 from Test_table where col1 = @param1
order by col1', N'@param1 int'

So your query should looks like 

select col1 from Test_table where col1 = @param1
order by col1 OPTION (RECOMPILE)

Notethat OPTION(RECOMPILE) has been added to the query. This option in some cases can boost your query performances.

Main problem here is well known in SQL Server and is called parameter sniffing, what means that SQL Server looks at the input parameters to build a query plan and if the parameters are fine for the first invocation of the query, than you are fine with the performances. But if parameters dramatically changes, than you might need a new execution plan which will not be any more index seek but index scan. With the option RECOMPILE you are requesting from SQL SErver ptimizer to generate a new plan for your query and to ignore the existing one. Thus, you ghet proper plan. Note, that using always option RECOMPILE will decrease query performances if the proper plan will be found in the cashed plans.

In my environement, this option increases some of the queries more than 100 times.

Applicationpoolidentity account – setting permissions in IIS for ASP.NET Application

Posted on by
Reply

If you have problems setting permissions on IIS 7 or higher that’S probably due to mis-understanding under which account your web site is running. By default, ApplicationPoolIdentity is used to run default application pool. When you create your new site, MyWebSite, in MyAppPool you will see in Task Manager that IIS process which runs your web site is run under MyAppPool user. Whenever a new application pool is created, the IIS management process creates a security identifier (SID) that represents the name of the application pool itself. The problem becomes real, when you need to set some permissions on your file system for this user. You won’t see it. So if you try to set permissions on file or folder by

  • Open Windows Explorer
  • Select a file or directory.
  • Right click the file and select “Properties”
  • Select the “Security” tab
  • Click the “Edit” and then “Add” button
  • Click the “Locations” button and make sure you select your machine.

if you search now for user “MyAppPool” you won’t find it. The trick is that you have to search for name “IIS AppPool\MyAppPool”. This user has been found and you can set desired permissions.

Using Events in .NET

Posted on by
Reply

Consuming events in .NET is possible on couple of ways. So, if object type Car contains an event SaySpeed it can be consumed on the following ways:

  • Usual way 
    car.SaySpeed += car_SaySpeed;

    where car_SaySpeed is function which implements SaySpeed delegate like:

    void car_SaySpeed(object sender, CarEventArgs e)
{
    ...
}
  • Another method is by using anonymous methods as 
    car.SaySpeed += delegate
			{
				...
			};
  • And a clean one, by using lambda expressions 
    car.SaySpeed += (sender, e) => { 
				... 
			};