Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Initial embeded SQL runs slow

Posted on 2011-03-07
7
Medium Priority
?
591 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 18

Expert Comment

by:Dave Ford
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 35

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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:
Dave Ford earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Files go missing when using DFS (Distributed File System) Replication and how to recover them and fix it.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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