7 ms·

At work, we have an internally generated report that was done purely in ruby. It worked, barely, munging numbers for about 1000 records. When we hit 4000 record

At work, we have an internally generated report that was done purely in ruby. It worked, barely, munging numbers for about 1000 records. When we hit 4000 records, the report took about 6 hours to run in ruby. We looked for some obvious optimizations like eager loading and some map reduce magic and implemented them. That brought us to a range of 45 minutes to 2 hours. We decided that it was still not good enough. We moved ALL of the report into SQL functions (Postgres) and we have the whole function running in 27 seconds. I agree with some of the top comments that maintaining this is a little trickier than ruby code, but some ideas we came up with in maintenance: 1) Write the SQL in a ruby file that has a class method of sorts that returns the generated function. This allows for version control to track the changes of the sql function. 2) Focus on tests at the interface level. We had great test coverage on the end result prior to moving to SQL. We were able to re utilize those tests to verify accuracy with calculations that are now in SQL. This really isn't for everyone, but for those that want to try something new, it is worth it.

herge 10y ago
Have you considered a hybrid approach, like doing the heavy lifting/querying/calculating in Postgres, but the final formatting of the report (something that might change often, or be very finicky to implement in pl/sql or in the db) is left to ruby?
- phereford 10y ago
  That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report.
  - koolba 10y ago
    > That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report. What's an "anonymous table of records"? Do you mean it's just a result set (i.e. query)?
    - phereford 10y ago
      Yea. It returns a table of rows, where the return columns are defined in the stored procedure. I may have used the wrong nomenclature. I was trying to differentiate between a table in a database schema versus one defined in a function.
      - mst 10y ago
        "The stored procedure returns a multi-row resultset via a SETOF[ROWTYPE] return value" might be a clearer description?
        phereford 10y ago
        Thank you!!!!
quink 10y ago
> report Always pure SQL, at least as you're using a DBMS. Never anything else. SQL can stretch to full Turing completeness, so it'll always be good enough for that. And between JSON support and PLPGSQL most everything can be done in Postgres.
- andrewprock 10y ago
  Unfortunately, "pure sql" usually means passing in strings, which leaves you without good tooling to manage the code. This can greatly slow down the speed of delivery. It's great if you are in an ossified section of the code/database, but it can be very brittle (or expensive to validate) otherwise.
  - quink 10y ago
    Modern RDBMSes can handle most of the ORM use cases at their end. What's wrong with VIEWs, for instance? Plus it solves the whole schema impedance mismatch thing to just leave it up to the DB. ORMs aren't that great. Having spent the past five years primarily with an object database that is also fully SQL (ISC Caché) I find myself preferring SQL more and more. Even when you have the ORM within the bloody database I'm going SQL. The string thing isn't an SQL problem, it's a tooling one. The only tool per se should be pgAdmin or an equivalent. And in your application the only "SQL" that should be written is SELECT * FROM viewforthisfunctionality and select runupsertwithjsonobjectthatpgorplpgsqlcanreadjus tfine(json)
dllthomas 10y ago
> This allows for version control to track the changes of the sql function. You... realize you could just put the SQL itself in git, right? Not saying there aren't potential benefits, but "it can go in version control" isn't one of them.
- phereford 10y ago
  Absolutely! We use rails. I just mentioned doing it in that method as opposed to throwing it in a migration. That migration shouldn't change in version control. We wanted a history of the changes and an easy way to access it in migrations. Sorry for not explaining that fully.
  - rpeden 10y ago
    Makes sense. I suppose you could also execute "REPLACE FUNCTION" and redefine the inside a new migration each time you want to change it. The downside is that you end up with the whole function in new a migration every time you want to change if. But if you're not updating your stored procedures very frequently (which is usually the case, IME), it probably wouldn't be too bad.
    - jeffasinger 10y ago
      At work we use a rather idiosyncratic tool called RoundhousE to deploy our SQL. It treats sql labelled as a stored procedure differently, and will run the file on every deploy, as opposed to scripts that alter the schema, which are only ran once, and may not be modified. The system works pretty well.
      - slagfart 10y ago
        You don't need a tool for this. If you store base data in one schema, and functions/views in another, you can update this second schema willy nilly without worrying about damaging your data. Just start the second file with DROP SCHEMA IF EXISTS schemaname CASCADE; CREATE SCHEMA schemaname;. Then, make sure any functions/views/permissions built on top of this schema are also contained within this file.
