[AccessD] Slow loop through list box

David Emerson davide at dalyn.co.nz
Wed Sep 24 16:58:24 CDT 2003


Thanks Rich,  The fields are indexed but I appreciate the obvious - often 
that is what is overlooked.

David

At 24/09/2003, you wrote:
>You said that the BE is SQL Server 2K...
>
>You didn't mention anything about it so I figure I'd bring up the obvious..
>Why don't you create an index using the ID, FirstName, LastName fields.
>This should give you some additional performance if you haven't already done
>so.
>
>rich
>
>-----Original Message-----
>From: Drew Wutka [mailto:DWUTKA at marlow.com]
>Sent: Wednesday, September 24, 2003 12:52 AM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Slow loop through list box
>
>
>What kind of ID are you using...a string?  I would recommend making a table
>that has an Autonumber to represent each customer's ID, that should shorten
>the length of their ID.
>
>You can always go with the class collection method, but that might be a
>little slower to run the query.
>
>Drew
>
>-----Original Message-----
>From: David Emerson [mailto:davide at dalyn.co.nz]
>Sent: Tuesday, September 23, 2003 11:23 PM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] Slow loop through list box
>
>
>Thanks Drew,
>
>The problem with the IN() solution is that because the max length of the
>SQL variable is 8000 chars and the average length of the ID is 8 that
>limits us to 1000 customers selected at a time.
>
>I think that I might have to rethink when I update the table with the
>selected ID's.  Perhaps I could do it after they have finished selecting
>all customers.
>
>David
>
>At 23/09/2003, you wrote:
> >Just a thought, I would go in one of two different directions with this.
> >One method would be to set a Global string variable.  Then when I looped
> >through the selected items, I would add them to the string, comma
>delimited.
> >Then I would build a function that returns the string, and change the query
> >criteria to look like this: In(MyStringReturningFunction()).
> >
> >The other approach would be to create a global 'collection'.  Then make a
> >class to hold the ID's.  Then I would add an instance of that class for
>each
> >ID that is selected, and set that instance's key in the collection to the
>ID
> >(if the id is numeric, the key has to be a string, so I would set it as
> >"ID:" & MyIDField.  Then I would write a public function that returned true
> >or false if that ID was in the collection or not (you can just use error
> >handling, and set a empty class variable to the collection with that ID, if
> >it returns, it exists, if not, it doesn't.).
> >
> >I think the In() statement would be faster, especially if you have indexing
> >on that field, where you are querrying it, because I think it can use the
> >string in the In() clause against it's index tables, instead of having to
>go
> >through each record determining if it's true or false.
> >
> >Drew
> >
> >-----Original Message-----
> >From: David Emerson [mailto:davide at dalyn.co.nz]
> >Sent: Tuesday, September 23, 2003 6:42 PM
> >To: accessd at databaseadvisors.com
> >Subject: [AccessD] Slow loop through list box
> >
> >
> >I have a listbox which is used to select clients for including in
> >reports.  When the listbox is updated I have a separate text box that shows
> >the names of the clients selected and the number selected.  At the same
> >time I store the ID's of the clients in a table that is used to filter out
> >the required client records. The initial list box has over 1800 names.  It
> >is taking about 10 seconds to update the lists each time a client is
> >selected.  If I rem out the adding of ID's to the table then the time
> >reduces to about 3 seconds.  The main part of the code is -
> >
> >      With Me!lstClientList
> >          For Each varItem In .ItemsSelected
> >              If .Selected(varItem) = True Then
> >                  strList = strList & .Column(1, varItem) & vbCrLf  ' Adds
> >client name to selected list
> >                  intClientNo = intClientNo + 1
>'
> >Counts number of selected clients
> >                  rst.AddNew
> >'Add client ID to table
> >                      rst!CustIDNo = .Column(0, varItem)
> >                  rst.Update
> >              End If
> >          Next varItem
> >      End With
> >
> >Any thoughts as to how I can speed up the process?  The BE is SQL2000.
> >
> >
> >Regards
> >
> >David Emerson
> >DALYN Software Ltd
> >25b Cunliffe St, Johnsonville
> >Wellington, New Zealand
> >Ph/Fax (877) 456-1205
> >
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 



More information about the AccessD mailing list