Subject Re: [firebird-support] mon$call_stack line numbers, procedure hanging
Author unordained
> Do you have any kind of firewall between client and server? Try turning
> it off and see what happens. If you have NOD32 on some of the machines,
> disable the IMON module and try.
> --
> Milan Babuskov

Milan, did you mean to respond to my question? I'm failing to see how firewalling is relevant to line numbering in call_stack?

In any case, I did eventually hunt down the problem -- a trigger was running an update statement that wasn't using an index properly, and the trigger didn't show up in mon$call_stack. Apparently the line number that gets reported is at "end of procedure" when it's a trigger that's executing, even if the trigger is being fired by a multi-row DML statement inside said procedure, and I would expect the line number to point to the exact statement that fired the trigger. Doesn't make sense to me, but whatever.

Exact performance issue in the trigger:

update a set blah = null where a.parent_id in (select id from b where ...)
 -> did NOT use index on a.parent_id(took 2 hours to run bulk update)
merge into a using (select id from b where ...) z on = a.parent_id when matched then update set blah = null
 -> DID use index on a.parent_id(took 2 minutes to run bulk update)

I made no changes to the index or its statistics. I saw a similar issue in the tracker (CORE-1969), but it was closed as the submitter had failed to recreate the index after switching to FB2. That wasn't the case for me.*shrug* It's fixed.


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