sometimes maybe here it makes sense to change a left join
into a nested select statement construct of course this can only be in a
stored procedure a trigger
but maybe this is new approach for you - we sometimes work this way in
case of bad performing left joins ...
Claus
Kurt R. Hoehn schrieb:

>Prior to the last email I explained what the static's were and how I got 9
>million fetches and it boils down to how the query optimizer is optimizing
>the plan on a left join.
>
>
>
>If a view could take parameters I would use a view but this is dynamic
>report data and hard coding dates is not an option with 5,000 users.
>
>
>
>Last email
>
>
>
>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.
>
>
>
>In TICKET_DATA we have 2894 rows of data for the dates we selected and
>depending
>
>on the type of join we have significant changes in performance and CAN that
>be adjusted
>
>via my own plan.
>
>
>Inner Join:
>| Table Name | Records | Indexed | Non-Indexed | Updates |
>| | Total | reads | reads | |
>| PROFILE| 0 | 222 | 0 | 0 |
>| *TICKET_DATA| 0 | 2894 | 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 |
>| PROFILE| 0 | 0 | 1543 | 0 |
>| *TICKET_DATA| 0 | 4465442 | 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 fetches.
>
>
>
>-kurt
>
>
>
>
>
>
>
> _____
>
>From: Helen Borrie [mailto:helebor@...]
>Sent: Sunday, August 15, 2004 11:21 PM
>To: firebird-support@yahoogroups.com
>Subject: RE: [firebird-support] Firebird has MAJOR performance issues
>
>
>
>At 10:43 PM 15/08/2004 -0400, you wrote:
>
>
>>Still wondering about this performance issue?
>>
>>
>>
>>
>>
>>
>>
>>>Don't think of a selectable stored procedure as a persistent database
>>>
>>>
>>structure or a temporary table.
>>
>>
>>>It isn't.
>>>
>>>
>>
>>
>>
>>>Use a view for this structure, not a stored procedure.
>>>
>>>
>>
>>"A special flavour are "selectable Stored Procedures" that can calculate
>>data row by row in the server and can be used like a View or a "virtual
>>table" from the client side, which is especially useful for reporting
>>tasks"
>>
>>
>>
>>-- Firebird Facts 1.5.
>>
>>
>
>It's quite true...selectable stored procedures designed to meet
>extraordinary reporting or viewing requirements are brilliant, fast,
>convenient, great encapsulation...but it doesn't say that it will be
>practicable to define a stored procedure and then outer join to it in such
>a way that it needs to do 9 million row fetches.
>
>
>
>>Just going buy what I read.
>>
>>
>
>...proving the truth of the saying "there is a medium in all things".
>
>:-))
>
>Did you try using a view instead?
>
>/heLen
>
>
>
>
>
>
>Yahoo! Groups Sponsor
>
>
>
>ADVERTISEMENT
>
><http://us.ard.yahoo.com/SIG=12908rc65/M=298184.5285298.6392945.3001176/D=gr
>oups/S=1705115386:HM/EXP=1092712887/A=2164331/R=0/SIG=11eaelai9/*http:/www.n
>etflix.com/Default?mqso=60183351> click here
>
>
>
><http://us.adserver.yahoo.com/l?M=298184.5285298.6392945.3001176/D=groups/S=
>:HM/A=2164331/rand=461454461>
>
>
>
> _____
>
>Yahoo! Groups Links
>
>* To visit your group on the web, go to:
>http://groups.yahoo.com/group/firebird-support/
>
>* To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
><mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
>* Your use of Yahoo! Groups is subject to the Yahoo!
><http://docs.yahoo.com/info/terms/> Terms of Service.
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>

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