Query performance comparision

[Mike Wang]

How could I tell the performance difference between two queries:

One is:
select * from table
where Lower(colomnname) = 'value'

The other is:
select * from table
where colomnname = 'value'

Basically the difference is in lower() function, how much this function will affect the query performance.
Is there a formal way to test it out, or by any logic.
Thanks, Mike

[Ray Miao]

If your sort order is non case sensitive, you don't need lower function. Otherwise, the first one will take longer since system have to do some ascii code convertion and the result will be different.


------------
Mike Wang at 1/19/00 2:35:57 PM


How could I tell the performance difference between two queries:

One is:
select * from table
where Lower(colomnname) = 'value'

The other is:
select * from table
where colomnname = 'value'

Basically the difference is in lower() function, how much this function will affect the query performance.
Is there a formal way to test it out, or by any logic.
Thanks, Mike

[Mike Wang]

Thanks, Ray.
I write a SP with while loop and find out the run time for 1000 times.
The statement is like this:

select getdate()
Declare @count int
set @count = 0
while (@count< 1000) begin
select * from AA_Customers
where lower(email) =&#39;mikew@ingredients.com&#39;
order by TimeStamp
set @count = @count +1
End
select getdate()

Thanks for your help, I got it. Mike

------------
Mike Wang at 1/19/00 2:35:57 PM


How could I tell the performance difference between two queries:

One is:
select * from table
where Lower(colomnname) = &#39;value&#39;

The other is:
select * from table
where colomnname = &#39;value&#39;

Basically the difference is in lower() function, how much this function will affect the query performance.
Is there a formal way to test it out, or by any logic.
Thanks, Mike