Query Running Longer by Adding Unused Where Conditions

Query running longer by adding unused WHERE conditions

Shouldn't SQL Server realize at
runtime that those conditions will
never be met and ignore them entirely?

No, absolutely not. There are two factors at play here.

1. SQL Server does not guarantee boolean operator short circuit. See On SQL Server boolean operator short-circuit for an example showing clearly how query optimization can reverse the order of boolean expression evaluation. While at a first impression this seems like a bug to the imperative C like programming mind set, it is the right thing to do for declarative set oriented world of SQL.

2. OR is the enemy of SQL SARGability. SQL statements are compliled into an execution plan, then the plan is executed. The plan gets reused between invocations (is cached). As such the SQL compiler has to generate one single plan that fits all separate OR cases (@AuthorType=1 AND @AuthorType=2 AND @AuthorType=3). When it comes to generating the query plan is it exactly as if @AuthorType would have all values at once, in a sense. The result is almost always the worst possible plan, one that cannot benefit any index because the various OR branches contradict each other, so it ends up scanning the whole table and checking rows one by one.

The bestthing to do in your case, and any other case that involves boolean OR, is to move the @AuthorType outside the query:

IF (@AuthorType = 1)
  SELECT ... FROM ... WHERE ...
ELSE IF (@AuthorType = 2)
  SELECT ... FROM ... WHERE ...
ELSE ...

Because each branch is clearly separated into its own statement, SQL can create the proper access path for each individual case.

The next best thing is to use UNION ALL, the way chadhoc already suggested, and is the right approach in views or other places where a single statement is required (no IF is permitted).

Query slows down if a unused join is removed

After a day or two SQL Server has laid out a new query-plan which make the two almost equally fast. Still don't know why.

Afterwards I switched to clustered columstore index on my main table giving me even faster respons time for my queries.

Why does adding this field make my query run slow?

The query below runs in 3 seconds and resolved the problem. Thanks.

select at.person_id, goc.group_id, count(at.attended) as attendance 
into #a
from core_occurrence_attendance at
join core_occurrence o on at.occurrence_id = o.occurrence_id
join smgp_group_occurrence goc on o.occurrence_id = goc.occurrence_id
where goc.group_id in (select group_id from @target_groups)
and o.occurrence_start_time between @start_date and @end_date
group by at.person_id, goc.group_id 

select p.person_id, cfm.family_id, p.nick_name, p.last_name, s.lookup_value as group_role, gm.active, g.group_id, g.group_name, 
g.active as group_active, tg.category, gc.cluster_name, coalesce(#a.attendance,0) as att--, --this field is the culprit
from smgp_group g
join smgp_group_cluster gc on g.group_cluster_id = gc.group_cluster_id
join smgp_member gm on g.group_id = gm.group_id
join core_person p on gm.person_id = p.person_id
join core_family_member cfm on p.person_id = cfm.person_id
join core_lookup s on gm.role_luid = s.lookup_id
join @target_groups tg on g.group_id = tg.group_id
left join #a on p.person_id = #a.person_id and g.group_id = #a.group_id
where tg.category = 'adults' or (tg.category = 'kids' and s.lookup_value in ('Leader-Teacher','Assistant Leader'))

Put all exclusion conditions in sql query or first get all results and then perform exclusion code in memory?

It depends, but in my general experience, if adding code in the database query and parameters going in to filter at the database results in significant reduction in data coming back, this usually means the database was actually able to use those things to make a better execution plan with a smaller working set internally (not just over the wire) and is generally better.

For instance, in a recent query I helped someone with, the query can be written to return all pairs of friends. But since, from an application point of view, only the friends of a particular person are needed on any particular page, there is no need to return extra data which is just discarded AND the query plan itself is different because there would be a smaller set on one side of a cross join. Anyway, my point is that USUALLY you are better off giving the database as much information as possible and letting it work from there.

How to remove statements from where clause in SQL

If @selectValid should be ignored when its value is an empty string, then just change this line from:

where valid = @selectValid

to

where (@selectValid = '' OR valid = @selectValid)

The question is a little ambiguous as to if @selectValid will be an empty string or null. If you want to handle both cases, then try:

where (@selectValid IS NULL OR @selectValid = '' OR valid = @selectValid)

Typically I use this approach, but if you are dealing with large tables or having performance issues with this approach, then you might want to read up a bit here:

• Proper way to handle 'optional' where clause filters in SQL?
• Query running longer by adding unused WHERE conditions

Hope that helps.

Adding datalength condition makes query slow

It takes a long time because your query needs DATALENGTH to be evaluated for every row and then the results sorted before it can return the 1st record.
If the DATALENGTH of the field (or whether it contains any value) is something you're likely to query repeatedly, I would suggest an additional indexed field (perhaps a persisted computed field) holding the result, and searching on that.

Performance of unused fields in an SQL View

The Query optimizer creates a query plan by combining the sql which defines the view with the sql you construct to "select" from the view into a single sql query. If, in this combined, optimized sql, any specific attribute (column) is not necessary, it will not be used.. specifically, if the final select does not require it, it will not be sent over the wire. Also, if it is not needed by any Where clause, or Order By or anything else within the optimized sql, and all the attributes that ARE needed happen to be in an index, then that query can read only the index, and not touch the table at all... even thought he view references a column not present in the index... You can see this by looking at the query execution plan for the query.

MYSQL adding OR clause in left join slowing up the query

Use two left joins and put the results in separate columns rather than rows:

select o.order_id, o.total, cb.customer_email, so.customer_email
from orders o left join
     customers cb
     on o.bill_email = cb.customer_email left join
     customers cs
     o.ship_email = cs.customer_email
where o.order_date >= '2020-06-01';

Note that the date() function is not needed.

That said, this seems more easily expressed as:

select o.order_id, o.total, o.bill_email
from orders o 
where o.order_date >= '2020-06-01'
union all
select o.order_id, o.total, o.ship_email
from orders o 
where o.order_date >= '2020-06-01' and s.ship_email <> o.bill_email;

---

Related Topics