Slow queries with parameter -rereadnolock

Posted by e.schutten on 01-Nov-2016 12:01

[View:/cfs-file/__key/communityserver-discussions-components-files/19/QueryTest1.p:320:240] I have done some query testing.  The physical and Dynamical quiries are retrieving 100.000 records for two tables like for each table1, each table 2 where.

In the table below you find the avarage time in ms. Can somebody explain why the queries are dramatically slow when using rereadnolock and why they are slow when not using rereadnolock and not using the CACHE option? 

 

Dynamic

Physical

Reread with no cache

99.000

58.000

Reread with cache

9.000

9.000

No reread with no cache

9.000

55.000

No reread with cache

9.000

9.000

 

All Replies

Posted by Brian K. Maher on 01-Nov-2016 12:06

It would be nice to see the code.

Posted by e.schutten on 02-Nov-2016 04:07

HI Brian,

I have added QueryTest1.p. I forgot to mention that the test was done with an OpenEdge 10.2B SP8 on Windows.

Posted by e.schutten on 02-Nov-2016 04:09

And I am busy to do the test on Linux.

Posted by Brian K. Maher on 02-Nov-2016 04:22

When you ran the code, did you run it only once in a given session or multiple times?
 
Was the database local or remote?
 
Were you connected single user, shared memory or client server?
 
What was the size of the database buffer pool (-B parameter)?
 
Do you have enough available memory so that the database buffer pool does not get swapped to disk?

Posted by e.schutten on 02-Nov-2016 04:52

With variable iNumSeq in the code, you can change the number of itterations. Per iteration you can set the iMaxCount (number of records to read). What I did was reading 15 x 100.000 records.

I did the test on my computer and on the server. I did not use shared memory or single user (production environment).

-B 5220000 (8 KB blocksize)

Total memory is 256 GB

Available 116 GB  (small database).

With Protop I did not see any OS-reads. We also using -lrkuskip 100.

We have a lot of other servers with bigger databases and different configurations. I can do this test also in those environments.

BTW with physical I mean Static query. And with 'no cache' I mean that I did not use the CACHE in the define query statement. I could also try CACHE 0.

Posted by Brian K. Maher on 02-Nov-2016 05:08

I’m not sure what the answer is and think there is more involved than has been posted.  I would recommend you open an official support case.

Posted by Marko Myllymäki on 02-Nov-2016 09:43

I have also tested this a couple of years ago and got similar results. In many cases, it seems to be quite important to use CACHE > 0 when using -rereadnolock. Here's one interesting kb article:

http://knowledgebase.progress.com/articles/Article/P147331

This thread is closed