Bad Queries

[Cringer]

Morning all,
As you probably all know I'm doing a presentation on Query Tuning at the EMEA PUG. One section I want to do is about common bad queries and how to avoid them/fix them. I've got a whole gamut of nausea-inducing examples but I'm sure I'm missing some. So if you have five minutes to share your queries from hell then please do. It will help me make an even more useful session for folks.
Thanks

Some examples of what I'm covering:

• Functions on the left of the query predicate
• Functions on the right of the query predicate
• Lots of OR statements
• Things like WHERE Order.OrderNum EQ (IF lvOrderNum NE 0 THEN lvOrderNum ELSE Order.OrderNum)
• Queries where the tables are in the wrong order, so Each order, each orderline, each item where item... turn that round to reference item first.

 

[Rob Fitzpatrick]

How about preventing the compiler from using multiple brackets on a single index by specifying USE-INDEX.

 

[Cringer]

Ah yes got USE-INDEX in there but not got that reason! Thanks.

 

[TheMadDBA]

Sorry in advance for the long post.. and probably the next one after some more coffee


Yes... a section on USE-INDEX is a must.

I assume when you say functions on the right you mean functions that refer back to the table and not just functions in general?

Here is one of my favorites (thanks QAD for making me so much easy money in the past )

Trying to use ranges to avoid multiple queries or a dynamic query...

for each tr_hist where 
              (tr_hist.tr_type >= type1 and tr_hist.type <= type2) and
              (tr_hist.tr_eff   >= date1  and tr_hist.tr_eff <= date2) and
              (tr_hist.tr_site >= site1     and tr_hist.tr_site <= site2) and
          (continue this for about 15 more fields)

Unnecessary BREAK BYs or the wrong BREAK BY column (order instead of customer)

for each customer no-lock,
each order of customer no-lock break by order.cust-num:
end.

NEXT logic in the wrong place (bad programming style imo but commonplace). I will admit that sometimes you have to keep fields out of the where clause to prevent bad index selection.. so NEXTs will happen.

for each order no-lock:

 find customer of order no-lock no-error.
 find state of customer no-lock no-error.

 for each order-line of order no-lock:
   /*--- accum some stuff ---*/
end.

if order.carrier <> "Some Carrier" then next.
end.

Probably a section on the lack of a cost based optimizer and/or the explicit rules based behavior of OE would be a good idea. People that have used SQL based databases or that or new to programming seem to assume that the database will take care of everything.

 

[Osborne]

One I remember seeing a few times was the use of ELSE TRUE:

FOR EACH OrderLine WHERE OrderLine.Ordernum = vOrdernum
    AND (IF vStatus <> "ALL" THEN
            OrderLine.OrderLineStatus = vStatus
         ELSE TRUE) NO-LOCK:
END.

 

[Cringer]

Yeah that's a sickening variation on the one I already have.

 

[TheMadDBA]

More coffee consumed.... a little more awake now ;-)

The evils of NE and NOT in where clauses. For some reason people expect a NOT or NE on a logical (or other types) to use an index.

Matches in the where clause... no index will be used regardless of the pattern. This is different from a LIKE in SQL that will use indexes depending on the pattern matching.

The horrors of conditional BREAK BYs

def var lSort as logical no-undo.

lSort = TRUE.

for each customer where
         customer.name begins "s"
      no-lock
     break by (if lSort = true then customer.name else customer.state):
end.

Not explicitly bad query related... but the benefits of caching records in temp-tables or classes.

 

[Cringer]

Thanks MrS. Couple of useful additions there.
Got the local caching already. Got a nice example of how it helps too.

 

[GregTomkins]

This is probably not helpful, but:

IMO, fixing bad queries is easy... the crazy thing is, waiting for customers to complain before you go looking.

In other words - IMO, you should have measurements in your code (eg. VST's, time measurements, AppServer logs, etc.) and alerting systems to identify bad queries proactively. It's SO easy to do, and with a little discipline, you can track down problems before anyone notices.

Some guys here hooked up some stuff that analyzes AppServer log files to create Kibana output that allow you to identify and query these sorts of problems - it's pretty incredible, actually, how slick this all works, with a relatively small amount of work.

Anyway, my point is to be proactive about it.

 

[Cringer]

VERY good point Greg. Thanks.

 

[TheMadDBA]

That is very true Greg. Especially important to catch as many as you can during development and testing.

James. There is a new release of SessionTrace that supports conditionally turning on tracing based on a config file with options for filtering by user and program and only output a file based on reaching a specified elapsed time or total activity.

I think an option for specific tables and indexes would be nice too.

 

[Cringer]

Thanks Keith. I'll download that later this week and get it in there. I'll be going into it in a bit of detail seen as Dan Foreman is stealing my thunder on the Application Profiler

 

[Rob Fitzpatrick]

Maybe next year you can be the first to do a session on the new profiler view in 11.6 PDSOE.

 

[Rob Fitzpatrick]

IMO, fixing bad queries is easy... the crazy thing is, waiting for customers to complain before you go looking.

Amen brother.

 

[Rob Fitzpatrick]

James, are you going to mention QryInfo and -zqil/-zqilv?

 

[Cringer]

Yes both in there Rob.

 

[Cringer]

Just spent a good hour working some of this into my presentation. Really good stuff. Thanks all so much. Keep the thoughts coming.

 

[oli]

Hi all,

Another way to identify bad (static) queries proactively is to use the XREF file.
Ages ago, I've written a tool to catch developer's attention on possible performance issue due to badly indexed queries.
You can download it from: http://www.4gl.fr/CheckIndexProject.zip
Feel free to test it and, if it's worth it, possibly mention it in your presentation.

Regards,

Olivier

 

[TheMadDBA]

Downloaded and trying to find time to play around with it

 

[TomBascom]

My personal favorite:

/* SR#1234 – enhanced lookup to improve performance!  */

update cName.

find first customer where cName matches customer.name
  use-index custNum no-error.


/* different variations used in different bits of code… */


find first customer where can-do( cName, name )
  use-index custNum no-error.

Read it carefully -- the number of bugs packed into such a compact snippet of code is truly astounding.