SQL Server Performance Forum – Threads Archive

Order of conditions in OR statement

Probably a stupid question this but what i want to know is: in SQL does the order in which your conditions come in an OR statement have an impact on performance? For example i have a store procedure (see code below) which takes quite a few optional parameters relating to primary/foreign keys where in general only one or two of them will be supplied. CREATE PROCEDURE ex_sel_Result_Individual
@ResultID as int,
@HeatID as int = 0,
@MeetingEventID as int = 0,
@MeetingSportID as int = 0,
@MeetingID as int = 0,
@EventID as int = 0,
@SportID as int = 0,
@RegID as int = 0,
@HeatEntryID as int = 0,
@EventEntryID as int = 0,
@IslandID as int = 0 AS SELECT
r.*
FROM
ex_view_Result_Individual r
WHERE
(r.ResultID = @ResultID OR @ResultID = 0)
AND
(r.HeatID = @HeatID OR @HeatID = 0)
AND
(r.MeetingEventID = @MeetingEventID OR @MeetingEventID = 0)
AND
(r.MeetingSportID = @MeetingSportID OR @MeetingSportID = 0)
AND
(r.MeetingID = @MeetingID OR @MeetingID = 0)
AND
(r.EventID = @EventID OR @EventID = 0)
AND
(r.SportID = @SportID OR @SportID = 0)
AND
(r.RegID = @RegID OR @RegID = 0)
AND
(r.HeatEntryID = @HeatEntryID OR @HeatEntryID = 0)
AND
(r.EventEntryID = @EventEntryID OR @EventEntryID = 0)
AND
(r.IslandID = @IslandID OR @IslandID = 0)
ORDER BY
r.MeetingID,
r.SportDesc,
r.EventDesc,
r.Ordering,
r.ResultPos,
r.DisplayOrder,
r.IslandDesc,
r.LastName,
r.Firstname
GO Would it be more efficient if i were to rearrange the OR statements in the WHERE clause so it tested whether the parameter was zero first (as this is more likely)? p.s. i realise using multiple parameters to a store procedure like this isn’t good for query optimisation but i need the flexibility thanks in advance for your time/assistence Andrew

The order in the Where clause will not make any ineffeciency in the performance. Madhivanan

as above, however your proc will probably have a table scan execution plan because sql server does not understand your really just looking for a few rows? try rewriting to move as much logic out of the query as possible
ex.
IF @ResultID <> 0 etc
SELECT xx FROM xx WHERE r.ResultID = @ResultID try to identify the most frequent combinations of not zero calls and have explicit statements for those,
and also make it is impossible to feed all 0’s, ie, each statement guarantees atleast 1 definite SARG

thank you both very much for the applies above. I have done a bit of experimenting this morning running SQL Profiler to get the duration of the query under the most common parameters. As stated rearranging the OR condtions made no difference, i just thought perhaps SQL might have had something like in java where | means OR, but || means OR but don’t bother to evaluate the second if the first is true. When i rearranged it for the most common condition of just the second parameter being supplied as below (also removing unnecessary order bys) duration went up from ~16 to ~50, but this is probably because the system hasn’t got all the data in it yet and only has a few hundred rows (at the most however it will be less than 10k) so doing the IF takes longer. Doing it with IFs is definitely something i’ll bear in mind however and will be able to look into further when i do the stress testing on the website the database is for. CREATE PROCEDURE ex_sel_Result_Individual
@ResultID as int,
@HeatID as int = 0,
@MeetingEventID as int = 0,
@MeetingSportID as int = 0,
@MeetingID as int = 0,
@EventID as int = 0,
@SportID as int = 0,
@RegID as int = 0,
@HeatEntryID as int = 0,
@EventEntryID as int = 0,
@IslandID as int = 0
AS IF @HeatID != 0
BEGIN
SELECT
r.*
FROM
ex_view_Result_Individual r
WHERE
r.HeatID = @HeatID
ORDER BY
r.Ordering,
r.ResultPos,
r.DisplayOrder,
r.IslandDesc,
r.LastName,
r.Firstname
END
ELSE
BEGIN
SELECT
r.*
FROM
ex_view_Result_Individual r
WHERE
(@ResultID = 0 OR r.ResultID = @ResultID)
AND
(@HeatID = 0 OR r.HeatID = @HeatID)
AND
(@MeetingEventID = 0 OR r.MeetingEventID = @MeetingEventID)
AND
(@MeetingSportID = 0 OR r.MeetingSportID = @MeetingSportID)
AND
(@MeetingID = 0 OR r.MeetingID = @MeetingID)
AND
(@EventID = 0 OR r.EventID = @EventID)
AND
(@SportID = 0 OR r.SportID = @SportID)
AND
(@RegID = 0 OR r.RegID = @RegID)
AND
(@HeatEntryID = 0 OR r.HeatEntryID = @HeatEntryID)
AND
(@EventEntryID = 0 OR r.EventEntryID = @EventEntryID)
AND
(@IslandID = 0 OR r.IslandID = @IslandID)
ORDER BY
r.MeetingID,
r.SportDesc,
r.EventDesc,
r.Ordering,
r.ResultPos,
r.DisplayOrder,
r.IslandDesc,
r.LastName,
r.Firstname
END
GO
p.s. this site is great by the way, have learn’t loads to improve my DB knowledge from reading the articles and forum over the last few days. You’ll make a DBA of this software developer yet!

The other option isnt much better and that is to use dynamic sql. From what I can see, you are allowing your users to select what they will or wont search for. You can either do it this way, or build the where clause dynamically.

With the AND conditions join each OR set you would not be able to perceive any difference anyway. And even if the whole thing was a giant set of OR conditions with no ANDs it would imperceiveable but I can guarantee you that shortcircuiting occurrs in ORs so the first true value returns without futher checking. SELECT
*
FROM
Northwind.db :confused:

rders
WHERE
1>0
OR
1/0 > 0 You will notice that because 1 is always greater than 0 it will never evaluate the 1/0 > 0 which would throw a divide by 0 error. But again it would be inperceviable you would notcie a difference (except when errors occurr) unless maybe the system was taxed heavily.

You could use derived tables to do the filtering on just one column, which may improve speed for each individal criteria item, and use inner joins which will give you the same result as AND-combined criteria – I’m assuming an KeyID identity column here: SELECT T1.*
FROM ex_view_Result_Individual AS T1
INNER JOIN (SELECT r.* FROM ex_view_Result_Individual r WHERE r.HeatID = @HeatID OR @HeatID = 0) AS T2
ON T1.KeyID = T2.KeyID
etc., etc.
WHERE T1.ResultID = @ResultID OR @ResultID = 0
ORDER BY T1.MeetingID, T1.SportDesc, T1.EventDesc, T1.Ordering, T1.ResultPos T1.DisplayOrder, T1.IslandDesc, T1.LastName, T1.Firstname I’d join each derived table with T1

]]>