Hi David,

I'm surprised to see so little comments yet. But who knows, maybe I'm not the only one with a reading backlog.

In the beginning of your article, you write that RAP rewards developers for creating their foreign keys by generating lookup routines. But in the rest of the article, you only describe routines generated upon primary keys and unique constraints. So technicallly, the developer can still get away with not defining the foreign key, as long as the PK and UQ constraints are defined.

More important is that the performance of your generated code on larger tables will be miserable. This is because you use multi-statement table-valued functions to retrieve rows. These are bad news for the optimizer - it has no other option but to execute them first, store the results, then combine these (unindexed) result sets for thte final result. Ouch!

You should convert to inline table-valued functions. These, like views, are treated as macros - the reference to the function is first replaced by its definition, and only then will the optimizer be invoked to work out an execution plan. You'll see a major performance gain!

As an example:

create function UFTBadmUser##PK

(

-- declare the 'As Of' parameter

@AsOf datetime,

-- declare other parameters

@userid bigint

)

returns table

as

return (select UserId,

LoginName,

Notes,

AuditDate,

AuditUserId,

AuditStatus

from TBadmUser

where UserId = @userid

and @AsOf is null

union all

select UserId,

LoginName,

Notes,

AuditDate,

AuditUserId,

AuditStatus

from TBadmUser#

where UserId = @userid

and AuditStatus <> 'D'

and A.AuditDate = (select max(AuditDate) from TBadmUser#

where UserId = A.UserId

and AuditDate <= @AsOf)

and @AsOf is not null

);

Another possible performance optimization is to create seperate function for querying live and archive data. That reduces complexity in the functions (making it easier for the optimizer to come up with an efficient plan). In my experience, it is quite uncommon to use the same queries on both live and archived data. (And since you generate the code, you could also go the whole way and simply generate functions for only live, for only archive, and for the combination, with a note to the developer to only use the latter when neeed, because the other perform better).