The Cost of Function Use In A Where Clause

Question

Post reply

The Cost of Function Use In A Where Clause

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

You must be logged in to reply to this topic. Login to reply

Michaell Grasshopper Points: 17 More actions · Answer 1

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

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

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 2

Sorry Michaell,

I dont understand the question here...

GAJ

Gregory A Jackson MBA, CSM

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Rishi Girdhar SSC Veteran Points: 200 More actions · Answer 6

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.

Thanks in advance.

Rishi

Rishi Girdhar SSC Veteran Points: 200 More actions · Answer 7

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Rishi Girdhar SSC Veteran Points: 200 More actions · Answer 9

Thanks Grant,

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

Thanks again,

Rishi