azernik 10y ago
Oh absolutely. I've tried doing complicated logic with (MySQL) stored procedures. It's painful. Definitely send the queries from a sane programming language. One way to think about this is that you just want to take control back from the ORM of the exact database queries sent down the pipe. Which, BTW, is why every ORM worth its salt has functions for dropping down to plain SQL - teach that as best practice for doing computation in SQL queries, not stored procedures.
- phereford 10y ago
  Most ORMs will not do the level of aggregations you want or need without writing raw SQL within your ORM. If you are already writing raw SQL in your ORM for aggregation level computations, you might as well create a stored procedure, make it IMMUTABLE (if you can) and gain those performance benefits. At the end of the day, I would just use the tool that has the most comfortability with the team on the project. You can obviously start with an ORM, but there may come a point in time where performance needs a little boost.
  - azernik 10y ago
    What are the real performance benefits of stored procedures over sending the hand-written queries over the wire through your ORM? I suspect small to none, and at the cost of having code that lives in the database rather than in version-controlled code.
    - gaius 10y ago
      code that lives in the database rather than in version-controlled code If you think you can't version control stored procs, then you are doing something badly, badly wrong. What's the difference between a "script" which is a text file on a filesystem at the end of the day, versus a "stored proc" which is a block of text in a database, loaded off a file in a filesystem?
      - jstanley 10y ago
        The "script" gets deployed simply and reliably using your existing tooling, and stays in sync with the surrounding code.
odammit 10y ago
I've had similar experiences. And it's not always Ruby processing being the slow part but returning multiple result sets to your application layer. I've seen some huge result sets (thousands of rows) returned to do a simple aggregation for a dashboard widget.
- phereford 10y ago
  Absolutely agreed. In our particular situation, the ruby calculations were the bottlenecks based on our benchmarks.
wsmith 10y ago
I once reduced the running time of a report from 45 minutes to 3 seconds (900x improvement) by moving the code inside the database. If a programming language wants to stay fast it must eventually become a database. I realize this is an unpopular opinion but popularity is the wrong metric to judge by.
- dom0 10y ago
  Hence Kx, ZODB.
  - the-dude 10y ago
    ZODB is not a database, nor is it fast.
    - dom0 10y ago
      More like a database toolkit. Performance depends on application but was clearly never the top priority. (Can still be pretty impressive in comparison due to the rather effective instance caching)
  - PeCaN 10y ago
    Yep, the K interpreter is pretty mediocre (it is very small and the language is inherently pretty fast, but the interpreter is otherwise not very advanced¹) but being directly integrated with a very optimized in-memory column-store database means it smokes anything else. Even the massive engineering effort in a JVM+RDBMS simply can't compete with running K directly on memory-mapped files. 1. I believe they added SIMD support a year or two ago and got a massive (2-10× IIRC) speedup.
  - takeda 10y ago
    I'm not too familiar with ZODB, but it looks like it tries to impose OO on a database, while in reality relational model works best with data, so making programing language able to interact with data that way would be better. I think something like JOOQ[1] is close to that. In order to get good performance you want to minimize number of back and forth requests over network. So instead of making a request to obtain list of items, and then fetching each of the item one by one (so called N+1 select issue) you will get a better performance if you make the database send only the data you want, nothing more, nothing less. [1] http://www.jooq.org/ http://www.jooq.org/
