Replace temp table with inline table-value function

PREFACE: We are getting rid of the temporary tables so I don't need to be
convinced not to use them.
In our current system we have a pattern where a temporary table is created
in one or more "calling" procedures and populated with selected keys of a
table and in the "called" procedure, those keys (from the temporary table)
are joined to a set of tables to produce a detail result set. Multiple
"calling" procedures exist that populate the temp key table based on various
criteria, but they all call the same "called" procedure which centralizes
the logic for pulling together the details.
This method causes concurrency problems because the "called" procedure is
re-compiled every time because it references a temporary table defined in
another procedure.
The method I have come up with to get rid of the temporary tables but to
still centralize and re-use the detail logic is as follows:
I have created an inline table-value function that replaces the common
"called" procedure in the above scenario. Now in the "calling" procedures,
instead of populating a temp table with keys and calling the "called"
procedure, they simply join the criteria with the user defined function,
selecting the needed fields from the results. Looking at the query plan,
this seems very optimal because it appears that the whole query (the key
criteria and the user-defined function statements) are merged together and
an execution plan is generated for them as a whole (instead of as 2 discrete
statements), giving me the best of both worlds: centralized, re-usable
logic, and a good execution plan.
My question is: Is there anything inherently non-scalable about using SQL
Server 2000's inline table-value function that will burn me under heavy
load?
Thanks,
Mike Jansen
(Abbreviated DDL follows)
OLD WAY
---
CREATE TABLE dbo.Entities
(
entity_pk int IDENTITY(100, 1) NOT NULL CONSTRAINT pk_Entities PRIMARY
KEY,
blah
blah
)
GO
CREATE PROCEDURE dbo.spGetEntityDetails
AS
SELECT
E.entity_pk, E.blah, E.blah, D.blah, D.blah
FROM
#EntityList E
INNER JOIN EntityDetails D ON E.entity_pk = D.entity_pk
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'one kind'
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'another kind' AND ...
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
NEW WAY
---
CREATE FUNCTION dbo.fnGetEntityDetails()
RETURNS TABLE
RETURN
(
SELECT D.entity_pk, D.blah, D.blah, D2.blah, D2.blah
FROM EntityDetails D INNER JOIN EntityDetails2 D2 ON ....
)
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'one criteria' AND ...
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'another criteria' AND ...Noting to self that my descriptions can be a little long (and hence take too
long to read...), here's my question succinctly:
Is there anything inherently non-scalable about using SQL Server 2000's
inline table-value function that will burn me under heavy
load?
Thanks,
Mike|||> Is there anything inherently non-scalable about using SQL Server 2000's inline table-valu
e
> function that will burn me under heavy
> load?
Not that I know of. I haven been told that they are optimized and used in th
e same way as views (and
they were called parametized views during early stages of development of SQL
Server 2000). I can't
offer proof or similar, I'm afraid, but that are my experiences and what I h
ave been told.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O19%23vR5kFHA.1044@.tk2msftngp13.phx.gbl...
> Noting to self that my descriptions can be a little long (and hence take t
oo long to read...),
> here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's in
line table-value
> function that will burn me under heavy
> load?
> Thanks,
> Mike
>|||Mike Jansen wrote:
> Noting to self that my descriptions can be a little long (and hence take t
oo
> long to read...), here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's
> inline table-value function that will burn me under heavy
> load?
I agree with Tibor.
A couple of years ago, I did a good bit of tuning work on a system that
made heavy use of udf's. My experience was positive with inline
table-valued functions. The plans produced looked to me like the
optimizer treats them as it would a view or a derived table. It can
"see inside" them and optimize to the base table level. I think I saw
this behavior even when nesting functions.
Multistatement table-valued functions, on the other hand, seemed to be
a black box to the optimizer. That makes sense. How could he (the
optimizer) evaluate the logic that could be inside a multi-statement
function. Instead, it uses a table scan of whatever table variable is
returned.
Best of luck
Payson

> Thanks,
> Mike