We saved $50k/year with a Go microservice coded in a hackathon

Post by mathgladiator » Wed, Apr 11, 2018, 2:10 AM UTC

Matheus28 wrote: post #24

owenmarshall wrote: post #13

Earlier quoted context omitted.

> Stuff like this is bread and butter SQL. Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case you'd slap a materialized view in and query that. But this is 2018! Programmers don't want to treat the database as anything but one big key value store ;)

If you aren't implementing your own tabular database on top of your existing tabular database, you aren't 2018 enough :)

Post by pbnjay » Wed, Apr 11, 2018, 2:11 AM UTC

any626 wrote: post #33

orf wrote: post #32

Earlier quoted context omitted.

Hmm, that does seem probable. In fact that could make the SQL even more efficient as you'd only need a combined index on the 'prop' and 'value' columns, rather than N arbitrary combinations of indexes that may or may not be used. Edit: Had some bad attempt at writing this query but it's rather late and it made no sense.

You would need to have a new join for each new property SELECT DISTINCT loyaltyMemberID from members as m INNER JOIN properties as p1 on m.id = p1.user_id INNER JOIN properties as p2 on m.id = p2.user_id INNER JOIN properties as p3 on m.id = p3.user_id AND (p1.prop = 'gender' AND p1.value = x) AND ((p2.prop = 'age' AND p2.value = y) OR (p3.prop = 'censor' AND p3.value = z))

Post by jwatte » Wed, Apr 11, 2018, 2:24 AM UTC

ThrustVectoring wrote: post #20

orf wrote: post #8

I don't quite get this. How fast was running this query: Select loyaltyMemberID from table WHERE gender = x AND (age = y OR censor = z) Why the random complexity with individual unions and a group? Of course that's going to be dog slow. Sure, the filters can be arbitrary but with an ORM it's really really simple to build them up from your app code. The Django ORM with Q objects is particularly great at this. Obviousl…

The UI they showed in the blog post looks like it has enough data available to generate that kind of query, too. Like, the ands/ors/nots are right there on the page, the filters are already there too getting translated to SQL as well, just mash them together and you get the same "algebra of sets" stuff right in the WHERE clause. As it stands the SQL query is quite silly. It gets a list of every user ID that is includ…

Post by voltagex_ » Wed, Apr 11, 2018, 2:40 AM UTC

flukus wrote: post #54

voltagex_ wrote: post #44

Earlier quoted context omitted.

One of the things I've done to harden an app is to revoke all permissions other than EXEC on a particular schema, then make sure everything is done via paramatised stored procedures - no chance of SQL injection then.

But that creates situations like this where you have to jump through hoops to solve simple problems. You solved one potential issue at the cost of creating many more. > no chance of SQL injection then. You know you can have sql injection attacks inside stored procedures? If you think stored procedures are a panacea then you don't understand the problem you're solving.

Post by TimJYoung » Wed, Apr 11, 2018, 2:45 AM UTC

any626 wrote: post #33

orf wrote: post #32

Earlier quoted context omitted.

Hmm, that does seem probable. In fact that could make the SQL even more efficient as you'd only need a combined index on the 'prop' and 'value' columns, rather than N arbitrary combinations of indexes that may or may not be used. Edit: Had some bad attempt at writing this query but it's rather late and it made no sense.

You would need to have a new join for each new property SELECT DISTINCT loyaltyMemberID from members as m INNER JOIN properties as p1 on m.id = p1.user_id INNER JOIN properties as p2 on m.id = p2.user_id INNER JOIN properties as p3 on m.id = p3.user_id AND (p1.prop = 'gender' AND p1.value = x) AND ((p2.prop = 'age' AND p2.value = y) OR (p3.prop = 'censor' AND p3.value = z))

  SELECT DISTINCT loyaltyMemberID
  from members as m
  INNER JOIN properties as p on m.id=p.user_id
  WHERE (p.prop='name' AND p.value = value) AND

  ...etc.