Joining table UDFs in queries

Hi,
I've got a table UDF which takes two parameters and returns a table, as
follows:
CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
datetime)
RETURNS @.tblPeriodYear TABLE
(
Period tinyint,
Year smallint
)
AS
BEGIN
<snipped to save space>
RETURN
END
That works fine. However, is it possible to use this UDF as part of a query
where the input parameters come from another table?
E.g. the two input parameters I want to pass to the function are contained
within the Sales table, and I could output them as follows:
SELECT
CompanyID,
SaleDate,
<other fields>
FROM
Sales
Ideally, I'm looking for some way of combining the query on the table with
the UDF e.g.
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM
Sales
Is this even possible?
Any assistance gratefully received.
MarkI'm afraid not in SQL Server 2000. This is new functionality added in SQL
Server 2005 via the APPLY table operator, e.g.,
SELECT ...
FROM Sales AS S
CROSS APPLY ftblPeriodYear(S.CompanyID, S.SaleDate) AS F;
You can find more details here:
http://www.windowsitpro.com/Article...47145.html?Ad=1
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:eueZ2kxyFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've got a table UDF which takes two parameters and returns a table, as
> follows:
> CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
> datetime)
> RETURNS @.tblPeriodYear TABLE
> (
> Period tinyint,
> Year smallint
> )
> AS
> BEGIN
> <snipped to save space>
> RETURN
> END
> That works fine. However, is it possible to use this UDF as part of a
> query where the input parameters come from another table?
> E.g. the two input parameters I want to pass to the function are contained
> within the Sales table, and I could output them as follows:
> SELECT
> CompanyID,
> SaleDate,
> <other fields>
> FROM
> Sales
> Ideally, I'm looking for some way of combining the query on the table with
> the UDF e.g.
> SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
> FROM
> Sales
>
> Is this even possible?
> Any assistance gratefully received.
> Mark
>|||That :
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM=20
Sales=20
doesn=B4t work. :-(|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1128675411.933739.210250@.g14g2000cwa.googlegroups.com...

>That :
>SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
>FROM
> Sales
>
>doesnt work. :-(
Er, yeah I know - that was the reason for my post...|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:unelksxyFHA.3720@.TK2MSFTNGP14.phx.gbl...

> I'm afraid not in SQL Server 2000. This is new functionality added in SQL
> Server 2005 via the APPLY table operator, e.g.,
Thanks - I was vaguely aware that there was something like this in SQL
Server 2005, but wondered if it had an equivalent in 2000...

Joining on a pivot table is sluggish

I am building a table-value UDF that joins a significant-sized pivot table (ends up with 10000 rows) to another significant-size table. The query takes about 3 minutes, and I noticed that the memory allocation for the sqlserver process goes through the roof.

I tried selecting the pivot table into a temp table, and joining on that, and it is real fast - about 2 seconds. However, since you can't use a temp table in a UDF, I needed to use a table variable instead. Changing the temp table to a table variable gave me the same slow results - about 3 minutes. Does anyone have a clue why this would make any difference? My SQL looks like the following:

Code Snippet

declare @.temp table

(

[PrimaryKey] bigint,

[CategoryName] varchar(15),

[Description] varchar(255),

[LongDescription] varchar(1000)

)

insert into

@.temp

select

PrimaryKey,

convert(varchar(15),[CategoryName]),

convert(varchar(255),[Description]),

convert(varchar(1000),[LongDescription])

from

history pivot

(

max(oldvalue)

for colname

in ([CategoryName],[Description],[LongDescription])

) as tn

select

*

from

Categories

left outer join @.temp on Categories.CategoryID=@.temp.PrimaryKey

I am running SQL Server 2005 SP2.

Try putting a primary key constraint on your Primary Key Column. No guarantees, but it might help.

Code Snippet

declare @.temp table

(

[PrimaryKey] bigint PRIMARY KEY,

[CategoryName] varchar(15),

[Description] varchar(255),

[LongDescription] varchar(1000)

)

If that doesn't help, check out the plan of the two queries (and post them), it might show something. Is categoryId the primary key for Categories?

|||

Adding the primary key definition didn't help anything. The real problem is when inserting into the table variable, not performing the join. Once the data is in the table variable, it will work fine. The join only time the join works slowly is if I either use a CTE or a derived table to do a brute join. It seems like this in-memory operation involving a pivot table is somehow causing it to perform slowly. It's slow inserting into a table variable, or simply doing the join. The only solution I've found so far is to use a temp table, which I can't use in a UDF.

I looked at the execution plans using a table variable vs a temp table, and they are essentially the same. I'm not sure how to upload a picture onto the forum, but here are the major tasks for each:

% for temp table / % for table variable

Clustered index scan: 48% / 46 %

Hash match: 10% / 17%

Clustered index seek 41% / 35%

Another odd thing I noticed about the pivot table performance is that it works much faster if I add an extra column to the table. I.e, if I add a blank bit column to the source table, and don't define it as one of the pivoted fields, it's a lot faster. Or rather, I should say it is really slow if I don't do this (even using a temp table).

=== Edited by jehhynes @. 05 May 2007 8:08 PM UTC===
Following is the table definition for my source table that I am pivoting:

CREATE TABLE [dbo].[history](

[colname] [varchar](50) NOT NULL,

[primarykey] [bigint] NOT NULL,

[oldvalue] [sql_variant] NULL,

[perfcol] [bit] NULL

) ON [PRIMARY]

perfcol is the empty bit column i added which increased the performance

Join table with a UDF?

Hello all,
I have a UDF that returns a few fields.
The UDF accepts a 'ConnoteId' and returns a table containing 4 fields
including the same 'connoteId'. I want to be able to join this table to the
connote table and be able to pass a connoteId to the UDF.
Essentially something like this:
Select *
From Connote C
Inner Join udf_GetTimeliness(C.kConnoteId) As CT On C.kConnoteId =
CT.kConnoteId
I cant put it into a view as it invloves quite a bit of processing.
Thanks in advance
Regards
IshanIt's possible in SQL Server 2005 , using CROSS APPLY.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
news:5EEB2357-4C6F-4BFB-8565-2095077BE027@.microsoft.com...
Hello all,
I have a UDF that returns a few fields.
The UDF accepts a 'ConnoteId' and returns a table containing 4 fields
including the same 'connoteId'. I want to be able to join this table to the
connote table and be able to pass a connoteId to the UDF.
Essentially something like this:
Select *
From Connote C
Inner Join udf_GetTimeliness(C.kConnoteId) As CT On C.kConnoteId =
CT.kConnoteId
I cant put it into a view as it invloves quite a bit of processing.
Thanks in advance
Regards
Ishan|||Thanks - u made my day!!
"Tom Moreau" wrote:

> It's possible in SQL Server 2005 , using CROSS APPLY.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
> news:5EEB2357-4C6F-4BFB-8565-2095077BE027@.microsoft.com...
> Hello all,
> I have a UDF that returns a few fields.
> The UDF accepts a 'ConnoteId' and returns a table containing 4 fields
> including the same 'connoteId'. I want to be able to join this table to th
e
> connote table and be able to pass a connoteId to the UDF.
> Essentially something like this:
> Select *
> From Connote C
> Inner Join udf_GetTimeliness(C.kConnoteId) As CT On C.kConnoteId =
> CT.kConnoteId
> I cant put it into a view as it invloves quite a bit of processing.
> Thanks in advance
> Regards
> Ishan
>