10 ms·

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 ra

• BinaryIdiot 9y ago
  Damn, you're not kidding. I wonder why they needed more than one query here plus UNION is slowwwwwwww. They never mention how frequent this query needs to run either, only the amounts of data involved in some aspects of this table.
• deleted 9y ago
  [deleted]
• owenmarshall 9y ago
  > 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 ;)
  • cookiecaper 9y ago
    Yeah, sadly, this is not too much of an exaggeration. I've worked on teams that insisted they needed DynamoDB, because, well, Dynamo is for "Big Data", and they certainly wouldn't work somewhere that had "Small Data"! Replace the buzzwords/products as applicable; you could actually probably just scramble them and it'd work just as well, since someone out there thinks "RabbitMQ means Web Scale", etc. SQL databases are amazing, robust examples of engineering. They are your friends and they're the appropriate choice for the vast majority of software. They are not outmoded or passe. Though I acknowledge there is a separate use case for K-V stores, I almost want to make policy preventing their use just because I know so many developers will abuse them badly and then stare back at you blankly during the semi-annual massive downtime event, muttering something like "Well, it's based on research at Google, so I'm sure there's a way to recover the data..."
    • toddBarkus 9y ago
      I think this is a case for the return of the traditional "sysadmin" as "devops"/"SRE" is now the role of unblocking deploying a solution instead of questioning it's complexity/fitness.
      • cookiecaper 9y ago
        While I agree, I also think that overt gating and approval processes create a high tension dynamic that frequently breaks down, whether it's ops v. dev, security v. dev, or others. It's easy for people to get their pride wounded, and they end up encouraged to find workarounds to the process. The simple answers to this are pretty much imaginary, unfortunately.
        • toddBarkus 9y ago
          Sure, of course. I was hoping to point out that there's an increasingly overlooked value in having someone question complexity. The "no, you don't need React" of the frontend dev or the "our data is actually relational" of the back end dev.
      • rbranson 9y ago
        If your “SRE” team is only “unblocking deploying a solution” then I’m sad to say they are an operations team who has rebranded themselves to appear more relevant.
        • toddBarkus 9y ago
          That's most "SRE" -- it's a title arms race in that field between the underqualified and those that wish to convey they know how to do more than write system scripts in DSLs
    • EGreg 9y ago
      Question, though... If your columns have types, how can you encrypt them using custom keys for each one? Is it possible? I want the keys to reside on the client and search for encrypted data. Basically single row lookups at a time.
  • Matheus28 9y ago
    If you aren't implementing your own tabular database on top of your existing tabular database, you aren't 2018 enough :)
    • mathgladiator 9y ago
      I'm changing the game by code generating a tabular database on top of an eventualy consistent store. 2020 here we come!!!
      • elvinyung 9y ago
        Rows by any other name would smell as sweet...
      • mgkimsal 9y ago
        better make sure it's all immutable too.
      • LoSboccacc 9y ago
        oh boy do I have news for you https://www.cockroachlabs.com/ https://www.cockroachlabs.com/
        • elvinyung 9y ago
          not an eventually consistent store though.
  • Regardsyjc 9y ago
    Do you have any recommendations for resources to learn best database practices? I'm currently designing my first database and I'm not sure what information is worth storing (like calculations) and how to choose which data to group in tables.
    • matte_black 9y ago
      Ok, first you need to decide: is this going to be a purely transactional database (for business processes) or do you also plan to do data analysis straight inside this database (meaning you won’t be extracting, transforming and loading data into another database and analyzing it there). If it’s transactional, I recommend keeping calculations only if you need to access summarized data frequently. For example, if you are tracking inventory by storing the history of transactions that occur into and out of inventory, it’s trivial to find out how much of each item you have in stock at any point in time by doing a sum of the change in quantities for each item type up to that point. If you were usually interested in the “current” count, it would be expensive to perform this sum every time, so instead you could keep a separate table for calculating the running total of inventory per item and referring to that. Keep this table up to date through the use of triggers on insert events (Note that your log of inventory transactions would thus be an immutable stream of events) An example of something not worth storing is derived data that is a combination of separate columns in a table. For most queries it’s probably trivial to be lazy and wait to perform such a calculation until you actually need it. If you still want to have a ready made “table” that has all the computations you want already entered as columns for easy querying, use a view. If you find yourself making liberal use of views, you’re on the right track. If you are using a separate data warehouse for data analysis, then precompute and denormalize as much as you can. *Disclaimer: most of what I’m talking about is from a Postgres perspective.
    • jonah 9y ago
      I read SQL Antipatterns[0] - Avoiding the Pitfalls of Database Programming by Bill Karwin a while ago and really liked it. [0] https://pragprog.com/book/bksqla/sql-antipatterns https://pragprog.com/book/bksqla/sql-antipatterns
    • fnord123 9y ago
  • jehlakj 9y ago
    You can thank full stack developers for that
    • chiefalchemist 9y ago
      Them, or the manager that hired them? If no one up the chain brings on a DBA what are they supposed to do? I hear ya. But this is as much a symptom of naive (and budget stretched) leadership as it is of the hands on deck.
      • olalonde 9y ago
        Your solution costs 150K$/year while theirs took a weekend and saves 50K$/year...
        • chiefalchemist 9y ago
          Allow me to clarify. 1) It took a weekend to complete. The friction was building for far longer. There's a cost to that, esp if it effects customer satisfaction and retention. They didn't refactor for fun, did they :) How many dev teams aren't so lucky? Is this article a no choice outlier, or a best practice? 2) My comment wasn't directed at the article but on another comment that blamed the developers. These problems should be owned by ownership / leadership / management more and engineers less. 3) That said, hire a DBA? I don't think that's necessary.
    • flukus 9y ago
      If they don't know how to look at a query plan and craft a solution can you really call them full stack?
      • vishnugupta 9y ago
        The stack has to bottom our somewhere and it's usually at the application code
        • 9y ago
