Home » Developer & Programmer » Forms » GO_RECORD performance
GO_RECORD performance [message #445942] Thu, 04 March 2010 18:01 Go to next message
Areint
Messages: 9
Registered: February 2010
Location: Iowa
Junior Member
Hey,

Is there anyway to speed up the performance of the go_record built it or is there an alternative way to do it.

I have a table with nearly 30,000 rows and I would like to implement a text field that will allow the user to jump to a specified record. The only problem is if they try to jump too far away it will take a long time to load (beginning to end of 30,000 takes over a minute).

This problem doesn't arise if all the records, or up to the one they are jumping to, have been fetched already, but even if I fetch all records at the beginning it will still take a long time to initially load them.

Any ideas or help would be greatly appreciated. Thanks.
Re: GO_RECORD performance [message #445975 is a reply to message #445942] Fri, 05 March 2010 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Gosh! What are you doing with 30.000 records at a time?!? Can't you restrict that number to a human-manageable size? I mean, who is capable of finding any information within that enormous data set? Perhaps you could perform query through certain items and get what you really want to see, not that much information.

Otherwise, check data block's "Number of Records Displayed" and "Number of Records Buffered" properties. You'll probably have to make them much larger than now (probably set to their default values). Try to modify these values and benchmark form performance. If it is not a problem, once you are satisfied, do come back and tell us what you've done.
Re: GO_RECORD performance [message #448299 is a reply to message #445942] Mon, 22 March 2010 11:46 Go to previous messageGo to next message
Areint
Messages: 9
Registered: February 2010
Location: Iowa
Junior Member
Hm, I tried to change the number of records buffered and the query array size for the block, but it didn't seem to affect the fetch time at all.

The form displays one out of ~ 30,000 records at a time, and I just want the user to be able to jump to any of the 30,000 records at any time with little fetch time.
Re: GO_RECORD performance [message #448300 is a reply to message #445942] Mon, 22 March 2010 11:48 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then you probably need to query one record at a time.
Re: GO_RECORD performance [message #448302 is a reply to message #445942] Mon, 22 March 2010 11:57 Go to previous messageGo to next message
gregor
Messages: 86
Registered: March 2010
Location: Germany
Member
Hi Areint,

relax, think about changing your applikation-logic.

When a user "stays" a record 1 and he knows, that he
has to go to record 25453 for example, than it could
be a solution to fetch always 1 record.
Perhaps it is an way for you to build an Search(CONTROLL-BLOCK)
and where the pushes the Serach-button the Query will
be manipulated, to show only records matching.
Re: GO_RECORD performance [message #448497 is a reply to message #445942] Tue, 23 March 2010 14:16 Go to previous messageGo to next message
Areint
Messages: 9
Registered: February 2010
Location: Iowa
Junior Member
Thanks for all the responses, I'm extremely new to SQL and databases (never taken a class or anything like that) so it will take me a while to figure out how to do this stuff.

With the control block is there a way to put the results from the users query into it and then query that block to pick out certain records? I am currently looking into the use/behavior of control blocks, so I do not know yet.
Re: GO_RECORD performance [message #448500 is a reply to message #448497] Tue, 23 March 2010 15:19 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unless I'm wrong, what you are looking for (i.e. want to code yourself) is default Forms behaviour. If you use data block wizard, it will create a simple and nice form with default actions enabled and working. It means that you'd be able to insert new values, update existing ones, delete records and - query records!

Querying is to be done as following: press the <Enter query> button on the toolbar. Enter search criteria. Press <Execute query> button. Records that satisfy search criteria are fetched and displayed on the screen.

So, why would you code all of that using a control block or something else? That's mostly unnecessary.
Re: GO_RECORD performance [message #487939 is a reply to message #448500] Mon, 03 January 2011 12:25 Go to previous messageGo to next message
Areint
Messages: 9
Registered: February 2010
Location: Iowa
Junior Member
Sorry for the super long delay, but I took a break from this project for school and now I'm back at in.

I feel like I should redefine what I want out of this, so...

I am wanting to create a toolbar that looks similar to my_toolbar.png (attached). The save, exit, enter query, execute query, previous record, next record, add record and, delete record should all behave as normal (which I have working). Thus the problem arises with the CURREC Text Item. What this should do is when a user enters a number (say 700) the form will display the 700th record from the query.

So, that being said, what would be the best method to achieve this? I realize I could make a call to GO_RECORD, but with 30,000+ records jump time can become a problem.

Also of note, this is a side project for my employer so I have very little control over the structure of the database (or anything like that).

[Updated on: Mon, 03 January 2011 12:31]

Report message to a moderator

Re: GO_RECORD performance [message #487940 is a reply to message #487939] Mon, 03 January 2011 12:33 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Areint wrote on Mon, 03 January 2011 13:25
Thus the problem arises with the CURREC Text Item. What this should do is when a user enters a number (say 700) the form will display the 700th record from the query.


Maybe I just don't understand what you are trying to do, but the 700th record of a query is the same as the first record of a query. There is no "record number" in a relational database. If 700 records match your search criteria, then there is no such thing as going to the 700th record. It's the same as going to any of the 700 that match.
Re: GO_RECORD performance [message #487943 is a reply to message #487940] Mon, 03 January 2011 12:39 Go to previous messageGo to next message
Areint
Messages: 9
Registered: February 2010
Location: Iowa
Junior Member
Sorry, my understanding of databases is very poor. Let my try this again. The currec should make the form display information in the 700th row (if the user enters 700), is that better?

The database table contains 30,000+ rows and the form I am creating displays one row at a time.
Re: GO_RECORD performance [message #487945 is a reply to message #487943] Mon, 03 January 2011 13:17 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
All I can say is to repeat the first paragraph of a message #445975.

So, why would anyone want to go to record 700? Or 12556? Or 23886? Or 5? What information does your user expect to find by entering such a number?

Performing query based on some criteria - YES. What you do (from what I understood) - NO.


Anyway: GO_RECORD is probably the simplest way. You could loop and do NEXT_RECORD, but I suppose that it would be slower.

Or: if records in a table are ordered (i.e. record number 700 really has "700" in one of its columns), then you could, actually, QUERY that table. You'd set the criteria (such as WHERE ID = 700) in PRE-QUERY trigger.

Or you could base that block on a procedure and pass 700 as a parameter; the procedure would return 700th record and display it on the form.

Once again: why would that user want to go to record number 700?
Previous Topic: Execute _query
Next Topic: open form on specific record after click on list button
Goto Forum:
  


Current Time: Tue Dec 06 00:22:46 CST 2016

Total time taken to generate the page: 0.07073 seconds