Initial embeded SQL runs slow

I have an rpgle program with embeded sql that runs slow (around 10 seconds) the first time it runs and subsequent runs are much faster (around 3 seconds).  I put the SQL through the optimizer and built the recommended indexes.  I don't think the problem is my sql statement because the subsequent runs are fast.
matt2doorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dave FordConnect With a Mentor Software Developer / Database AdministratorCommented:

Greetings, matt2door.

It's been a couple months since you posted this question. Did you ever come up with a solution?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Please post your SQL so that we can look for any glaring problems.

The first run of a SELECT statement can take longer for a variety of reasons. Firstly, the query optimizer has to analyze and optimize the query the first time it’s run. After that, it’ll probably use a stored query plan, so it doesn’t need to take the time to re-optimize.

Secondly, the optimizer will sometimes locally create an index “behind the scenes” that it thinks it could use. That kind of index used to be immediately thrown away after the query finishes, but in more recent versions, it actually keeps it around for a while (usually until IPL). So, subsequent queries can use that “temporary” index ... thereby possibly running faster.

Again, post the SQL so we can look over it.

HTH,
DaveSlash
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Definitely need to see the SQL.  

What OS version are you running?

How are you executing it: embedded SQL in and RPG program, ODBC/JDBC/DRDA/OLEdb, STRQMQRY command, RUNSQLSTM command, etc?

It is useful to run the query under debug.  This causes the optimizer to generate a lot of detailed messages about the optimization process.  Also, running a database monitor while executing the query can be very useful n determining where the DBMS is spending it's time.  

Finally, run a visual explain of the query, and post the visual explain diagram.  

Chapter 4 of the DB2 UDB for iSeries Database Performance and Query Optimization manual explains in detail how to perform the analysis needed to determine the cause of problems like this:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/rzajq.pdf
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/rzajq.pdf
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=/rzajq/rzajqprint.htm

- Gary Patterson
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
tliottaCommented:
...runs slow ... the first time it runs and subsequent runs are much faster...

No SQL is available yet, but there might be other elements that are relevant.

Please clarify the execution environment. Is this program called in interactive job streams as a straight program call? Is it called as a stored proc? Is it called from remote clients?

Also, does "the first time" mean "the first time each job calls it" or "the first time any job calls it, but the next job gets the faster response time"?

Tom
0
 
MurpheyApplication ConsultantCommented:
To get detailed info about this DB actions, you can investigate the real problem.
the fact that the program is slow, isn't always due to the SQL...

A simple way to check the SQL activity is to start debugging just befor you run the progran STRDBG.
you can see more info about the SQL job.

For extended info (run times, used views etc) use the SQL-Performancemonitor in the iSeries Navigator.
with this info you can see what view to create to speer thingd up.

Regards,
Murph

0
 
daveslaterCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.