Dynamic Query Vs For Each.... Performance Issues

[eseargentina]

Hi people from the forum! I'm working with Progress 9.1d and I'm having problems with the performance of my reports. I've written two procedures: one uses a "normal" query and the other one uses a dynamic query. The "normal" query takes around 15 seconds to run, while the other one takes more than a minute and a half! I'm including both queries so you can see what I'm doing and tell me what's wrong with it.

Normal:
FOR EACH invoice NO-LOCK where ..... USE-INDEX IND-CompAnuFecEnt,
EACH Invoice_Detail NO-LOCK OF invoice,
FIRST Products NO-LOCK WHERE ....,
FIRST Customers NO-LOCK WHERE .... :
.......
......
......
end.

Dynamics:
CREATE BUFFER Bh FOR TABLE "invoice".
CREATE BUFFER Bh1 FOR TABLE "Invoice_Detail".
CREATE BUFFER Bh2 FOR TABLE "products".
CREATE BUFFER Bh3 FOR TABLE "Customers".
CREATE QUERY qh.
qh:SET-BUFFERS(bh,bh1,Bh2,Bh3).
wQuery = "FOR EACH invoice FIELDS (field1 field2 field3 ....) NO-LOCK where " + Filter_Options + " USE-INDEX IND-CompAnuFecEnt, " +
" EACH Invoice_Detail FIELDS(field1 field2 field3 ....) NO-LOCK OF invoice " + Filter_Options +
" FIRST Products FIELDS(Field1) NO-LOCK WHERE .... " +
" FIRST Customers FIELDS(Field1) NO-LOCK WHERE .... " +
qh:QUERY-PREPARE(wQuery).
qh:QUERY-OPEN.
repeat:
qh:get-next(no-lock).
.......
......
......
end.
qh:close.


Thanks in advance

 

[RealHeavyDude]

What is in the "Filter_Options"?
Is there any particular reason as to why you use the USE-INDEX option?

Regards, RealHeavyDude.

 

[Stefan]

1. are you getting identical records (ie are the queries which look the same, returning the same results?)
2. are you getting an equal number of record reads (see _tablestat) when both queries are performed (once again, checking if the queries are doing the same)
3. you are using FIELDS phrase on the dynamic but not on the static query - FIELDS does not need to increase performance
4. use DO WHILE qh:GET-NEXT( NO-LOCK ): instead of the REPEAT: construct - REPEAT has some extra functionality and thus some extra overhead

-> first ensure dynamic and static queries are identical, compare results and then add functionality to the dynamic query, compare results, repeat.

 

[MaximMonin]

try qh:forward-only = true.

 

[eseargentina]

RealHeaveyDude,

Filter-Options contains the Where-Clause "Where Field1 = ...."
I used the "USE-INDEX" just to try and see if it was better.

Stefan,
1. and 2. I get the same number of registers with both queries.
4. I'm going to use it because the code is neater, but the performance hasn't improved.

qh:QUERY-OPEN.
repeat:
qh:get-next(no-lock).
.......
......
......
end.
qh:close.

It takes around 40 seconds to resolve the first get-next(), but the following are very quick. Any idea why? I've tried it in several servers and the result is the same.

Client's start-up parameters:
-Mm 16384
-T c:\temp
-s 80
-d dmy
-db ese -H server -N TCP -S service1
-db Grafpro -H server -N TCP -S service2
-cpcase Basic
-cpcoll Basic
-yy 1950
-numsep 46
-numdec 44
-h 10

Thanks a lot

 

[MaximMonin]

repeat:
qh:get-next(no-lock).
.......
......
......
end.
qh:close.[/COLOR]

It takes around 40 seconds to resolve the first get-next(), but the following are very quick. Any idea why? I've tried it in several servers and the result is the same.

It takes 40 seconds for DB Server to return set. And it doesnt matter, it is dynamic query or static.

try this:
qh:get-first(no-lock).
repeat:
if qh:query-off-end then leave.
....
qh:get-next(no-lock).
end.

 

[eseargentina]

It's a dynamic query. If I write the same query as static(FOR EACH....) it takes a third of the time. Do you have any idea why? I tried your solution but it takes the same time. It's only 7500 registers, it cannot take that much to return them, or does it?

Case 1:
For each table1 ......,
each table2 ..... .... .... 15 seg.

Case 2:
create buffer Table 1.
create buffer Table 2.
create query .....
.....
query:get-first more than a minute

 

[MaximMonin]

If both queries (static and dynamic) are the same I really dont know why it takes diffrent time to get set from DB server.

run promon
r&d -> 2 (Activity) -> 1 (Summary) and check RECORD Reads parameter (before and after query).
It is logical reads made by DB Server. It is a lot more than number of records DB server returns back to client (Shoud be 10mln+ for 1min query)

 

[eseargentina]

The query return 7907 Registers.

Promon R&D:

Records Reads 91187
Per Sec: 2399.6

Records Locks 7617 ????????
Per sec: 200.4

Buffer Hits 100%

 

[MaximMonin]

db server paramaters: (activate table statistics for 1000 tables in DB )
-basetable 1
-tablerangesize 1000
-baseindex 1
-indexrangesize 1000

code examle to analyze tables activity.

find first _ActSummary.
uptime = _ActSummary._Summary-UpTime.
RUN PUStr (2, 0, subst ("UpTime: &1 sec (&2 hrs &3 min &4 sec)", uptime,
trunc (uptime / 3600, 0), trunc (uptime / 60, 0) mod 60, uptime mod 60)).

