Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

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.
0
matt2door
Asked:
matt2door
1 Solution
 
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
 
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
theo kouwenhovenCommented:
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
 
Dave FordSoftware 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
 
daveslaterCommented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now