Most of SQL queries have no math operation on where clause.
What is wrong having them on 'where clause'? Is there any performance issue?
Eg:
SELECT * FROM Employee WHERE Salary*3 = 5000
+7
A:
If a where clause can utilise an index, it is often (but not always) faster. Using a math operation on a field will stop the index from being utilised.
For example, if you had a table with a million rows, and a date column that was indexed, query 1 here would by far outperform query 2 (they both retrieve all rows where the date is in the last 7 days):
query 1:
select date from table where date > dateadd(d, -7, getdate())
query 2:
select date from table where dateadd(d, 7, date) > getdate()
In your example, the query would be far better as:
select * from employee where salary = (5000 / 3)
ck
2009-04-21 10:43:08
+1 for a very good sample...
Lucero
2009-04-21 10:48:58
Would have given +2 if I could. Excellent example describing the core issue and solution.
CDR
2009-04-21 11:19:01
+1
A:
Huh? No, nothing wrong at all with having math in a where clause. A where clause can contain any expression that uses column names, functions, or constants, as long the expression is a legal predicate.
ck gives the following two examples:
select date from table where date > dateadd(d, -7, getdate())
select date from table where dateadd(d, 7, date) > getdate()
and explains that the first is likely faster because it can use an index.
ck's correct, but it should also be noted that the first can be faster because dateadd(d, -7, getdate()) is a constant expression (it only needs to be evaluated once, no matter the number of rows) while dateadd(d, 7, date) in the second needs to be evaluated for each row.
But both of ck's examples are examples of "math" (expressions) being used in a where clause, which is what the OP is asking about.
tpdi
2009-04-21 10:44:25
Yes, but as soon as you have a math clause in your WHERE, SQL Server will not be able to use indexes anymore! That's a MAJOR drawback.
marc_s
2009-04-21 10:58:46
@marc_s: Not necessarily, however in the majority of circumstances using the index will be better. In data mining, indexes could be a hindrance if the entire table needs to be analysed.
ck
2009-04-21 11:03:24
First, we need to code what works, and optimize if that proves not to be fast. Yes, an expression may mean an index can't be used, but if we need to use an expression, then we need to use it.
tpdi
2009-04-21 11:08:28
An expression WILL exclude the index - not "may". Plus: most of the time, you can easily reorganize your WHERE clause so that you don't have math expression where an index COULD help. It's just a matter of knowing to check for it - it *IS* a performance killer (at least in most OLTP scenarios).
marc_s
2009-04-21 16:46:32
A:
Can you give an example... you can using a HAVING clause after the WHERE clause with certain functions
SELECT filter, count(*) FROM Test GROUP BY filter HAVING count(*) > 1
The same logic applies if you want to use HAVING clauses on MAX, MIN, etc...
For your example you need to surround Salary*3 in parenthesis
WHERE (Salary * 3) > 5000
Eoin Campbell
2009-04-21 10:45:13