karl & betty schendel

Did you create the session temp separately, or did you use
declare global temporary table ... as select?
The latter creates a compressed heap by default, which might be slowing
things down some (although an order of magnitude sounds like a lot).

I have to assume that the select returns more than just a few rows.
Maybe the DMF cache is too small and the temp table is spilling to
disk inefficiently.

We'll need more info on how many rows you're inserting and into what
storage structure before offering further ideas.

Karl

chip nickolett

Hi Manny,

Are all inserts slow or just this one? Are you using RAID 5? How much
data is "some data" that is being inserted? A few rows, a few hundred
wide rows, or a few thousand rows? You could set log_trace and see how
much logging is being done. This would help show if there were many
pages being written, or if there were log waits.

You might also want to look at DM420 tracepoint data. A temporary
table will reside in the DMF cache while it can, but will force pages
to be written to disk if the cache is not sized large enough.

Just for grins you could run a lock_trace and see if it shows anything.
It's unlikely, but you never know.

There are many, many things that could cause this type of issue. One
of the first goals is to determine if the issue is systemic, or
isolated to a single process or event. From there just keep logically
digging until you find your answer.

BTW, we have a really good Ingres Performance Tuning course that covers
these and many other useful topics! :-) See
http://www.comp-soln.com/ingres.html for more information.

Best wishes.

Chip Nickolett (ChipN@Comp-Soln.com)

US - Comprehensive Solutions
www.Comp-Soln.com
UK - Comprehensive Solutions International, Ltd. www.Comp-Soln.co.uk

manny

Thank you for your replies.

On this occasion the code has been changed completely and the original
query is not needed anymore.

Cheers,

Manny

chip nickolett

Hi Manny,

Are all inserts slow or just this one? Are you using RAID 5? How much
data is "some data" that is being inserted? A few rows, a few hundred
wide rows, or a few thousand rows? You could set log_trace and see how
much logging is being done. This would help show if there were many
pages being written, or if there were log waits.

You might also want to look at DM420 tracepoint data. A temporary
table will reside in the DMF cache while it can, but will force pages
to be written to disk if the cache is not sized large enough.

Just for grins you could run a lock_trace and see if it shows anything.
It's unlikely, but you never know.

There are many, many things that could cause this type of issue. One
of the first goals is to determine if the issue is systemic, or
isolated to a single process or event. From there just keep logically
digging until you find your answer.

BTW, we have a really good Ingres Performance Tuning course that covers
these and many other useful topics! :-) See
http://www.comp-soln.com/ingres.html for more information.

Best wishes.

Chip Nickolett (ChipN@Comp-Soln.com)

US - Comprehensive Solutions
www.Comp-Soln.com
UK - Comprehensive Solutions International, Ltd. www.Comp-Soln.co.uk