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