473,888 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ListBox requery is too slow

19 New Member
Good afternoon folks, I have a performance question if anybody might have suggestions.

Functionally, everything i'm about to describe works as intended. The only problem I'm having is speed.

Assume:

1) Using MS Access 2003 in an ADP project, SQL Server 2000 Enterprise backend
2) Main form is populated by a stored procedure, which works flawlessly
3) There is a listbox on the form that will display matching records based on a key field in the form's recordset.

For example, I return 10,000 records, and there's a specific field called key1. The listbox has it's own Stored Procedure behind it, and whenever I change records on the form (Utilizing OnCurrent), the listbox requeries and finds related records based on key1.

The problem I'm having is that the requery takes too long. So if you have 10 or 100,000 records on the form, even if only one or two records are returned in the listbox is takes a good 1-2 seconds to populate, meaning that navigating to other records on the form appears to "lag" as the listbox requries.

I have tried the stored procedure route for the listbox rowsource, and also building SQL on the fly via VBA to pull directly from the underlying table, and both are the same in performance. This is going to kill my users.

Any suggestions? :)
Dec 1 '07 #1
6 6099
Jim Doherty
897 Recognized Expert Contributor
Good afternoon folks, I have a performance question if anybody might have suggestions.

Functionally, everything i'm about to describe works as intended. The only problem I'm having is speed.

Assume:

1) Using MS Access 2003 in an ADP project, SQL Server 2000 Enterprise backend
2) Main form is populated by a stored procedure, which works flawlessly
3) There is a listbox on the form that will display matching records based on a key field in the form's recordset.

For example, I return 10,000 records, and there's a specific field called key1. The listbox has it's own Stored Procedure behind it, and whenever I change records on the form (Utilizing OnCurrent), the listbox requeries and finds related records based on key1.

The problem I'm having is that the requery takes too long. So if you have 10 or 100,000 records on the form, even if only one or two records are returned in the listbox is takes a good 1-2 seconds to populate, meaning that navigating to other records on the form appears to "lag" as the listbox requries.

I have tried the stored procedure route for the listbox rowsource, and also building SQL on the fly via VBA to pull directly from the underlying table, and both are the same in performance. This is going to kill my users.

Any suggestions? :)

If you are populating the list box via stored procedure there should not be a speed issue. Do you have indexes properly set on the server specific for your needs ie: on SELECT and WHERE clause columns? and calling back only the columns you need and sufficient row depth ie not falling into the trap of SELECT * from blah then only displaying 2 columns in the listbox and so on. Without knowing the design of your db, server optimisation its somewhat difficult working blind here

Regards

Jim :)
Dec 1 '07 #2
binky
19 New Member
The table i'm pulling from has about 20 columns, and i'm grabbing six of them. All the fields in my select and where clauses happen to be indexed, so that shouldn't be an issue.

I think it's less of a server problem and more of a client problem, i.e. the listbox.requery command that's causing it.
Dec 1 '07 #3
Jim Doherty
897 Recognized Expert Contributor
The table i'm pulling from has about 20 columns, and i'm grabbing six of them. All the fields in my select and where clauses happen to be indexed, so that shouldn't be an issue.

I think it's less of a server problem and more of a client problem, i.e. the listbox.requery command that's causing it.
hmm ..how are you populating the listbox? I,ve never had a problem on this with listboxes and am running Acc2000 adp's and sql Server2000

Jim
Dec 1 '07 #4
binky
19 New Member
I tried two different methods: using a stored procedure and building the SQL source on the fly.

Building it dynamically:

Expand|Select|Wrap|Line Numbers
  1. lstSearch.RowSource = "SELECT  key_field2 AS k2, key_field3 AS k3, key_field4 as k4, key_field5 as k5, err_field1 as e1, err_field2 as e2 FROM cv WHERE cv.key_field1 = '" & key_field1 & "'"
  2.  
  3. lstSearch.Requery
  4.  
  5.  
The stored procedure is exactly the same query, although it uses the parameter @key_field1 which is passed from the form (from the form control "key_field1 ").
Dec 3 '07 #5
Jim Doherty
897 Recognized Expert Contributor
I tried two different methods: using a stored procedure and building the SQL source on the fly.

Building it dynamically:

Expand|Select|Wrap|Line Numbers
  1. lstSearch.RowSource = "SELECT key_field2 AS k2, key_field3 AS k3, key_field4 as k4, key_field5 as k5, err_field1 as e1, err_field2 as e2 FROM cv WHERE cv.key_field1 = '" & key_field1 & "'"
  2.  
  3. lstSearch.Requery
  4.  
  5.  
The stored procedure is exactly the same query, although it uses the parameter @key_field1 which is passed from the form (from the form control "key_field1 ").

Hi Binky,

I do not see anything wrong with the SQL other than to make this observation.... . if...... that is a straight lift from your on current procedure you are requerying the listbox twice by first setting the rowsource and then using the line lstSearch.reque ry. The setting of the rowsource dynamically like that is sufficient to cause the list to requery itself. As is you are firing off two SELECT commands to the server and it is having to pull back two recordsets back. (You only see the one of course) but that may be one reason for your delay?

You are obviously aware that a stored procedure is the optimum method for pulling back the records because of its pre-compiled execution plan. When you have used an SP in the past I'm rather guessing/hoping your syntax is the following method for doing that in the on current event? if not then try it out.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Me!Listbox.rowsource="EXEC dbo.MyStoredProcedureName '"&Me!key_field1 &"'"
  3.  
