default DateTime parameters to an old date, never NULL

you want to use:

CREATE PROCEDURE …
@StartDate = ‘1/1/2000’

not

CREATE PROCEDURE …
@StartDate = NULL

the reason for this is that if you use NULL, you have to put WHERE (@StartDate IS NULL OR DateValue > @StartDate) in your where clause, which screws your query optimization. if you default to an old date, you can just put WHERE (DateValue > @StartDate) so the optimizer will be happy—

Tutor.com Classroom How-To #5: providing automatic reader-to-writer lock upgrade

These How-To tips are taken from The Tutor.com Classroom: Architecture and techniques using Silverlight, WPF, and the Microsoft .NET Framework.

You can check out the Silverlight Tutor.com Classroom in "practice" mode, although the real experience is with a live tutor on the other side!

Create a class that contains a module-level System.Threading.ReaderWriterLock (e.g. m_Lock) and wrap the AcquireReaderLock() function. Then create an AcquireWriterLock() function that returns a LockCookie wrapper (SafeLockCookie), checks to see if the read lock is held, and upgrades if necessary, and a ReleaseWriterLock()function that processes the SafeLockCookie and releases the lock or downgrades back to read:

public SafeLockCookie AcquireWriterLock()
{
  //if we have a read lock, upgrade
  if (m_Lock.IsReaderLockHeld)
  {
    return new SafeLockCookie(m_Lock.UpgradeToWriterLock(TIMEOUT_MS));
  }
  else
  {
    m_Lock.AcquireWriterLock(TIMEOUT_MS);
  return null;
  }
}

public void ReleaseWriterLock(SafeLockCookie SafeLockCookie)
{
  //do we need to downgrade?
  if (SafeLockCookie != null)
  {
    m_Lock.DowngradeFromWriterLock(ref SafeLockCookie.LockCookie);
  }
  else
  {
    m_Lock.ReleaseWriterLock();
  }
}

Then have calling functions request write locks by storing a SafeLockCookie when requesting the lock and returning it when releasing:

SafeLockCookie lc = m_Lock.AcquireWriterLock();
try
{
  ...
}
finally
{
  m_Lock.ReleaseWriterLock(lc);
} 

make sure you manually stop storyboards on hidden objects

storyboards continue to run even if the object you’re animating is invisible, so you’re generally burning CPU cycles for nothing. in silverlight, you can stop any storyboard just by calling Stop:

myStoryboard.Begin();
myStoryboard.Stop();

in WPF, you need to specifically let the runtime know that you want Stop functionality when you call Begin via the isControllable paramater:

myStoryboard.Begin(this, true);
myStoryboard.Stop();

web service response caching: CacheDuration property doesn't work

if you want to cache the results from a web service call on the server, you’d think you just set the CacheDuration property the way you can with aspx pages (i.e. [WebMethod(CacheDuration=5)] and everything would be happy. turns out... no.

• web service output is NEVER cached for http-post web service requests, so you have to use http-get, which is disabled by default for web services and enabling it is not a recommended practice. great.
• browsers can stipulate “no-cache” in their request header, and the server will ignore its own cache for these requests. so even once you get http-get caching working, Firefox requests force the server to ignore its cache.
  

why this isn’t configurable on the server, i have no idea, but the short of it is: if you want real control over web service output caching, don’t use CacheDuration (i.e. write it yourself using Context.Cache, Application[] variables, or static variables). Context.Cache is a very cool class; you can specify an absolute expiration date, or dependencies on the cached content, so that the item is cleared from the cache when a dependency changes, or a sliding expiration date, like “5 seconds from last access”—

helpful sql perf queries

Ian Strike has a very interesting article on some of the perf stats that sql2005 automatically maintains:

http://msdn.microsoft.com/msdnmag/issues/08/01/SqlDmvs/default.aspx

writing OLAP queries - best practices

this is a list of some query tuning best practices i’ve compiled, with the general the goal being to avoid the problems of inefficient report queries that take a ton of time to execute.

• begin each sproc with "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED". this allows you to avoid NOLOCK hints within the query and ensures you don't accidentally forget one
• write the query one join at a time
• turn on ‘Show Execution Plan’ and study the plan with each join you add
  o look for the item taking the greatest % of execution time
  o check for thick lines (lots of data)
  o check for table scans/bookmark lookups
  
  
  
• turn on 'Set Statistics Time' and 'Set statistics IO' from the Tools...Options...Connection Properties menu
  o look for extremely high logical reads (in general, < 10,000 logical reads per table is ok)
  o total execution time for a query of average complexity on a server under average load should not exceed 60 seconds for one day's worth of data
  
  
  
• MAKE USE OF INDEXES! if you expected an index to be used that wasn't, figure out why
• it could be that you are missing columns in your join; for example, an index on Table1 on presenceid,userid,starttime will not be used for "select starttime from Table1 where presenceid = sp.presenceid", but will be used for "select starttime from Table1 where presenceid = sp.presenceid and userid = sl.userid"
• if there is no way to make use of an existing index, request that a new one be added or add columns to an existing one
• use a "covering index" to avoid bookmark lookups
  o a covering index is an index that is both for the seek (where/join clause) and the selected data (select clause)
  o include the columns in your where/join clause first, then the columns you are selecting. for example, with an index on Table1 on presenceid,userid,starttime, no bookmark lookup will be needed when you "select starttime from Table1 where presenceid = sp.presenceid and userid = sl.userid", since presenceid and userid are in the where/join clause and starttime is in the select clause
  o in general, make the covering index as wide as you need to avoid bookmark lookups (the exception to this is extremely wide varchar columns that will inflate the size of your index too much)
  
  
  
• if you still can't get the query execution time down, figure out another way to get the same data
• use different tables/joins
• use "EXISTS" instead of join
• use subquery instead of join
• use "=" not "<>", and "NOT EXISTS" instead of "NOT IN", whenever possible
• follow examples in existing sprocs that perform well

lots of other good tips here: http://www.sql-server-performance.com/transact_sql.asp