semi-extrinsic 10y ago
I'm not a database guy, but I have to ask: you're talking about 4000 records, which is essentially nothing. Why not just load all the data from SQL into a DataFrame in Python or R and do the processing there? I've written some pretty big R DataFrame crunching scripts, including very wide-window moving averages operating on tens of billions of records, that run in less than five minutes on my laptop. Hell, I have a web app built with Flask+Pandas, running on a single t2.small instance, that does complex queries into a 6000 records big chemistry data set based on user input of selectors. It can serve thousands of simultaneous users without noticeable slowdown. What am I missing here?
- phereford 10y ago
  4000 records with a one-to-many join (the many part capped at 240 records). The aggregations are done mostly on that join table. In our original ruby process, it would return just that, 240 records per join and aggregate on that. Our SQL process aggregates that in a stored procedure and returns the flat 4000 records. We really didn't want to introduce another piece of technology to this stack, hence we didn't look at R or Python or Elixir. We did consider storing all of this inside elasticsearch for a hot second but keeping things in sync at the correct times seemed sub optimal and would add a layer of complexity we were all uncomfortable with.
  - jstimpfle 10y ago
    ONE join? A join is usually just a linear scan through one table, doing hashtable lookups on the other table. If there is no hash table, at the worst both tables have to be sorted on the join key and scanned linearly. The absolute worst case of a "join" is a cartesian product which runs in quadratic time. 6000^2 = 36000000, which should still easily finish in ~1 second.
  - rtpg 10y ago
    This really sounds like you're having an issue linked to the ORM. I don't know the Rails equivalent, but Django's ORM has a way of prefetching related fields on a model in a single query. My bet is that your code wasn't 1 query, but actually 4000 * 240 queries, which will always take a while. EDIT: I just saw in a sibling comment that the actual data fetching was only taking 30s. I imagine you were doing that part mostly right
bbcbasic 10y ago
27s is still an awfully time. Why does it take so long?
- phereford 10y ago
  We didn't make it immutable and we didn't add an index to it. Those are our next action items to help improve the performance of it. Honestly, instead of looking into more performance gains, I wanted the team to get a huge win and for the team to look good in front of senior management :D.
  - bbcbasic 10y ago
    Ha ha politics driven development. Love it.
    - phereford 10y ago
      Ehh. More about boosting confidence and team morale than politics. The goal was achieved, and we can yak shave during slack time if need be.
  - majewsky 10y ago
    If you want to optimize for looking good in front of senior management, you would put a sleep() in there to make it run for 5 minutes. Then when senior mgmt comes back and complains that 5 minutes is still too much, you remove the sleep(), bill a few hours to time tracking, and praise the new performance improvements that you just deployed. ;)
    - phereford 10y ago
      Hahahahahaha.
    - striking 10y ago
      See also: http://thedailywtf.com/articles/The-Speedup-Loop http://thedailywtf.com/articles/The-Speedup-Loop
davyjones 10y ago
6 hours to 15 minutes here. This is with the right indices and stock install. I am sure an expert can do much better.
dom0 10y ago
Correct usage of ORMs if it's not in the database is another important and easy thing to get right. This often gives a 10-1000 times performance improvement just due to the avoided "ping pong" game by using eg. query annotations that tell the ORM which related objects you'll access.
tkyjonathan 10y ago
I took a 20-page multi-threaded ruby code that took 2-4 days to complete and changed it to a 1-page MySQL stored procedure with some @variables. Brought it down to 4 minutes to run. And to top it off, reduce the overall server load from 96% to 20%. I kept that CPU usage chart and added it to my CV as it was quite impressive to look at.

herge 10y ago

Have you considered a hybrid approach, like doing the heavy lifting/querying/calculating in Postgres, but the final formatting of the report (something that might change often, or be very finicky to implement in pl/sql or in the db) is left to ruby?

phereford 10y ago
That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report.
- koolba 10y ago
  > That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report. What's an "anonymous table of records"? Do you mean it's just a result set (i.e. query)?
  - phereford 10y ago
    Yea. It returns a table of rows, where the return columns are defined in the stored procedure. I may have used the wrong nomenclature. I was trying to differentiate between a table in a database schema versus one defined in a function.
    - mst 10y ago
      "The stored procedure returns a multi-row resultset via a SETOF[ROWTYPE] return value" might be a clearer description?
      - phereford 10y ago
        Thank you!!!!

quink 10y ago

> report Always pure SQL, at least as you're using a DBMS. Never anything else. SQL can stretch to full Turing completeness, so it'll always be good enough for that. And between JSON support and PLPGSQL most everything can be done in Postgres.

andrewprock 10y ago
Unfortunately, "pure sql" usually means passing in strings, which leaves you without good tooling to manage the code. This can greatly slow down the speed of delivery. It's great if you are in an ossified section of the code/database, but it can be very brittle (or expensive to validate) otherwise.
- quink 10y ago
  Modern RDBMSes can handle most of the ORM use cases at their end. What's wrong with VIEWs, for instance? Plus it solves the whole schema impedance mismatch thing to just leave it up to the DB. ORMs aren't that great. Having spent the past five years primarily with an object database that is also fully SQL (ISC Caché) I find myself preferring SQL more and more. Even when you have the ORM within the bloody database I'm going SQL. The string thing isn't an SQL problem, it's a tooling one. The only tool per se should be pgAdmin or an equivalent. And in your application the only "SQL" that should be written is SELECT * FROM viewforthisfunctionality and select runupsertwithjsonobjectthatpgorplpgsqlcanreadjus tfine(json)

