Hi Folks,

Speed related query now…

Is there any way to speed up Read x from y ? I have a large table 1m+ records. I use a btree extract which retrieves 19,400 keys - this takes a second or 2, fab, but then looping through these records takes almost 3 minutes.

Server is a VM - Xeon 6142 @ 2.6Ghz - 8gb ram - WS2016

OI is 9.4.4 - UD47

The table itself was rebuilt last week using RTI_LH_STATISTICS

There is no AV running on the OI folder tree, or UD folder tree.

Sample code:

loop

	remove ref from keylist at key.poz setting flag1

while (ref or flag1) and abort = 0 do

	reado rec from table, ref then

		OKtoGo = 1

	end

repeat

The second time around is faster as data is cached, but it's not often the same report is generated twice. If I take out the readO it completes almost instantly.

Is there a faster way to pull records from a table, or something I could try?

Cheers, Rich

As you are working from a resolved list the primary bottleneck is going to be the distribution - LK vs OV. What are the respective sizes and what is the average record size?

Or a RAM disk is blazingly fast ;)

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Hi Andrew,

I did use the rebuild tool last week but still the LK/OV balance still looks off to me, anyway, here's what I have…

No.Records 1068840

Avg Size 510

Frame size 2018

Primary frame 49%

Frames (modulo) 304795

file version 2

LK size 609,590kb

OV size 478,678kb

Cheers, Rich

I have spent literally months performance profiling LH and it's an arcane science. What is the key-structure? Is the order of retrieval important? As in must it be sorted by X or do you just need to process Y records?

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Hi Andrew,

Thanks for responses.. I don't care about the order in this scenario as the data is sorted further down the logic, so just need to pull records and massage them around a bit.

The key structure is random - could be anything to be honest - we are sent keys ranging from around 16 chars up to typically 64 but could be beyond that out in the wild as we have to accept what we are sent.

Cheers,Rich

Do the keys have confidential meaning? In other words can you share the keys off line so I can create my own database? (Row contents don't affect retrieval speed so I'll just populate with strings).

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Hi Rich,

We were in a similar situation and unfortunately had to make drastic decisions to remedy the problem. It was about web access statistics data, which means that the database server was hit very hard. The figures in our case were somehow bigger compared to yours, approx. 30 million records in the file and approx. 50'000 rows to retrieve for an average query. The read process took forever, at least a couple of minutes on a highly optimized LH/OV file, stored on an enterprise level SSD. To cut a long story short, the only way we found to speed up the process was to migrate the data to a hierarchical structure of OS files (i.e. text files). Now the same read process takes no longer than 2-3 seconds!

Best,

Stefano

Hi folks,

Stefano - thanks for the heads-up - hopefully we won't have to look into that, our apps are located on-premise with our customers, so performance can vary depending on how big their server budget is, which is typically not that high! But anything we can squeeze out of what we have would help for now.

Andrew - keys are on our dev platform, so anonymized - I will send you a file via OneDrive with all the keys in that we have…

Cheers, Rich

Cheers Rich, I have some ideas I'd like to play with - and always looking for new blog content :)

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

View this thread on the Works forum...