Static SQL With Dynamic Where Clause

I’ve been using this sort of syntax for a couple of years now to write dynamic where clauses:

SELECT
Col1
FROM
dbo.Table
WHERE
@param IS NULL OR Col1 = @param

Recent application timeouts at my company have us investigating SQL queries for performance issues. One of the things I have discovered is that queries like that one are prone to performance problems. That query example on a table that has 6800 rows in it caused 3869 reads when a the column being compared has an index, and the parameter was not null (query returned 1 row in 253 ms).

For whatever reason, rewriting the query like this:
SELECT
Col1
FROM
dbo.Table
WHERE
(Col1 = @param AND @param IS NOT NULL) OR (@param IS NULL)

The query only causes 106 reads and returns the same single row in 33 ms. I don’t pretend to understand why the query that looks worse and has a more complicated execution plan performs better, but it does. I got the idea to try it from this article

The degree to which the syntax difference helps depends on how the table is indexed, and the column involved. Sometimes that syntax change makes no difference at all. The same article also showed me another trick that works even better for performance, but only works for non-nullable columns and is more complicated because you have to provide appropriate @min and @max values, which may be difficult depending on the data type and data itself:

SELECT
Col1
FROM
dbo.Table
WHERE
Col BETWEEN COALESCE(@param, @min) AND COALESCE(@param, @max)

Overriding Web Service URL in web.config for Referenced Class Library

I do this so rarely, that I always forget how to do it. Sometimes in a web application, you reference a class library that uses a web service. The web service is not directly added to the web application, so nothing is in the web.config to override, and since it is a class library versus a web application, you need to add a sectionGroup so you won’t throw the yellow screen of death due to an unknown section “applicationSetttings”.

<configSections>

<sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="ErrorHandler.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</sectionGroup>

</configSections>
<applicationSettings>

<ErrorHandler.Properties.Settings>
<setting name="ErrorHandler_ErrorLoggingWebService_ErrorLoggingWebService"
serializeAs="String">
<value>http://localhost/errorloggingwebservice/errorloggingwebservice.asmx</value>
</setting>
</ErrorHandler.Properties.Settings>

</applicationSettings>