In SQL Server, I have a CLR integration based table valued function, GetArchiveImages. I call it something like this:
SELECT ...
FROM Items
CROSS APPLY GetArchiveImages(Items.ID) AS archiveimages
WHERE ...
The issue is that there is overhead for each individual call to the function.
If it could be joined with the whole table at once, the overhead would be quite minor, but since it's called once for each row, that overhead scales with the number of rows.
I don't use a stored procedure, because a table returned by a stored procedure can't be joined with anything (as far as I know).
Is there an efficient way to join tables with the results of a stored procedure or function in bulk, instead of row by row?
As the result of GetArchiveImages depends on the Items.ID SQL Server has to call the function for each item, otherwise you wont get correct results.
The only function that SQL Server can "break up" is a T-SQL Inline Table Valued Function. So if you can rewrite your CLR as a ITVF, you will get better performance.
In my experience, the overhad of calling a CLR function however is not that big. It is much more likely that you are having problems somewhere else in the query. For example, SQL Server has no idea how many rows will be returned by that function and just assumes it will be one (for each call). That can lead to misinformed decisions in other places during the optimization process.
UPDATE:
SQL Server does not allow to keep static non-constant data within a CLR class. There are ways to trick the system, e.g. by creating a static final collection object (you can add and remove items from a static collection), however, I would advise against that for stability reasons.
In you case It might make sense to create a cache table that is refreshed either automatically with some sort of (database- or file-system-) trigger or on a schedule. Instead of calling the function you can then just join with that table.