The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Optimizing records fetching and display

I am using VB.NET 2003 and MS Access.

I have a TextBox and below it is a ListView control. As the user types a character in the TextBox, the records starting with that character get filled in the ListView control. The records are fetched from either Oracle or Access database.

Previously, I used to execute a SQL query, which looked something like this:

"Select FirstName from TableName where FirstName like '" & TextBox.Text & "'%"

This query was set to execute as the text in the TextBox changed. If the TextBox is blank, then all the records get displayed on the ListView alphabetically. Now I have decided to use a different approach so as not to query the database again and again. Now what we are doing is that we have taken a Collection Object in a Module. When the first character is typed in the Text Box, the above mentioned query executes and fills the Collection with all the records that start with that characted, e.g., 'A'.

Now as the user keeps on typing in the TextBox after, say A, the searching now starts in the collection, instead of requerying the database.

I guess this is comparitively fast. I have few queries regarding the optimization of this process:

1.)  What is the maximum capacity of a Collection?

2.)  Which one is fast, a Structure or a Collection?

3.)  Suggest any alternative way to speed the search process and filling of records in the ListView.

4.)  If I get this component developed in VC++, can I connect it with VB.NET 2003?
RPK Send private email
Monday, August 14, 2006
 
 
Kudos for reducing DB load.

What are you doing with the firstname after you have it in the app?

If you are going back to the db, is firstname a unique key?

If you want all records, you shouldn't need the where.

Also, technically if you don't specify an 'order by' clause your results from the db are not guaranteed to be in a certain order (maybe you fix this in the collection)?
D in PHX Send private email
Monday, August 14, 2006
 
 
'FirstName' is actually an example. Below I brief the exact situation.

I developed a software for a client. He works for many firms and maintains accounts for them. Each firm has many customers who buy products from them.

My client first stores all the names of the customers, firmwise in a database. In one of the data-entry forms, he wants to enter the transaction done by any customer of a firm.

The names of the customer of each firm sometimes exceeds 50,000. I therefore want that in the Data Entry form for recording transactions done by any customer, the names of all the customers that are displayed in a ListView are fetched fast enough.
RPK Send private email
Monday, August 14, 2006
 
 
Caching the records in a collection is a good idea.

However, the nature of the problem is such that you may want to offer a different solution than the "type-ahead" search. Specifically...

1) You always start with 50,000 records (or more as he gains customers).

2) Every time you pull up a record, you are going to have to fetch the firm's name so that the customer can confirm he wants John Doe from Company B, not John Doe from Company A. If you have two John Does from the same company, you're going to have to display more info so he can choose.

You may want to bucket sort the names and ids in advance, then fetch records as desired.

(Although, with today's technology, 50,000 records may not be a big deal.)
TheDavid
Monday, August 14, 2006
 
 
Exactly, 50,000 records are not a big deal in this case.

I am planning to use this feature on large databases.

As of duplicate customer names, our SQL query looks somewhat like this:

Select CustomerName from TableName where CustomerName like 'A%' and FirmID = 1;
RPK Send private email
Monday, August 14, 2006
 
 
>3.)  Suggest any alternative way to speed the search process and filling of records in the ListView.

While we all love these neat-o search things as you type, for the most part they do NOT really increase usability that much. Further, if you have more then 100, or so matches….then you not really saved much, as user will have to scroll a lot anyway….

I would suggest that you ONLY fire off the query when the user hits enter key..and not on change (each character). You will find the results much the same, and performance will go up. I mean, I start typing a name

    Albert

The above 5 characters was SO FAST to enter…but, your original idea here would fire off 5 quires before I even stopped to look at the results…

I don’t think you need to resort to a collection, or try to optimize the speed of the query, but you just need a slight change in your UI…..

I talk about searching, and few screen shots here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
Albert D. Kallal Send private email
Monday, August 14, 2006
 
 
You should find an MS Access guru and see if there's any significant delay time in retrieving records from Access verses SQL Server. (I'm under the impression there is, for very large recordsets.)

My next question is of course, how big is each recordset? If you're going for a full blown PIM that shows a cell phone photo of each client, then yes, that would be a consideration.

However, based on what you've described, I can answer the following questions:

1) To the best of my knowledge, there's no hard coded limit to the number of items in a collection; either in VB or C++. You're more likely to hit the physical RAM or 32-bit/64-bit integer limit first.

2) Structures are faster. However, it's easier to ensure reliability and accuracy with a Collection. For example, no index out of bounds errors. I would stick with the Collection.

4) Yes and there are various ways to do it, but in a nutshell, the C++ code would be treated as an external third-party control.
TheDavid
Monday, August 14, 2006
 
 
Sorry Albert, but I really don't like your suggestion. Can you imaging if google had separate search boxes for different parts of the content you were searching on? One for title, one for author, one for content, one for headings, etc...  You might argue that everyone knows how to differentiate firstname/surname but many countries have the family name as the first name.

KISS. Give users one box to search for and it should be your application that knows which fields to search on. If you've already got all records locally (depending on app) then why not filter results in real time, it can only help.
Adrian
Monday, August 14, 2006
 
 
Typically, you have a timer running so that the query is fired once, after the user stops typing for one second or so.
MBJ Send private email
Monday, August 14, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz