4 ms·

Yes well it would be ideal if I had code to show you and compare it to the SQL, but all of that is at my old workplace and I will get PTSD if I ever look at it

Yes well it would be ideal if I had code to show you and compare it to the SQL, but all of that is at my old workplace and I will get PTSD if I ever look at it again. I agree that loops in SQL are not great. Loops in programming languages are fine obviously. There are just many more and nicer constructs in a programming language to manipulate data. Maybe you have a root table that is anchoring your query, say a Staff table. The Staff table has a Manager column, which is another row in the Staff table. You then need to do a bunch of aggregate stuff. So in a programming language, you can maybe query the database 3 times, once for the staff you need, and then again for maybe shifts completed and so on. You can then easily put the staff into a dictionary with virtually no code. Then you loop over the non-dictionary staff array, and you have something like: for (var staff in shittyStaff) { processedStaff.add({ manager: staffDic[staff.manager], shiftsCompleted: shifts[staff.id].length, shiftsWithManager: shifts[staff.id].filter(s => s.coworker == staff.manager.id).length }); Or if it is setup with entity framework/c# stuff, it is just: var staff = db.Staff .Include(s => s.Manager) .Include(s => s.Shifts) .Select(s => new { Manager: s.Manager, ShiftsCompleted: s.Shifts.Count(), ShiftsWithManager: s.Shifts.Where(shift => shift.CoWorker == s.Manager).Count() }); Probably a terrible and not particularly complex example because I made it up, but to do that in SQL requires a lot more stuff, a self-join, group by with count etc, etc.

astine 8y ago
I use both raw SQL and the entity framework on a regular basis. That Linq to entities query gets translated pretty directly to SQL. Each of those includes translates directly to a left join on whatever column is specified as the key. You would need a group by, but no self joins. Assuming that you are familiar with the database schema and are proficient in SQL, it shouldn't be any slower to write the SQL version than the C# version. It depends a little on the exact columns you need, but it would look something like this, which is a supper common form for a SQL query: select m.id Manager, count(sh.id) as ShiftsCompleted, sum(iif(sh.coworker = m.id,1,0) as ShiftsWithManager from staff s left join manager m on m.id = s.manager_id left join shifts sh on sh.id = s.shifts_id group by s.id, m.id The SQL version has the advantage that it's more intuitive to specify the columns that you need so if your query is running slow because you're pulling too much data (something that's happened to me a bunch,) you can omit unneeded columns pretty easily.

astine 8y ago

I use both raw SQL and the entity framework on a regular basis. That Linq to entities query gets translated pretty directly to SQL. Each of those includes translates directly to a left join on whatever column is specified as the key. You would need a group by, but no self joins. Assuming that you are familiar with the database schema and are proficient in SQL, it shouldn't be any slower to write the SQL version than the C# version. It depends a little on the exact columns you need, but it would look something like this, which is a supper common form for a SQL query: select m.id Manager, count(sh.id) as ShiftsCompleted, sum(iif(sh.coworker = m.id,1,0) as ShiftsWithManager from staff s left join manager m on m.id = s.manager_id left join shifts sh on sh.id = s.shifts_id group by s.id, m.id The SQL version has the advantage that it's more intuitive to specify the columns that you need so if your query is running slow because you're pulling too much data (something that's happened to me a bunch,) you can omit unneeded columns pretty easily.