# The Cost of Function Use In A Where Clause

• Would it be the time itself is pretty much an unique value already?

So it don't produce a good index on it?

• Sorry Michaell,

I dont understand the question here...

GAJ

Gregory A Jackson MBA, CSM

• Michaell (3/5/2008)

Would it be the time itself is pretty much an unique value already?

So it don't produce a good index on it?

Absolutely not... the more unique a value it is, the better an index can be made from it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Just a test... please ignore...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Just another test... please ignore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Greg,

How can we improve the following query?

-------------------------

--------------------------

WHERE

filter 1

AND filter 2

AND CONVERT(DATETIME,A.DateValue) BETWEEN (GetDate()-30) AND (GetDate()+30)

Column DateValue in table A has datatype (varchar/nvarchar) but datetime.

Rishi

• Many times we need to use function 'MAX' in 'Select' clause specially for dates. Does anyone has a thought on different approach.

For example

select max(a.closedate) as closedate, a.offerid

from a inner join b on a.id =b.id

inner join c on b.xx = c.xx

group by a.offerid

Thanks,

Rishi

• Rishi Girdhar (3/6/2008)

Many times we need to use function 'MAX' in 'Select' clause specially for dates. Does anyone has a thought on different approach.

For example

select max(a.closedate) as closedate, a.offerid

from a inner join b on a.id =b.id

inner join c on b.xx = c.xx

group by a.offerid

Thanks,

Rishi

We did a series of tests and found, depending on how you do your indexing, TOP actually works a bit better than MAX. You have to include an ORDER BY and get the ASC/DESC right.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

• Thanks Grant,

I will try with TOP and ORDER BY and see the difference.

Thanks again,

Rishi

Viewing 9 posts - 91 through 98 (of 98 total)