User Defined Better than Stored Proceudre!!!

[Maximus]

Hi everyone, this might be a repost but its a different topic altogether.
I have done the following.
I have a table which contains referenec keys to 3 other tables.

from eternity we are taught that stored procedure are better.
agreed.

now why wud this happen.

If i make a stored procedure ,sp_A

whose only statement is


select A,B,C from tableX innerjoin table Y innerJOin tableZ



assume the above statement is correct with reference to joins and a,b,c are values from table X, tably Y and tableZ respectively.


Now if i were to copy the same above stament and make a user defined function which returns a table
i will call it fn_A


now i run them in the query analyzer in a batch and see the execution plan.


here is the surpirsing thing

the stored proecure relatiev cost is 85.32% and the cost of the user defined function is 14.68%.


Shouldnt be the opposite way???

[rmiao]

Depends. You can check details in execution plan, or set statistics io on to see which one has less logical and/or physical read/write.

[Maximus]

the stored proecure has 0.000785 CPU while the user defined function has 0.0000823
Really strange and its the same sql statement.

[nosepicker]

Did you name your stored procedure starting with "sp"? And are you using SQL Server? Stored procedures that start with "sp" and are not in the master db will take longer to execute because SQL Server will assume that it exists in the master db and will look for it there first.

[Maximus]

no, the stored procediure name is "tp_test"

[skhanal]

Since cost is not Absolute cost, it is relevant to current sql batch, so it can't be compared with another batch.

Only good measure is logical reads/physical reads.