Solved

Initial embeded SQL runs slow

Posted on 2011-03-07
7
528 Views
Last Modified: 2014-04-30
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.
0
Comment
Question by:matt2door
7 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 35060914

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
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 35061490
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
 
LVL 27

Expert Comment

by:tliotta
ID: 35090394
...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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 35108516
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
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
ID: 35722266

Greetings, matt2door.

It's been a couple months since you posted this question. Did you ever come up with a solution?
0
 
LVL 14

Expert Comment

by:daveslater
ID: 40031499
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
With more and more people tethered to the internet, advertisers can reach you in new and creative ways. Push notifications on your smartphone, customized advertisements based on your previous searches, and email inboxes filled with promotional pitch…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now