StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>EDIT: Adding <a href="https://stackoverflow.com/questions/1709348/query-running-longer-by-adding-unused-where-conditions/1709698#1709698">link to similar question/answer with context as to why the union / if...else approach works better than OR logic</a> (FYI, Remus, the answerer in this link, used to work on the SQL Server team developing service broker and other technologies)</p> <p>Change from using the "or" syntax to a union approach, you'll see 2 seeks that should keep your logical read count as low as possible:</p> <pre><code>SELECT * FROM dbo.MyTableName t1 WHERE t1.ThisField = 'test' AND @MyOptionalParam IS NULL union all SELECT * FROM dbo.MyTableName t1 WHERE t1.ThisField = 'test' AND t1.MyField = @MyOptionalParam </code></pre> <p>If you want to de-duplicate the results, use a "union" instead of "union all".</p> <p>EDIT: Demo showing that the optimizer is smart enough to rule out scan with a null variable value in UNION:</p> <pre><code>if object_id('tempdb..#data') > 0 drop table #data go -- Put in some data select top 1000000 cast(a.name as varchar(100)) as thisField, cast(newid() as varchar(50)) as myField into #data from sys.columns a cross join sys.columns b cross join sys.columns c; go -- Shwo count select count(*) from #data; go -- Index on thisField create clustered index ixc__blah__temp on #data (thisField); go set statistics io on; go -- Query with a null parameter value declare @MyOptionalParam varchar(50); select * from #data d where d.thisField = 'test' and @MyOptionalParam is null; go -- Union query declare @MyOptionalParam varchar(50); select * from #data d where d.thisField = 'test' and @MyOptionalParam is null union all select * from #data d where d.thisField = 'test' and d.myField = '5D25E9F8-EA23-47EE-A954-9D290908EE3E'; go -- Union query with value declare @MyOptionalParam varchar(50); select @MyOptionalParam = '5D25E9F8-EA23-47EE-A954-9D290908EE3E' select * from #data d where d.thisField = 'test' and @MyOptionalParam is null union all select * from #data d where d.thisField = 'test' and d.myField = '5D25E9F8-EA23-47EE-A954-9D290908EE3E'; go if object_id('tempdb..#data') > 0 drop table #data go </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload