Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...
Database Forum / DB2 Topics / September 2006

Tip: Looking for answers? Try searching our database.

Slow prepare times for inserts

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
TC - 27 Sep 2006 22:21 GMT
I am having an issue where inserts are taking a lot longer than I would
expect.  So far it seem that it is just the first insert. (Although, I
have other people telling me it is happening more than just the firs
time)  The times for a single insert are anywhere from 2-10 seconds.  I
can't imagine what takes that long.  We use JDBC and the bulk of this
time is on the prepare of the statment.  Does anyone have any ideas on
what could be causing this?
Reply to this Message
Serge Rielau - 27 Sep 2006 23:54 GMT
> I am having an issue where inserts are taking a lot longer than I would
> expect.  So far it seem that it is just the first insert. (Although, I
[quoted text clipped - 3 lines]
> time is on the prepare of the statment.  Does anyone have any ideas on
> what could be causing this?

Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message
TC - 28 Sep 2006 15:58 GMT
> Does the target table have triggers or RI constraints?
> If there are triggers are some of them AFTER o INSTEAD OF triggers which
> in turn modify tables with triggers, etc, etc.
> A quick look at the plan will show whether the INSERT in simple (just a
> few operators in visual explain) or nasty (pages of stuff in explain).

Thanks for your response.  We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge.  We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit)  These functions emulate equivilent functions that
exist in Oracle.  Is it possible that these functions are causing this
issue?  I will try to experiment with taking these out and running
another plan.  If this is the cause, I am not sure what I am going to
do about it.  We rely on these functions pretty heavily.
Reply to this Message
TC - 28 Sep 2006 17:29 GMT
> Does the target table have triggers or RI constraints?
> If there are triggers are some of them AFTER o INSTEAD OF triggers which
> in turn modify tables with triggers, etc, etc.
> A quick look at the plan will show whether the INSERT in simple (just a
> few operators in visual explain) or nasty (pages of stuff in explain).

Thanks for your response.  We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge.  We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit)  These functions emulate equivilent functions that
exist in Oracle.  Is it possible that these functions are causing this
issue?  I will try to experiment with taking these out and running
another plan.  If this is the cause, I am not sure what I am going to
do about it.  We rely on these functions pretty heavily.
Reply to this Message
TC - 28 Sep 2006 18:52 GMT
> Does the target table have triggers or RI constraints?
> If there are triggers are some of them AFTER o INSTEAD OF triggers which
> in turn modify tables with triggers, etc, etc.
> A quick look at the plan will show whether the INSERT in simple (just a
> few operators in visual explain) or nasty (pages of stuff in explain).

Thanks for your response.  We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge.  We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit)  These functions emulate equivilent functions that
exist in Oracle.  Is it possible that these functions are causing this
issue?  I will try to experiment with taking these out and running
another plan.  If this is the cause, I am not sure what I am going to
do about it.  We rely on these functions pretty heavily.
Reply to this Message
TC - 28 Sep 2006 18:54 GMT
> Does the target table have triggers or RI constraints?
> If there are triggers are some of them AFTER o INSTEAD OF triggers which
> in turn modify tables with triggers, etc, etc.
> A quick look at the plan will show whether the INSERT in simple (just a
> few operators in visual explain) or nasty (pages of stuff in explain).

Thanks for your response.  We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge.  We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit)  These functions emulate equivilent functions that
exist in Oracle.  Is it possible that these functions are causing this
issue?  I will try to experiment with taking these out and running
another plan.  If this is the cause, I am not sure what I am going to
do about it.  We rely on these functions pretty heavily.

I just removed the calls to the MTK toolkit and the graph in the
explain plan became very simple.
Reply to this Message
TC - 28 Sep 2006 20:35 GMT
> Does the target table have triggers or RI constraints?
> If there are triggers are some of them AFTER o INSTEAD OF triggers which
> in turn modify tables with triggers, etc, etc.
> A quick look at the plan will show whether the INSERT in simple (just a
> few operators in visual explain) or nasty (pages of stuff in explain).

Thanks for your response.  We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge.  We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit)  These functions emulate equivilent functions that
exist in Oracle.  Is it possible that these functions are causing this
issue?  I will try to experiment with taking these out and running
another plan.  If this is the cause, I am not sure what I am going to
do about it.  We rely on these functions pretty heavily.

