Dynamic Query Question

[jdpjamesp]

This is making me feel rather dense because I can't work it out!
A simple query against sports2000.

FOR EACH s2k.Customer NO-LOCK , EACH  s2k.Order NO-LOCK WHERE s2k.Order.CustNum EQ s2k.Customer.CustNum

As a static query that reads:
Table Name read
Order 3954
Customer 1118

But when run dynamically reads are
Table Name read
Order 7906
Customer 5070

CREATE QUERY QueryHandle. 
Tables = fn-GetTables(QueryString).
DO i = 1 TO NUM-ENTRIES(Tables):
  QueryHandle:ADD-BUFFER(ENTRY(i,Tables)).
END. 
QueryHandle:QUERY-PREPARE(QueryString).
QueryHandle:QUERY-OPEN().


i = 0. 
DO WHILE QueryHandle:GET-NEXT():
  i = i + 1.
END. 

IF VALID-HANDLE(QueryHandle) THEN 
  DELETE OBJECT QueryHandle.

Progress 11.5.1.

What is my tired brain missing?!

 

[TheMadDBA]

I can't get it to behave differently... message QueryString and make sure it is actually doing the same thing.

 

[jdpjamesp]

---------------------------
Message (Press HELP to view stack trace)
---------------------------
FOR EACH s2k.Customer NO-LOCK , EACH s2k.Order NO-LOCK WHERE s2k.Order.CustNum EQ s2k.Customer.CustNum
---------------------------
OK Help
---------------------------

 

[jdpjamesp]

---------------------------
Message (Press HELP to view stack trace)
---------------------------
-pf C:\Progress\OpenEdge\startup.pf,-cpinternal ISO8859-1,-cpstream ISO8859-1,-cpcoll Basic,-cpcase Basic,-d dmy,-numsep 44,-numdec 46,-debugalert,-Mm 8192,(end .pf),-s 200,-mmax 65534,-T C:\OpenEdge\WRK,-db s2k.db,-H localhost,-S 1001,-debugalert,-rereadnolock,-h 10,-inp 16000,-tok 4000,-nosplash,-p C:\Progress\OpenEdge\oeide\eclipse\plugins\com.openedge.pdt.debug.core_11.5.1.00\abl\_debuglauncher.p
---------------------------
OK Help
---------------------------
Will it be the -rereadnolock setting? Not 100% sure where that's coming from. Hmmm.

 

[jdpjamesp]

Found it, removed it, and yes the reads are as expected now. Duuuh.

 

[TheMadDBA]

That somewhat explains it... Now I am curious enough to look into why the -rereadnolock impacted the dynamic query but not the static query...

Or was it two different sessions?

 

[jdpjamesp]

Different sessions actually.

 

[Stefan]

And unless you want complete insanity to take over, put the -rereadnolock back in pronto!

 

[jdpjamesp]

It's my local DB and I'm the only user.

 

[Stefan]

And you only connect with one single session? As soon as you have multiple fat client sessions or multiple AppServer / WebSpeed agents working for you madness will creep in.

 

[jdpjamesp]

Just a single session - it's sports2000 - I'm using it for preparation for my talk at the PUG - profiling queries.

 

[TheMadDBA]

Something of interest for you that you might want to include in your presentation.... You can use the CACHE setting on the dynamic query to stop it from reading those extra records.

Progress KB - Multi-table dynamic ABL query generates a high number of database reads when using -rereadnolock

 

[jdpjamesp]

Nice stuff thanks for that. It'll be in there.