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]