Thank you, Philip, you helped me much.



I have a table, where customers can record their daily order, like
On 29/10/2010 I'd like to have a menu B, and on 31/10/2010 I'd like to
have A.
So there will be two records in the table, one for each order. But I
have to show their orders for a week, like
Okay, so on the week starting on 25/10/2010, you have none, none, none,
none, B, none, A ...
So I created an SP which gives me back these records. Then I created the
view to have a selectable, updatable pseudo-table. The SP itself takes
approx 8sec to run.
I use "update orderviewbyweek set MENU3='B' where orderdate='2010.10.25'
and customer=2345" where orderdate is the start of the week, which third
day menu has to be changed. And that update takes 8sec again. Now I know
why.
I don't know if there is any solution for this, or I have to handle the
whole thing on the client side.

SanTa



2011.01.25. 21:10 keltezéssel, unordained írta:

> ---------- Original Message -----------
> From: Sándor Tamás<sandortamas@...>
>> Hi,
>>
>> It's a great thing to have selectable SPs in Views, but I have a problem.
>> According to my tests, is it true that if a view consists a selectable
>> SP and not on a simple SQL, it always executes that SP? Because I
>> realized that if I make an update on that view (through an update
>> trigger), it takes as much time as the original SP to run.
>> Before you say that, the trigger simply runs another SP, which takes
>> some milliseconds to run, so it can't be the problem.
>>
>> Am I right that if a view gets it's datas from an SP, it runs it every
>> time something changes?
> ------- End of Original Message -------
>
> When a trigger fires on a view, it's given full NEW.* and OLD.* values,
> regardless of what was in your statement. So for "update myview set blah = 3
> where id = 2" to work, it has to find the row in the view where id = 2, get the
> rest of the fields for OLD and NEW, then call the trigger with NEW.BLAH = 3.
> Since it can't know where in the SP's output that row might show up, it has to
> start from scratch; and since the output of an SP can't be marked as having a
> PK, there could be multiple matching rows and it won't know until it exhausts
> the SP's entire output. Views also can't tell an SP "hey, the user wanted id =
> 2, can you maybe do less work and retrieve just that row?" -- there's no hidden
> back-channel of communication there. (Sure would be nice, though.)
>
> Views are dynamic, so Firebird won't be cacheing the result of the SP to use
> instantly every time you access the view; your use of "every time something
> changes" makes me think you're thinking of materialized tables, which Firebird
> doesn't have as a feature (but you can build yourself in various ways -- I have
> some incomplete thoughts on the matter at http://www.pseudotheos.com/
> view_object.php?object_id=1541#1) By default, a true view fetches its data from
> source every time you ask for it. In your case, that means an SP gets run.
>
> Are you in a situation where you're selecting from the view first, then
> updating what you got back, and your problem isn't that the SP gets run once,
> but that it somehow gets run multiple times? Are you attempting to run update
> statements on it directly (as in my example above), or via an open cursor,
> where Firebird might be able to use the currently-selected data to run the
> trigger, without calling the SP again? (I'm not sure that would help, but I'm
> curious.)
>
> -Philip
>

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