Applying logical operators in either complex SQL xor hashes?

mldvx4 has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, PerlMonks,

Thanks for DBI. It works very well with SQLite3. I have a short (for now) Perl script which is accessed via CGI::Fast which will soon have a small but varying number of SQL queries for SQLite3. The queries are targeted at various FTS5 tables. The number of queries is between 1 and n where n is a "small" number, but in any case changing each time. They are executed theough prepare and execute statements. The results from each query are collected by the Perl script into its own hash with the records' unique key (recno) as the hash key. For illustration, here is the basic sample query:

SELECT old_keys.recno AS recno, 
        'http://example.org/' || old_keys.file, 
        old_metadata.value AS title 
    FROM old_keys 
    JOIN old_metadata 
    ON old_keys.recno = old_metadata.recno 
    WHERE term='dc.title' AND old_metadata.recno 
    IN (
        SELECT rowid AS recno 
        FROM old_fts5_metadata 
        WHERE old_fts5_metadata MATCH ?);
[download]

When there is more than one query, there will be an operator XOR, AND, OR, or NOT applied between them.

My question is about the recommended approach, should I apply the operators to the various hashes after separate queries, or should I have the script build out more complex queries into a single SQL query for each search?

Comment on Applying logical operators in either complex SQL xor hashes? Select or Download Code

Replies are listed 'Best First'.
Re: Applying logical operators in either complex SQL xor hashes? by talexb (Chancellor) on May 10, 2025 at 16:40 UTC
Push as much complexity onto the database as possible. The reason is that the performance of a database has been optimized like crazy, so the more you can get the database to handle, the better. Alex / talexb / Toronto For a long time, I had a link in my .sig going to Groklaw. I heard that as of December 2024, this link is dead. Still, thanks to PJ for all your work, we owe you so much. RIP Groklaw -- 2003 to 2013.	[reply]
Re^2: Applying logical operators in either complex SQL xor hashes? by mldvx4 (Friar) on May 11, 2025 at 15:23 UTC
Thanks, talexb, Marshall, and LanX! I now have the script concatenating as large a query string as needed by repeating parts from template strings. Then it pulls everything in from the database through a single `prepare()` and `execute()` pair. One of the hard parts was knowing to push the query bind values into two lists to be passed to DBI's `execute()` and along the way another hard part was getting visible error messages even with the help of the `CGI::Carp` module. Two bindvalue lists are often needed because when the final SQL query sometimes starts with a WITH clause, that has to be prepended to the query which means in turn that the bind values have to be at the start. So I use a separate list for the the normal bind values. Both get `push`ed and sometimes several tables are used at the same time so the push has to take that into account by repeating the same value: `push(@bindvalues, ($value) x 3);` then the two lists of bind values are combined for the `execute()` statements. The template which the Perl script uses to assemble the query is big on its own. Anyway, I now see a way forward with rest of the Perl script.	[reply] [d/l] [select]
Re: Applying logical operators in either complex SQL xor hashes? by Marshall (Canon) on May 10, 2025 at 19:09 UTC
I would push as much logic as possible into the SQL. When writing the AND, OR, etc statements, be aware that although in Perl statements are evaluated left to right, there is no such guarantee in SQL. The Query Optimizer can change the order to whatever it figures will be the most efficient. This can lead to some surprises.	[reply]
Re: Applying logical operators in either complex SQL xor hashes? by LanX (Saint) on May 10, 2025 at 13:13 UTC
> When there is more than one query, there will be an operator XOR, AND, OR, or NOT applied between them. The question is not clear to me, are you talking about combining several sub-queries? Maybe you should show an example (SSCCE) where you combined two "queries"? My general advice is going for the better maintainable code with multiple short SQL-queries and only start optimizing when performance is bad.˛ So maybe better "hashes" in your case, (whose keys I suppose are fed as row-IDs into the IN-clause ?)ł Anecdotal: I once wrote a very elaborated SQL code for complex tree manipulations in MariaDB, which was very fast, very atomic, and very nightmarish to test and maintain. YMMV.š Cheers Rolf _{(addicted to the Perl Programming Language :) see Wikisyntax for the Monastery} š) Of course it's a matter of perspective. I once worked with a colleague who was uncomfortable with Perl and shifted most of the business logic into stored procedures. I'm sure he would object here. ˛) In that case SQL-Window-Functions might(???) be what you really need. ł) TIMTOWTDI, you might also combine all row-IDs into a temporary SQL-table instead of using a Perl hash.	[reply]