I say this because I've seen a number of occasions overly large procedures created at module level simply to populate a listbox
because some good people perceive the only way of pulling back a dataset from the server is by using the command object method creating parameters, appending parameters to collections opening recordsets and all sorts of convoluted code within a functional procedure when the simplest of lines does the job.

As an aside, If it does have to pull back 'many many' records per main form record, then the practical advice is to fire the query only by activation of a button 'Fetch List' or something like that and design your app around that. You will be paying homage to the networking guys who for once will be seeing a develpper who is actually restricting network traffic :)))

If the row depth of the listbox is only a few records each time then generally this shouldnt be an issue but if it has to contend with huge datasets then the fundamental principle of 'performance versus overhead' in designing your app has to be taken into account.

I'm not sure I,ve helped but I hope this helps a little

Regards

Jim :)
Dec 4 '07 #6
binky
19 New Member
I contimplated having a toggle button that will turn the box on and off, but the users really want it on all the time.

I tried:

Expand|Select|Wrap|Line Numbers
  1. lstSearch.RowSource = "EXEC dbo.usp_ssn_exception_srch '" & Combo102 & "', '" & Combo143 & "', '" & key_field1 & "'"
The above works, but actually seems to run slower than the following:

Expand|Select|Wrap|Line Numbers
  1.     lstSearch.RowSource = "SELECT " & _
  2.                           "key_field2 AS K2, " & _
  3.                           "key_field4 AS K4, " & _
  4.                           "err_field1 AS E1, " & _
  5.                           "err_field2 AS E2, " & _
  6.                           "err_field3 AS E3 " & _
  7.                           "FROM cv_error_log_s2 " & _
  8.                           "WHERE prcs_nm = '" & Combo102.Value & "'" & _
  9.                           "AND err_msg = '" & Combo143.Value & "'" & _
  10.                           "AND key_field1 = '" & key_field1 & "'"
  11.                           '"ORDER BY key_field4, err_field1"
I took out the requery, and it sped it up just a little bit. But there's still a very noticable lag when you are navigating records. I suppose it will work, my users will just have to deal with it for the time being :)

Thanks for all your help, Jim. Your explanations were very informative!
Dec 4 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
3581
by: Annette Massie | last post by:
I would like to have a form that lists current addresses being used. On this form I would also like to have a command button that would allow the user to add an address if they do not see it listed. Is it possible to have the form show the list box (which is a combinination of the address and city/state/zip), along with the detail fields address and city/state/zip? I guess I was thinking of have the listbox display and then when the...
4
8918
by: Kyralessa | last post by:
In Access 2000, I have a base form with a ListBox of conference registrants. In the form's declarations section I include Dim f as Form To add a registrant I'm doing this: Set f = New Form_frmSingleRegistrant f.Caption = "New Registrant" f.cmdSave.Caption = "&Save New Registrant"
1
3000
by: M Wells | last post by:
Hi All, I am developing an Access 2003 project application with the back end in SQL Server 2003. I have a master form that tracks projects, and several subforms on it that track various aspects of the project. On the masterform I have two unbound listboxes that I populate with information regarding the current record in one of the subforms.
7
2018
by: Colleyville Alan | last post by:
I have an app in which users are displayed a list of mutual fund from which they can choose. There is a listbox embedded in a two-tabbed notebook control. When the form is initally opened, the listbox loads info: Private Sub Form_Open(Cancel As Integer) TabControl.Value = 0 Tab_Label1.Caption = "Select Alternatives from the Select List" FundAlternativesList.RowSource = "Load_Select_Alternatives_Form" Call Highlight_Defaults End Sub
4
26211
by: N. Graves | last post by:
Hello... thank you for your time. I have a form that has a List box of equipotent records and a sub form that will show the data of the equipment select from the list box. Is it possible to make a change in the name field in the details, then refresh the list box with new name? Please help and thanks!
3
2439
by: RC | last post by:
I have a form where the user enters the Model info. The user picks the Model from either a combobox or listbox which both are filled from the Models table. If the Model type isn't in the boxes, then the user can click a button and add a new Model to the Models table. When the user clicks the "Add new Model" button, a pop-up Form opens the user types in the new Model name into a text box and a new record is added to the Models table and...
6
9997
by: Mark | last post by:
Hello. I have a listbox whose rowsource is set to a saved query (call it "qry_customer_list.") When I add customers to my database, I call the listbox Requery method so that the listbox will display the customers that I added. Unfortunately, the listbox display does not change. If I close the form and reload it, then the listbox will show the new customers. Any suggestions on how to fix this?
1
4036
by: ANDRESUK | last post by:
All I have a listbox in a form footer. When the form footer is visible the listbox requeries fine but when I turn the form footer visible=false the listbox does not requery at all. What I am trying to achieve is to change the listbox's rowsource, as I said this works fine when the form footer is visible but then nothing happens when the form footers is invisible. I know the rowsource is changing in the listbox but it doesn't requery....
3
8111
by: Prochot | last post by:
I'm trying to design a database to track projects and the associates assigned to them. I have almost no experience with this. I have three tables: -Projects -Associates -Assignments where assignments contains projectID and associateID and is related to the projects.projectID primary key and the associates.associateID primary key. I have a form based on the projects table that contains a list box that I would like to use to...
0
9800
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11182
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10886
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7990
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4245
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.