SQL Server 2008, different WHERE clauses with one

问题:

I have a stored procedure which takes the same columns but with different WHERE clause.

Something like this.

SELECT
     alarms.startt, alarms.endt, clients.code, clients.Plant,
     alarms.controller, alarmtype.atype, alarmstatus.[text]
FROM alarms
INNER JOIN clients ON alarms.clientid = clients.C_id 
INNER JOIN alarmstatus ON alarms.statusid = alarmstatus.AS_id
INNER JOIN alarmtype ON alarms.typeid = alarmtype.AT_id

and I put the same query in 3 if's (conditions) where the WHERE clause changes according the parameter passed in a variable.

Do I have to write the whole string over and over for each condition in every if?

Or can I optimize it to one time and the only thing what will change will be the WHERE clause?

回答1:

You don't have to, you can get around it by doing something like

SELECT  *
FROM    [Query]
WHERE   (@Parameter = 1 AND Column1 = 8)
OR      (@Parameter = 2 AND Column2 = 8)
OR      (@Parameter = 3 AND Column3 = 8)

However, just because you can do something, does not mean you should. Less verbose SQL does not mean better performance, so using something like:

IF @Parameter = 1
    BEGIN
        SELECT  *
        FROM    [Query]
        WHERE   Column1 = 8
    END
ELSE IF @Parameter = 2
    BEGIN
        SELECT  *
        FROM    [Query]
        WHERE   Column2 = 8
    END
ELSE IF @Parameter = 3
    BEGIN
        SELECT  *
        FROM    [Query]
        WHERE   Column3 = 8
    END

while equavalent to the first query should result in better perfomance as it will be optimised better.

回答2:

You can avoid repeating the code if you do something like:

WHERE (col1 = @var1 AND @var1 IS NOT NULL)
OR ...
OPTION RECOMPILE;

You can also have some effect on this behavior with the parameterization setting of the database (simple vs. forced).

Something that avoids repeating the code and avoids sub-optimal plans due to parameter sniffing is to use dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ...';
IF @var1 IS NOT NULL
  SET @sql = @sql + ' WHERE ...';

This may work better if you have the server setting "optimize for ad hoc queries" enabled.

回答3:

I would probably stick with repeating the whole SQL Statement, but have resorted to this in the past...

WHERE (@whichwhere=1 AND mytable.field1=@id)
  OR (@whichwhere=2 AND mytable.field2=@id)
  OR (@whichwhere=3 AND mytable.field3=@id)

Not particularly readable, and you will have to check the execution plan if it is slow, but it keeps you from repeating the code.

回答4:

Since no one has suggested this. You can put the original query in a view and then access the view with different WHERE clauses.

To improve performance, you can even add indexes to the view if you know what columns will be commonly used in the WHERE clause (check out http://msdn.microsoft.com/en-us/library/dd171921(v=sql.100).aspx).

回答5:

Well like most things in SQL: it depends. There are a few consideration here.

• Would the the different WHEREs lead to substantially different query plans for execution e.g. one of the columns indexed but not the other two
• Is the query likely to change over time: i.e. customer requirements needing other columns
• Is the WHERE likely to become 4, then 8, then 16 etc options.

One approach is to exec different procs into a temp table. Each proc would then have its own query plan.

Another approach would be to use dynamic SQL, once again each "query" would be assigned is own plan.

A third appoach would be to write an app that generated the SQL for each option, this could be either a stored proc or a sql string.

Then have a data set and do test driven development against it (this is true for each approach).

In the end the best learning solution is probably to a) read about SQL Kalen Delaney Inside SQL is an acknowledged expert. b) test your own solutions against your own data

回答6:

I would go this way: WHERE 8 = CASE @parameter WHEN 1 THEN Column1 WHEN 2 THEN Column2 . . .