January 16, 2003 at 2:11 am
We have some fairly complex User Defined Functions that select data from various tables and return this information as a Table
The overall query does an inner join to the table returned by the function.
e.g.
Select id from tablea inner join dbo.myudf(@param) on tablea.id = myudf.id
In this case 'myudf' selects from several tables in order to return a table of id's that match @param.
When setting STATISTICS IO on, the IO performed by the UDF is not shown.
Also, the run-time of the overall query is 6 times longer when STATISTICS IO is ON, compared to the same query when STATISTICS IO is off.
The same is true for running the query with and without Profiler running (it takes much longer when profiler is running). I suspect the reason for the poor performance in Profiler is that it treats each invocation of the function as a Stored Procedure call, and in the particular query we are doing, the function is invoked a large number of times.
Does anybody have any idea how to get meaningful IO stats that include the IO performed by the function?
Why is the performance badly affected when STATISTICS IO or Profiler are running.
January 20, 2003 at 8:00 am
This was removed by the editor as SPAM
January 24, 2003 at 1:09 pm
quote:
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Simply run the SQL that comprises the UDF within a QA window, with STATS IO ON.
January 28, 2003 at 11:05 pm
I have considered this some time ago, and basically concluded you cannot get stats on such functions (multi-statement table-valued functions).
If you do a "Show Query Plan" on something like
Select id from tablea inner join dbo.myudf(@param) on tablea.id = myudf.id
The function appears as a 'table scan', it does not reflect the operations WITHIN the function. So IO Statistics will never include the internals of the function.
I believe that tablefunctions really just create a hidden temp table that gets joined into the query. It explains why you cannot have columns as parameters -- the function-table must be generated BEFORE the outer query can join to it. It means the ON clause will NOT limit the amount of work the function has to do -- all data is generated first, then ON throws away non-matches.
I can only suggest you create the equivalent SP -- one that populates a temp table, the temp table must be created by the calling procedure -- and tune that, then convert back to the function call.
Use an INLINE table-valued function if at all possible - they DO get flattened into the overall query plan, and Join-ON limits the work they have to do - just like a view.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply