5 ms·

I wince every time I use application code to do what I know SQL can do faster and easier. ORMs are nice but it's often impossible to translate performant SQL in

I wince every time I use application code to do what I know SQL can do faster and easier. ORMs are nice but it's often impossible to translate performant SQL into the ORM's API. SQL is just so concise.

jmnicolas 7y ago
We recently changed one of our supplier. The previous one allowed us a full read access to their database (400 tables) but the new one insists that we use their SOAP webservices. So now when someone ask me for some data, I need to code, manually joining objects instead of crafting a quick SQL query and using the export to CSV function. Dictionaries (C# key value collection) have fast become a staple diet since Linq is way too slow when you try to 'join' hundred of thousands of objects together. It feels backward (and I'm saying this as a dev, I'm certainly no DBA or SQL expert).
- stefano 7y ago
  If the data fits in RAM, you could try loading it into an in-memory sqlite database, and run SQL queries there.
  - jmnicolas 7y ago
    I thought about making a "shadow database" on a server since my work PC is only 8 GB ram but ultimately decided against it. The first reason was GDPR compliance and the second is that we have an already complex architecture as it is (and we're only 2 to manage it) so I don't want to add one more brittle layer that will need debugging at the worst time.
    - wwweston 7y ago
      About GDPR: is it possible that holding data in a non-storage/volatile medium could be legally distinct from non-volatile storage, especially if it's essentially syncing with an authoriative data source that's responsible for managing GDPR? Because if not, it seems to me every proxy and persistence layer runs legal risks....
      - jmnicolas 7y ago
        To be honest I have no idea. But since our supplier still hasn't implemented the mandatory 4 months max data retention I'm not taking any risk.
      - iovrthoughtthis 7y ago
        No. GDPR covers any processing. Simply having the data pass through your machines / software makes you a part of the chain of processors / controllers. They do.
- paulddraper 7y ago
  Oh, this happens so much and it hurts so bad. Being able to join data in SQL or GraphSQL is a 10x time saver. As the sibling commented, I often create a shadow database though of course isn't perfect.
paulddraper 7y ago
Indeed. Doing data retrieval and manipulation with application code instead of SQL is like doing web layout with JS instead of CSS or doing graphics with canvas instead of HTML/SVG. Sure it's more flexible, but it's also far less simple, readable, and in many circumstances less performant. "Rule of least power" is a good principle. [1] [1] https://en.wikipedia.org/wiki/Rule_of_least_power https://en.wikipedia.org/wiki/Rule_of_least_power
- deleted 7y ago
  [deleted]

jmnicolas 7y ago

We recently changed one of our supplier. The previous one allowed us a full read access to their database (400 tables) but the new one insists that we use their SOAP webservices. So now when someone ask me for some data, I need to code, manually joining objects instead of crafting a quick SQL query and using the export to CSV function. Dictionaries (C# key value collection) have fast become a staple diet since Linq is way too slow when you try to 'join' hundred of thousands of objects together. It feels backward (and I'm saying this as a dev, I'm certainly no DBA or SQL expert).

stefano 7y ago
If the data fits in RAM, you could try loading it into an in-memory sqlite database, and run SQL queries there.
- jmnicolas 7y ago
  I thought about making a "shadow database" on a server since my work PC is only 8 GB ram but ultimately decided against it. The first reason was GDPR compliance and the second is that we have an already complex architecture as it is (and we're only 2 to manage it) so I don't want to add one more brittle layer that will need debugging at the worst time.
  - wwweston 7y ago
    About GDPR: is it possible that holding data in a non-storage/volatile medium could be legally distinct from non-volatile storage, especially if it's essentially syncing with an authoriative data source that's responsible for managing GDPR? Because if not, it seems to me every proxy and persistence layer runs legal risks....
    - jmnicolas 7y ago
      To be honest I have no idea. But since our supplier still hasn't implemented the mandatory 4 months max data retention I'm not taking any risk.
    - iovrthoughtthis 7y ago
      No. GDPR covers any processing. Simply having the data pass through your machines / software makes you a part of the chain of processors / controllers. They do.
paulddraper 7y ago
Oh, this happens so much and it hurts so bad. Being able to join data in SQL or GraphSQL is a 10x time saver. As the sibling commented, I often create a shadow database though of course isn't perfect.

paulddraper 7y ago

Indeed. Doing data retrieval and manipulation with application code instead of SQL is like doing web layout with JS instead of CSS or doing graphics with canvas instead of HTML/SVG. Sure it's more flexible, but it's also far less simple, readable, and in many circumstances less performant. "Rule of least power" is a good principle. [1] [1] https://en.wikipedia.org/wiki/Rule_of_least_power https://en.wikipedia.org/wiki/Rule_of_least_power

deleted 7y ago
[deleted]