• ThrustVectoring 9y ago
  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 included by each filter and compares which ones are in the filters you want and not the filters you don't want. Much better is to pass the filters into SQL, let it figure out which users match the filters you want and not the filters you don't, and just use that result.
  • jwatte 9y ago
    Most Enterprise CRM like solutions store tables of customer-property-value instead of using one column per property. This leads to lots of unions in advanced queries, and makes filtering harder. Some databases even calculate column block statistics to optimize these queries by doing less IO even for seeming table scans. Why not one table with all customers and one column per property? There are a few reasons, having to do with anything from MySQL sucking at schema alters for really big tables, to expectations of Enterprise customers.
• wetha 9y ago
  I’m not the author of the post. Your comment assumes a well known schema. My understanding from the post is that this solution can join and filter on “custom” datasets of arbitrary schema that each of their customers upload.
  • Matheus28 9y ago
    I've never played with this, but couldn't you create a table based on the dataset that the customers upload, and let your database engine handle filtering those queries? From the looks of it, even if they were doing full table scans for each query, it'd still be faster than all those unions...
• TimPC 9y ago
  I think the point is they don't know in advance what the query is and they didn't think they had a good solution to optimize all user entered variants across the range of possible groupings so they wanted a solution that was easier to optimize globally. The general form of this is: Select loyaltyMemberID from table WHERE V1_1= x_1 OR ... OR V1_n=x_n) AND (V2_1 = x_2_1 OR V2_2=x_2_2 OR ... V2_n=x_2_n) AND ... AND (Vn_1 = x_n_1 OR ... OR Vn_n= x_n_n) (some of these n's should actually be m_i's but I was lazy) There may be some ability to optimize this in a number of ways but optimizing one example is not optimizing the general form. I can easily see how technology change could be a cleaner solution.
  • orf 9y ago
    > There may be some ability to optimize this in a number of ways but optimizing one example is not optimizing the general form. I totally get that, but isn't that the point of the query optimizer within the database itself? Why are you trying to outwit it? It should select the right indexes, provided the columns are indexed, and "do the right thing(tm)". It might take a bit of cajoling but they seem pretty good at this. Postgres collects statistics about the distribution of values themselves within the table to guide its choice of index, so in theory it could rewrite the boolean logic to use a specific index if it's sure that it will eliminate a higher % of the rows than another plan. In any case, it seems the SQL they posted is a bit off. Why nest each individual filter as a UNION? If you wanted to go down the UNION route couldn't you do each individual group as a UNION, with standard WHERE filters?
• toddBarkus 9y ago
  I was going to ask how to optimize the SQL in your post as it seems like the obvious/naive implementation of the query. If you're missing something, so am I. I can only imagine it was built up over time from googling specific terms that already missed the point, e.g. "rds mySQL union query"
• flukus 9y ago
  > Edit: I've just read the query in the post again and I really can't understand why you would write it like that. Am I missing something here? Oh I've seen this happen a lot. Somewhere along the line, often from a DBA, it is decided that sql in an app is evil and that everything must be in a stored proc. Then instead of some simple string concatenation you have to jump through hoops like this.
  • voltagex_ 9y ago
    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.
    • flukus 9y ago
      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.
      • voltagex_ 9y ago
        Perhaps I should have said "greatly reduced the risk of". I'm not using something like Entity Framework and the CRUD apps I mostly wrote at work were well suited to a few simple sprocs. The time it takes to write an ALTER script to change something pales in comparison to the two week change control process anyway...
        • voltagex_ 9y ago
          https://stackoverflow.com/a/24029829/229631 https://stackoverflow.com/a/24029829/229631 https://blogs.msdn.microsoft.com/brian_swan/2011/02/16/do-stored-procedures-protect-against-sql-injection/ https://blogs.msdn.microsoft.com/brian_swan/2011/02/16/do-st... https://security.stackexchange.com/questions/68701/how-does-stored-procedure-prevents-sql-injection https://security.stackexchange.com/questions/68701/how-does-...
