HANA: xsodata: Huge performance gap between first and 2nd request execution
<p>If I expose a VIEW</p>

<pre><code>CREATE VIEW myView AS
SELECT ...
FROM ...
</code></pre>

<p>via xsodata</p>

<pre><code>service namespace "oData" {
    entity "mySchema"."myView" as "myView";
}
</code></pre>

<p>and GET /myView for the first time after VIEW creation the performance is very low:</p>

<p><a href="https://i.stack.imgur.com/NNmVo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NNmVo.png" alt="enter image description here"></a></p>

<p>However: After performing the same request again (and everytime after that) the performance is what I want it to be:</p>

<p><a href="https://i.stack.imgur.com/mSS5B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mSS5B.png" alt="enter image description here"></a></p>

<p>Questions:</p>

<ul>
<li><p>Why?</p></li>
<li><p>How to avoid the first long-running request?</p></li>
</ul>

<p>Already tried:</p>

<ul>
<li><p>Execution of the sql profiler-output (without statement preparation) in HANA Studios SQL console gives good performance always</p></li>
<li><p>Table hotloading (<code>LOAD myTable ALL;</code>) had no effect</p></li>
</ul>

<p><strong>Update</strong></p>

<p>We found out the "Why"-Part: xs-engine is running the query as a prepared statement even if there are no parameters in the request. On first execution (within the user's context) the query gets perpared, resulting in an entry in <code>M_SQL_PLAN_CACHE</code> (<code>SELECT * FROM M_SQL_PLAN_CACHE WHERE USER_NAME = 'myUser'</code>). Clearing the plan cache (<code>ALTER SYSTEM CLEAR SQL PLAN CACHE</code>) makes the oData request slow again, leading to the assumption that the performance gap lies in the re-preparation of the query.</p>

<p>We are now stuck with the 2nd question: How to avoid that? Our approach to mark certain plan cache entries for recompilation (<code>ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY 123</code>) just invalidated the entry and did not update it automatically...</p>