BTW, I tested it and when I get rid of the MTK function calls the
explain graph gets very simple.
Reply to this Message
TC - 28 Sep 2006 20:37 GMT
> Does the target table have triggers or RI constraints?
> If there are triggers are some of them AFTER o INSTEAD OF triggers which
> in turn modify tables with triggers, etc, etc.
> A quick look at the plan will show whether the INSERT in simple (just a
> few operators in visual explain) or nasty (pages of stuff in explain).

Thanks for your response.  We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge.  We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit)  These functions emulate equivilent functions that
exist in Oracle.  Is it possible that these functions are causing this
issue?  I will try to experiment with taking these out and running
another plan.  If this is the cause, I am not sure what I am going to
do about it.  We rely on these functions pretty heavily.

BTW, I tested it and when I get rid of the MTK function calls the
explain graph gets very simple.
Reply to this Message
Serge Rielau - 28 Sep 2006 21:11 GMT
>> Does the target table have triggers or RI constraints?
>> If there are triggers are some of them AFTER o INSTEAD OF triggers which
[quoted text clipped - 14 lines]
> BTW, I tested it and when I get rid of the MTK function calls the
> explain graph gets very simple.

Care to post the INSERT statement.
While ORA8.* SQL UDF will certainly blow up you graph I'm surprised that
teh result is huge unless you have a lot of them. then th eqyestion is: Why?

After you have successfully migrated the function with MTK you need to
take alook at teh slo areas and first decide whether soem emulatiosn of
the MTK are really needed.
E.g. all the code injected by MTK for "emtpy string equals NULL" won't
be needed if you don't use NULLs.
The MTK is very liberally adding ORA8.* functions even if the
differences to DB2 functions are pathological in nature.

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message
TC - 29 Sep 2006 14:28 GMT
> Care to post the INSERT statement.

insert
into canpck
( cangrp, wh_id, wrkref, wrktyp, schbat,
srcloc, srcare, dstare, ship_line_id, ship_id,
client_id, ordnum, ordlin, ordsln, concod,
cmbcod, lblseq, pckqty, appqty, pcksts,
prtnum, prt_client_id, invsts, lodlvl, untcas,
untpak, ftpcod, visflg, splflg, locflg,
lodflg, subflg, dtlflg, prtflg, orgflg,
revflg, lotflg, qtyflg, adddte, candte,
can_usr_id, cancod, remqty, invsts_prg, frsflg,
min_shelf_hrs, pckdte, catch_qty_flg, pipflg, list_id,
list_seqnum)
values
( 'grp1', 'WMD1', 'TC_22', 'R', NULL,
'x', 'x', 'x', 'x', 'x',
'x', 'x', 'x', 'x', 'x',
'x', 'x', 0, 0, 'x',
'x', 'x', 'x', 'x', 0,
0, 'x', 0, 0, 0,
0, 0, 0, 0, 0,
0, 0, 0, ora.to_date('20060807120000', 'YYYYMMDDHH24MISS'),
ora.to_date('20060807120000', 'YYYYMMDDHH24MISS'),
'x', 'x', 0, 'x', 0,
0, ora.to_date('20060807120000', 'YYYYMMDDHH24MISS'), 0, 0, 'x',
0)

> By any chance is this the only client attached to the database?  And is
> the connection made at the begining of this insert?  DB2 can shut
> itself down if there are no connections made, and will wake up with the
> first connection, which takes a bit of time, which could explain this,
> I think.  If that is so, there are ways to make DB2 stay awake, if you
> need to.

No, I tested this many times by forcing it to recompile by adding a
space character.
Reply to this Message
Knut Stolze - 29 Sep 2006 15:16 GMT
>> By any chance is this the only client attached to the database?  And is
>> the connection made at the begining of this insert?  DB2 can shut
[quoted text clipped - 5 lines]
> No, I tested this many times by forcing it to recompile by adding a
> space character.

I guess the question was if you disconnect between the insert statements or
if you keep at least one connection alive and, thus, the database
activated.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message
ChrisC - 28 Sep 2006 22:40 GMT
> I am having an issue where inserts are taking a lot longer than I would
> expect.  So far it seem that it is just the first insert. (Although, I
[quoted text clipped - 3 lines]
> time is on the prepare of the statment.  Does anyone have any ideas on
> what could be causing this?

By any chance is this the only client attached to the database?  And is
the connection made at the begining of this insert?  DB2 can shut
itself down if there are no connections made, and will wake up with the
first connection, which takes a bit of time, which could explain this,
I think.  If that is so, there are ways to make DB2 stay awake, if you
need to.

-Chris
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.