5 ms·

I’m struggling with a lot of things in this. Why couldn’t you test your stored procedures? Assuming you use an ORM, do you test it’s logic? Because if you don’t

by moksly 6y ago

I’m struggling with a lot of things in this. Why couldn’t you test your stored procedures? Assuming you use an ORM, do you test it’s logic? Because if you don’t, then what’s the difference? Why wouldn’t you have more than one database? What if you’re global? Why would a database ever be a single point of failure? Why wouldn’t you be capable of rolling it back, or test in anyway that’s different from what you do with your other code? Is it perhaps because you don’t know the tools to do so? Why would database logic save you pain in development? Typically utilising stores procedures would make development less straightforward because you split your logic up, but you do it because database servers are a lot better and a lot more efficient at giving you the data you need. Don’t get me wrong. I only use stored procedures, or even any SQL tooling, when they are the only way to get efficient or persistent data, but it’s not because or any of the reasons you list. It’s because 95% or the developers we hire don’t really know how to use databases. A good example is an issue one of juniors had recently. We have a lot of data organised in what is essentially a tree structure where the nodes only know their parents. He needed to pick any node and gets its children + related rows from a range of tables in three databases. The task itself was simple enough, except the tree has millions and millions of nodes and even more millions of related rows, and he needed the data fast. He had hacked around, trying different stuff like loading it gradually, caching it between uses and so on, but he couldn’t get it fast enough. We made a recursive stored procedure that was capable of giving him the data almost instantly, because thats what database servers do. In a couple of years, when this needs tweaking, however, it’s very likely that someone like me will be needed to look at it, because recursive SQL isn’t something a lot of our developers do, so in any other case, doing it in C# would’ve been better.

Phrodo_00 6y ago
> Assuming you use an ORM, do you test it’s logic? Because if you don’t, then what’s the difference? You don't test the ORM, you test your models. If you're writing db procesures you'd test the procedures, not the db's procedure engine. But yeah, you should be doing your testing in a test database already. The only problem I see is that you might have a tighter coupling between db schema version and software version with procedures (although then again, you could avoid that if you're careful and make it less coupled)
TedDallas 6y ago
Recursion in SQL is one of the questions I always ask Data Engineers during interviews. The answer in MS T-SQL is to use a recursive CTE. It is not a common problem, but it comes up once in a while. Using a CTE is a better solution than calling an SP recursively because you can only nest 32 calls deep. In the MS Azure SQL Data Warehouse product you are simply fucked if you need to do recursion.
- mycall 6y ago
  > SP recursively because you can only nest 32 calls deep T-SQL only gives you 100 calls deep. Also, having CTEs calling other CTEs can create a very slow query plan, which takes unrolling each CTE into either a table variable or temp table to resolve.
- deleted 6y ago
  [deleted]
- thinkloop 6y ago
  > Recursion in SQL is one of the questions I always ask Data Engineers during interviews Everyone always asks that in every language at every interview and it is rarely used in practice - ahhh interviews.
  - kls 6y ago
    I think it depends on the developers style of how they reason. I use recursion a lot when dealing with loose data, to clean it up. Recursion is one of my go to's for deduplicating list.

by moksly 6y ago

Phrodo_00 6y ago

> Assuming you use an ORM, do you test it’s logic? Because if you don’t, then what’s the difference? You don't test the ORM, you test your models. If you're writing db procesures you'd test the procedures, not the db's procedure engine. But yeah, you should be doing your testing in a test database already. The only problem I see is that you might have a tighter coupling between db schema version and software version with procedures (although then again, you could avoid that if you're careful and make it less coupled)

TedDallas 6y ago

Recursion in SQL is one of the questions I always ask Data Engineers during interviews. The answer in MS T-SQL is to use a recursive CTE. It is not a common problem, but it comes up once in a while. Using a CTE is a better solution than calling an SP recursively because you can only nest 32 calls deep. In the MS Azure SQL Data Warehouse product you are simply fucked if you need to do recursion.

mycall 6y ago
> SP recursively because you can only nest 32 calls deep T-SQL only gives you 100 calls deep. Also, having CTEs calling other CTEs can create a very slow query plan, which takes unrolling each CTE into either a table variable or temp table to resolve.
deleted 6y ago
[deleted]
thinkloop 6y ago
> Recursion in SQL is one of the questions I always ask Data Engineers during interviews Everyone always asks that in every language at every interview and it is rarely used in practice - ahhh interviews.
- kls 6y ago
  I think it depends on the developers style of how they reason. I use recursion a lot when dealing with loose data, to clean it up. Recursion is one of my go to's for deduplicating list.