row = 5.
for each _TableStat no-lock,
each _File no-lock where
_File._File-Number = _TableStat._TableStat-id
by _TableStat._TableStat-read desc
:
run CPReg (5, 0, 5, 9, row, 0).
run PUStr (row, 0, _File._File-Name).
run PUDec (row, 1, _TableStat._TableStat-read ).
run PUDec (row, 2, _TableStat._TableStat-update).
run PUDec (row, 3, _TableStat._TableStat-create).
run PUDec (row, 4, _TableStat._TableStat-delete).
run PUDec (row, 6, _TableStat._TableStat-read / uptime).
run PUDec (row, 7, _TableStat._TableStat-update / uptime).
run PUDec (row, 8, _TableStat._TableStat-create / uptime).
run PUDec (row, 9, _TableStat._TableStat-delete / uptime).
row = row + 1.
end.

 

[eseargentina]

Table1 reads: 23992 64.67
Table2 reads: 23890 64.39
Table3 reads: 23825 64.22
Table4 reads: 16094 43.38

 

[GregTomkins]

Finally, something interesting on PT !

We use dynamic queries all the time and have benchmarked them to be as performant as static. I'm sure there is some tiny overhead but not enough to account for your problem.

I think the best suggestion is the guy who told you to check PROMON stats. From your reply above its not clear that you looked at the numbers in the static and dynamic case. You should do that and make sure they are about equal. If they are not, then you know your query is somehow subtly different.

Also I noticed you mentioned a lock count there - you should make sure your NO-LOCK's are working, but this might be tricky, I know there are some locks taken even with NO-LOCK, and in any case, NO-LOCK vs. EXCLUSIVE-LOCK wouldn't make that much difference (unless you're competing with someone, which I assume is not the case).

If the PROMON counts are the same, then there must be something different about the environment - hopefully you didn't omit some crucial detail, such as these are different types of clients or different Progress versions or one is run across the network and one is self-serve, etc.

 

[eseargentina]

Well.....Let's See...

Dynamics and Static are running on a client in a network with the server and the client alone.

Promon for Dymamics:
Records Reads: 70432 Per Sec. 1498.5
Records Lock: 3020 Per Sec. 64.2

Promon for Statics:
Records Reads: 49960 Per Sec. 1850.3
Records Lock: 0 Per Sec. 0

The Statics "for each" is a "output to" from de Query:query-prepare of the dymamic query.

AAAAAAAAAAHHHHHHHHHHHHHH!!!!!

 

[GregTomkins]

Well, there you go - the dynamic version is doing almost 2x the reads, and, it is locking. I am still a bit dubious that this would cause such a large performance degradation, but it would certainly cause some problems. The obvious thing to do would be to make the dynamic query exactly identical to the static one, including figuring out why it is locking despite the NO-LOCK's.

One thing that might help, there is some debugger-type things you can stick on query objects to get it to tell you what index it is using and so on. I hardly ever use that stuff and I'm too lazy to look it up, but you might want to.

Also, I am not sure about this, but the way I read the documentation, the NO-LOCK in the GET-NEXT should be enough to prevent locks, but you have it in the query predicate as well; I'm not sure if this is required or is perhaps confusing something inside Progress. Anyway, for more reasons than just raw performance, you need to figure out why you are taking locks that you don't need.

Final suggestion, try starting with a very simple static v. dynamic query, just FOR-EACH an entire (small) table or something, and make sure that the PROMON stats for both versions are equivalent. I am not aware of any reason why they wouldn't be. The only difference should be a tiny, tiny, tiny cost to compiling the dynamic version on the fly.

Good luck!

 

[MaximMonin]

Table1 reads: 23992 64.67
Table2 reads: 23890 64.39
Table3 reads: 23825 64.22
Table4 reads: 16094 43.38

Actually it looks kinda strange to me.
DB server can logically read 100000 - 1mln records per second if all records are cached (100% buffer hits).

I think your query is good optimized.
ratio: records returned / records reads = 0.1 - it is ok, indexes should be used.
Problem is DB server/processors.

 

[GregTomkins]

I think you missed the issue that the performance is way worse when dynamic queries are used. Which is kinda the whole point.

 

[eseargentina]

We've tried taking the NO-LOCK clause out of the query and leaving it in the query:get-next(No-lock) and the other way around but it still locks registers. After that, we tried a similar dynamic query with other tables with and wihtout No-lock and it never locks registers. It's very weird.

MaximMonin, what do you mean by "db server/processor problems" ?

What's really off is that the database should normally read between 100000 and 1 mil records per second, which should mean that the query should take around 1 sec. The first get-next takes more than 10 seconds and then it starts processing. Is that possible??

We have checked the indexes the dynamic query used and it was using a rather inefficent one. After changing the query so it takes the correct index into account, the performance time went down to 8 seconds. The problem seems to be that the dynamic query couldn't find the proper index for the main table wheareas the static query (FOR EACH) did. Could it be a bug of the version? Has this happened to anyone else?

 

[MaximMonin]

I am working with 10 progress. Maybe table statictics differs from one version to another.
Record reads parameter/table reads shows me the following: db server reads whole table in cache and compares every record in memory or uses indexes to find records and after this checks other query conditions.
If reads/returned ratio > 100, if db server returns first record in 2 sec or more then inefficent indexes are used.

It is good idea comment your query until you find out "inefficent condition", which increase query time.

 

[TomBascom]

9.1d. Do I really need to say it?

In ancient, obsolete and unsupported versions of Progress dynamic queries have some quirks. One of those quirks is that they build a result set in a scratch file. This is an IO intense operation which can have a dramatic impact on performance.

You can see this if you set the -T and -t parameters and watch the growth of the srt file associated with the session.

If the query does not need to scroll forward and backward you must use the FORWARD-ONLY attribute to prevent this. (That was suggested early in the thread but I don't see that the advice was tried...)