Designing a business automated task and trying to optimize the queries performed to the database
Hi,
I'm building an automated task, that read a file everyday, perform operations on every line, and insert rows in database.
While performing operations, the naive approche is to iterate over each line and query the database to get the necessary datas
I originally wanted to limit the number of query to the database, by grouping the queries necessaries for each row into multiple big queries.
Is it a good approach ? What are the limits ?
Because now, instead of querying the database on each line and getting the necessary data directly, i need to retrieve in memory the data that i need for the current line, and it feels really bad honestly. My "where" conditions in my queries are not that trivial, and i find myself having to duplicate it in my data access layer and in my task.
One idea i had was to somehow tell the database which result row was for which line in my file, so i could only iterate on this line number, it would feel a bit better, but i dont know how
Am i overthinking ? I heard premature optimization is the root of all evil
Thank you for your help, i really appreciate it
I'm building an automated task, that read a file everyday, perform operations on every line, and insert rows in database.
While performing operations, the naive approche is to iterate over each line and query the database to get the necessary datas
I originally wanted to limit the number of query to the database, by grouping the queries necessaries for each row into multiple big queries.
Is it a good approach ? What are the limits ?
Because now, instead of querying the database on each line and getting the necessary data directly, i need to retrieve in memory the data that i need for the current line, and it feels really bad honestly. My "where" conditions in my queries are not that trivial, and i find myself having to duplicate it in my data access layer and in my task.
One idea i had was to somehow tell the database which result row was for which line in my file, so i could only iterate on this line number, it would feel a bit better, but i dont know how
Am i overthinking ? I heard premature optimization is the root of all evil
Thank you for your help, i really appreciate it
