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 ....

Addendum:

Inline table-value function was chosen over a view because the actual
implementation has parameters to the function (which you can't do in a
view).

Mike

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

Not that I know of. I haven been told that they are optimized and used in the 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 have been told.

Show quoteHide quote

Mike Jansen wrote:
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

Show quoteHide quote

Creating a trigger using a cursor
JDBC client - CURSOR as OUT parameter -supported ?
Just Venting
Recyle an identity column number when max is reached
Nested sets in SQL - inventor?