ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Fetch performance issue

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Fetch performance issue

    Hi guys, I have a job running a SQL RPG, it usually takes about a minute to run but sometimes it starts dropping performance and runs for 30m or so:
    Exec Sql
    Declare C1 Cursor for
    SELECT DISTINCT ORCO48,
    ORSO48,ORCN48,ORDS48,cono48,ordn48,A.catn55,B.orlv 55,A.ordl55,A.locd5
    a.qtor55, a.qtal55,a.qtos55,a.dtdr55
    FROM NOEP048 inner join cuscodes on cono20=orco48 and
    cusn20=orcn48 inner join cusnames on cono05=orco48 and
    cusn05=orcn48 inner join ordlnord A on A.cono55=cono48 and
    A.ordn55=ordn48
    inner join OEP40E C on C.cono40=orco48 and C.ordn40=ORSO48
    join ordlnord B on B.cono55=orco48 and
    B.ordn55=ORSO48 and B.ordl55= A.ordl55 WHERE cono48=:L#CONO
    and A.qtal55>0 and rsts05='' and b.ortp55 <>:@Ortp#
    and C.DPTN40='A' and a.Cusn55 = :@Cusn ;

    Click image for larger version

Name:	Captura de ecrã 2022-01-18 153113.jpg
Views:	357
Size:	88.4 KB
ID:	156780

    It stalls in the QDBGETMQO procedure, do you have any idea on how to improve the performance?

    thanks
    Nuno
    Attached Files

  • #2
    Have you double checked whether access path (indexes / logical files) are available and used?
    Run you query through Visual Explain (Part of the IBM i Access Client Solutions) and check whether there are MTI (Maintained Temporary Indexes) used.
    If temporary indexes are used, you should create permanent indexes instead of them ... otherwise those indexes will disappear and (re)creating indexes cost a lot of time and performance.

    I assume from time to time (especially after an IPL) temporary indexes are created. After the MTI is available, the queries run again in the normal time.

    Comment


    • #3
      Hi Brigitta,

      Thanks for your reply. I can see some indexes missing in Visual Explain, I will create them. Just can't see why it runs fast sometimes and then stops, even without IPL.
      Should I use logicals or the physical files on the sql?

      Thanks
      Nuno

      Comment


      • #4
        NEVER EVER specify logical files in SQL Statements!
        Even though logical files can be specified, the query optimizer first has to rewrite your SQL Statement based on underlying physical files or SQL Tables, before the optimization can start.
        >>Just can't see why it runs fast sometimes and then stops, even without IPL.
        There are also other reasons why a MTI gets (finally) deleted, ... if the last access plan that uses the MTI disappears from the SQE Plan Cache.
        But without knowing more or doing a deep analysis it is like looking in a crystal ball.

        Comment


        • #5
          I've noticed in the Visual Explain that, even though the logical had the access path, it wasn't using it.
          thanks!

          Comment

          Working...
          X