<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Sun, Mar 26, 2017 at 5:12 PM, Yevgeny Zaspitsky <span dir="ltr"><<a href="mailto:yzaspits@redhat.com" target="_blank">yzaspits@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div>Hi All,<br><br></div>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.<br>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. <br><br>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:<br><ul><li>no looking for a stored procedure name that is buried in the DAO class hierarchy<br></li><li>no looking for the SP definition</li></ul></div></div></div></div></blockquote><div><br></div><div>There are additional pros and cons for the suggestion to consider:<br><br></div><div>Pros:<br></div><div><ol><li>No need to run engine-setup after changing DB related code (in case of SQL inside Java). <br></li></ol></div><div>Cons:<br></div><ol><li>DAO files might become very long.</li><li>If you decide to return the business entity associated with the DAO as a returned object, you won't know as a caller which fields to expect to be populated, which lead to 3:</li><li>An inflation of business entities to represent partial populated business entity or inflation of mappers inflation (this will be required for SP as well).</li><li>SQL code inside of Java: <br></li><ol><li>Beside of the fact that a multi-line concatenated string that cannot be easily copied and run with psql, it means that we should compile the code in order to test the change (vs building with DEV_REBUILD=0 which only package the SQL file).</li><li>No syntax highlighting when performing code review. i.e. I don't think reviewing a patch such as <a href="https://gerrit.ovirt.org/#/c/66729/10/packaging/dbscripts/network_sp.sql">https://gerrit.ovirt.org/#/c/66729/10/packaging/dbscripts/network_sp.sql</a> would be more clear inside a java file.</li><li>The user permissions management is implemented on DB level. That means that SQL will be more complex (more concatenated java lines). </li></ol><li>Stored procedure are cached by project's code. See SimpleJdbcCallsHandler.<wbr>getCall(), while the NamedParameterJdbcTemplate are cached by spring's code which seems less optimal (sync all calls using synchronized vs using ConcurrentHashMap as in SP cache).</li><li>With the NamedParametersJdbcTemplate there is no use of the DbEngineDialect. What's the impact of it ? </li></ol><div>So besides 5 and 6, the rest is a matter of style. I'd like to hear more opinions from other members.<br><br></div><div>Regards,<br></div><div>Moti<br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div><div><div>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).<br>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.<br>IMHO the proposed approach would simplify adding new specific queries and by that would prevent a decent part of performance issues in the future.<br><br></div><div>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.<br></div><br>[1] - <a href="https://gerrit.ovirt.org/#/c/74456" target="_blank">https://gerrit.ovirt.org/#/c/7<wbr>4456</a><br>[2] - <a href="https://gerrit.ovirt.org/#/c/74458" target="_blank">https://gerrit.ovirt.org/#/c/7<wbr>4458</a><br><br></div>Regards,<br></div>Yevgeny<br></div>
<br>______________________________<wbr>_________________<br>
Devel mailing list<br>
<a href="mailto:Devel@ovirt.org" target="_blank">Devel@ovirt.org</a><br>
<a href="http://lists.ovirt.org/mailman/listinfo/devel" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman<wbr>/listinfo/devel</a><br></blockquote></div><br></div><div class="gmail_extra"><br clear="all"><br>-- <br><div class="gmail-m_1038891330517396707gmail_signature"><div dir="ltr"><div>Regards,<br></div>Moti<br></div></div>
</div></div>