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.

Admittedly the code I was moving away from was hilariously bad. Threaded custom build of PHP bad. Then again I haven't tried to optimise the code I wrote at all

You are absolutely right about fast code becoming a database, this is simply down to the query planner - it can try and do the least possible work for the data you actually have.

I implemented a small function where you could list the tables that you were working with, and it would create empty temporary tables with the same name in a namespace that takes precedence over the normal namespace in postgres' search_path, therefore giving you a blank slate to work with for the unit test, while the other tables were still populated with useful data. (Some of these data come from expensive infrastructure scanning jobs, so starting from an entirely empty database was not an option.)

Just curious - not sure if you were using Ruby like the parent above. I literally just built a library to back ActiveRecord models with temporary tables. It's very useful since you can use AR scopes off of your temporary table then (I had to do this instead of using CTE's because my DB currently doesn't support CTE's)

https://github.com/ajbdev/acts_as_temptable

Just thought I'd share it in case it helps, or to hear if anyone else has a better approach to this. (warning: it's very experimental and not production tested yet). I couldn't find any other gem that would do this.

How did you resist the temptation to call it temptation?

Although I think temptable is almost the opposite of contemptible, therefore also good going in the pun department.

My code is all Ruby, and I ended up pushing all the work into SQL and just eventually selecting with find_by_sql into existing models for the results. There is possibly concurrent invocation vs updates and a race against itself, so it's also all wrapped with a serializable transaction and starts with a mutex lock on the controlling model.

The approach I went with works really well for cases when you want to persist the temporary table through the end of the request (it works good for master/index type views that may have a lot of sums/counts and filter options available on it).

Unfortunately I don't think I have the skills for that, so posting here in the hopes that someone that can likes the idea :)

One thing that makes improving it cumbersome is that the semantics of many operations are slightly different in the database than in C#. For example, SQL Server doesn't do NaNs and infinities, supports a zillion string string collations, and its date types may not 100% map to C# ones.

Also, databases may run stored procedures faster than the SQL that LINQ genrerates on the fly because they can cache their query plans (most databases will detect that an on-the-fly query is equal to one run recently, though, so this may be somewhat of a moot argument)

Sometimes I think I should just be using sql.. (which we do on other projects)

Sometimes it's really just easier to write SQL directly.

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/

ZODB itself is essentially a transactional single-object store, where that "single object" is usually the root node of an arbitrary (possibly cyclic) object graph. Storage depends on the backend, nowadays you'd usually use file storage for development and relstorage for any kind of deployment.

It doesn't have any query language, Python is. There are no indices beyond those that you explicitly create (eg. a dictionary would be a simple index, but other packages provide more complex indices, like B-Trees - the nice thing here is that anything that goes into the DB is fully transacted, which removes a lot of headache that you'd have with other solutions (eg. flat XML files)).

ZODB thrives when your data model is too complex to easily or efficiently map into SQL, when you don't do or need efficient complex ad-hoc queries and when 180 % performance and concurrency (although this was recently improved through the MVCC reimplementation) isn't the highest priority. Since it's pretty good at caching (much better than most ORMs) performance usually doesn't suck.

1. I believe they added SIMD support a year or two ago and got a massive (2-10× IIRC) speedup.

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.

Sorry for not explaining that fully.

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.

The system works pretty well.

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.

Now it's an ops problem :-)

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?

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.

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.

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

You wouldn't likely put the whole app to run on the database server, but typically PostgreSQL makes good use of the available RAM and disk, but leaves lots of CPU cores idle. So running some R and Python on the PostgreSQL server is an overall improvement in performance.

P.S. Yes, I know that PostgreSQL 9.6.1 will use multiple cores for aggregate and join queries on large tables, but few people have upgraded to that yet.

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.

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.

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?

Or your stored procedures now become one more thing that has to be backwards compatible, in addition to your DB schema...

I've run into all of these problems with stored procedures, and they SUCK. Good deployment tooling can solve them, but it's a lot of effort for questionable reward.

It's a small amount of effort for a thousand-fold increase in performance and not having to re-implement the same functionality in every app and in every language that connects to that DB. YMMV.

If you're not already version controlling your schema, you should seriously consider starting whether you're using sprocs or not.

http://archiveopteryx.org/db/mailstore

http://archiveopteryx.org/db/schema

There are other patterns you can use to track via version control the changes for your stored procedure.

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.

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)

What's an "anonymous table of records"? Do you mean it's just a result set (i.e. query)?

I've seen some huge result sets (thousands of rows) returned to do a simple aggregation for a dashboard widget.

I kept that CPU usage chart and added it to my CV as it was quite impressive to look at.

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.