is "where (ParamID = @ParamID) OR (@ParamID = -1)" a good practice in sql selection

Question

i used to write sql statments like

select * from teacher where (TeacherID = @TeacherID) OR (@TeacherID = -1)

read more

and pass @TeacherID value = -1 to select all teachers

now i'm worry about the performance can you tell me is that a good practice or bad one?

many thanks

Answer 1

If TeacherID is indexed and you are passing a value other than -1 as TeacherID to search for details of a specific teacher then this query will end up doing a full table scan rather than the potentially far more efficient option of seeking into the index to retrieve the details of the specific teacher...

... Unless you are on SQL 2008 SP1 CU5 and later and use the OPTION (RECOMPILE) hint. See Dynamic Search Conditions in T-SQL for the definitive article on the topic.

Answer 2

We use this in a very limited fashion in stored procedures.

The problem is that the database engine isn't able to keep a good query plan for it. When dealing with a lot of data this can have a serious negative performance impact.

However, for smaller data sets (I'd say less than 1000 records, but that's a guess) it should be fine. You'll have to test in your particular environment.

If it's in a stored procedure, you might want to include something like a WITH RECOMPILE option so that the plan is regenerated on each execution. This adds (slightly) to the time for each run, but over several runs can actually reduce the average execution time. Also, this allows the database to inspect the actual query and "short circuit" the parts that aren't necessary on each call.

If you are directly creating your SQL and passing it through, then I'd suggest you make the part that builds your sql a little smarter so that it only includes the part of the where clause you actually need.

---

Another path you might consider is using UNION ALL queries as opposed to optional parameters. For example:

SELECT * FROM Teacher WHERE (TeacherId = @TeacherID)
UNION ALL
SELECT * FROM Teacher WHERE (@TeacherId = -1)

This actually accomplishes the exact same thing; however, the query plan is cacheable. We've used this method in a few places as well and saw performance improvements over using WITH RECOMPILE. We don't do this everywhere because some of our queries are extremely complicated and I'd rather have a performance hit than to complicate them further.

Ultimately though, you need to do a lot of testing.

---

There is a second part here that you should reconsider. SELECT *. It is ALWAYS preferable to actually name the columns you want returned and to make sure that you are only returning the ones you will actually need. Moving data across network boundaries is very expensive and you can generally get a fair amount of performance boost simply by specifying exactly what you want. In addition if what you need is very limited you can sometimes do covering indexes so that the database engine doesn't even have to touch the underlying tables to get the data you want.

Answer 3

If you're really worried about performance, you could break up your procedure to call on two different procs: one for all records, and one based on the parameter.

If @TeacherID = -1
   exec proc_Get_All_Teachers
else
   exec proc_Get_Teacher_By_TeacherID @TeacherID

Each one can be optimized individually.

It's your system, compare the performance. Consider optimizing on the most popular choice. If most users are going to select a single record, why hider their preformance just to accomodate the few that selct all teachers (And should have a reasonable expectation of performance.).

I know a single select query is easier to maintain, but at some point ease of maintenance eventually gives way to performance.