SQL-Server Query with Optional Parameters Help

1

I have a large table that I am trying to write a query on. I created some optional parameters @PSID and @ClockNumber.

I always thought that the first query would work for this.

However, since my table is quite large, I discovered the 1st query runs for several seconds where the 2nd query is super-fast. Is there a way to rewrite this query with optional parameters? I don't want to write dynamic sql or multiple queries. This is just a sample query.

Sample Query 1
Declare @PSID As VarChar(3) = 'CLH'
Declare @ClockNumber As VarChar(5) = '00468'

Select * From dbo.Employee
Where 1 = 1
And (@PSID = '' Or PSID = @PSID)
And (@ClockNumber = '' Or ClockNumber = @ClockNumber)
…

Sample Query 2
Declare @PSID As VarChar(3) = 'CLH'
Declare @ClockNumber As VarChar(5) = '00468'

Select * From dbo.Employee
Where 1 = 1
And (@PSID = PSID)
And (@ClockNumber = ClockNumber)
…

2

Are you running this as an adhoc query or is it part of a stored proc where the @PSID and @Clocknumber are input parameters? If it is the latter, it is likely that the stored proc is reusing a query plan which is inefficient for some set of input parameters.

The recommended way to get around this problem (generally referred to as "Parameter Sniffing" problem) is to use dynamic SQL. There are safe ways to do it, which you can find if you search the web. If you don't want to do that, you might try "with recompile" option. See here

3

I'm pretty new to SQL and am trying hard to learn as much as I can -- what is the point of the "1 = 1", which would always evaluate to true? Thanks.

4

Since 1 always = 1, the first condition is always true. This way your other Where conditions can all start with the word "And". Otherwise, the first condition must not include the word And. This helps if you might comment out or delete conditions. You don't have to worry about whether or not to use the word and in the condition.