DEV Community

loading...

Discussion on: Example of complex SQL query to get as much data as possible from Database.

Collapse
gregorgonzalez profile image
Gregor Gonzalez

I love this! I work with this kind of query every day.

I think It's really good, the first important thing is to reach your goal. I do the same process as you and only use inner-left joins as a standard to keep it simple (not right, not outer, etc). Then check the performance, try with thousands of records (I do this for huge reports), use "explain" and others approach to test.

I work mostly with MySQL, I can only tell you a few things: Make this query a view. In MySQL try to avoid a lot of subquerys because performance can be lost. Don't make "view of views" because the performance and maintaining this can be difficult.

I still avoid complex functions and stored procedures to keep it simple for new teammates and jr developers.

Collapse
tyzia profile image
Andrei Navumau Author

Hi, Gregor.

Thanks for commenting. Yes, I'll consider using view in this case. And I do agree, that stored procedures can be tricky for new teammates.

Forem Open with the Forem app