| GO_RECORD performance [message #445942] |
Thu, 04 March 2010 18:01  |
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 #448299 is a reply to message #445942] |
Mon, 22 March 2010 11:46  |
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 #448302 is a reply to message #445942] |
Mon, 22 March 2010 11:57 |
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 |
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 #487939 is a reply to message #448500] |
Mon, 03 January 2011 12:25 |
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 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Areint wrote on Mon, 03 January 2011 13:25Thus 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 |
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 |
 |
Littlefoot
Messages: 21823
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