dllthomas 10y ago

> This allows for version control to track the changes of the sql function. You... realize you could just put the SQL itself in git, right? Not saying there aren't potential benefits, but "it can go in version control" isn't one of them.

phereford 10y ago
Absolutely! We use rails. I just mentioned doing it in that method as opposed to throwing it in a migration. That migration shouldn't change in version control. We wanted a history of the changes and an easy way to access it in migrations. Sorry for not explaining that fully.
- rpeden 10y ago
  Makes sense. I suppose you could also execute "REPLACE FUNCTION" and redefine the inside a new migration each time you want to change it. The downside is that you end up with the whole function in new a migration every time you want to change if. But if you're not updating your stored procedures very frequently (which is usually the case, IME), it probably wouldn't be too bad.
  - jeffasinger 10y ago
    At work we use a rather idiosyncratic tool called RoundhousE to deploy our SQL. It treats sql labelled as a stored procedure differently, and will run the file on every deploy, as opposed to scripts that alter the schema, which are only ran once, and may not be modified. The system works pretty well.
    - slagfart 10y ago
      You don't need a tool for this. If you store base data in one schema, and functions/views in another, you can update this second schema willy nilly without worrying about damaging your data. Just start the second file with DROP SCHEMA IF EXISTS schemaname CASCADE; CREATE SCHEMA schemaname;. Then, make sure any functions/views/permissions built on top of this schema are also contained within this file.

azernik 10y ago

Oh absolutely. I've tried doing complicated logic with (MySQL) stored procedures. It's painful. Definitely send the queries from a sane programming language. One way to think about this is that you just want to take control back from the ORM of the exact database queries sent down the pipe. Which, BTW, is why every ORM worth its salt has functions for dropping down to plain SQL - teach that as best practice for doing computation in SQL queries, not stored procedures.

phereford 10y ago
Most ORMs will not do the level of aggregations you want or need without writing raw SQL within your ORM. If you are already writing raw SQL in your ORM for aggregation level computations, you might as well create a stored procedure, make it IMMUTABLE (if you can) and gain those performance benefits. At the end of the day, I would just use the tool that has the most comfortability with the team on the project. You can obviously start with an ORM, but there may come a point in time where performance needs a little boost.
- azernik 10y ago
  What are the real performance benefits of stored procedures over sending the hand-written queries over the wire through your ORM? I suspect small to none, and at the cost of having code that lives in the database rather than in version-controlled code.
  - gaius 10y ago
    code that lives in the database rather than in version-controlled code If you think you can't version control stored procs, then you are doing something badly, badly wrong. What's the difference between a "script" which is a text file on a filesystem at the end of the day, versus a "stored proc" which is a block of text in a database, loaded off a file in a filesystem?
    - jstanley 10y ago
      The "script" gets deployed simply and reliably using your existing tooling, and stays in sync with the surrounding code.

odammit 10y ago

I've had similar experiences. And it's not always Ruby processing being the slow part but returning multiple result sets to your application layer. I've seen some huge result sets (thousands of rows) returned to do a simple aggregation for a dashboard widget.

phereford 10y ago
Absolutely agreed. In our particular situation, the ruby calculations were the bottlenecks based on our benchmarks.

wsmith 10y ago

I once reduced the running time of a report from 45 minutes to 3 seconds (900x improvement) by moving the code inside the database. If a programming language wants to stay fast it must eventually become a database. I realize this is an unpopular opinion but popularity is the wrong metric to judge by.

dom0 10y ago
Hence Kx, ZODB.
- the-dude 10y ago
  ZODB is not a database, nor is it fast.
  - dom0 10y ago
    More like a database toolkit. Performance depends on application but was clearly never the top priority. (Can still be pretty impressive in comparison due to the rather effective instance caching)
