Question Loading of Data to slow

[Mark Ezekiel P. Torio]

Hi to all,

Just want to ask why every time the transaction(any irregardless if the record loads in fill-in or browser) at first show the data loading was to slow then after that when i try to open again the transaction (any irregardless if the record loads in fill-in or browser) it was normal now.. it's like watching movie in youtube need to buff first before you can watch continuously.. is there any way to remove that buffer?

 

[Cringer]

Your question is far too vague I'm afraid. Please give details of what you are trying to do such as code snippets so that we can comment.

 

[Mark Ezekiel P. Torio]

Hi Cringer,

What Im trying to say was every time i open a transaction, like patient masterfile in a form of browser it take time to load the data during first open then i close it and open again in a second all data appears without any hang

 

[Cringer]

One thing that is confusing the issue is your use of the term transaction. If you are just reading data then it is not a transaction in Progress DB terms.
So let me clarify: You load a screen and it takes some time to load. You then close the screen and reopen it and it loads much quicker?
If this is the case it could be all manner of things, and depends how the queries are written. I would hazard a guess that the query is reading a lot of data from disk that is then present in the buffer the next time you run the query. What is the size of your -B DB startup parameter and your DB Block Size?

 

[Rob Fitzpatrick]

As Cringer said, it sounds like you are seeing the effect of caching. On first read, data comes off the disk, which is slow. On a second read immediately following the first, the data still resides in the buffer pool (in memory on the database server), so it is fast.

 

[TomBascom]

it's like watching movie in youtube need to buff first before you can watch continuously.

Yes. Exactly. Buffered data is in RAM and is much faster to access.

is there any way to remove that buffer?

Sure. Set -B to some really small value like 256.

But that will not have the impact that you are hoping for... removing the buffering is not a good thing. Buffering is your friend. What you really want to think about is your data request. Is it well organized? Are you asking the db for what you need? Or are you asking the db to send all sorts of useless data that the client must sort through and select from ultimately tossing most of it away?

Human scale data requests are generally smallish numbers of records -- one, two, thirty, a few hundred. Even unbuffered those are probably returned relatively quickly. If you're noticing a difference then it is very, very likely that your queries are poorly constructed and feature excessive numbers of logical io operations to satisfy the human oriented result set.

Look at you result set. Then look to see how much work the database had to perform to support that. Are the numbers reasonable?

ProTop is your friend. http://dbappraise/protop.html

 

[Rob Fitzpatrick]

When investigating query efficiency, it is very helpful to have the -tablerangesize and -indexrangesize database broker parameters set correctly. "Correctly" means higher than the highest-numbered application table and application index respectively.

Also helpful is the log entry type QryInfo. you specify it on the client side, e.g. with the startup parameters -clientlog filename.log -logentrytypes QryInfo. It will show you how many records were fetched for each query and how many were useful to the client.

All startup parameters are described in the Startup Command and Parameter Reference manual. Database broker parameters are also described in the Database Administration manual. Log entry types are also described in the Debugging and Troubleshooting manual.

To get your highest numbered table and index:

find last _file no-lock where _file._tbl-type = "T" use-index _file-number.
find last _index no-lock where not _index._index-name begins "_" use-index _index-number.
display _file-number _idx-num.

 

[Mark Ezekiel P. Torio]

Hi Cringer,

Sorry for wrong term, your clarification was right, Number of Database Buffers (-B): 100000, Database Blocksize (-blocksize): 4096

 

[Mark Ezekiel P. Torio]

Hi Rob,

My current -tablerangesize and -indexrangesize was 50, but the highest numbered table and index was 391 & 883 should i adjust the -tablerangesize = 400 and -indexrangesize = 900?

 

[Mark Ezekiel P. Torio]

Hi Tom,

Thanks for the clarification of buffer funcation, anyway how can i use the protop?

 

[Cringer]

Hi Rob,

My current -tablerangesize and -indexrangesize was 50, but the highest numbered table and index was 391 & 883 should i adjust the -tablerangesize = 400 and -indexrangesize = 900?

Yes those values are sensible values IMO.

 

[Cringer]

Hi Tom,

Thanks for the clarification of buffer funcation, anyway how can i use the protop?

Protop instructions are on the website. It's quite simple to set up the CHUI client by following the instructions.

 

[Mark Ezekiel P. Torio]

Hi Cringer,

what do you mean by sensible values IMO.

 

[Cringer]

IMO - In My Opinion.

 

[Mark Ezekiel P. Torio]

so in short i will increase the value of -tablerangesize and -indexrangesize

 

[Cringer]

Yep then you will be able to get better information on what your queries are doing as per the previous posts in this thread.

 

[Mark Ezekiel P. Torio]

Thanks Cringer and thanks to all

 

[Rob Fitzpatrick]

Once you get ProTop installed it can show you table and index statistics, but only if your database has been started with -tablerangesize and -indexrangesize values that are high enough. The values you have chosen will be sufficient. Remember that if your schema grows in the future (i.e. more tables or indexes are added) you may need to increase these parameter values.

 

[Mark Ezekiel P. Torio]

Hi Rob,

Thank you so much

 

[Mark Ezekiel P. Torio]

Hi to All,

Just want to ask when i was trying to get the protop OE 10.2B+ is that applicable to OE 11.4?