Work around for creating object from database just to access a single property of the object

Question

I'm trying to optimize a small patch of code that's connecting to our servers over and over again. To start off, here's the patch of code

Dim sale as Sale()
With sale
    If .CustomerID > 0 Then
        .CustomerName = CCCustomer.SelectByID(.CustomerID).FirstNameLastName
    Else
        .CustomerName = "Cash Sale"
    End If
End With

So the issue is that if you have, say 50 customers that this is iterating through, it takes a long long time to get through the data, as for each customerID all you have to access the CCCustomer object, connect to the database, find the customer by ID, and then select the FirstNameLastName from the object.

My first thought was to just pull a list or array of ALL the customers right off the bat, and then match them by ID, but this seems to take even LONGER

Dim sale as Sale()
With sale
 Dim allCustomers() = CCCustomer.SelectAll
 If .CustomerID > 0 Then
   .CustomerName = Array.FindIndex(allCustomers, Function(c) c.CustomerID = .CustomerID)
 Else
   .CustomerName = "Cash Sale"
 End If
End With

(This code returns the customerId, not the names, but it still takes forever either way)

The only other option I can think of would be a complete rewrite of multiple stored procedures and updating the object to just natively hold the customers name as well as the customerID, but figured I'd check in before I go about completely ripping apart the rest of the program.

So does anyone know a good way to get a decent load time for something like this when you're pulling from a database?

EDIT: Sorry that was a bad explanation on my part - it's not iterating through the customer it's CREATING the customer object. So if you had something like this....

|  SaleID  |  CustId  |  ...  |
|    1     |    1     |  ...  |
|    2     |    1     |  ...  |
|    3     |    2     |  ...  |
|    4     |    1     |  ...  |
|    5     |    2     |  ...  |

Then it would use CCCustomer.SelectByID(1) 3 times - basically creating the customer object 3 times JUST to get the firstnamelastname information.

Answer 1

the most efficient and easy (not the most secure or readable) way would be to construct a query yourself adding all the Ids to a single SQL query string "...(where id=12 or id=44 or id=77 or etc..)" and then passing the query string to db using runsql() function or whatever equivalent of it in the system you are using. it will be even faster if you can put the logic which collects the ids into the query itself, and just pass a criteria (like persons name) to select theID.

the db will be very fast in analyzing the query, iterating through data and filling tables.

alternatively you could use linq (i.e. linqtosql) to have better readable and secure code, with somewhat worth but comparable performance. if this is not an option you could also consider putting the functionality into a database server stored procedure and calling the procedure with the parameter (criteria or a list of ids).

iterating through db data the way it is now, is the most inefficient way possible. i expect all other options i have mentioned to be one to two orders of magnitude faster.