- PeCaN 10y ago
  Yep, the K interpreter is pretty mediocre (it is very small and the language is inherently pretty fast, but the interpreter is otherwise not very advanced¹) but being directly integrated with a very optimized in-memory column-store database means it smokes anything else. Even the massive engineering effort in a JVM+RDBMS simply can't compete with running K directly on memory-mapped files. 1. I believe they added SIMD support a year or two ago and got a massive (2-10× IIRC) speedup.
- takeda 10y ago
  I'm not too familiar with ZODB, but it looks like it tries to impose OO on a database, while in reality relational model works best with data, so making programing language able to interact with data that way would be better. I think something like JOOQ[1] is close to that. In order to get good performance you want to minimize number of back and forth requests over network. So instead of making a request to obtain list of items, and then fetching each of the item one by one (so called N+1 select issue) you will get a better performance if you make the database send only the data you want, nothing more, nothing less. [1] http://www.jooq.org/ http://www.jooq.org/

semi-extrinsic 10y ago

I'm not a database guy, but I have to ask: you're talking about 4000 records, which is essentially nothing. Why not just load all the data from SQL into a DataFrame in Python or R and do the processing there? I've written some pretty big R DataFrame crunching scripts, including very wide-window moving averages operating on tens of billions of records, that run in less than five minutes on my laptop. Hell, I have a web app built with Flask+Pandas, running on a single t2.small instance, that does complex queries into a 6000 records big chemistry data set based on user input of selectors. It can serve thousands of simultaneous users without noticeable slowdown. What am I missing here?

phereford 10y ago
4000 records with a one-to-many join (the many part capped at 240 records). The aggregations are done mostly on that join table. In our original ruby process, it would return just that, 240 records per join and aggregate on that. Our SQL process aggregates that in a stored procedure and returns the flat 4000 records. We really didn't want to introduce another piece of technology to this stack, hence we didn't look at R or Python or Elixir. We did consider storing all of this inside elasticsearch for a hot second but keeping things in sync at the correct times seemed sub optimal and would add a layer of complexity we were all uncomfortable with.
- jstimpfle 10y ago
  ONE join? A join is usually just a linear scan through one table, doing hashtable lookups on the other table. If there is no hash table, at the worst both tables have to be sorted on the join key and scanned linearly. The absolute worst case of a "join" is a cartesian product which runs in quadratic time. 6000^2 = 36000000, which should still easily finish in ~1 second.
- rtpg 10y ago
  This really sounds like you're having an issue linked to the ORM. I don't know the Rails equivalent, but Django's ORM has a way of prefetching related fields on a model in a single query. My bet is that your code wasn't 1 query, but actually 4000 * 240 queries, which will always take a while. EDIT: I just saw in a sibling comment that the actual data fetching was only taking 30s. I imagine you were doing that part mostly right

bbcbasic 10y ago

27s is still an awfully time. Why does it take so long?

phereford 10y ago
We didn't make it immutable and we didn't add an index to it. Those are our next action items to help improve the performance of it. Honestly, instead of looking into more performance gains, I wanted the team to get a huge win and for the team to look good in front of senior management :D.
- bbcbasic 10y ago
  Ha ha politics driven development. Love it.
  - phereford 10y ago
    Ehh. More about boosting confidence and team morale than politics. The goal was achieved, and we can yak shave during slack time if need be.
- majewsky 10y ago
  If you want to optimize for looking good in front of senior management, you would put a sleep() in there to make it run for 5 minutes. Then when senior mgmt comes back and complains that 5 minutes is still too much, you remove the sleep(), bill a few hours to time tracking, and praise the new performance improvements that you just deployed. ;)
  - phereford 10y ago
    Hahahahahaha.
  - striking 10y ago
    See also: http://thedailywtf.com/articles/The-Speedup-Loop http://thedailywtf.com/articles/The-Speedup-Loop

davyjones 10y ago

6 hours to 15 minutes here. This is with the right indices and stock install. I am sure an expert can do much better.

dom0 10y ago

Correct usage of ORMs if it's not in the database is another important and easy thing to get right. This often gives a 10-1000 times performance improvement just due to the avoided "ping pong" game by using eg. query annotations that tell the ORM which related objects you'll access.

tkyjonathan 10y ago

I took a 20-page multi-threaded ruby code that took 2-4 days to complete and changed it to a 1-page MySQL stored procedure with some @variables. Brought it down to 4 minutes to run. And to top it off, reduce the overall server load from 96% to 20%. I kept that CPU usage chart and added it to my CV as it was quite impressive to look at.