Thank you for the response,

>Short answer, you included enough information. Don't think of a

selectable

>stored procedure as a persistent database structure or a temporary

table.

>It isn't. It's one row at a time output to a buffer. Once the row has

>been fetched by the caller (in this case, the query engine) it is

discarded

>and the caller then asks for the next row.

>So go figure how this behaves in an outer join and how you triggered

off 9 million fetches for a join

>that had 7000 actual matches.

Ok,



What I noticed is on the inner join it selects from the larger result
set executes each record joining to the smaller result set, but on the
outer join it executes the smaller result set then executing the
selectable stored procedure for each record from the smaller result set.
Can this behavior be adjusted via a plan? If not, it's a performance
problem, IMO of course.



Inner Join:

+-----------------+-----------+-----------+-------------+---------+-----
----+---------+

| Table Name | Records | Indexed | Non-Indexed | Updates |
Deletes | Inserts |

| | Total | reads | reads | |
| |

+-----------------+-----------+-----------+-------------+---------+-----
----+---------+

| PROFILE| 0 | 222 | 0 | 0 |
0 | 0 |

| *TICKET_DATA| 0 | 2894 | 0 | 0 |
0 | 0 |

+-----------------+-----------+-----------+-------------+---------+-----
----+---------+



It's not broken out to total fetches but reads times 2 is pretty close
to the amount of fetches in this query but not exact.



Left Join:



+---------------+-----------+-----------+-------------+---------+-------
--+---------+

| Table Name | Records | Indexed | Non-Indexed | Updates |
Deletes | Inserts |

| | Total | reads | reads | |
| |

+---------------+-----------+-----------+-------------+---------+-------
--+---------+

| PROFILE| 0 | 0 | 1543 | 0 |
0 | 0 |

| *TICKET_DATA| 0 | 4465442 | 0 | 0 |
0 | 0 |

+---------------+-----------+-----------+-------------+---------+-------
--+---------+

(*) = SP_ROLLUP_TEST



4465442/1543 = 2894 (This matches the number of reads from above so that
would tell me for every record in the profile the stored procedure is
being executed )



(4465442 + 1543) x 2 gets pretty close to 9 million reads.



I would like to use a view but can I send parameters into views?



-kurt



[Non-text portions of this message have been removed]