• any626 9y ago
  The user data is most likely in rows instead of columns. Instead of having id, name, age, gender 1213, fake, 60, female they would have property_id, user_id, value 1 (assume age), 1213, 60 2 (gender), 1213, female This gives them the freedom to add more properties to the user without always having to add a column to the users table. When querying the database you'll have to do unions or joins.
  • deleted 9y ago
    [deleted]
  • orf 9y ago
    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.
    • any626 9y ago
      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))
      • pbnjay 9y ago
        Especially with partial indexes, I still feel like this structure will be significantly faster than the original UNION ALL ... GROUP BY on calculated fields. And they mention in the post that most queries don't use that many fields.
      • TimJYoung 9y ago
        There's no need for the extra joins, you can just do the one join and then filter everything in the WHERE clause: 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.
• nemothekid 9y ago
  >Seems like a fundamental misunderstanding of SQL rather than a particularly hard problem to solve. Without knowing the rest of their stack, or what their data ingestion looks like, I think your query is oversimplified. If they are doing a union, then it's likely they aren't querying one table, but they are querying multiple tables. The article mentions that individual customers had as many as 500 million rows. Likely each customer has their own set of data they also pipe into the system. Next their own custom query language may support more complex algebra than standard equality. IMO, the article doesn't sufficiently describe the problem for us to understand why their solution works. To you and I there are 100 other solutions they could have tried that seem simpler than the one they presented. It's less likely that they overengineered - we are probably just underinformed.
  • hueving 9y ago
    >It's less likely that they overengineered - we are probably just underinformed. Based on 15 years in software companies in the valley it's much less likely that this isn't over-engineered. Nearly every decision I've seen chasing technology hype has been based on ignorance of existing solutions.
• deizel 9y ago
  Can be tricky to use EAV data models with traditional ORMs.. this type of functionality can often be slow or require plugins, if implemented at all: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...
  • philliphaydon 9y ago
    Wondering if they shoved the data in PostgreSQL with JSONB how well it would perform over EAV.
    • jjeaff 9y ago
      I think that jsonb may not be as performant as EAV. You don't need joins or unions, but if you are dealing with dynamic fields, you need to know the fields ahead of time and set indexes for them in jsonb. For eav you just have to index your values table.
      • philliphaydon 9y ago
        PostgreSQL can use multiple indexes so you don't need to worry about needing to know about the fields ahead of time. Likewise you can get away with a full document GIN index. I played around with some basic report stuff at work last year, the EAV data on my local machine, the report took ~7 seconds to run. I shoved the same data into PostgreSQL as JSONB, indexed it just as full doc cos I was lazy, the same report took ~80ms. Obviously this isn't 'proof' my dataset was only 1.5m by 15m records. But with my limited knowledge i do believe it would perform better, I don't know how much better... but I think better...
• est 9y ago
  I am building a rule engine quite similar to this. An AST parser will run all python DSLs and generated list of tables for INNER JOIN, then SELECT all the tables data out with filters in one pass, then run all results through the Python code. It's quite fun.
• rypskar 9y ago
  I blame ORMs', if you don't understand SQL and how databases work you should not be allowed to use an ORM. If you know how databases work you, in many cases, will not use an ORM except for the most simple CRUD operations.
  • orf 9y ago
    I know how databases work and I use a (good) ORM almost exclusively for app work (SQL for exploration usually though). The benefits are huge.
    • rypskar 9y ago
      OK, the part about not using ORM if you know SQL is a bit of an exaggeration. At least when you know SQL you know when to use an ORM and when to not use it. If all you know is ORM then you will always use it, and ORM seems to lead to many developers not learning SQL
      • scruple 9y ago
        I think it depends... Who are we talking about here? Juniors, even intermediates, in my experience, haven't had enough time on the job to have learned enough to be writing raw SQL statements or query objects unless they're actively punching up on a daily basis. I am unfortunately talking from experience here. What I am saying is, I really do not want a situation on my hands where the juniors that I work with, or most of the intermediates, and even a few of the seniors and leads, are writing raw SQL or query objects. Most of these folks have n years of experience in web and desktop application development and couldn't give you a passable answer to simple questions like, "What's a database index?" I know this isn't isolated to my current employer, or former employers, and I've seen it in other organizations where I've done some consulting on the side, and all of these folks I'm talking about here have largely worked else where in the past, too. And this in itself leads to other third-order effects, like the "SQL wizards" who get asked all of the "tough" SQL / database questions. I want to stress that I understand the point that you're making, and I do agree with it, and of course, so do many (all?) ORM authors themselves, but I think the advice is wrong and is prone to take you to a much worse situation. I think we have an obligation as people who do grok SQL and databases to gently introduce our less experienced co-workers to the idea that ORMs are not a panacea to all database interactions, but until the companies we work have enough of an incentive to give us that sort of time and empowerment then I, for one, am going to recommend ORMs for everyone for everything unless they really, absolutely, demonstrably know what they're doing.
• benmmurphy 9y ago
  age/gender are probably simple but i'm guessing censor is probably derived from a transaction table. if they are letting users select arbitrary time ranges to filter the transactions then you can't store a precomputed censor value for each user. but seeing that they are talking about caching maybe a lot of stuff can be precomputed.