I am building a web application and wanted to know if I will see a performance increase if I create some Classes that are Shared across the application to hold all the data which would otherwise be accessed from a MySQL database.
I have three tables: tblHouses, tblBrands, and tblProducts.
tblProducts has a primary key: idProduct and a foreign key: idBrand.
tblBrands has a primary key: idBrand and a foreign key: idHouse.
tblHouse has a primary key: idHouse and no foreign key.
I can create Classes for each of these tables with methods to retrieve information from the database based on idHouse, idBrand, or idProduct but I wondered if creating a Shared Class Catalogue which contains a List(Of Class Products), Class Products containing the information from tblProduct along with idBrand and idHouse which are retrieved from the database on initialisation, might give improved performance.
I envisage being able to then iterate through the list of Products within Catalogue to get a List of Products dependent on Catalogue method parameters. One example method would be:
Catalogue.getProductsByBrand(idBrand) returns List(Of Products)
For x = 0 to List.Count Step 1
if List(x).idBrand = idBrand then
! I know that this is just pseudocode but it's only there to help explain my requirements.
Will iterating through these objects provide a performance increase compared to repeat SQL database calls or not given that I can also load Class House and Class Brand similarly as shared Classes. The instantiation could be done from a CMS when administrators make a change to the database tables and because the Class is Shared these Classes will be available in the same state to all users of the application.
Please advise as I have been unable to find anything conclusive when searching online for an answer or advice.
If you are unsure of the definition of Catalogue, House, Brand, or Product then they are described below:
Catalogue: A list of all products
House: A 3rd Party Supplier (e.g. Diesel)
Brand: The 3rd Party Suppliers Brand (e.g. Fuel For Life)
Product: A unique product under the Brand (e.g. Fuel For Life EDT 50ml)