Hello Good afternoon, I hope someone can give me an idea with this matter
I have my database with several tables (users, invoices, types, brands, equipment, components ...). One of the requirements of the system is that the user can generate a .csv file to send their reports to another company, this company requests that the information go in a certain order and with certain data, they are almost 50 fields that this .csv must contain For each team, information on brand, model, series, user, internal components of the team, capabilities etc. will be reported.
I will comment on how I try to resolve it and ask for a suggestion if there is another more efficient alternative that could be given to me. Thanks
I have a query with 10 join different tables, in order to get the basic information. One of the tables contains the components of a computer equipment (RAM, DD, SO, CPU, etc.) In this table you can have 2 or 3 RAM registers for a PC and the same for each component because each machine can have several. In the .csv file, they ask me for each row:
- • team number,
• responsable,
• bill,
• guarantee
• and what complicates me is how many discs do you have ?, how much capacity, how much RAM etc, this because this information will not be obtained with a single query since I would have to do one to know how many CPU's, another one for the total capacity storage depending on how many hard drives have the pc ...
Then the information is presented in a table with a cycle query that shows each row, but at the same time in the cases of certain columns where I require another query to present the information (number of disks, capacities ...), I made a function where I send 3 parameters that allow me to define which query will be executed to obtain the information for this column and that function serves me as for 30 of the 50 columns.
In the end I do what is necessary to export this query as a .csv file
The dilemma in all this is: Is there another way? I know that what I did works but will analyze the fact that this table will grow and that over time per row will be running about 30 queries and if it had only 10 rows it would be 300 queries and this loads the BD; you really have to think about a solution that besides solving the problem does not generate excess load in the database management system, any idea?