[ovirt-devel] Writing SQL queries in DAO code

[Martin Sivak]

I agree with Moti, but I think we might have an alternative.

Does our DAO layer support named queries (aka PreparedStatements
loaded from a properties file)? Those are generally plain SQL queries
with arguments that are compiled in advance and still allow direct
access to the JDBC internals without the heavy translation layer.

Martin

On Sun, Mar 26, 2017 at 4:12 PM, Yevgeny Zaspitsky <yzaspits at redhat.com> wrote:
> Hi All,
>
> Recently I had a task of performance improvement in one of our network
> related flows and had some hard time following our DAL code and one of the
> outcomes of the task was defining a couple of new quite simple, but neat
> queries.
> When I came to coding those new queries I remembered how hard was following
> the existing DAL code, I decided to make my own new methods clearer. So I
> created [1] and [2] patches.
>
> Everything is quite standard there beside the fact that they do not use any
> stored procedure, but use SQL directly, IMHO by that they save time that I
> spent in trying to follow what a DAO method does. Looking into the method
> code you get the understanding of what this method is all about:
>
> no looking for a stored procedure name that is buried in the DAO class
> hierarchy
> no looking for the SP definition
>
> So I'd like to propose moving towards this approach in general in all cases
> when nothing beyond a simple SQL is needed (no stored procedure programming
> language is needed).
> From my experience with the performance improvement task it looks like
> people avoid adding new queries for a specific need of a flow, instead they
> use the existing general ones (e.g. dao.getAllForX()) and do the actual join
> in the bll code.
> IMHO the proposed approach would simplify adding new specific queries and by
> that would prevent a decent part of performance issues in the future.
>
> I do not propose changing all existing SP's to inline queries in a once, but
> to allow adding new queries this way. Also we might consider converting
> relatively simple SP's to inline SQL statements later in a graduate way.
>
> [1] - https://gerrit.ovirt.org/#/c/74456
> [2] - https://gerrit.ovirt.org/#/c/74458
>
> Regards,
> Yevgeny
>
> _______________________________________________
> Devel mailing list
> Devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/devel