Should I select the entire table or make a connection each time with a WHERE condition?

Question

I appreciate on first glance this could seem obvious but I'm not sure on the context. I'm making an application that calculates a shopping list from a given meal plan. This involves many iterations, tables and connections to the database. Tables include:

MealPlan (Contains the mealID for 6 meals a day for 7 days)

Meal (Contains the name of each meal and an ID)

Ingredients (Contains every ingredient on the system)

MealIngredients (Contains a foreign key of mealID and ingredientID, showing which ingredients and their quantities relate to a meal)

If each meal contains, on average, 10 ingredients and I've got 6 meals a day for 7 days of the week, that's 420 separate connections to the database to get the data for particular ingredients.

So, instead, would it be quicker/more efficient to select the entire table?

Option 1 (New connection each time):

ingredientID = 5

For Each row As DataRow In ingredients.table.Rows

    ingredients.setTableContents("SELECT `quantity` , `name`, `measurement` FROM `tblIngredients` WHERE ingredientID=" + ingredientID)

    'Do stuff with the ingredients attributes

Next

Option 2 (Select entire table and reference DataTable):

ingredientID = 5

dataTable = ingredients.setTableContents("SELECT `quantity` , `name`, `measurement` FROM `tblIngredients`")

For Each row As DataRow In ingredients.table.Rows

    quantity = table.Rows(mealID).Item(0).ToString()

Next

if you have a website app tht sifts through the data and shows the wanted information, you would get it ince and use it locally. if you have a website with databsse and gather depending on selection your data, get it for every selection — nbk, Jul 26 '20 at 14:34
420 rows is not that big of a deal, I would say... grab them all. There is a saying, the best query is the query you don't make. If you are not closing your connections, you are going to run into issues with available connection pools (or at least in SQL Server you will, not sure about MySQL). — Pete -S-, Jul 26 '20 at 14:42
Sorry, I didn't clarify this very well in the original question. The database is a MySQL database running on the back of my website. I setup a SSH tunnel connection when the program loads and then send through my queries. By 420 rows, that was an example of how many 'queries' I'd have to send if I had a separate one for each ingredient. The actual ingredient table could contain thousands of records (hypothetically with scale in mind). Might be minimal gains now although could help long term as the system grows. — William Eddy, Jul 26 '20 at 14:59
Also, is having one connection that you send all your queries through advisable? The only reason I did it that way is that to setup a SSH tunnel with a slow web server takes about 5 seconds. — William Eddy, Jul 26 '20 at 15:00

Andrew Morton · Accepted Answer · 2020-07-26T14:50:34.710

2

It seems to me that you could improve it by getting all the ingredients for a particular meal. You can do that using JOIN.

For example,

SELECT I.Name
FROM Meal AS M
JOIN MealIngredients AS MI
    ON M.ID = MI.MealID
JOIN Ingredients AS I
    ON MI.IngredientID = I.IngredientID
WHERE M.ID = 123

Might yield a result of:

Name
--------------
Brown bread         
Cheddar cheese      
Butter

You just need to pass it a value for the Meal ID - 123 in that example.

Or to see everything available from that query, using SELECT * might give:

ID  Name                    MealID  IngredientID IngredientID Name
----------------------------------------------------------------------------
123 Cheese sandwich         123     22           22           Brown bread         
123 Cheese sandwich         123     54           54           Cheddar cheese      
123 Cheese sandwich         123     51           51           Butter

You should select the columns you want explicitly, i.e. do not use * in actual code.

edited Jul 26 '20 at 14:50

answered Jul 26 '20 at 14:46

Andrew Morton

24,203
9
60
84

Nice job of analyzing the question... I was looking at the question's face value. Kudos!!! – Pete -S- Jul 26 '20 at 14:48
Great example, that's a huge help, thank you. I'll do a little more research into using join specifically in VB.Net as I need to change my current class setup for connections. That's the solution it would seem though. – William Eddy Jul 26 '20 at 15:02
@WilliamEddy You're welcome :) Oh, and you should make a new connection for each query, then dispose of it, there's an example of doing that using the Using statement at [SqlConnection Class](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=netframework-4.8) - you might need to select "VB" instead of "C#" in the dropdown near the top-right of that page. – Andrew Morton Jul 26 '20 at 15:17
@WilliamEddy There is no difference in the sql query between vb.net language and other languages. Join would be written exactly the same in, say, C#. – Mary Jul 26 '20 at 18:46
1

@AndrewMorton I was replying to William's second comment where he says "using join specifically in VB.Net ". I was trying to communicate that the query string would be the same. – Mary Jul 26 '20 at 23:10

Should I select the entire table or make a connection each time with a WHERE condition?

1 Answers1