April 25, 2003 at 2:05 pm
Hi!
I got two questions
1) performance comparison between sp and udf. which faster?
2) for query the result which one will be easier and faster?
Thanks
April 28, 2003 at 8:00 am
This was removed by the editor as SPAM
April 28, 2003 at 11:26 am
It is going to be dependent on what you are trying to accomplish. The best bet is to use the execution plan and see which you beleive is better suited to solve your particular issue.
I like UDFs for some problems, like when I need a single value returned to a where clause based on some column in the query...
Michael R. Schmidt
Developer
April 29, 2003 at 9:50 am
My general rules are as follows:
1) NOONE hits tables directly from ANY application. Security is applied both to the SProx and to the Application.
2) Since ADO seems to easily call SProx (I don't know if you can even call a UDF directly from ADO???) I tend to use SProx as the "middle area" between the any App and the database.
3) I use UDF's almost exclusively to return scalers(single values) or to return a table to use as part of a WHERE clause - here's a sample:
CREATE FUNCTION dbo.CSVToTable
(
@AList varchar(8000)
)
/*
Parse a User Selected string of ID's and return a TABLE of ID's for use in a WHERE clause.
*/
RETURNS @Results table
(
ListID int NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Counter int
DECLARE @Temp char(1)
DECLARE @Current varchar(10)
SET @Counter = 1
SET @Current = ''
WHILE (@Counter < (LEN(@AList) + 2))
BEGIN
SET @Temp = SUBSTRING(@AList, @Counter, 1)
IF (@Temp <> ',') AND (@Temp is not null) AND (@Temp <> '')
SET @Current = @Current + @Temp
ELSE
BEGIN
INSERT INTO @Results VALUES(@Current)
SET @Current = ''
END
SET @Counter = @Counter + 1
END
RETURN
END
I use this so that an App can pass a String to a SProc with a list of column ID's which are then parsed and translated into a table.
So, this passed in:
'34, 12, 599'
becomes a tables like this:
Col1
----
34
12
599
and can be used like this
...
WHERE
ATable.ItsIdentityColumn IN(SELECT * FROM dbo.CSVToTable('12, 34, 599')
Or scaler values like this:
CREATE FUNCTION ToDateOnly (@ADate datetime)
RETURNS datetime AS
BEGIN
RETURN CONVERT(datetime, CONVERT(varchar, @ADate, 112))
END
or this:
CREATE FUNCTION GetDaysInYear
(
@ADate smalldatetime
)
RETURNS smallint
WITH SCHEMABINDING
AS
/*
Checking for leap-years here - non-365s
*/
BEGIN
RETURN CAST(DATEPART(y, CAST(CAST(DATEPART(yy, @ADate) AS varchar(4)) + '-12-31' AS smalldatetime)) AS smallint)
END
Use SProx for 99% of your query result sets. USF's are more "Helper functions" than "A faster replacement for SProx"
IMHO 🙂
- B
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply