Solved

Initial embeded SQL runs slow

Posted on 2011-03-07
7
550 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check Group Membership 2 122
iSeries - Add Users to a Group 1 190
best way to update esxi hosts 6 127
Secure coding guides/standards for Cobol programming 10 312
Data breaches are on the rise, and companies are preparing by boosting their cybersecurity budgets. According to the Cybersecurity Market Report (http://www.cybersecurityventures.com/cybersecurity-market-report), worldwide spending on cybersecurity …
February 24, 2017 — On February 23, Travis Ormandy, a vulnerability researcher at Google, reported on Twitter (https://twitter.com/taviso/status/834900838837411840) that massive stores of data have been leaked by CloudFlare, a company that provide…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

777 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