Re:How I Reduced My DB Server Load by 80%
by pmontra on Monday October 09, 2017 @04:18PM
(#15437119)
I've been thinking about writing SQL queries again recently because I'm using too many ORMs and at least one of them is unnecessarily complicated.
Writing queries is easy. Validating data is not too difficult. Building objects/whatever from the resultsets is not so easy anymore, especially with joins.
By the way, the mistake this post is about is a classic with Rails. You have to use a unique index in the db, not making queries. You want to protect your data anyway, regardless of which application you start building your db for. Others will follow, possibly in different languages.
I don't want a full-fledged ORM, and I sort of like SQL, but just normalizing nested objects to put them in the database and then unnormalizing them on the way out is really annoying to do in raw SQL in my experience. Every time you want to store a new object in the database, it feels like so much effort to write a bunch of boilerplate functions. Or do you have a solution to this?
I enjoy using SQL in an environment with first class hash/map/ whatevers; I don't like to build up an object. SELECT X, Y, Z from .... gets you an array of things with X, Y, and Z keys.
Turing your hash into parameters for an insert isn't going to be great fun, although perl hash slices aren't bad. I think I've seen something about named SQL variables instead of just ?, that might be usable with a hash directly?
Re:How I Reduced My DB Server Load by 80%
by stuartaxelowen on Tuesday October 10, 2017 @05:57AM
(#15440595)
If you're using python, you can use named tuples for everything. It's really nice! Check out the psycopg2 namedtuple connection.
I just glanced at this, and maybe I don't understand, but it sounds like psycopg2 namedtuples only work on one table at a time; is this right?
Re:How I Reduced My DB Server Load by 80%
by stuartaxelowen on Tuesday October 10, 2017 @09:21PM
(#15446627)
The namedtuple is just a datastructure interface with psycopg2. For instance, you could write an arbitrary query "SELECT left.x AS cats, right.created_at FROM left JOIN right ON etc" and you'd get namedtuples with attrs `cats` and `created_at`. Similarly, you can insert namedtuples just like rows, since they are iterables, or as namedparameters with the nt._asdict() method. It's great!
As a bonus, if you're thinking ahead, you can avoid writing queries that will perform poorly. If you're not thinking ahead, at least you can more easily fix the ones that do.
I've never really understood why people want to interact with their database outside of its native query language. There's just so much mismatch between an ORM and the underlying interface, it feels like fighting all the time. And I don't feel like it's really saving any time either. The one thing that's sort of nice is it's easier to write a general filtering function, but SQL filtering lets you write so many things that won't perform well, making that easier isn't really helping anybody.