With regards to the Constant Scan, could that not be the actual call to the function and not what the function is doing?

Example:

If dbo.GetVehicleMakeByCode('410') <> ''

...

he he, the data changed... 🙂

UDFCrispin

UDFCrispin

UDFCrispin

>>> Point at which I updated it...

UDFCrispin22

UDFCrispin22

UDFCrispin22

The cool thing about the UDF is while 1 < 2 SQL is LOCKED!

Someone want to sponser me a...

Hi,

The problem is I can't "reuse" the variable.

What I have is a loop getting vehicles, this function returns the vehicle type to me.

If the table where normal, I could just...

ok.....

Now I have a bit of time to play around with the differences.

When I use the UDF, it does a Constant Scan. I told it to use the Clustered index...

ahhhh, the penny drops.

Thanks for the help. I shall go read BOL and see what it says.

From the apparent scans, a UDF does not use the index. Does this seem...

No luck, still runs faster.

What I have noticed is the proc and UDF run the same, or close to, speed if only one is run. It's when you run...

Here are the only indexes on that table.

CREATE CLUSTERED INDEX [IX_CCMakeModel] ON [dbo].[CCMakeModel]([MakeCode]) ON [PRIMARY]

CREATE INDEX [IX_CCMakeModel_1] ON [dbo].[CCMakeModel]([ModelCode]) ON [PRIMARY]

CREATE INDEX [IX_CCMakeModel_2] ON [dbo].[CCMakeModel]([CombinedModelSfx]) ON...

Here you go.

Cheers,

Crispin

CREATE FUNCTION GetVehicleMakeByCode

(

@MakeCodeVarchar(20)

)

RETURNS Varchar(100) AS

BEGIN

Declare

@vMakeNameVarchar(100)

If IsNumeric(@MakeCode) = 1

Begin

Set @vMakeName = (Select Distinct MakeDesc From CCMakeModel Where MakeCode = @MakeCode)

End

Else

Begin

Set @vMakeName = (Select Distinct MakeDesc...

Greetings, Here's the test.

I can see why it's faster but not sure why it would be...

Crispin

StmtText ...

Hi Antares686,

Thanks for the reply.

I did what you suggested and they all slowed down. BUT, the UDF's still ran much faster.

Proc (100 times) was 1913 and UDF (100 times) was...