ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Software generating sql

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

  • Software generating sql

    We have a BI tool that generates and runs its own sql. We the programmers/system admins hate it as it can write some really goofy looking statements. Just wondering if there would ever be a reason to run statement A vs B. A was generated by the software and I just updated it to look a little more normal. Seems to me the sub query in the from clause could have quite a few ramifications.

    Statement A
    Code:
    SELECT *                                                        
        FROM (SELECT *                                              
                  FROM SWANWHSE30.SNORDRSM                          
                  WHERE OSCOMP = 1 AND OSDICD IN (1,9) AND OSDZDT >=1170425)  T0                                                          
        WHERE ((T0.OSDZDT >= 1170425) AND (T0.OSDZDT <= 1170425))
    Statement B
    Code:
    SELECT *                                     
        FROM SWANWHSE30.SNORDRSM t0              
          WHERE OSCOMP = 1 AND OSDICD IN (1,9)   
        and T0.OSDZDT between 1170425 AND 1170425

  • #2
    There is no reason to run the first statement instead of the second one. IMHO the first one will perform worse, because of the nested sub-select.
    Just run them both trough Visual Explain.
    May be the Query Optimizer is smart enough to find out that both statements are identical.

    Sometimes software tools need to implement those constructs for being able to easily determe the number of rows, or for using the names of created columns.

    Birgitta

    Comment


    • #3
      They do perform the same, and I did run them through the analyzer and they do execute the same way. This software does this a lot so hopefully the optimizer figures it out correctly every time. I was asked what I thought and basically said the same thing but was just looking for confirmation.

      Thanks.

      Comment


      • #4
        That's the hazard of tools to generate code - the result will always be "clunky" and oftentimes look goofy. It's the nature of the beast.

        Comment


        • #5
          I would agree and the questions more pertained to what performance issues may it run into since every statement it generates runs a sub query in the from clause.

          Comment


          • #6
            What is the actual concern? Are the generated statements somehow needed by users? Do they perform relatively poorly?

            If they're generated and used internally by software, then their form can be ignored. OTOH, if users need to manipulate statements, complexity could have an impact. And if V.Explain shows approximately equal analyses between the two forms, then performance (for this example) might be ignored.

            How many different statement examples have you reviewed for alternative performance metrics?

            Also, is there an alternative product that you're considering for this statement-generator software? Or is "manual" the potential alternative?
            Tom

            There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

            Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

            Comment

            Working...
            X