Performance Hits
-
I'm building a form which takes way too long to load up over the WAN. The user needs to navigate through the list of Part Numbers which means that I've loaded a lot of data over the network. To speed this process up and give the user the impression they are seeing all the data and navigating between them I have loaded a bound form with only the primary key so when they navigate to the previous, next, last or whatever I fill the remaining data from a single record using the key as the parameter and filling the remaining controls using a stored procedure. This ends up being SIGNIFICANTLY faster than the previous method. There are 109,000 records. I'm a beginner when it comes to tuning a client/server application for performance and would appreciate any alternatives/suggestions for enhancing performance.
One idea was to merely fill a control with the keys and have the user pick each one. This takes time to fill the control but once filled it works ok. However, there is a limit on the number of records returned for the control in Access and unless I could overcome this limitation it's unacceptable.
Steve King
-
This was removed by the editor as SPAM
-
I personnaly don't like that kind of programing behavior in a client/server environment.
That one fits well on a standalone database/application of Clipper/ACCESS... type of database.
I usually let enter the key or a part of the key (in case of a varchar field) and retrieve only the relevant records.
Bye
Gabor
Bye
Gabor -
And that is the reason I've requested some help here. Your response however was somewhat limited since I would need to implement any alternatives. One problem is the user would not necessarily know what primary keys were entered in the database so I would need to provide them with that information is some fashion. Any help is appreciated.
-
quote:
I personnaly don't like that kind of programing behavior in a client/server environment.That one fits well on a standalone database/application of Clipper/ACCESS... type of database.
I usually let enter the key or a part of the key (in case of a varchar field) and retrieve only the relevant records.
Bye
Gabor
I had not posted earlier because I know zilch about Access, but 109,000 rows seems a bit excessive for a user to deal with in a control. I agree with Gabor that you should probably have the client restrict what is returned: this will not only limit the amount of work by the server, it will also present the user with a more manageable subset; certainly they can't need to scan through all 109,000 rows.
If the application is constantly using large sets of rows and you have RAM to spare on the clients and the table is relatively static, then you could fill and then disconnect a local ADO recordset via a stored procedure that returns the "lookup" column(s) and the primary keys. Then index the fields of the local recordset and use the Filter property with LIKE to populate the control based on the user's filter entry. We've done this in order-entry applications where every line of the order needs a lookup to the inventory.
--Jonathan
--Jonathan -
Thanks for the idea. I definitely concur with limiting the return and was looking for proven ways of accomplishing this. I'm leaning toward returning the keys into an ADO recordset, disconnecting and using the recordset to drive the navigation. When the user navigates between the records the server would return the data and the unbound form populated. Actually, whenever I only return a single record, the form populated very fast. The application allows the user to look at the data using may different filters and also builds an assembly set of parts inside a treeview using the keys. That's modified to only build if the user chooses.
-
quote:
Thanks for the idea. I definitely concur with limiting the return and was looking for proven ways of accomplishing this. I'm leaning toward returning the keys into an ADO recordset, disconnecting and using the recordset to drive the navigation. When the user navigates between the records the server would return the data and the unbound form populated. Actually, whenever I only return a single record, the form populated very fast. The application allows the user to look at the data using may different filters and also builds an assembly set of parts inside a treeview using the keys. That's modified to only build if the user chooses.
Seems kind of strange that the PK is meaningful enough that users can filter on it, but your design obviously diferes from what we do. Don't forget to index ("Optimize" in ADO) the field.
The SP to populate the form once the item is chosen should probably be written with output parameters rather than a recordset.
--Jonathan
--Jonathan -
Steve,
In your case, as I've already told you, you should implement a kind of search form where the user is entereing the search criteria and based on those criteria you will either call a stored proc or -if the query is very complicated and unpredictable- you ohave to build an SQL query dynamically.
I'm trying to use always stored procedures because of safty and performance reasons.
Basically in all my applications I've implemented search fields or search forms and if multiple values have been retrieved then I show a result form, where the end user can select the right one.
The basic idea of the client server programming is the separation of the data and the presentation.
You have to be aware of the fact that all the data is residing elsewhere and a lot of users are accessing to those data.
So you only have to retrieve the data you absolutly need. No more, No less.
That's why we are preaching not make a select * from table1 if you only need select xxx from table1, net to retrieve all the rows if you only need 1 single records etc.
At my customers I'm seeing very often that kind of mistake made by other programmers who had no really experience with client/server programming and had tried to implement port a dBase/Clipper/Access database-application in a hundreds of user type of application.
After they asked me to find why this app is so slow.
Transporting 100.000 records over the network means each time at least 1 MB of data trasfer per user! Where you only need 1KB!
Think on it
Bye
Gabor
Bye
Gabor
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply