0

Sybase executes SELECT query with 50k rows in 10 seconds. If execute INSERT INTO ... SELECT ... FROM ... construction it executes 2 minutes

But if we increase retrieved rows number in 2 times - 100k rows in the same query, it has the same execution plan - it tries to INSERT them a couple hours and then fails with the error:

There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure ASE with more procedure cache.

Though just SELECT execution to retrieve those 100k rows takes about 15 seconds.

We increased our procedure cache on 100 times but no help. Still long waiting and the same error. When 50k rows INSERT doing well.

I mean, we increased our dataset to insert in 2 time and have such an issue. What should I consider else?

1
  • thanks for the link, yes, it was checked, but couldn't help Sep 6, 2021 at 15:41

1 Answer 1

0

Procedure cache is not used for generic insert statements so I'm guessing what's really happening is the insert is firing a trigger (on the table being inserted into).

If this is the case I'd want to take a look at the source code for the trigger. My guess is the trigger works 'ok' for single row inserts but was not designed/coded properly to support multi-row inserts.

Other issues to be aware of:

  • nested triggers - the current trigger performs DML commands (insert/update/delete) on the same table and/or other tables and said DML commands cause additional triggers to fire
  • subordinate stored procedures - the trigger(s) in question calls stored procedures
  • user defined functions (UDF) used to created computed column(s) (eg, implement default value assignments, perform data validations, used for function-based indexing)

Net result: It will be necessary to review all code (triggers, stored procs, UDFs), that is fired in relation to this insert into call, for possible issues that could be causing the procedure cache to be depleted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.