SQL Select Statement Performance
 

This is probably a dumb question, but I’ll ask anyways. I have an application that has a GetAll() function that gets all the records from a Customer table in a MS Access database. I just use a select statement that looks like this:
I use this function a fair bit throughout my application for different things. Sometimes I only want the first and last name from the record; so should I make another function for this and go:
Would doing this make all that much of a difference performance wise, because if it doesn’t I won’t bother changing it in my code. There are about 10 other fields in the Customer table other than FirstName and LastName, so I was thinking maybe if it only had to get the two fields it my save some time a bit of memory. What do you guys think?

I don't think it will make that big of a difference. Either way it still has to grab every row from the table. But then again it kind of depends on how large the table is. If it is really really big then it would probably make a difference but if its not huge then it might not make that much of a difference. I guess the best way to find out is to try it and see what happens.

You know what, unless it's a big heavy program dependant on a heavy db, I don't think it's going to matter. From what you say, it doesn't sound like it's not going to hurt to make an extra sql command here or there. Unless the db is remote, or under heavy usage, I wouldn't bother. In a recent db program I made, I had lots of calls to a mysql db, and didn't seem slow at all. The question is: Is the change in code going to make a difference in speed or performance noticable to the user(s)? Because a user can't really count in nano seconds.

Also.

When you have a WHERE clause you return fewer records, using less direct I/O compared to a full-table scan. This gets down to size of the table - ie., number of records in the table. You are doing a defacto full table scan and retreive with your current SQL.

In other words, if you had an index on the field(s) in the query, and if you were using a where clause and if there were say 20000+ records in Customer, then you would speed things up a noticeably by using a restricted query.

yea I doubt this table will ever have more than 100 to 200 records. Maybe after I load it with that much test data I will try different things to see if I see a difference. As Booooze said, a user can't count nano seconds. Thanks for the input guys.

Famous words from the design stage. Even if it is a small project you might as well make a scalable solution... unless of course, you actually need every single record... :rolleyes:

There are no scalable solutions that use Access databases! Anyway, enough prejudice...

It seems eminently reasonable just to go on SELECTing * - but I have to admit I'd rather do some more selective SELECTing where possible. Performance probably won't improve significantly, but it'll give you a warm cosy feeling. Especially when you unexpectedly gain a few thousand customers (but then I guess if that happens you'll be getting a warm cosy feeling from your bank account anyway).