![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Editorial on SQL abstraction
They say that every Perl hacker's journeyman project is an HTML templating system. I'm learning about a variant, running into clients who each developed their own SQL abstraction modules before hiring me. Like every other SQL abstraction module, it really has just one purpose: an attempt to avoid having to embed one programming language (SQL) inside another one (Perl).
To this I say: Bleah. I used to agree with this sentiment, and for years used modules like Class::DBI, which treat tables as classes and rows as instances. Clever, and easy to work with! But now try doing a right join. Ha ha, no, I know: show me how do to a subselect with that. Yeah. Either you punt and shove raw SQL into your code anyway, or you insist on doing it "Perlishly" with loops and checks, in which case a query that should have taken a tenth of a second takes several seconds or more.
In fact, I'll wager that your code is already running at horrible efficiency because you're pounding the crap out of your DB with unneccessary loop-based SELECTs instead of carefully doing case-by-case SQL queries that get exactly the rows you need, each holding exactly the columns you care about, every time you call an information-seeking object method. I'll also bet your INSERTs are ass-slow because you're not using bind values in them. Actually, you're not using bind values anywhere, right? Yeah, see.
My friend, if you're going to work with a little raw SQL, I argue you're already blown it, and may as well just let it all hang out, throwing out your half-useful abstraction layer. There is no sin in openly acknowledging that you're using an SQL database by actually writing SQL. If the fastest way to get some particular information out of the DB is to write a crazy-long and baroque query, then you should do so. Let the database do the work it's optimizied for and stop treating it like a set of config files that you'll need to write your own logic around.
Another way to put it: If you're writing nested loops in your SQL-driving Perl, you're probably doing it wrong.
To this I say: Bleah. I used to agree with this sentiment, and for years used modules like Class::DBI, which treat tables as classes and rows as instances. Clever, and easy to work with! But now try doing a right join. Ha ha, no, I know: show me how do to a subselect with that. Yeah. Either you punt and shove raw SQL into your code anyway, or you insist on doing it "Perlishly" with loops and checks, in which case a query that should have taken a tenth of a second takes several seconds or more.
In fact, I'll wager that your code is already running at horrible efficiency because you're pounding the crap out of your DB with unneccessary loop-based SELECTs instead of carefully doing case-by-case SQL queries that get exactly the rows you need, each holding exactly the columns you care about, every time you call an information-seeking object method. I'll also bet your INSERTs are ass-slow because you're not using bind values in them. Actually, you're not using bind values anywhere, right? Yeah, see.
My friend, if you're going to work with a little raw SQL, I argue you're already blown it, and may as well just let it all hang out, throwing out your half-useful abstraction layer. There is no sin in openly acknowledging that you're using an SQL database by actually writing SQL. If the fastest way to get some particular information out of the DB is to write a crazy-long and baroque query, then you should do so. Let the database do the work it's optimizied for and stop treating it like a set of config files that you'll need to write your own logic around.
Another way to put it: If you're writing nested loops in your SQL-driving Perl, you're probably doing it wrong.
no subject
no subject
no subject
my $sth_for_some_specific_thing = get_statement_handle_for_that_specific_thing();$sth_for_some_specific_thing->execute('bind_value1', 'bind_value2');
And then just packing all the raw-SQL-returning subroutines down in the basement of the source code, in clearly labeled boxes.
no subject
no subject
no subject
no subject
no subject
no subject
no subject
no subject
Then, you want an abstraction layer.
Also, you want an abstraction layer which will prevent you from ever having to think about quote-quoting again.