ListBox requery is too slow

[binky]

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? :)

[Jim Doherty]

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 :)

[binky]

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.

[Jim Doherty]

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

[binky]

I tried two different methods: using a stored procedure and building the SQL source on the fly.

Building it dynamically:

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 & "'"
 
lstSearch.Requery
 
 

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").

[Jim Doherty]

I tried two different methods: using a stored procedure and building the SQL source on the fly.

Building it dynamically:

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 & "'"
 
lstSearch.Requery
 
 

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.requery. 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.

 
Me!Listbox.rowsource="EXEC dbo.MyStoredProcedureName '"&Me!key_field1 &"'"
 

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 :)

[binky]

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:

lstSearch.RowSource = "EXEC dbo.usp_ssn_exception_srch '" & Combo102 & "', '" & Combo143 & "', '" & key_field1 & "'"

The above works, but actually seems to run slower than the following:

    lstSearch.RowSource = "SELECT " & _
                          "key_field2 AS K2, " & _
                          "key_field4 AS K4, " & _
                          "err_field1 AS E1, " & _
                          "err_field2 AS E2, " & _
                          "err_field3 AS E3 " & _
                          "FROM cv_error_log_s2 " & _
                          "WHERE prcs_nm = '" & Combo102.Value & "'" & _
                          "AND err_msg = '" & Combo143.Value & "'" & _
                          "AND key_field1 = '" & key_field1 & "'"
                          '"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!